Disaster Recovery - Replicate databases to Recovery & NoRecovery Modes simultaneously
Published:
Browse All Articles > Disaster Recovery - Replicate databases to Recovery & NoRecovery Modes simultaneously
Last month I joined with a new organization as a DBA and implementing disaster recovery was my first task. I faced some issues while configuring high availability options to the databases.
Without knowing that Copy-only is configured on the databases, I configured Log Shipping on those databases and it started giving errors. And after the investigation of the errors I came know the cause of the issues.
But as per our company structure we need to sync the database in recovery mode to a different server for QA team.
A question I posed at Experts-Exchange got me some good suggestions to help me configure replication, mirroring, etc.
To configure transactional replication, we don't have primary keys on all the tables, so the replication option is ruled out. And mirroring will not work as per our requirements.
Finally we need to sync the production databases to different servers in recovery mode (Testing Team - at least in one server - needs to be in sync every 2 hours) and Non-recovery mode (Disaster recovery - to at least two servers - needs to be synchronized every 15 minutes).
So I started this using scripting as per our requirement. I created backup scripts, restore scripts and a table to track the process. I am attaching the scripts to this article. Here are some details about the attached scripts:
Similar to Logshipping, all the backup scripts will be executed on Primary Server and backup info will be stored in the table (BRInfo).
BRInfo will act as master table for copy, restore & deleting backups.
BRInfo table should be created on each secondary server. And copy, restore & delete scripts will be executed separately on each secondary server.
Please check the attached scripts. Feel free to post any suggestion that you have for improvements. Thanks!
Primary-Server-Scripts.zip
Secondary-Server-Scripts-for-NoR.zip
Secondary-Server-Scripts-for-Rec.zip
Without knowing that Copy-only is configured on the databases, I configured Log Shipping on those databases and it started giving errors. And after the investigation of the errors I came know the cause of the issues.
But as per our company structure we need to sync the database in recovery mode to a different server for QA team.
A question I posed at Experts-Exchange got me some good suggestions to help me configure replication, mirroring, etc.
To configure transactional replication, we don't have primary keys on all the tables, so the replication option is ruled out. And mirroring will not work as per our requirements.
Finally we need to sync the production databases to different servers in recovery mode (Testing Team - at least in one server - needs to be in sync every 2 hours) and Non-recovery mode (Disaster recovery - to at least two servers - needs to be synchronized every 15 minutes).
So I started this using scripting as per our requirement. I created backup scripts, restore scripts and a table to track the process. I am attaching the scripts to this article. Here are some details about the attached scripts:
DR_FullBackups.sql
This procedure is created for taking full backups of all the databases or the given databases. This SP will be created on primary database servers.
This procedure is created for taking full backups of all the databases or the given databases. This SP will be created on primary database servers.
DR_DiffBackups.sql
This procedure is created for taking differential backups of all the databases or the given databases. This SP will be created on primary database servers.
This procedure is created for taking differential backups of all the databases or the given databases. This SP will be created on primary database servers.
DR_LogBackups.sql
This procedure is created for taking transactional log backups of all the databases or the given databases. This SP will be created on primary database servers.
This procedure is created for taking transactional log backups of all the databases or the given databases. This SP will be created on primary database servers.
DR_FullBackupsForRestoresFailed.sql
This procedure is created to take full backups for the databases if the they are failed during restore process. This SP will be created on each secondary database servers.
Needs to be executed from secondary server.
This procedure is created to take full backups for the databases if the they are failed during restore process. This SP will be created on each secondary database servers.
Needs to be executed from secondary server.
BRInfo - Tables.sql
Table created for information and monitoring the disaster process like status of backup/copy/restore and time taken for backup/copy/restore.This table is created on all the secondary databases. Please check the attached Excel file for the sample structure of the table.
Table created for information and monitoring the disaster process like status of backup/copy/restore and time taken for backup/copy/restore.This table is created on all the secondary databases. Please check the attached Excel file for the sample structure of the table.
DR_CopyBackups.sql
This procedure is created to copy the backups to the secondary servers. This SP will be created on each secondary database servers.
This procedure is created to copy the backups to the secondary servers. This SP will be created on each secondary database servers.
DR_RestoreBackups.sql
This procedure is created to restore the backups to the secondary database servers. Databases are restored in NO-RECOVERY mode using this procedure. This SP will be created on each secondary database servers. Using this procedure we can restore FULL, DIFFERENTIAL & LOG backups.
To restore the backup in recovery mode.
This procedure is created to restore the backups to the secondary database servers. Databases are restored in NO-RECOVERY mode using this procedure. This SP will be created on each secondary database servers. Using this procedure we can restore FULL, DIFFERENTIAL & LOG backups.
To restore the backup in recovery mode.
DR_RestoreBackups - RM.sql
This procedure is created to restore the backups to the secondary database servers. Databases are restored in RECOVERY mode using this procedure. This SP will be created on each secondary database servers as per the requirement. Using this procedure we can restore only the FULL & DIFFERENTIAL backups. During the restore, all the processes running on the databases will be killed for smooth restore.
This procedure is created to restore the backups to the secondary database servers. Databases are restored in RECOVERY mode using this procedure. This SP will be created on each secondary database servers as per the requirement. Using this procedure we can restore only the FULL & DIFFERENTIAL backups. During the restore, all the processes running on the databases will be killed for smooth restore.
DR_DeleteBackups.sql
This procedure is created to delete all the previous day backups from the sharing folder & local folder. The above table is also purged during this process.
This SP will be created on one secondary database servers.
This procedure is created to delete all the previous day backups from the sharing folder & local folder. The above table is also purged during this process.
This SP will be created on one secondary database servers.
DR_DeleteBackups - RM.sql
This procedure is created to delete all the previous day backups from the local folder. The above table is also purged during this process. This SP will be created on all remaining secondary database servers.
This procedure is created to delete all the previous day backups from the local folder. The above table is also purged during this process. This SP will be created on all remaining secondary database servers.
Shrinking Log Files_SimpleFull.sql
This script is executed before full backups to truncate log from all the databases.
This script is executed before full backups to truncate log from all the databases.
Similar to Logshipping, all the backup scripts will be executed on Primary Server and backup info will be stored in the table (BRInfo).
BRInfo will act as master table for copy, restore & deleting backups.
BRInfo table should be created on each secondary server. And copy, restore & delete scripts will be executed separately on each secondary server.
Please check the attached scripts. Feel free to post any suggestion that you have for improvements. Thanks!
Primary-Server-Scripts.zip
Secondary-Server-Scripts-for-NoR.zip
Secondary-Server-Scripts-for-Rec.zip
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)