Create DR Database for SQL Server

Follow

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.

  1. 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

Have more questions? Submit a request

Comments