Choosing the right high availability (HA) and disaster recovery (DR) solution for SQL Server depends on several factors such as the business requirements, budget, infrastructure, RTO and RPO targets, and data growth. In this answer, we will discuss some of the common SQL Server HA/DR solutions and the factors that companies should consider when evaluating and selecting the right solution for their business.
1. Always On Availability Groups
Always On Availability Groups (AG) is a SQL Server feature that provides high availability and disaster recovery capabilities by replicating databases to multiple secondary replicas. AGs support automatic failover and manual failover, depending on the configuration. Companies can use AGs for both local and remote DR scenarios.
When evaluating Always On AG, some of the factors to consider include:
- Compatibility: AGs require Enterprise Edition of SQL Server and Windows Server Failover Clustering (WSFC). Companies should ensure that their infrastructure supports these requirements.
- RPO and RTO: Always On AG allows for synchronous and asynchronous replication, providing different levels of RPO and RTO. Companies should consider their recovery time objectives and recovery point objectives when choosing between synchronous and asynchronous replication.
- Cost: Enterprise Edition of SQL Server is expensive, and the cost of licensing and hardware requirements for AGs can be significant.
- Scalability: Always On AG can handle up to eight secondary replicas for a particular primary replica. Companies with large databases and high data growth should consider whether AG can scale to meet their needs.
2. Failover Cluster Instances
Failover Cluster Instances (FCI) is a SQL Server feature that provides HA by clustering multiple SQL Server instances on different nodes of a Windows Server Failover Cluster. In the event of a failure, the cluster will automatically failover to a healthy node.
When evaluating FCI, consider the following:
- Compatibility: FCI requires SQL Server Enterprise Edition and Windows Server Failover Clustering. Companies should ensure that their infrastructure supports these requirements.
- Cost: Licensing and hardware requirements can be significant for FCI solutions.
- Complexity: FCI requires more complex infrastructure and maintenance than other HA solutions, including shared storage and cluster nodes.
- Scalability: FCI is limited to two nodes per cluster, so companies that require more nodes may need to consider other solutions.
3. Log Shipping
Log Shipping is a SQL Server feature that replicates transaction logs from a primary database to one or more secondary databases. This solution provides DR capabilities and can be set up asynchronously, meaning that the secondary database is not updated in real-time.
When evaluating Log Shipping, consider the following:
- RPO and RTO: Log Shipping provides a minimum of 15 minutes RPO, and it can take longer to bring the secondary database online in the event of a failure.
- Cost: Log Shipping is included with SQL Server and can be a cost-effective solution.
- Scalability: Log Shipping can handle large databases, but it is not as scalable as Always On AG.
4. Database Mirroring
Database Mirroring is a deprecated SQL Server feature that allows for SQL Server instances to mirror the database from the primary server to a secondary server. Database Mirroring can provide high availability and can be set up to be asynchronous.
When evaluating Database Mirroring, consider the following:
- Compatibility: Database Mirroring is compatible with Standard, Enterprise and Datacenter editions of SQL Server and can only mirror individual databases.
- RPO and RTO: Database Mirroring provides a minimum of 15 minutes RPO.
- Cost: Database Mirroring is included with SQL Server and can be a cost-effective solution.
- Availability: Database Mirroring has been deprecated since SQL Server 2012, and while it is still available in SQL Server 2017, Microsoft recommends moving to Always On Availability Groups for high availability scenarios.
Conclusion:
Choosing the right HA and DR solution for SQL Server is dependent on various factors such as cost, infrastructure, RPO and RTO targets, and data growth. By evaluating the above solutions’ compatibility, RPO, RTO, cost, scalability, and availability, companies can make the best choice for their business scenarios.