Scenario 6

You can materialize a table where activity to the primary table is going on while the materialization takes place. Use this option when there is a more than a small amount of data to synchronize between the primary and replicate table. This scenario assumes that the table has already been marked using sp_setreptable or sp_setreplicate.

Begin the resynchronization

Create the replication definition:

create replication definition t1_repdef
with primary at sunak1505x.pri
with all tables named t1
(a char(10),
b char(10))
primary key (a,b)
go
Replication definition 't1_repdef' is created

If the replication definition was previously created with replicate minimal columns, alter the replication definition to replicate all columns.

Define the subscription:

define subscription t1_sub
for t1_repdef
with replicate at sunak1505x.rep
subscribe to truncate table
go
Subscription t1_sub is in the process of being
defined.

Validate that all connections are successful:

admin who
go
Spid     Name         State              Info
----     ---------- -------------------- --------
139      DSI EXEC   Awaiting Command     104(1) sunak1505x.pri
135      DSI        Awaiting Message     104 sunak1505x.pri
129      DIST       Awaiting Wakeup      104 sunak1505x.pri
130      SQT        Awaiting Wakeup      104:1 DIST sunak1505x.pri
123      SQM        Awaiting Message     104:1 sunak1505x.pri
56       SQM        Awaiting Message     104:0 sunak1505x.pri
143      REP AGENT  Awaiting Command     sunak1505x.pri
144      NRM        Awaiting Command     sunak1505x.pri
156      DSI EXEC   Awaiting Command     106(1) sunak1505x.rep
155      DSI        Awaiting Message     106 sunak1505x.rep
124      SQM        Awaiting Message     106:0 sunak1505x.rep

Activate subscription with suspension:
```
activate subscription t1_sub
for t1_repdef
with replicate at sunak1505x.rep
with suspension
go
Subscription 't1_sub' is in the process of being
activated.
```
Note: The with suspension clause suspends the DSI to the target to allow the initial materialization of the replicate database. Any changes to the primary table are placed in the outbound queue of the Replication Server, waiting for the DSI to be resumed.

Validate that the DSI is now suspended. Enter:

admin who
go
Spid      Name           State               Info
----     ----------     ---------------      --------
139      DSI EXEC       Awaiting Command     104(1) sunak1505x.pri
135      DSI            Awaiting Message     104 sunak1505x.pri
129      DIST           Awaiting Wakeup      104 sunak1505x.pri
130      SQT            Awaiting Wakeup      104:1 DIST sunak1505x.pri
123      SQM            Awaiting Message     104:1 sunak1505x.pri
56       SQM            Awaiting Message     104:0 sunak1505x.pri
143      REP AGENT      Awaiting Command     sunak1505x.pri
144      NRM            Awaiting Command     sunak1505x.pri
156      DSI EXEC       Suspended            106(1)sunak1505x.rep
155      DSI            Suspended            106 sunak1505x.rep
124      SQM            Awaiting Message     106:0 sunak1505x.rep

Copy the data from the primary table:

% bcp pri..t1 out 't1.bcp' -Usa -P -Ssunak1505x -c
Starting copy...
5 rows copied.
Clock Time (ms.): total = 9 Avg = 1 (555.56 rows per
sec.)

Insert the data in to the target table:

% bcp rep..t1 in 't1.bcp' -Usa -P -Ssunak1505x -c
Starting copy...
5 rows copied.
Clock Time (ms.): total = 30 Avg = 6 (166.67 rows
per sec.

On the Replication Server, set autocorrection on:
```
set autocorrection on
for t1_repdef
with replicate at sunak1505x.rep
go
autocorrection' is modified for replication
definition 't1_repdef' with replicate at
'sunak1505x.rep'.
```
Note: If there are multiple replication definitions from the same primary table going to the same replication table, make sure that autocorrection is set on all the replication definitions for this table.
Resume the connection to let the data stored in the queues be applied to the target table:
```
resume connection to sunak1505x.rep
go
Connection to 'sunak1505x.rep' is resumed.
```
Note: Because of the overhead associated with autocorrection, Sybase recommends that you do not keep autocorrection on when doing normal replication activities.
Send through a marker on the primary so that we know when we no longer need autocorrection. In this example, we are going to use rs_ticket. On the replication server, validate that rs_ticket is available on the DSI. By default, it is available.
```
isql –Usa –Psa_pass –SPRS2
admin config, "connection",sunak1505x,rep,"ticket"
go
Configuration      Config Value       Run Value
--------------    ------------------ ------------
dsi_rs_ticket_report <server default> <server
default>
Default Value   Legal Values Datatype  Status
-------------- ------------   -------- ------------
on              list: on, off string Connection/route
restart required
```
Start the rs_ticket marker at the primary database.
```
isql –Usa –P –Ssunak1505x
use pri
go
rs_ticket "done t1"
go
(return status = 0)
```
To check whether rs_ticket made it to the replicate database:
```
use rep
go
select ticket from rs_ticket_history where h1 =
"done t1"
go
ticket
-------------------------------------
V=2;H1=done t1;PDB(pri)=11/30/09
12:14:26.253;EXEC(143)=11/30/09 12:14:26.261;B
(143)=19705;DIST(129)=11/30/09
12:14:27.273;DSI(158)=11/30/0912:14:28
294;DSI_T=12;DSI_C=15;RRS=PRS2
```
Note: rs_ticket version 2, which includes all stored procedures and tables to support it, is available in Replication Server 15.1 and later. If you have Replication Servers earlier than 15.1, read the Administration Guide for limitations.

Turn off autocorrection when the marker is seen at the replicate database:

set autocorrection off
for t1_repdef
with replicate at sunak1505x.rep
go
'autocorrection' is modified for replication
definition 't1_repdef' with
replicate at 'sunak1505x.rep'.

Validate the subscription:

validate subscription t1_sub
for t1_repdef
with replicate at sunak1505x.rep
go