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 –P –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