Replicating Tables in the Example Replication System

Learn the procedures for replicating tables between two Adaptive Servers.

To prepare the replicate tables, check replication system components by using Sybase Central or isql to log in to the servers identified for the primary and replicate sites.
To prepare the primary table in the TOKYO_DS primary data server, log in to the pubs2 database in TOKYO_DS, and verify that the publishers table exists:
```
isql -Usa -P -STOKYO_DS
use pubs2
go
sp_help publishers
go
```
To create subscriptions, prepare login names and grant the relevant permisssions for the “pubs2_user” user that is creating the subscription in the TOKYO_DS Adaptive Server. This user must also exist in both Replication Servers:
1. In TOKYO_DS, create the “pubs2_user” login name:
```
isql -Usa -P -STOKYO_DS
sp_addlogin pubs2_user, pubs2_pw, pubs2
go
```
2. In TOKYO_DS, add the “pubs2_user” login name to the pubs2 database, and grant pubs2_user select permission on the publishers table:
```
use pubs2
go
sp_adduser pubs2_user
go
grant select on publishers to pubs2_user
go
```
3. In the TOKYO_RS primary Replication Server, create the “pubs2_user” login name and grant primary subscribe permission to pubs2_user:
```
isql -Usa -P -STOKYO_RS
create user pubs2_user
set password pubs2_pw
go
grant primary subscribe to pubs2_user
go
```
4. In the SYDNEY_RS replicate Replication Server, create the “pubs2_user” login name and grant create object permission to pubs2_user in the SYDNEY_RS replicate Replication Server:
```
isql -Usa -P -SSYDNEY_RS
create user pubs2_user
set password pubs2_pw
go
grant create object to pubs2_user
go
```

In TOKYO_RS, create the replication definition publishers_rep for the publishers table:

isql -Ujohn -P -STOKYO_RS
create replication definition publishers_rep
with primary at TOKYO_DS.pubs2
with all tables named 'publishers'
(pub_id char(4), pub_name varchar(40),
city varchar(20), state char(2))
primary key (pub_id)
searchable columns (pub_id, pub_name)
replicate minimal columns
go

In this example, the user “john” creates the replication definition. This user requires create object permission in TOKYO_RS.

In TOKYO_DS, mark the publishers primary table for replication. To mark the table for replication with the sp_setreptable system procedure, you must be the database owner or system administrator for the data server. Enter:
```
sp_setreptable publishers, 'true'
go
```
In the SYDNEY_DS replicate data server, log in to the pubs2 database, and verify that the publishers table exists:
```
isql -Usa -P -SSYDNEY_DS
use pubs2
go
sp_help publishers
go
```
When you add the replicate pubs2 database using Sybase Central or rs_init, the maintenance user is created and given replication_role. The maintenance user must have replication_role, sa_role, or alias the database owner to replicate truncate table.
In SYDNEY_DS, verify that the maintenance user has select, insert, delete, and update permissions on the publishers table:
```
grant all on publishers to SYDNEY_DS_maint
go
```
Log in to SYDNEY_RS Replication Server as pubs2_user and create the subscription publishers_sub for the replication definition publishers_rep:
```
isql -Upubs2_user -Ppubs2_pw -SSYDNEY_RS
create subscription publishers_sub
for publishers_rep
with replicate at SYDNEY_DS.pubs2
subscribe to truncate table
go
```
This subscription uses the default atomic materialization. No where clause is included, so all rows will be replicated. Execution of the truncate table command will be reproduced at the destination database.
While still logged into SYDNEY_RS, use the check subscription command to monitor the status of the subscription materialization:
```
check subscription publishers_sub
for publishers_rep
with replicate at SYDNEY_DS.pubs2
go
```
You can verify if replication is occurring as expected by verifying that a row you insert is copied to the replicate table.
1. In TOKYO_DS, insert a row into the publishers table:
```
isql -Usa -P -STOKYO_DS
use pubs2
go
insert publishers
values ('9950', 'Who Donut', 'Butler', 'CA')
go
```
2. In SYDNEY_DS, verify that the row you inserted was replicated into the replicate copy of the publishers table:
```
isql -Usa -P -SSYDNEY_DS
use pubs2
go
select * from publishers
go
```