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

 

Lesson 2: Creating the message server

While it is possible to use the consolidated database as your message server, in this tutorial, you use a separate database server to host the message server. This helps distribute the amount of work performed to process messages between the two database servers, and also adds a level of security because you haven't opened up HTTP access to your consolidated database.

Prérequis

You must have the SYS_REPLICATION_ADMIN_ROLE system role.

 Task
  1. From the c:\tutorial\msgsrv directory, run the following command to create the message server database (msgsrv):

    dbinit -dba DBA,sql msgsrv.db
  2. Start the message server:

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

    -xs http(8033) is required on the command line because this is the database server that accepts HTTP requests from the remote database and accesses the messages files that exist in the c:\tutorial\messages directory. While no web services have been defined at the time the database server starts, they are created in this lesson. As well, only the personal database server has been started, so only SQL Remote processes on this computer can 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.

  3. When you create a separate message server, you need to copy much of the schema of the consolidated database into the message server, particularly information about the remote users that are defined and their addresses. While you can do this manually, the easiest way to accomplish this task is to use the dbunload utility to create a new database with the same schema as the consolidated database:

    dbunload -n -xx -ac "SERVER=msgsrv;DBN=msgsrv;UID=DBA;PWD=sql" -c "SERVER=cons;DBN=cons;UID=DBA;PWD=sql"

    The options used in the dbunload command do the following:

    • -n   Indicates that only the schema is to be unloaded, and none of the data in the consolidated database is added to the message server.

    • -xx   Performs an external unload and reload, which is needed when both databases involved are already running.

    • -ac "SERVER=msgsrv;DBN=msgsrv;UID=DBA;PWD=sql"   Defines the destination connection for the unload, which for this lesson is the message server.

    • -c "SERVER=cons;DBN=cons;UID=DBA;PWD=sql"   Specifies the source connection for the unload, which for this lesson is the consolidated database.

  4. Using Interactive SQL, connect to the message server database (msgsrv) as a user with the SYS_REPLICATION_ADMIN_ROLE system role:

    dbisql -c "SERVER=msgsrv;DBN=msgsrv;UID=DBA;PWD=sql"

    In lesson 1, you did not create passwords for the publisher (cons) and remote user (rem), so neither of those users is able to connect to the consolidated database. A password is required for these users in the message server, since the HTTP requests that come from remote users use the publisher of the remote database and the password that you supply to authenticate with the message server. Execute the following statements on the message server database (msgsrv) to define passwords for the publisher and remote user:

    GRANT CONNECT TO cons IDENTIFIED BY cons;
    GRANT CONNECT TO rem IDENTIFIED BY rem;
  5. When a database is first initialized, none of the web services needed to accept HTTP requests from remote users is defined, and neither are definitions to allow the database server to access the directory where the message files are stored. The creation of these objects is automated with the use of the sr_add_message_server stored procedure, which takes an optional parameter to specify who owns all the objects. Execute the following statements for the message server database (msgsrv) to define all the objects needed for the message server and specify that all the objects are owned by the cons user:

    CREATE ROLE FOR USER cons;
    SET REMOTE http OPTION cons.root_directory='c:\\tutorial\\messages';
    CALL sr_add_message_server( 'cons' );
    COMMIT;

    For more information, see sr_add_message_server system procedure.

  6. Disconnect from Interactive SQL.

Résultat

A separate database server now hosts the message server.