Disaster recovery, as the name suggests, is recovering from all kinds of disasters and organizations have been adopting this as a popular method to safeguard their infrastructure. Set up and timely execution of a disaster recovery plan has been helping organizations recover data losses from manual or inevitable disasters. It is a given that the nature of every organization is different. This is why customized disaster recovery policies are touted to reflect the specific requirements of an organization.
Disaster recovery is a must-have for organizations and the primary driving factor here is business continuity for their mission-critical applications. These applications generate revenue, provide continuous dependable services, and support life-preserving functionality. Therefore, it is of utmost importance for organizations that their applications are highly available and are hosted on the cloud.
Best Practices for SQL-based Disaster Recovery
Always On Availability Groups and Basic Availability Groups
An availability group (AG) supports replicated databases, which are copied to other SQL server instances. Always On Availability Groups require a Windows Failover Cluster and it is necessary that servers are in the same Domain and AD.
| Tasks Overview | Duration |
| Creating service accounts, shared folders, and prerequisites. | 2 hrs |
| Configuring the AG or listener. | 2 hrs |
| Adding the database to AG (for a 100GB database) | 5 hrs |
(Please note that time duration may vary depending on the size)
Pros of Always On Availability Groups
- Real-time data synchronization.
- Availability of options in the case of automatic failover, manual failover, or forced manual failover.
- Possibility of having a readable secondary server.
- Possibility of having multiple secondary server replicas.
Cons of Always On Availability Groups
- Always On Availability Groups are provided only in the SQL Server Enterprise edition.
- Basic Availability Groups are available in the SQL Server Standard edition. Only one database can be stored in one availability group.
- Basic Availability Groups require you to choose between synchronous replication for HA or asynchronous replication for DR.
Database Mirroring
Database mirroring involves a couple of SQL instances, among which one acts as the primary and the other one is mirrored. In some cases, there can be additional instances, which act as witnesses.
| Tasks Overview | Duration |
| Creating service accounts and other prerequisites. | 1 hr |
| Backing up and restoring the database to a secondary server (for a 100GB database). | 5 hrs |
| Creating a mirrored instance and adding the database to the same. | 1 hr |
(Please note that time duration may vary depending on the size)
Pros of Database Mirroring
- Real-time data synchronization.
- Availability of options in the case of automatic failover and manual failover.
Cons of Database Mirroring
- Secondary server in a database mirroring is not readable.
- Database mirroring will be deprecated in upcoming SQL versions.
- Network glitches might cause latencies.
Log Shipping
Log shipping is generally used for creating incremental backups. It helps in copying and restoring data to a secondary server at a specific frequency.
| Tasks Overview | Duration |
| Creating service accounts, shared folders, and other prerequisites. | 1 hr |
| Complete backup, copying file, and restoring the database to the secondary server (for a 100GB database). | 5 hrs |
| Creating log shipping and adding the database to the same. | 1 hr |
(Please note that time duration may vary depending on the size)
Pros of Log Shipping
- Having a readable secondary server is possible.
- Having more than one secondary server is possible.
Cons of Log Shipping
- There is no support in the case of automatic failover.
- All logs are synchronized at an interval of nearly 15 minutes by default.
SQL Server Failover Clustering
SQL Server failover clusters are created using a group of servers that run cluster-enabled applications and share the storage with multiple SQL instances.
| Tasks Overview | Duration |
| Based on Windows Failover cluster, installing SQL Server cluster, selecting the disk, and networking during installation. | 4 hrs |
(Please note that time duration may vary depending on the size)
Pros of SQL Server Failover Clustering
- Increased SQL Server Availability
Cons of SQL Server Failover Clustering
- Not optimal for Disaster Recovery.
- Zero downtime cannot be achieved.
- As the disks are shared, SQL server failover clusters do not protect the data.
To Conclude
Business continuity is an important focus area among organizations to plan, design, and implement business-critical systems. When organizations bring data to the mix, it becomes mandatory to ensure business continuity. SQL Server ensures intelligent performance, robust security, and high availability to Windows, Linux, and containers. Implementing SQL Server Disaster Recovery Strategies can help organizations tackle data workloads ranging from BI and AI to data warehousing.
Recommended reading:
- MySQL Disaster Recovery – All You Need to Know
- 4 Approaches of Backup and Disaster Recovery Explained using Amazon Cloud