Setting up the example

This section outlines the tasks for setting up the replication system.

If you already have Adaptive Servers or Replication Servers installed, you can use them for the example. Substitute the names of your servers throughout the instructions.

You must choose the machines where each server will execute. You must also locate disk space for Adaptive Server database devices and Replication Server partitions.

Create a route from the primary Replication Server to the replicate Replication Server.

1. Log in to the primary Replication Server as “sa”:

   isql -Usa -STOKYO_RS
   

2. Enter the create route command:

   create route to SYDNEY_RS 
      set username SYDNEY_RS_rsi 
      set password SYDNEY_RS_rsi_ps 
   go
   

   Find the user name and password for the create route command in the “Remote site connections” section of the Replication Server Installation Worksheet for the replicate Replication Server.

3. Create a login account in the primary Adaptive Server for the user who will create the replication definitions and subscriptions. In this example, the login name is “repsys.” Add the user to the pubs2 databases and grant the user select permissions for the tables to replicate.

   isql -Usa -STOKYO_DS
         sp_addlogin repsys, repsys_ps
         go
   
         use pubs2
         go
   
         sp_adduser repsys
         go
   
         grant select on authors to repsys
         grant select on titles to repsys
         go
         quit
   

4. Add the “repsys” user to the two Replication Servers and grant create object permission to the user. The “repsys” user must have the same login name and password in the two Replication Servers and the primary Adaptive Server:

   isql -Usa -STOKYO_RS
         create user repsys
         set password repsys_ps
         go
   
         grant create object to repsys
         go
         quit
   
   isql -Usa -SSYDNEY_RS
         create user repsys
         set password repsys_ps
         go
   
         grant create object to repsys
         go
         quit
   

5. Create replication definitions for the authors and titles tables:

   isql -Urepsys -Prepsys_ps -STOKYO_RS
         create replication definition authors
         with primary at TOKYO_DS.pubs2
         with all tables named 'authors'  
   			(
   				au_id varchar(11),
   				au_lname varchar(40),
   				au_fname varchar(20),
   				phone char(12),
   				address varchar(40),
   				city varchar(20),
   				state char(2),
   				country varchar(12),
   				postalcode char(10)
   			)
         primary key (au_id)
         searchable columns (state, postalcode)
         go
   
         create replication definition titles
         with primary at TOKYO_DS.pubs2
         with all tables named 'titles'
         (
            title_id varchar(6),
            title varchar(80),
            type char(12),
            pub_id char(4),
            price money,
            advance money,
            total_sales int,
            notes varchar(200),
            pubdate datetime,
            contract bit
         )
         primary key (title_id)
         searchable columns (type, pub_id)
         go 
   

6. Set replication to on for the authors and titles tables in the primary pubs2 database:

   isql -Usa -STOKYO_DS
         use pubs2
         go
   
         sp_setreptable authors, true
         go
   
         sp_setreptable titles, true
         go
   

7. In the replicate pubs2 database, grant permissions on the authors and titles tables to the maintenance user:

   isql -Usa -SSYDNEY_DS	
   
         use pubs2
         go
   
         grant select, insert, delete, update
         on authors to pubs2_maint
         grant select, insert, delete, update
         on titles to pubs2_maint
         go
   

   You can find the maintenance user in the “Database information” section of the Database setup worksheet you completed for the replicate pubs2 database.

8. In the replicate Replication Server, create subscriptions for the authors and titles tables:

   isql -Urepsys -Prepsys_ps -SSYDNEY_RS
   
         create subscription authors_sub
         for authors 
         with replicate at SYDNEY_DS.pubs2 
         without materialization
         go
   
         create subscription titles_sub
         for titles 
         with replicate at SYDNEY_DS.pubs2 
         without materialization
         go