Data Replication with SQL Remote
   PART 1. Introduction to SQL Remote
     CHAPTER 5. A Tutorial for Adaptive Server Enterprise Users       

A tutorial using isql and ssxtract

The following sections are a tutorial describing how to set up a simple SQL Remote replication system. This version of the tutorial does not use Sybase Central.

This tutorial describes the stored procedures used to configure and manage SQL Remote. It also describes how to run the ssxtract command-line utility to extract remote databases from a consolidated database and the Message Agents to send information between the databases in the replication system.

In this tutorial you act as the administrator of the consolidated database, and set up a simple replication system using the file-sharing message link. The simple example is a primitive model for a sales-force automation system, with two tables. One contains a list of sales representatives, and another a list of customers. The tables are replicated in a setup with one consolidated database and one remote database. You can install this example on one computer.

  First steps

Create a login name and password 

To work through the tutorial, you must have system administrator privileges on an Adaptive Server Enterprise server. The tutorial assumes that your login name is the two-letter word sa and that this login name has a password of sysadmin.

The tutorial uses the Adaptive Server Enterprise isql utility. With the login name and password as given above, you can connect to your Adaptive Server Enterprise server using the following command line:

isql -S server-name -U sa -P sysadmin

where server-name is the name of the Adaptive Server Enterprise to which you connect.

Ensure that you have an appropriate login ID and can connect to your server before starting this tutorial.

Create a database 

Create a database named hq on your Adaptive Server Enterprise server with sufficient space to hold the tables and data required by the tutorial database. A space of 4 Mb is sufficient.

  To create a database:

1. Using isql, connect to the server as a user with system administrator privileges:

   isql -S server-name -U sa -P sysadmin

2. Use the master database:

   use master
   go

3. Create a database named hq. In this example, we use a 5 Mb database with a 5 Mb log, on two different devices:

   create database hq
   on database_device = 5
   log on log_device = 5
   go

     For more information on how to create databases and assign space to them, see your Adaptive Server Enterprise documentation.

Install SQL Remote 

You need to install SQL Remote into the hq database.

  To install SQL Remote into the hq database:

1. If the system administrator login name you are using does not have the hq database as the default database, make a backup copy of the ssremote.sql script from your installation directory, and add the following two lines to the beginning of the script:

   use hq
   go

2. Change to the tutorial directory. Then, using isql, connect to the server using the hq database, and run the ssremote.sql script from your SQL Remote installation directory. The following command should be entered all on one line:

   isql -S server-name -U sa -P sysadmin -i ssremote.sql

3. If the system administrator login name you are using does not have the hq database as the default database, make a backup copy of the stableq.sql script from your installation directory, and add the following two lines to the beginning of the script:

   use hq
   go

4. Using isql, connect to the server using the hq database, and run the stableq.sql script from your SQL Remote installation directory. The following command should be entered all on one line:

   isql -S server-name -U sa -P sysadmin -i stableq.sql

Create directories for messages 

Create a directory to hold the files from this tutorial. For example:

mkdir c:\tutorial

You should create a directory for each of the two users of the replication system under your parent directory for this tutorial:

mkdir c:\tutorial\hq
mkdir c:\tutorial\field

The next step is to add a pair of tables to the consolidated database.

  To add tables to the consolidated database:

1. Connect to the hq database from isql, as a system administrator.

2. Use the hq database:

   use hq
   go

3. Create the SalesRep table with the following statement:

   create table SalesRep (
      rep_key char(12) not null,
      name char(40) not null,
      primary key (rep_key) )
   go

4. Create the Customer table with the following statement:

   create table Customer (
      cust_key char(12) not null,
      name char(40) not null,
      rep_key char(12) not null,
      primary key (cust_key) )
   go

5. Alter the Customer table to add a foreign key to the SalesRep table:

   alter table Customer
   add foreign key 
   ( rep_key ) references SalesRep
   go

You are now ready for the rest of the tutorial.