Start replicating data

First Guide to SQL Anywhere Studio
PART 2. Getting Results with SQL Anywhere Studio
CHAPTER 11. Replicating Data with SQL Remote

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 a row at the consolidated database.

To enter data at the consolidated database:

Connect to the sample database from Interactive SQL as user ID dba, with password sql.

Enter and commit a row into the department table:

INSERT INTO department (dept_id, dept_name)
VALUES (202, 'Eastern Sales') ;
COMMIT ;

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

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 \tutorial
```
Enter the following statement at the command line to run the Message Agent against the consolidated database:
```
dbremote -c "dbn=asademo;uid=dba;pwd=sql"
```
Use the absolute path (above) to link to the database file or use the path which is relative to your server. For example, if your server is in the C:\tutorial\dba directory, you can use the statement:
```
dbremote -c "dbn=..\asademo.db;uid=dba;pwd=sql"
```
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 \tutorial
```
Enter the following statement at the command line to run the Message Agent against the field database:
```
dbremote -c "dbf=C:\tutorial\field.db;uid=field;pwd=field"
```
Use the absolute path (above) to link to the database file or use the path which is relative to your server. For example, if your server is in the C:\tutorial\dba directory, you can use the statement:
```
dbremote -c "dbf=..\field.db;uid=field;pwd=field"
```
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.

Notes

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 asademo, 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.

Depending on the current status of the database, there are three different connectivity parameters you can use to connect to a database:

dbf Connect to the database using the database file. This parameter requires you to specify the database file itself. If no server is currently running, a server will be started and the database will be loaded onto it. If a server is already running, the database will be loaded onto the default server.
dbn Connect to the database using the database name. This parameter requires you to specify the name of the database. You can only use this parameter when the database is already running.
dbs Connect to the database using the database source. A data source is a collection of parameters stored in the system registry or in a set of files. The source is referenced simply by its name.

For more information about data sources, see DataSourceName connection parameter.

Verify that the data has arrived

Connect to the field database using Interactive SQL, and inspect the department table, to see that the row has been received.

To verify that the data has arrived:

Connect to the field database using Interactive SQL.
Inspect the department table by typing the following statement:
```
SELECT * 
FROM dba.department
```
You will see that the department table contains the row entered at the consolidated database.

Replicate from the remote database to the consolidated database

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 dba.Department (dept_id, dept_name) 
VALUES (203, 'Western Sales')

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