User's Guide

Part III. Using SQL Anywhere

Chapter 27. Introduction to SQL Remote Replication

The following sections are a tutorial describing how to set up a simple SQL Remote replication system using Sybase Central.

You do not need to enter SQL statements if you are using Sybase Central to administer SQL Remote. A tutorial for those who do not have access to Sybase Central is presented in "Tutorial: setting up SQL Remote using ISQL and DBXTRACT", and contains the SQL statements executed behind the scenes by Sybase Central.

In this tutorial you will act as the DBA of the consolidated database, and set up a simple replication system. The simple example is a primitive model for an office "news" system, with a single table containing messages together with the database from which they were sent. The table is replicated in a setup with one consolidated database and one remote database. You can install this example on one computer.

The tutorial takes you through the following steps:

1. Ensure that the consolidated database site and all remote sites have properly configured message systems.
2. Add the message type you need to the database.
3. Add a publisher user ID to the database to identify the source of outgoing messages.
4. Add a remote user to the database for each remote database in the setup.
5. Create a publication on the consolidated database.
6. Create subscriptions for remote users to the publication.
7. Ensure that each remote user has an initial copy of the data on their database by synchronizing the data.
8. Configure and run the Message Agent at both consolidated and remote sites.
9. Start replicating data.

The following sections describe how to carry out each of these steps, apart from the message system installation, which is independent of SQL Anywhere.

Preparing for the Sybase Central replication tutorial

The following steps prepare you for the replication tutorial:

1. Create a directory to hold the files you make during this tutorial; for example C:\TUTORIAL.
2. The tutorial uses two databases: a consolidated database named HQ.DB and a remote database named FIELD.DB. At this point, you should create the hq database with the Create Database utility in Sybase Central:
   • Start Sybase Central. You will be creating a new database so you do not have to connect to any particular existing database.
   • Click Database Utilities in the left panel.
   • Double-click Create Database in the right panel. The Create Database wizard is displayed.
   • Create a database with filename C:\TUTORIAL\HQ.DB.
   • You can use the default settings for this database. Make sure you elect to maintain a transaction log. Replication cannot take place without a transaction log.
3. If you wish to set up the example system using a file-sharing link you must create a subdirectory for each of the two user IDs in the replication system. Create these subdirectories using the following statements at a system command line:

   mkdir c:\tutorial\hq

   mkdir c:\tutorial\field

4. If you wish to use a MAPI e-mail system as the message system you must have two e-mail user IDs and addresses available; one for the consolidated database and one for the remote database. In this tutorial these will be MAPI user IDs hq_mapi and field_mapi, with MAPI addresses hq_address and field_address.
5. Add a table to the consolidated database:
   • Connect to the hq database from Sybase Central, as user ID DBA:
6. Click Connect from the Tools Menu.
7. Enter the user ID DBA and the password SQL, then click More>>.
8. If the hq database is not running, enter the file name with path in the Database File field. If it is running, enter the name hq in the Database Name field.
9. Click OK to connect.
   • Click the Tables folder of the hq database.
   • Double-click Add Table, enter the name news, and click OK to create the empty table.
   • Double-click the news table icon, then its Columns folder, and then double-click Add Column.
   • Enter the column name id on the General tab, and on the Data type tab enter the column type INTEGER. Click Edit, and choose a pre-defined default of AUTOINCREMENT. Uncheck the Column Allows NULL setting so that NULLs are not allowed in this column. Then click OK to create the column.
   • Double-click Add Column, and enter column name pub, of type CHAR( 30 ) and with a pre-defined default of CURRENT PUBLISHER, not allowing NULL. Click OK to create the column.
   • Double-click Add Column again, and enter column name text, of type CHAR( 255 ), allowing NULL and with no default setting. Click OK to create the column.
   • Create a two-column primary key. Select the id and pub columns (in that order), click the right mouse button, and select Add to Primary Key from the popup menu.

The two-column primary key, containing an autoincrementing integer and a user ID column, is a useful form of primary key for many tables involved in replication; inclusion of the CURRENT PUBLISHER column default prevents users at different databases from inserting rows with the same primary key value, which would cause conflicts. The CURRENT PUBLISHER default identifies each row by the database from which it originates. For more information, see "Designing publications".

You are now ready for the rest of the tutorial.