I recently had to resync a warm standby database for a customer. When I can’t stop the transactions on primary database I use the “dump marker” clause while recreating the connection to the warm standby database with replication server. Indeed, you can resync a warm standby database anytime with no downtime on the primary database side using the “dump marker” method. Let’s assume the following warm-standy architecture:
- SYBPARFRDEV01_DS as the primary dataserver
- SYBPARFRDEV02_DS as the target dataserver
- SYBPARFRDEV02_RS as the replication server
- projectdb as the primary and replicated database
[1] Drop connection to warm standby database in replication server:
Connect to the replication server SYBPARFRDEV02_RS and drop the connection SYBPARFRDEV02_DS.projectdb:
isql –Usa -SSYBPARFRDEV02_RS –w1000 Password: drop connection to SYBPARFRDEV02_DS.projectdb go
[2] Stop replication agent and remove the secondary truncation point in primary database:
Connect to primary dataserver SYBPARFRDEV01_DS. In primary database projectdb, stop the replication agent :
isql –Usa -SSYBPARFRDEV01_DS –w1000 Password: use projectdb go sp_stop_rep_agent projectdb go
Then remove the secondary truncation point in primary database projectdb and dump the transaction log:
use projectdb go dbcc settrunc (ltm, ignore) go dump transaction with truncate_only go
[3] Purge the replication server stable queues:
Connect to the replication server SYBPARFRDEV02_RS, and collect the inbound and outbound queue numbers that belong to connection SYBPARFRDEV01_DS.projectdb.
isql –Usa -SSYBPARFRDEV02_RS –w1000 Password: admin who go
Spid Name State Info
---- ---------- -------------------- ----------------------------------------
14 DIST Awaiting Wakeup 137 SYBPARFRDEV01_DS.projectdb
17 SQT Awaiting Wakeup 137:1 DIST SYBPARFRDEV01_DS.projectdb
10 SQM Awaiting Message 137:1 SYBPARFRDEV01_DS.projectdb
9 SQM Awaiting Message 137:0 SYBPARFRDEV01_DS.projectdb
19 DSI EXEC Awaiting Command 138(1) SYBPARFRDEV01_DS.projectdb
11 DSI Awaiting Message 138 SYBPARFRDEV01_DS.projectdb
46 REP AGENT Awaiting Command SYBPARFRDEV01_DS.projectdb
123 DSI EXEC Awaiting Command 140(1) SYBPARFRDEV02_DS.projectdb
122 DSI Awaiting Message 140 SYBPARFRDEV02_DS.projectdb
18 DSI EXEC Awaiting Command 101(1) SYBPARFRDEV02_DS.rssd_projectdb
12 DSI Awaiting Message 101 SYBPARFRDEV02_DS.rssd_projectdb
8 SQM Awaiting Message 101:0 SYBPARFRDEV02_DS.rssd_projectdb
13 dSUB Sleeping
6 dCM Awaiting Message
7 dAIO Awaiting Message
15 dREC Sleeping dREC
16 dSTATS Sleeping
140 USER Active sa
5 dALARM Awaiting Wakeup
From the example above, inbound and outbound queue numbers are the number that show up in column “Info”, just in front of the connection name. See below:
10 SQM Awaiting Message 137:1 SYBPARFRDEV01_DS.projectdb
9 SQM Awaiting Message 137:0 SYBPARFRDEV01_DS.projectdb
Once you have collected the inbound and outbout queue numbers, you can purge these queues, so it will delete all remaining transactions in the queues of the warm standby:
sysadmin sqm_purge_queue,137,1 go sysadmin sqm_purge_queue,137,0 go
[4] Reset ltm, set the secondary truncation point and start the replication agent:
Connect to primary dataserver SYBPARFRDEV01_DS. In primary database projectdb, set the secondary truncation point for replication agent:
isql –Usa -SSYBPARFRDEV01_DS –w1000 Password: use projectdb go dbcc settrunc (ltm, valid) go
Connect to the secondary dataserver SYBPARFRDEV02_DS, and reset ltm in rssd database:
isql –Usa -SSYBPARFRDEV02_DS –w1000 Password: use rssd_projectdb go rs_zeroltm SYBPARFRDEV01_DS, projectdb go
Connect to primary dataserver SYBPARFRDEV01_DS. Start the replication agent in primary database projectdb.
isql –Usa -SSYBPARFRDEV01_DS –w1000 Password: use projectdb go sp_start_rep_agent projectdb go
[5] Add to the replication server the connection to the warm standby database:
Connect to the replication server SYBPARFRDEV02_RS, and create manually the connection to the warm standby database with “dump marker” clause.
isql –Usa -SSYBPARFRDEV02_RS –w1000 Password: create connection to SYBPARFRDEV02_DS.projectdb set error class to rs_sqlserver_error_class set function string class to rs_sqlserver_function_class set username to projectdb_maint set password to projectdbpasswd with log transfer on as standby for SYBPARFRDEV01_DS.projectdb use dump marker go
After executing create connection with the use dump marker clause, wait until admin logical_status shows the status of the standby connection as: “Suspended/Waiting for Enable Marker” and “State of operation in progress” is None.
admin logical_status go
As long as the latter says “Attempting to place marker in the Active Log”, do not dump the active database yet !
[6] Dump the primary database:
Connect to primary dataserver SYBPARFRDEV01_DS, and dump the database to the backup file system.
isql –Usa -SSYBPARFRDEV01_DS –w1000 Password: dump database projectdb to “compress::1::/usr/sybase/dump/projectdb.dmp1” stripe on “compress::1::/usr/sybase/dump/projectdb.dmp2” stripe on “compress::1::/usr/sybase/dump/projectdb.dmp3” stripe on “compress::1::/usr/sybase/dump/projectdb.dmp4” go
Once the backup is done, transfert the backup files.
[7] Load the standby database with the dump from primary database:
Connect to the secondary dataserver SYBPARFRDEV02_DS, and load the standby database projectdb in SYBPARFRDEV02_DS with the dump from the primary database projectdb from SYBPARFRDEV01_DS.
isql –Usa -SSYBPARFRDEV02_DS –w1000 Password: load database projectdb from “compress::/usr/sybase/dump/projectdb.dmp1” stripe on “compress::/usr/sybase/dump/projectdb.dmp2” stripe on “compress::/usr/sybase/dump/projectdb.dmp3” stripe on “compress::/usr/sybase/dump/projectdb.dmp4” go online database projectdb go
[8] Requirements before to resume connection:
Connect to the secondary dataserver SYBPARFRDEV02_DS, and remove the secondary truncation point in the warm standby database. Also, make sure to dump transaction log and truncate rs_lastcommit table.
isql –Usa -SSYBPARFRDEV02_DS –w1000 Password: dbcc settrunc(ltm, ignore) go dump tran projectdb with truncate_only go truncate table projectdb..rs_lastcommit go checkpoint go
[9] Resume connection to the warm standby database:
Connect to the replication server SYBPARFRDEV02_RS, and resume connection to the warm standby database:
isql –Usa -SSYBPARFRDEV02_RS –w1000 Password: resume connection to SYBPARFRDEV02_RS.projectdb go
[10] Testing the warm standby replication system:
To check if the warm standby replication is working fine, you can insert some data into the table “dummy_table” on the primary database. Data should be replicated in table “dummy_table” in the warm standby database.
Proceed as follow:
Connect to the primary dataserver SYBPARFRDEV01_DS, create a table named dummy_table if it does not exists and insert data into it.
isql –Usa -SSYBPARFRDEV01_DS –w1000 Password: use projectdb go create table dummy_table(a varchar(255),b varchar(255)) go insert dummy_table(a,b) values (“26052010”,”it works”) go
Connect to the secondary dataserver SYBPARFRDEV02_DS, and select data in table projectdb..dummy_table.
isql –Usa -SSYBPARFRDEV02_DS –w1000 Password: use projectdb go select * from dummy_table go
Here is an example output:
a b
-------------------------------------------------- --------------------
26052010 it works
As you can see, we find the data previously inserted in the primary database, so the warm standby replication is working fine.
