Setting up the example

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

“General setup tasks” explains how to install the servers and databases used in the example. These instructions may refer you to other chapters in this guide, or to other books.

“Establishing replication” explains how to set up replication between the pubs2 databases in the two Adaptive Servers.

General setup tasks

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.

Install the primary Adaptive Server, TOKYO_DS.

The Adaptive Server must have room on its database devices for a 2MB pubs2 database, a 10MB RSSD, and at least 10MB for logs.

Install the replicate Adaptive Server, SYDNEY_DS.

The Adaptive Server must have room on its database devices for a 2MB pubs2 database, a 10MB RSSD, and at least 10MB for logs.

Install the pubs2 database in the primary Adaptive Server:

isql -Usa -Psa_passwd -STOKYO_DS < \
      $SYBASE/$SYBASE_ASE/scripts/installpubs2

Install the pubs2 database in the replicate Adaptive Server:

isql -Usa -Psa_passwd -SSYDNEY_DS < \
      $SYBASE/$SYBASE_ASE/scripts/installpubs2

Complete Appendix A, “Worksheets” for the primary Replication Server (TOKYO_RS).

Create the RSSD for TOKYO_RS in the TOKYO_DS Adaptive Server.
The RSSD requires a RepAgent.

Complete Appendix A, “Worksheets” for the replicate Replication Server (SYDNEY_RS).

Create the RSSD for SYDNEY_RS in the SYDNEY_DS Adaptive Server.
The RSSD does not require a RepAgent.

Complete “Database setup worksheet” for the primary pubs2 database.

The database is in Adaptive Server TOKYO_DS.
The database is managed by the TOKYO_RS Replication Server.
The database requires a RepAgent.

Complete “Database setup worksheet” for the replicate pubs2 database.

The database is in Adaptive Server SYDNEY_DS.
The database is managed by the SYDNEY_RS Replication Server.
The database does not require a RepAgent.

Log in to the machine where the primary Replication Server will execute. Run rs_init and install Replication Server TOKYO_RS using the worksheet you completed in step 5.

Log in to the machine where you installed the primary Replication Server. Run rs_init and add the primary pubs2 database to the replication system using the worksheet you completed in step 7.

Log in to the machine where the replicate Replication Server will execute. Run rs_init and install Replication Server SYDNEY_RS using the worksheet you completed in step 6.

Log in to the machine where you installed the replicate Replication Server. Run rs_init and add the replicate pubs2 database to the replication system using the worksheet you completed in step 8.

Establishing replication

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

isql -Usa -STOKYO_RS

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.

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

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

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

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

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.

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

Since there is already data at the replicate database, this example uses the create subscription without materialization option. For other methods, see Chapter 3, “Replication Server Commands,” in the Replication Server Reference Manual.