Scenario 2

You can materialize a replicate database from a primary database where all user activity has stopped at the primary database.

Use this materialization method when replication is broken between the primary database and the replicate database for a significant period of time and the queues are filling up, which needs to be purged. Refresh the replicate database with a copy from the current primary database. This can be used with either table replication or database replication. This scenario assumes that user activity is stopped at the primary while the database dump is taken.

Before you begin

At the RSSD, obtain the maintenance users for the connections:

use PRS2_RSSD
go
rs_helpuser
go

User Name      Permission(s) Name
------------ ------------------------------
PRS2_id_user    no grants
sa              sa
PRS2_ra         connect source
PRS2_rsi        connect source
PRS2_RSSD_prim  source, primary subscr

Maintenance Users
User name       Destination DS.DB
------------ -------------------------------------
PRS2_RSSD_maint  sunak1505x.PRS2_RSSD
pri_maint        sunak1505x.pri
pri_maint        sunak1505x.rep
(return status = 0)

In this example, “pri_maint” is the maintenance user for both the connection to the primary database, sunak1505x.pri. and replicate database, sunak1505x.rep.

Begin the resynchronization

If it is not already suspended, suspend the connection to the replicate database:

isql –Usa –P –SPRS2
suspend connection to sunak1505x.rep
go
Connection to 'sunak1505x.rep' is suspended.

In the primary database, release the secondary truncation point—any log records not previously replicated are already reflected in the data.

isql –Usa –P –SSunak1505x
use pri
go
sp_stop_rep_agent pri
go
The Replication Agent thread for database 'pri' is
being stopped.
(return status = 0)
dbcc settrunc (ltm,ignore)
go
secondary trunc page secondary trunc state dbrepstat
-------------------- --------------------- -------
2669                   0                     172
generation id database id database name ltl version
------------- ------------ ------------- ----------
0                7               pri         720

Note: When secondary truncation state is 0, the secondary truncation point is inactive on the primary database.

Dump the primary database:

dump database pri to '/c11014900/sybase1520x/ASE-
15_0/bin/pri.dmp'
go

Load the replicate database:

load database rep from '/c11014900/sybase1520x/ASE-
15_0/bin/pri.dmp'
go

Purge queues of any existing work from the primary database (inbound queue) to the replicate database (outbound queue):

isql -Usa -P –SPRS2
sysadmin hibernate_on
go
The Replication Server has now entered hibernation
mode.
sysadmin sqm_purge_queue, 104,1
go
sysadmin sqm_purge_queue, 106,0
go
sysadmin hibernate_off
go
The Replication Server has now finished hibernation
mode.

Bring the replicate database online:
```
online database rep
go
```
To the replicate database, add the maintenance user of the replicate connection, and grant the appropriate privileges.
```
use rep
go
sp_adduser pri_maint
go
New user added.
(return status = 0)
grant execute on rs_get_lastcommit to pri_maint
go
grant all on rs_lastcommit to pri_maint
go
```
Note: If the maintenance user is new to this server, add the maintenance user login to this server, and grant the replication role:
```
sp_addlogin <maintenance user>, <maintenance userpassword>, <database>
go
sp_role "grant", replication_role, <maintenance
user>
go
```
If the password to the maintenance user is unknown, set a new password in Adaptive Server. In the Replication Server, change the password of the maintenance user to match with what was assigned in the Adaptive Server:
```
alter connection to <dataserver>.<database>
set password to <new password>
go
```

Resume the connection to the replicate database:

resume connection to sunak1505x.rep
go
Connection to 'sunak1505x.rep' is resumed.

On the Replication Server, confirm that the connection to the replicate database has successfully resumed:

admin who
go
Spid Name        State        Info
----- ------- ------------ -------------------
103 DSI EXEC Awaiting Command 104(1) sunak1505x.pri
100 DSI Awaiting Message 104 sunak1505x.pri
95 DIST Awaiting Wakeup 104 sunak1505x.pri
96 SQT Awaiting Wakeup 104:1 DISTsunak1505x.pri
92 SQM Awaiting Message 104:1 sunak1505x.pri
56 SQM Awaiting Message 104:0 sunak1505x.pri
REP AGENT Down sunak1505x.pri
NRM Down sunak1505x.pri
107 DSI EXEC Awaiting Command 106(1)sunak1505x.rep
106 DSI Awaiting Message 106 sunak1505x.rep
91 SQM Awaiting Message 106:0 sunak1505x.rep

Note: If the connection does not successfully resume, look at the Replication Server log to see what errors occurred, correct the errors, and resume the connection.

Add the replication definition and subscription to the Replication Server, if needed. In this example, we are using a database replication definition and a database subscription. Enter:

create database replication definition pri_db_repdef
with primary at sunak1505x.pri
replicate DDL
go
Database replication definition 'pri_db_repdef'
for sunak1505x.pri is created.
create subscription rep_db_repdef
for database replication definition pri_db_repdef
with primary at sunak1505x.pri
with replicate at sunak1505x.rep
without materialization
subscribe to truncate table
go

Subscription 'rep_db_repdef' is in the process of
being created

Reset the start point on the primary database to the end of the primary database log.

Zero the rs_locater entry for this connection in the RSSD:

use PRS2_RSSD
go
rs_zeroltm sunak1505x,pri
go
Locater has been reset to zero.
(return status = 0)

Enable the secondary truncation point in the primary database:

isql –Usa –P –SSunak1505x
use pri
go
dbcc settrunc (ltm,valid)
go
secondary trunc page secondary trunc state dbrepstat
-------------------- --------------------- ---------
2669                      1               173
generation id database id database name ltl version
------------- ------------ ------------- ----------
0                7                pri     720

Start the Replication Agent on the primary database:

use pri
go
sp_start_rep_agent pri
go
Replication Agent thread is started for database
'pri'.
(return status = 0)

Validate that the Replication Server connections are ready to replicate:

admin who
go
Spid Name      State         Info
---- -------- ------------ -------------
103 DSI EXEC Awaiting Command 104(1) sunak1505x.pri
100 DSI Awaiting Message 104 sunak1505x.pri
95 DIST Awaiting Wakeup 104 sunak1505x.pri
96 SQT Awaiting Wakeup 104:1 DIST
sunak1505x.pri
92 SQM Awaiting Message 104:1 sunak1505x.pri
56 SQM Awaiting Message 104:0 sunak1505x.pri
108 REP AGENT Awaiting Command sunak1505x.pri
109 NRM Awaiting Command sunak1505x.pri
107 DSI EXEC Awaiting Command 106(1)
sunak1505x.rep
106 DSI Awaiting Message 106 sunak1505x.rep
91 SQM Awaiting Message 106:0 sunak1505x.rep

Validate that data can be replicated from the primary to the replicate.:
Note: If Replication Agent goes down, look in the ASE error log for messages. If the DSI is suspended, look in the Replication Server error log or the ASE error log for messages. To start the Replication Agent on the primary database, use sp_start_rep_agent <dbname> command. To resume the DSI at the Replication Server, use the resume connection to <dataserver>.<database> command. To move past and not apply transactions in the outbound queue, use the skip transaction clause of the resume connection command.
Allow users on the primary database.