Start replicating data

Data Replication with SQL Remote
PART 1. Introduction to SQL Remote
CHAPTER 5. A Tutorial for Adaptive Server Enterprise 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

In this section we enter data into the SalesRep and Customer tables at the consolidated (Adaptive Server Enterprise) database, and replicate this data to the Adaptive Server Anywhere database.

To enter data at the Adaptive Server Enterprise database:

Connect to the Adaptive Server Enterprise server from isql:
```
isql -S server-name -U sa -P sysadmin
```

Ensure you are using the hq database, and enter a series of rows:

use hq
go
insert into SalesRep (rep_key, name)
values ('rep1', 'Field User')
go
insert into SalesRep (rep_key, name)
values ('rep2', 'Another User')
go
insert into Customer (cust_key, name, rep_key)
values ('cust1', 'Ocean Sports', 'rep1')
go
insert into Customer (cust_key, name, rep_key)
values ('cust2', 'Sports Plus', 'rep2')
go
commit
go

Ocean Sports is assigned to Field User, and Sports Plus is assigned to Another User. You must commit the changes, as SQL Remote replicates only committed changes.

Having entered the data at the consolidated database, you now need to send the relevant rows to the remote Adaptive Server Anywhere 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 ssremote program is the Message Agent for Adaptive Server Enterprise.

To replicate the data from Adaptive Server Enterprise:

Enter the following statement (on a single line) at the command line to run the Message Agent against the consolidated database:
```
ssremote -c "eng=server-name;dbn=hq;uid=sa;pwd=sysadmin"
```
Click Shutdown on the Message Agent window to stop the Message Agent when the messages have been sent.

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 the data at Adaptive Server Anywhere:

With the database server running, receive the data using the Message Agent for Adaptive Server Anywhere:
```
dbremote -c "eng=field;dbn=field;uid=DBA;pwd=SQL"
```
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 status information while running. This information can be output to a log file for record keeping in a production setup.

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 Customer table by typing the following statement:
```
SELECT * FROM Customer
```
You will 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 "eng=field;dbn=field;uid=DBA;pwd=SQL"
```
(For Windows 3.x, run the dbremotw equivalent.)
Run ssremote to receive the message at the consolidated database:
```
ssremote -c "eng=server-name;dbn=hq;uid=sa;pwd=sysadmin"
```
Connect to the consolidated database and display the Customer table. This now has three rows:
```
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.