Start replicating data

Data Replication with SQL Remote
PART 1. Introduction to SQL Remote
CHAPTER 4. A Tutorial for Adaptive Server Anywhere Users

Start replicating data

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.

Enter data at the consolidated database

First, enter some data into the consolidated database.

To enter data at the consolidated database:

Connect to the consolidated database hq from the Interactive SQL utility as user ID DBA, with password SQL.

Enter and commit two rows into the SalesRep table:

INSERT INTO SalesRep (rep_key, name)
VALUES ('rep1', 'Field User') ;
INSERT INTO SalesRep (rep_key, name)
VALUES ('rep2', 'Another User') ;
COMMIT ;

Enter and commit two rows into the Customer table:

INSERT INTO Customer (cust_key, name, rep_key)
VALUES ('cust1', 'Ocean Sports', 'rep1' ) ;
INSERT INTO Customer (cust_key, name, rep_key)
VALUES ('cust2', 'Sports Plus', 'rep2' ) ;
COMMIT ;

Confirm that the data is entered by viewing the data in the tables:
```
SELECT * 
FROM SalesRep;

SELECT *
FROM Customer;
```

The next step is to send the relevant rows to the remote database.

Send data from the consolidated database

To send the rows to the remote database, you must run the Message Agent at the consolidated database. The dbremote program is the Message Agent for Adaptive Server Anywhere.

To send the data to the remote database:

From a command prompt, change to your tutorial directory. For example,
```
> c:
> cd c:\tutorial
```
Enter the following statement at the command line to run the Message Agent against the consolidated database:
```
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"
```
with the tutorial directory as the working directory. These command lines assume that the hq database is currently running on the default server.

For more information on dbremote command line switches, see The Message Agent.
Click Shutdown on the Message Agent window to stop the Message Agent when the messages have been sent. The Message Agent window displays the message Execution completed when all processing is complete.

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.

To receive data at the remote database:

From a command prompt, change to your tutorial directory. For example,
```
> c:
> cd c:\tutorial
```
Enter the following statement at the command line to run the Message Agent against the field database:
```
dbremote -c "dbn=field;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=field;uid=dba;pwd=sql"
```
These command lines assume that the field database is currently running on the default server.

For more information on dbremote command line switches, see The Message Agent.
Click Shutdown on the Message Agent window to stop the Message Agent when the messages have been processed. The Message Agent window displays the message Execution completed when all processing is complete.

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.

Verify that the data has arrived

You should now connect to the remote field database using Interactive SQL, and inspect the SalesRep and Customer tables, to see which rows have been received.

To verify that the data has arrived:

Connect to the field database using Interactive SQL.
Inspect the SalesRep table by typing the following statement:
```
SELECT * FROM SalesRep
```
You will see that the SalesRep table contains both rows entered at the consolidated database. This is because the SalesRepData publication included all the data from the SalesRep table.
Inspect the SalesRep table by typing the following statement:
```
SELECT * FROM Customer
```
You will also see that the Customer table contains only row (Ocean Sports) entered at the consolidated database. This is because the SalesRepData publication included only those customers assigned to the subscribed Sales Rep.

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. Only the outlines are presented here.

To replicate data from the remote database to the consolidated database:

Connect to the field database from Interactive SQL.

INSERT a row at the remote database. For example

INSERT INTO Customer (cust_key, name, rep_key) 
VALUES ('cust3', 'North Land Trading', 'rep1')

COMMIT the row.:
```
COMMIT;
```
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.)
With the hq.db database running, run dbremote to receive the message at the consolidated database:
```
dbremote -c "dbn=hq;uid=dba;pwd=sql"
```
Connect to the consolidated database and display the Customer table:
```
SELECT *
FROM Customer
```
cust_key

name

rep_key

cust1

Ocean Sports

rep1

cust2

Sports Plus

rep2

cust3

North Land Trading

rep1

cust_key	name	rep_key
cust1	Ocean Sports	rep1
cust2	Sports Plus	rep2
cust3	North Land Trading	rep1

In this simple example, there is no protection against duplicate entries of primary key values. SQL Remote does provide for such protection. For information, see the chapters on SQL Remote Design.