Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Remote » Tutorial: Setting up a replication system using the HTTP message system with the consolidated database as the message server

 

Lesson 1: Creating the consolidated database

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.

Prérequis

You must have the SYS_REPLICATION_ADMIN_ROLE system role.

Contexte et remarques

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.

 Task
  1. Create the following directories to hold the consolidated database and the remote database:

    • c:\tutorial
    • c:\tutorial\cons
    • c:\tutorial\rem
  2. Create the following directories to hold the message files generated by the consolidated database, the remote database, and the message server database:

    • c:\tutorial\messages
    • c:\tutorial\messages\cons
    • c:\tutorial\messages\rem
  3. From the c:\tutorial\cons directory, run the following command to create the consolidated database (cons):

    dbinit -dba DBA,sql cons.db
  4. Start the consolidated database:

    dbeng16 -n cons c:\tutorial\cons\cons.db -xs http(port=8033)

    -xs http(8033) is required on the command line because this is the database server that will be accepting HTTP requests from the remote database and accessing the message files that exist in the c:\tutorial\messages directory. While no web services are defined at the time you start the database server, they are created in the next lesson. In this lesson, you only start the personal database server, so only SQL Remote processes on this computer are able to communicate with the message server using HTTP. In a production environment, you would typically use the network server so that SQL Remote processes on other computers would also have access to the web services. For more information about using -xs, see -xs database server option.

  5. Using Interactive SQL, connect to the consolidated database (cons) as a user with the SYS_REPLICATION_ADMIN_ROLE system role:

    dbisql -c "SERVER=cons;DBN=cons;UID=DBA;PWD=sql"
  6. 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;
  7. The schema for the database in this tutorial consists of a single table and all the columns and rows from the table replicate to every remote user. Execute the following statements for 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
    );
  8. 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;
  9. 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
  10. In this tutorial, the publisher and remote users are assigned passwords because the consolidated database will be acting as the message server for the HTTP message system. Execute the following statements to create the user cons that has CONNECT and PUBLISH privileges:

    GRANT CONNECT TO cons;
    GRANT PUBLISH TO cons;
  11. 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'; 
  12. Execute the following statements to create the remote user rem without a password, and then grant REMOTE privilege, while defining the user's address in the FILE message system:

    GRANT CONNECT TO rem IDENTIFIED BY rem;
    GRANT REMOTE TO rem TYPE FILE ADDRESS 'rem';
  13. 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;
  14. Disconnect from Interactive SQL.

Résultat

The directories needed to store the databases and their transaction logs are created, as well as the directory structure for the messages. The schema of the consolidated database is defined, including creation of the remote user and the publication and subscription needed to replicate data.