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 for users without Windows 95 or Windows NT 3.51 or later, who cannot run Sybase Central. It may also be useful to users of Sybase Central who want to know what Sybase Central is doing behind the scenes.

This tutorial describes the SQL statements for managing SQL Remote, which can be run from ISQL. It also describes how to run the DBXTRACT command-line utility to extract remote databases from a consolidated database.

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 user who entered them. The table is replicated in a two-level 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. Create a MESSAGE TYPE in the database.
3. Grant PUBLISH permissions to identify the source of outgoing messages.
4. Grant REMOTE permissions for each remote database in the setup.
5. Create publications on the consolidated database.
6. Create subscriptions for remote users to each required publication.
7. Ensure that each remote user has an initial copy of the data on their database by synchronizing the data.
8. Install 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 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. Create these databases using the following statements at a command line:

   dbinit c:\tutorial\hq.db

   dbinit c:\tutorial\field.db

3. The database initialization tool for Windows 3.x is DBINITW rather than DBINIT. Under Windows 3.x, you should execute the commands from the Program Manager File>.Run menu or you could make an icon for each command.
4. 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 command line:

   mkdir c:\tutorial\hq

   mkdir c:\tutorial\field

5. If you wish to use a MAPI e-mail system as the message system you need to have two e-mail user IDs and addresses available. In this tutorial these will be user IDs hq_mapi and field_mapi, with MAPI addresses hq_address and field_address.
6. Connect to HQ.DB from ISQL as user ID DBA, and create a table in the consolidated database to replicate. To do this:
7. Type CONNECT in the ISQL Command window, and click Execute.
8. Enter the user ID DBA and the password SQL, and click More.
9. If the hq database is already running, type hq in the Database Name box. If it is not running, enter the Database File C:\TUTORIAL\HQ.DB. Then click OK to connect.
10. Enter the following CREATE TABLE statement:

         CREATE TABLE news (
             id INT DEFAULT AUTOINCREMENT,
             pub CHAR(30) DEFAULT CURRENT PUBLISHER,
             text CHAR(255),
             PRIMARY KEY ( id, pub )
         )
    

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.