Setting up the example

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

StepsGeneral 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.

  1. Install the primary Adaptive Server, TOKYO_DS.

  2. Install the replicate Adaptive Server, SYDNEY_DS.

  3. Install the pubs2 database in the primary Adaptive Server:

    isql -Usa -Psa_passwd -STOKYO_DS < \
          $SYBASE/$SYBASE_ASE/scripts/installpubs2
    
  4. Install the pubs2 database in the replicate Adaptive Server:

    isql -Usa -Psa_passwd -SSYDNEY_DS < \
          $SYBASE/$SYBASE_ASE/scripts/installpubs2
    
  5. Complete Appendix A, “Worksheets” for the primary Replication Server (TOKYO_RS).

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

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

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

  9. 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.

  10. 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.

  11. 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.

  12. 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.

StepsEstablishing replication

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
    

    NoteFind 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
    

    NoteYou 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
    

NoteSince 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.