Scenario 5

You can materialize a table to the replicate database where the Replication Server is doing the materialization. In this scenario there are either no users using the table or the primary table can be locked while the materialization is taking place and there are few rows in the table. This scenario lets Replication Server populate the replicate table.

Ensure that the password of the “sa” user is the same for the primary ASE and the Replication Servers. You cannot use this option if these passwords are not identical.

Before you begin

The maintenance user for the replicate database must have access to insert data into the replicate table. 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)
go
Replication definition 't1_repdef' is created

Create the subscription:

create subscription t1_sub
for t1_repdef
with replicate at sunak1505x.rep
incrementally
subscribe to truncate table
go
Subscription 't1_sub' is in the process of being
created.

Note: This option places a hold on the table at the primary database while the selection of the rows for materialization is taking place.

Check if the subscription is valid at the primary and replicate site using the check subscription command:

check subscription t1_sub
for t1_repdef
with replicate at sunak1505x.rep
go
Subscription t1_sub is VALID at the replicate.
Subscription t1_sub is VALID at the primary.

If the subscription is not valid, check the Replication Server to see if a materialization queue has been created.
```
admin who
go
Spid   Name    State         Info
---- ------ -------------------- -------------
2      SQM     Awaiting Message 106:-2147483541
t1_repdef.t1_sub
```
If the subscription has not materialized within an acceptable time, and the materialization queue still exists, look in the Replication Server log for any error messages, correct the error, drop the materialization queue (sysadmin drop_queue), drop the subscription, and re-create the subscription.