SQL Server: Activation and DR Standby Mode
1. Introduction
When the disaster strikes and the primary database is no longer available, the standby database must be activated and brought online for the users to connect and the application to point to the Activated DR database. The instructions are similar to Oracle Activation
The steps to activate the standby database are:
Stop the scheduling of Dbvisit Standby.
Change the network configuration (or DNS) so that users will connect to the standby database (or server) instead of the primary database (or server).
Activate and bring the standby database online for normal operation as per the instructions below. Note that as soon as the standby database is activated and becomes the new primary database that is online. The link to the original primary database is lost and it is no longer possible to apply new logs to the original standby database.
2. Activating Standby Database using control center
Click on activate Standby to bring the standby database online from restoring mode.
Once this option is selected all the available transaction logs on the standby will be restored and the database will be put into online. The configuration roles would also be swapped and no changes will be done to the primary database.
Once you click on Activate it would ask for confirmation to proceed or not. This is an additional check to ensure the database activation is not done accidentaly.
The database is activated and the database status is now online.
Below is the status of the configuration post activation. The configuration would be shown as IN FAILOVER STATE ( 1 ) with the database on standby clearly marked as online ( 2 )
You can re-create the standby database on the server wha by deleting the database from the actual server and then set up the Disaster recovery again.
3. Switch into Standby Mode
This option would switch the standby database into read only mode. The users can connect to the standby database and query tables to validate the standby database.
Once you click on Switch into Standby Mode, you can see that a path must be provided to Backup Undo File location ( 1 ) where a snapshot of the database is taken and then the Click start to convert the standby database that in restoring mode to readonly mode
The status of the standby database configuration is changed from Restoring to STANDBY mode.
The standby database is in Standby/Read-only mode now.
The Automated updates can still happen between primary and standby as the transaction logs are backed up in primary and sent to standby and its automatically restored in standby.
In the below example a new table EMP has been created after the standby database has been put in read-only mode and we can see that the table is automatically created with rows once the transaction logs are transferred and applied in standby.
In Primary:
In Standby:
The snapshot database is
created to undo changes present in the standby server on the location provided when we convert the standby database from Restoring to Standby/Read-Only mode. The snapshot database records the changes from primary to standby that occur during the time when the database is in read-only mode
After, the testing is complete, the database can be switched back to restoring mode.
After the task is completes the database is back to restoring mode and the database configuration status is also updated from STANDBY mode to RESTORING mode.