User's Guide

Part III. Using SQL Anywhere

Chapter 27. Introduction to SQL Remote Replication

You now have a replication system in place. In this section, data is replicated from the consolidated database to the remote database, and from the remote to the consolidated database.

Send data from the consolidated database

First, connect to the consolidated database hq from the ISQL utility as user ID DBA, with password SQL.

Enter and commit a row into the news database:

     INSERT INTO news (text)
     VALUES ('Welcome to our news database.') ;
     COMMIT ;

To send the new row to the remote database, you must run the Message Agent at the consolidated database. The DBREMOTE program is the Message Agent. The following statement at the command line runs DBREMOTE:

dbremote -c "dbn=hq;uid=dba;pwd=sql"

In Windows 3.x, you should run the following command either from File>.Run in Program Manager or from an icon:

dbremotw -c "dbn=hq;uid=dba;pwd=sql"

These command lines assume that the hq database is currently running on the default server. For information on DBREMOTE command line switches, see "The SQL Remote Message Agent".

If you are running under MAPI, you will be prompted for a MAPI user ID and password in order to connect to the mailing system. You should use the user ID and password for the consolidated database user.

Receive data at the remote database

To receive the insert statement at the remote database, you must run the Message Agent, DBREMOTE, at the remote database. The following statement at the command line runs DBREMOTE on the field database running on a server named field.

dbremote -c "dbn=field;uid=dba;pwd=sql"

If you are running under MAPI, you will be prompted for a MAPI user ID and password in order to connect to the mailing system. You should use the user ID and password for the remote database user.

The Message Agent window displays status information while running. This information can be output to a log file for record keeping in a real setup. You will see that the Message Agent first receives a message from hq, and then sends a message. This return message contains confirmation of successful receipt of the replication update; such confirmations are part of the SQL Remote message tracking system that ensures message delivery even in the event of message system errors.

If you now connect to the remote field database using ISQL, and inspect the news table, you will see that the row entered at the consolidated database has been replicated.

Replicate from the remote database to the consolidated database

You should now try entering data at the remote database and sending it to the consolidated database:

1. INSERT a row at the remote database. For example

        INSERT INTO news (text)
        VALUES ('Thanks very much.')
   

2. COMMIT the row.
3. With the FIELD.DB database running, run DBREMOTE to send the message to the consolidated database.

   dbremote -c "dbn=field;uid=dba;pwd=sql"

   (For Windows 3.x, run the DBREMOTW equivalent.)

4. With the HQ.DB database running, run DBREMOTE to receive the message at the consolidated database:

        dbremote -c "dbn=hq;uid=dba;pwd=sql"
   

5. Connect to the consolidated database and display news:

Show the rows of the news table.

• Type the following statement:

       SELECT *FROM news
  

  id	pub	text
  1	hq	Welcome to our news database.
  2	field	Thanks very much.

The pub column identifies the publisher of the database at which the row was entered. The first row was entered at the consolidated database, and has an entry hq, while the second row was entered at the remote database and so has a value field. With this column as part of the primary key, there will be no conflicts arising from different users at different databases inserting the same primary key value.