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

SQL Anywhere 12.0.1 » 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.

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

    dbinit msgsrv.db
  2. Start the message server:

    dbeng12 -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 dbeng12/dbsrv12 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 DBA authority:

    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:

  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:

    GRANT GROUP TO cons;
    SET REMOTE http OPTION cons.root_directory='c:\\tutorial\\messages';
    CALL sr_add_message_server( 'cons' );

    For more information, see sr_add_message_server system procedure.

  6. Disconnect from Interactive SQL.

  7. Proceed to Lesson 3: Creating the remote database.