In this lesson you send data from the remote database (field) to the consolidated database (hq).
Replicate data from the remote database (field) to the consolidated database (hq) (Interactive SQL)
1. If you are not currently connected to the remote database (field), run the following command: dbisql -c "UID=DBA;PWD=sql;SERVER=server_hq;DBF=c:\tutorial\field.db" 2. Execute the following statement to insert a row at the remote database (field):
INSERT INTO Customers ( cust_key, name, rep_key ) VALUES ( 'cust5', 'North Land Trading', 'rep1' ); COMMIT;
3. From the c:\tutorial directory, run the dbremote utility against the remote database (field):
dbremote -c "UID=DBA;PWD=sql;SERVER=server_field;DBF=c:\tutorial\field.db" In the c:\tutorial\hq directory, the file field.1 appears.
Receive data at the consolidated database (hq)
1. If you are not currently connected to the consolidated database (hq), run the following command: dbisql -c "UID=DBA;PWD=sql;SERVER=server_hq;DBF=c:\tutorial\hq.db 2. At the consolidated database (hq) run the Message Agent from the c:\tutorial directory:
dbremote -c "UID=DBA;PWD=sql;SERVER=server_hq;DBF=c:\tutorial\hq.db" 3. When the Message Agent window displays Execution Completed, click Shutdown. 4. Browse to c:\tutorial\field.
The hq.1 file has been replaced by a file named hq.2. The hq.2 file contains the receipt confirmation. 5. Execute the following statement to view the data in the Customers table in the consolidated database
(hq):
SELECT * FROM Customers; This query returns the following results:
cust_key name rep_key
cust1 Ocean Sports rep1
cust2 Sports Plus rep2
cust3 Land Sports rep1
cust4 Air Plus rep2
using the HTTP message system
Use the lessons in this tutorial to learn how to set up a SQL Remote replication system that uses both a SQL Anywhere consolidated database and a remote database. The consolidated database uses the FILE message system to replicate changes, while the remote database uses the HTTP message system to replicate changes.
In this tutorial you:
● Create a consolidated SQL Anywhere database and a remote SQL Anywhere database that contains all the data in the consolidated database.
● Create a directory structure to store the messages generated by SQL Remote. The consolidated database accesses the files using the FILE message system, while the remote database uses the HTTP message system.
● Create a message server SQL Anywhere database to act as a web server to receive messages from the remote database using the HTTP protocol.
● Replicate data between the consolidated and remote databases.
Lesson 1: Creating the consolidated database
In this lesson you create the directories needed to store the databases and their transactions logs, as well as the directory structure for the messages. You also define the schema of the consolidated database,
including creation of the remote user and the publication and subscription needed to replicate data. When SQL Remote runs against the consolidated database, it uses the FILE message system to send and receive messages, but the remote database uses the HTTP message system.
Create the consolidated database and directories for the tutorial
1. Create the following directories to hold the consolidated database, the remote database, and the message server database:
● c:\tutorial
● c:\tutorial\cons
● c:\tutorial\rem
● c:\tutorial\msgsrv
2. Create the following directories to hold the message files generated by the consolidated database and the remote database:
● c:\tutorial\messages
● c:\tutorial\messages\cons
3. From the c:\tutorial\cons directory, run the following command to create the consolidated database (cons):
dbinit cons.db
4. Using Interactive SQL, connect to the consolidated database (cons) as a user with DBA authority, and ensure that you leave the database running when you disconnect by specifying AutoStop=NO for the AutoStop connection parameter:
dbisql -c "UID=DBA;PWD=sql;SERVER=cons;DBF=c:\tutorial\cons \cons.db;autostop=no"
5. To set the global database ID for the consolidated database (cons), execute the following statement (the global database ID is needed so that distinct primary keys are chosen for all databases when using the GLOBAL AUTOINCREMENT default):
SET OPTION public.global_database_id=0;
6. The schema for the database in this tutorial consists of a single table that replicates, and all the columns and rows from the table replicate to every remote user. Execute the following statements on the consolidated database (cons) to create the single table in the database:
CREATE TABLE employees (
employee_id BIGINT NOT NULL DEFAULT GLOBAL AUTOINCREMENT(1000000) PRIMARY KEY,
first_name VARCHAR(128) NOT NULL, last_name VARCHAR(128) NOT NULL,
hire_date TIMESTAMP NOT NULL DEFAULT TIMESTAMP );
7. Execute the following statements on the consolidated database (cons) to add sample data to the employees table:
INSERT INTO employees (first_name, last_name) VALUES ('Kelly', 'Meloy'); INSERT INTO employees (first_name, last_name) VALUES ('Melisa', 'Boysen'); COMMIT;
8. Execute the following statement on the consolidated database (cons) to confirm that the table was created and populated with data:
SELECT * FROM employees;
The query returns the following data from the employees table, although the hire_date column contains the time you inserted the row, and not the values you see in the following table:
employee_id first_name last_name hire_date
1 Kelly Meloy 2011-03-25 08:27:56.310
2 Melisa Boysen 2011-03-25 08:27:56.310
9. In this tutorial, the publisher and remote users are not assigned passwords, so while the users exist in the database, you cannot connect to the database with these users. Execute the following statements to create the user cons that has CONNECT and PUBLISH permissions:
GRANT CONNECT TO cons; GRANT PUBLISH TO cons;
10. For performance reasons, the HTTP message system can only be used at the remote database, and not at the consolidated. The following statements configure the use of the FILE-based message system at the consolidated database:
CREATE REMOTE MESSAGE TYPE FILE ADDRESS 'cons';
SET REMOTE FILE OPTION public.directory='c:\\tutorial\\messages'; SET REMOTE FILE OPTION public.debug='yes';
11. Execute the following statements to create the remote user rem without a password, and then grant REMOTE permissions while defining the user's address in the FILE message system:
GRANT CONNECT TO rem;
GRANT REMOTE TO rem TYPE FILE ADDRESS 'rem';
12. A publication describes the set of data to be replicated. Create a publication named pub_employees that replicates all rows of the employees table. You subscribe a user to a publication by creating a subscription.
CREATE PUBLICATION pub_employees ( TABLE employees ); CREATE SUBSCRIPTION TO pub_employees FOR rem;
13. Disconnect from Interactive SQL.