Establishing the Sample Replication

Create a route from the primary Replication Server to the replicate Replication Server to establish a sample replication.

Log in to the primary Replication Server as “sa”:
```
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
```
Note: 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
```
Note: 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
```
Note: Since there is already data at the replicate database, this example uses the create subscription with the without materialization option. For other methods, see Replication Server Commands in the Reference Manual .