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.

In general, do not switch the active and standby databases when the active data server experiences a transient failure from which the Adaptive Server recovers upon restarting with no need for additional recovery steps. You may want to switch if the active database will be unavailable for a long period of 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
Related tasks
Adding the Standby Database to the Replication Server
Initializing the Standby Database