Create DR Database for SQL Server
1. Introduction
In this section, we will discuss how to create a DR database using standby Multiplatform. There are certain pre-requisites when creating a DR database for SQL Server.
The primary database must be in the Full recovery model. Look at the below screenshot and ensure that the database is not simple or Bulk-logged.
The below error is shown when the database is not in full recovery model.
2. All scheduled backups that are taken for the primary database must be a copy only backup and the transaction logs must not be backed up the schedules as the transaction log backups are taken by Multiplatform.
2. Create SQL Server DR database
Below screenshot is after creating a configuration where there are no DR database available for the particular configuration. You can either click on the configuration which says Set up Disaster Recovery now? ( 1 ) or click on the configuration to get the actions for the configuration and click on Set Up Disaster Recovery ( 2 )
( 1 ) Transportable media option can be used to avoid file transfer from primary to standby. By default, this option is disabled. This backup can be taken in an external media or NFS mount (CISF format is not supported) and restoration proceed once the NFS mount is available in standby and the DR database creation is resumed.
( 2 ) Full Backup Location on Primary: Location where backup on primary database is taken, the location specified is the default location. But its recommended to change it to a custom location where there is enough free space available for backup to be taken.
( 3 ) Full Backup Location on Standby: Location where backup of the primary database is transferred to. The location specified is the default location. But its recommended to change it to a custom location where there is enough free space available for backup to be taken.
( 4 ) Database File Location for mdf: The location provided is the default location where the mdf’s or the datafiles are stored, but its recommended to provide a custom location where there is enough free space available.
( 5 ) Database File Location for ldf: The location provided is the default location where the ldf’s or the log files are stored, but its recommended to provide a custom location where there is enough free space available.
( 6 ) Automated Standby Update: This is enabled by default with a set value of 300 sec. This is the interval for logshipping from primary to standby. Every 300sec transaction log backups are taken from primary and copied to standby and then the transaction log backups are restored to the standby database. The location provided is the default location and this can be changed only by recreating the configuration as the location is provided when the configuration is created.
( 7 ) Observer: This is enabled by default as well. This is an automated monitoring utility that will start monitoring the configuration as soon the standby database is created. This will send alerts based on the monitoring and alerting mechanism set on the Observer.
( 8 ) Press the start button to create the DR database.
( 1 ) Task progress of the DR database creation
( 2 ) The database file that was restored after backup in primary and transfer to standby. The backup files are deleted from both primary and standby as soon the restore is syc
( 3 ) End action successful
As soon as the DR database is created we see the configuration getting updated with the time gap information
( 1 ) The time gap difference between standby and primary.
( 2 ) The database/ configuration name
( 3 ) The source database server (toru) and the status (online).
( 4 ) The standby database server and (wha) status (restoring).
( 5 ) Automated Standby update is enabled with the default of 300sec.
( 6 ) Observer is enabled and watching the configuration
2. Create a Second Standby SQL database
In this section, we will discuss how to add a second SQL standby database. This feature is newly introduced from Standby Multiplatform Version 11.1.0. You must upgrade to this version to create a second standby SQL database in a different server
The Example below shows the detailed steps with the screenshots on how to create a second standby database on host tahi.
Primary host: toru
Standby host: wha
Second Standby host: tahi
Database : AdventureWorks
Existing Configuration where Adventureworks2014 standby database is created on host wha. Click on (1) to add a new SQL Server Instance
2. Add a new Instance
3. Choose the New Hostname Tahi (1), Choose the authentication type, and Add the Instance (2)
4. Next step is to create a Second configuration for Host Toru and Tahi
5. Choose the Primary Server SQL instance and Source database (1), Choose the Second SQL Server Instance (2), Enter the License Key for the second configuration(3), and click on Create Configuration (4)
Note: Please enter a new license key for the second standby configuration.
6. Second Configuration is created for the database Adventureworks. Now click on Set up now to create a Second Standby Database on Host tahi
7. Review and enter the below field
(1) Enter the backup locations on both primary and standby and ensure to have sufficient space to hold a full backup of your primary database
(2) & (3) Data file and log file location
(4) To Create a Standby Database
8. Standby Database Adventureworks created on host Tahi.
Performing Graceful switchover
Performing Switchover on configuration 1, swaps the role Database AdventureWorks on tahi to Primary and toru to Restoring. The change is reflected in the 2nd configuration where toru and wha are Restoring. Even though synchronisation doesn't happen between toru and wha , it is recommended to Disable Automatic Standby update and Observer.
After switchback of configuration (1), Enable the Auto Standby update and Observer for Configuration (2)for toru and wha.