SQL Server: Choosing the Right Disaster Recovery and High Availability
There’s no shortage of options in SQL Server for meeting the high availability (HA) and disaster recovery (DR) requirements of your application portfolio, particularly with the release of SQL Server 2014. But how do you determine the right HA and DR solutions, especially if you’re moving mission-critical applications to the cloud? While the version and edition of SQL Server you employ will play an important role simply by virtue of the features available, there are also some key considerations to keep in mind.
First and foremost, your business requirements should drive your choice of HA and DR solutions. HA should be determined by the SLAs associated with your system. DR is usually driven by measurable characteristics such as recovery time objective (RTO) and recovery point objective (RPO).
- Recovery Time Objective (RTO) is the amount of time your application can be unavailable before it starts to impact your business operations. The shorter the downtime window, the more robust the solution needs to be. Solutions that depend on manual intervention, such as database mirroring, will have longer recovery times than automatic solutions. On the other end of the spectrum, high availability clustering can automatically complete a failover and restore operations quickly, allowing you to meet a recovery time objective of less than a minute.
- Recovery Point Objective (RPO) is the age of the data when service is restored and the amount of data loss that can be tolerated in the event of a failure. Depending on the solution and the time required to restore operations, there could be loss of data. If you are using log shipping, for example, you could lose any transactions that have not yet been shipped when the failure occurs. With clustering, the SQL Server services need to stop on one server and start on the other when the servers failover. Transactions attempted in that brief period could be lost, which means SQL Server will need to redo or undo any transactions that did not complete when the failover occurred. The recovery point for clustering will be much better than that with log shipping.
Regulatory requirements for data retention and availability, such as Sarbanes-Oxley, Gramm-Leach-Bliley and HIPAA, also fall under the category of business requirements.
Look at the complexity of your environment. The more databases you have to protect, the more you have to set up and manage. With solutions such as Availability Groups (AGs), database mirroring, replication or log shipping, each database that is replicated requires additional SQL Server processes to manage data movement to the other server. System databases are not protected with these options, so SQL logins and SQL Agent jobs need to be manually maintained in multiple places. All databases in the instance are protected with failover clustering. However, you would need shared storage for this solution. That’s not natively available between an on-premise and cloud server. All add overhead to SQL Server.
Consider the cost implications. For example, if you go with AGs, you will need to purchase the Enterprise edition of SQL Server. That entails a bigger investment than required for the Standard edition. (You can find information about the various SQL Server editions and licensing costs on the Microsoft site.)
You will also need to consider the cost of the cloud-based service you are implementing, as well for any additional bandwidth required for replicating data between your on-premise and cloud servers.
Consultants and Resources
There are numerous resources available from Microsoft, as well as other companies, to help guide you in choosing the HA and DR solutions best suited for your organization. If you lack SQL Server expertise within your company, you may also want to consider consulting a third-party service provider that specializes in SQL Server.