SQL Remote User's Guide
  Using SQL Remote with Replication Server

Setting up SQL Remote Open Server

This section describes how to set up a SQL Remote installation using the SQL Remote Open Server. The procedure depends on whether the SQL Remote stable queue is being kept in a separate Adaptive Server Enterprise database from the tables being replicated or in the same Adaptive Server Enterprise database.

For more information about stable queue location, see The stable queue.

Initial copies of the data 

The setup procedure assumes you are using the extraction utility to produce an initial copy of the data in each remote database. You must be sure not to use the Replication Server materialization feature for this purpose.

The procedure for setting up SQL Remote Open Server has two stages:

• Prepare a SQL Remote setup    This stage depends on whether you have an existing SQL Remote installation or not.

• Add the SQL Remote Open Server to the setup    This stage is the same regardless of previous installations.

To prepare your SQL Remote setup, if you have an existing SQL Remote installation

1. On a quiet primary database, use the Message Agent to scan any remaining transactions into the stable queue.

   A quiet database is one where neither the Message Agent nor the SQL Remote Open Server is running, and where no transactions are being replicated.

2. Follow the steps in the section Upgrading SQL Remote for Adaptive Server Enterprise to upgrade your SQL Remote software at the consolidated site.

3. Invalidate the Message Agent truncation point at the consolidated database using the following command:

   dbcc settrunc('ltm', 'ignore')

4. At the stable queue database, execute the stored procedure sp_queue_log_transfer_reset.

To prepare your SQL Remote setup, with no existing installation

1. Set up SQL Remote as described in Setting Up SQL Remote.

2. Set up your SQL Remote publications and subscriptions at this point. For information on this procedure, see SQL Remote Design for Adaptive Server Enterprise.

3. Extract the remote databases. For information on this procedure, see Using the extraction utility.

You are now ready to set up the SQL Remote Open Server.

To set up the SQL Remote Open Server

1. If the SQL Remote stable queue is in a separate database:

   • Set up the stable queue database as a replicate database in a Replication Server setup. This will create the tables and procedures needed by Replication Server, such as rs_lastcommit.

   • Drop the Replication Server connection to the stable queue database.

2. Add an entry to your interfaces file for the SQL Remote Open Server. The default name used on the SQL Remote Open Server command line is SSQueue.

3. Start the SQL Remote Open Server.

4. Create a Replication Server connection to the SQL Remote Open Server. The user ID and password for this connection must match the user ID and password specified on the SQL Remote Open Server command line for the stable queue connection (that is, the -cq option, or -c if -cq is not specified).

   Configure Replication Server now  You should configure Replication Server for this connection at this point. For a description, see Configuring Replication Server.

5. Define, activate, and validate Replication Server replication definitions and subscriptions for the SQL Remote tables sr_marker, sr_remoteuser, sr_subscription, and sr_passthrough. The script ssremote.rs is a sample script to perform this task. You will need to edit the server and database names in the script to match your names.

   If the SQL Remote system tables have any data in them, create the replication definitions so that no materialization happens.

   For information on creating replication definitions with no materialization, see the Replication Server Administration Guide. The section in Chapter 10, Managing Subscriptions entitled Bulk Materialization describes how to set up Replication Server for the case where data exists at a remote database.

6. Define, activate, and validate replication definitions and subscriptions for the tables in your database that need to be replicated by SQL Remote. These must be created without materialization.