Switching the Active and Standby Databases

Switch from the active to the standby database if the active database will be available for a long time.

1. Ensure that the RepAgent is shut down at the active database. If the RepAgent is still active, issue:

   isql -Usa -P -Ssunak1505i
   use pubs2
   go
   sp_stop_rep_agent pubs2
   go
   The Replication Agent thread for database 'pubs2' is
   being stopped.
   (return status = 0)

2. At the Replication Server, enter:

   isql -Usa -P -SPRS
   switch active for pubs2a.pubs2s to wingak1505i.pubs2
   go
   Switch active to wingak1505i.pubs2 for logical
   connection to pubs2a.pubs2s is in progress

3. To monitor the progress of a switch, use:

   admin logical_status
   go

   Logical Connection Name	Active Connection Name	Active Conn State	Standby Connection Name	Standby Conn State
   [102] pubs2a.pubs2s	[104] wingak1505i.pubs2	Active/	[104] sunak1505i.pubs2	Suspended/Waiting for Enable Marker
   Controller RS	Operation in Progress	State of Operation in Progress	Spid
   [16777317] PRS	None	None

   When the switch is complete, you can see the following in the Replication Server log:

   I. 2009/10/28 22:43:18. SQM starting: 102:1
   pubs2a.pubs2s
   I. 2009/10/28 22:43:18. Resetting Replication Agent
   starting log position for wingak1505i.pubs2
   
   
   I. 2009/10/28 22:43:19. DIST for 'pubs2a.pubs2s' is
   Starting
   I. 2009/10/28 22:43:19. Resuming LogTransfer for
   wingak1505i.pubs2
   I. 2009/10/28 22:43:19. Switch completed :
   pubs2a.pubs2s
   I. 2009/10/28 22:43:19. The DSI thread for database
   'wingak1505i.pubs2' is started.

4. When the active database switch is complete, restart RepAgent for the new active database:

   isql -Usa -P -Swingak1505i
   sp_start_rep_agent pubs2
   go
   Replication Agent thread is started for database
   'pubs2'.
   (return status = 0)

   A successful start-up writes messages to the Replication Server log:

   I. 2009/10/28 22:52:25. Replication Agent for
   wingak1505i.pubs2 connected in passthru mode.
   I. 2009/10/28 22:52:25. Setting system upgrade
   locater for version 1100 to 00000000000000
   0000000000000000000000000000000000000000
   000000000000000000 for
   database wingak1505i.pubs2.
   I. 2009/10/28 22:52:26. Distributor for
   'pubs2a.pubs2s' received and proceessed enable
   marker.

5. Resume the standby connection to replicate the data to the standby database, if the new standby database does not need to be resyncronized with the new active database, go to step 7:

   resume connection to sunak1505i.pubs2
   go
   Connection to 'sunak1505i.pubs2' is resumed.

6. To verify whether the warm standby is operational or not, use:

   admin logical_status
   go

   Logical Connection Name	Active Connection Name	Active Conn State	Standby Connection Name	Standby Conn State
   [102] pubs2a.pubs2s	[104] wingak1505i.pubs2	Active/	[104] sunak1505i.pubs2	Active/
   Controller RS	Operation in Progress	State of Operation in Progress	Spid
   [16777317] PRS	None	None

7. If the old active database needs to be resynchronized with the new active database, first drop the standby connection from the Replication Server:

   drop connection to sunak1505i.pubs2
   go
   Connection to 'sunak1505i.pubs2' is dropped.

8. Rebuild the standby side of the warm standby connection using a dump of the new active database and synchronize with a dump marker by following the steps previously described in these topics:
   • Adding the standby database to the Replication Server
   • Initializing the standby database