High Availability and Disaster Recovery (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions
Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.
Microsoft SQL Server 2008 R2 (as well as SQL Server 2008 and SQL Server 2005) offers a variety of technologies for customers to pick from to meet the high availability and disaster recovery (HA/DR) requirements of their application portfolio. Customers typically adopt at least two of these technologies to accomplish their HA/DR goals; Tier-1 deployments in particular, which are often either mission or business critical, generally require more than one technology.
Examples referenced in the sections that follow show that customers can achieve up to 99.99% or 99.999% availability, even when upgrading to new versions of SQL Server and/or upgrading their hardware. Furthermore, Windows security patching can be completed while meeting availability goals.
Availability goals can be met with a variety of configurations. A popular architecture combines failover clustering for HA and database mirroring for DR. Additionally, log shipping can be added to achieve tertiary site DR protection. (More information about these options can be found later in this document.)
The following resources provide examples of customer scenarios for implementing SQL Server HA/DR, in addition to general SQL Server HA/DR reference material. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)
Slide 8 in the PowerPoint presentation Proven Customer Deployed Architectures & Scenarios For SQL Server HA/DR1 provides customer requirements and suggested HA/DR architectures.
The white paper Proven SQL Server Architectures for High Availability and Disaster Recovery2 shows the details of five commonly used architectures:
Failover clustering for HA and database mirroring for DR.
Synchronous database mirroring for HA/DR and log shipping for additional DR.
Geo-cluster for HA/DR and log shipping for additional DR.
Failover clustering for HA and storage area network (SAN)-based replication for DR.
Peer-to-peer replication for HA and DR (and reporting).
- Failover clustering for HA and database mirroring for DR.
The white paper SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability3 describes best practices that customers, system integrators, and partners can use to design and install more reliable, highly available SAP implementations using SQL Server 2005. An update to the SAP white paper is available in the PowerPoint presentation Microsoft SQL Server: The Platform for SAP.4
The white paper Failure Is Not an Option: Zero Data Loss and High Availability5 describes how to combine SQL Server high-availability technologies to provide a zero data loss, highly available solution for database applications involving financial transactions.
The web pages SQL Server 2008: High Availability—Always On6 and Selecting a High Availability Solution7 are good general references and may be helpful, depending on your experience level.
Case Studies and References
Customers have successfully configured SQL Server to achieve up to 99.999% availability. Examples of successful architectures are described in the following case studies and white papers:
The white paper High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study8 describes how ServiceU, a provider of online and on-demand event management software, successfully uses failover clustering, asynchronous database mirroring, and log shipping.
The case study bwin: Global Online Gaming Company Deploying SQL Server 2008 to support 100 Terabytes9 describes how bwin uses database mirroring and log shipping.
The white paper Using Replication for High Availability and Disaster Recovery10 describes how a travel enterprise uses peer-to-peer replication and log shipping.
The white paper SQL Server High Availability and Disaster Recovery for SAP Deployment at QR: A Technical Case Study11 describes how a Queensland Rail SAP deployment uses synchronous database mirroring within a data center and log shipping to a remote data center.
Questions and Considerations
This section provides questions and issues to consider when working with your customers.
Understand the business motivations and regulatory requirements that are driving the customer's HA/DR requirements. Understand how your customer categorizes the workload from an HA/DR perspective. There is likely to be an alignment between the needs and categorization.
Ask for both the recovery time objective (RTO) and the recovery point objective (RPO) for different workload categories, for both a failure within a data center (local high availability) and a total data center failure (disaster recovery). While RPO and RTO vary for different workloads because of business, cost, or technological considerations, customers may prefer a single technical solution for ease in operations. However, a single technical solution may require trade-offs that need to be discussed with customers so that their expectations are set appropriately.
Understand the network infrastructure at the primary and the planned (or proposed) DR site and the communication links between the two—these are important factors in selecting an acceptable architecture. Also consider the planned maintenance operations (regular backup and index maintenance) while selecting the architecture; the constraints for maintenance workloads may be greater than those for regular production workloads because of the time in which the maintenance operations must be completed.
Ask if there is an organizational preference for a particular HA/DR technology. Customers may have a preference because of previous experiences, established operational procedures, or simply the desire for uniformity across databases from different vendors. Understand the motives behind a preference: A customers' preference for HA/DR may not be because of the functions and features of the HA/DR technology. For example, a customer may decide to adopt a third-party solution for DR to maintain a single operational procedure. For this reason, using HA/DR technology provided by a SAN vendor (such as EMC SRDF) is a popular approach.
Understand the implications of both hardware and software maintenance (including Windows security patching) when discussing the design and adoption of an HA/DR solution. Database mirroring is often adopted to minimize the service disruption that can be caused by maintaining hardware and software.
Review the following material for a better understanding of HA/DR options. (This section may be unnecessary if you have extensive prior experience with SQL Server HA/DR options.)
SQL Server 2008 R2 offers several HA/DR options:
12With failover clustering, the nodes share disks, but only a single node has access to the database at a time. It is possible to install additional SQL Server failover cluster instances across the nodes; however, this configuration cannot be used to re-direct workloads for a single database (for example, separating reads from writes).
13 Database mirroring is used both for high availability and disaster recovery. Database mirroring also provides read access to the data by making it possible to create a database snapshot on the mirror. The read workload can run against the database snapshot. While the creation of database snapshots on a mirror partner has the potential to be useful, very few customers are deploying it for the following reasons:
The name of the database in the snapshot is different, which requires making a change in order to connect.
Database mirroring provides a static snapshot of the database. New snapshots have to be created (with potentially new names unless a redirection mechanism is also configured) to get more current data. In other words, accessing current data on the mirror is not practical. (This challenge is addressed in the next release of SQL Server, code named “Denali.”)
No out of the box solution is provided for refreshing database snapshots, particularly when users are connected to the snapshots. It is possible to automate this process however.
Many applications are not designed with read-only databases in mind. For example, even when a database snapshot is available, an application might update internal tables when a user connects to the database, even if the user is only performing reporting.
The key takeaway is that database mirroring offers high availability and disaster recovery, but it is not practical for a readable secondary solution for current data.
14 Similar to database mirroring, the log shipping feature can be used to set up procedures that enable read-only access at the backup site so that secondary sites can receive log backups. However, because of latency and deployment challenges, customers may find it impractical to use the secondary sites for real-time reporting.
15 Transactional and peer-to-peer transactional replication are practical and commonly deployed solutions for offloading read workloads. However, they do not ensure zero data loss for high availability and disaster recovery. The challenges for very large deployments include:
Replication can be complex to monitor and troubleshoot.
Replication may require schema changes (for example, adding a unique key).
Replication may have very high transaction throughput.
- Failover clustering 12With failover clustering, the nodes share disks, but only a single node has access to the database at a time. It is possible to install additional SQL Server failover cluster instances across the nodes; however, this configuration cannot be used to re-direct workloads for a single database (for example, separating reads from writes).
Following are the full URLs for the hyperlinked text.
1 Proven Customer Deployed Architectures & Scenarios for SQL Server HA/DRhttp://ecn.channel9.msdn.com/o9/te/NorthAmerica/2010/pptx/DAT401.pptx
2 Proven SQL Server Architectures for High Availability and Disaster Recoveryhttp://sqlcat.com/whitepapers/archive/2010/06/07/proven-sql-server-architectures-for-high-availability-and-disaster-recovery.aspx
3 SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalabilityhttp://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/sap_sql2005_best%20practices.doc
4 Microsoft SQL Server: The Platform for SAPhttp://ecn.channel9.msdn.com/o9/te/NorthAmerica/2010/pptx/DAT314.pptx
5 Failure Is Not an Option: Zero Data Loss and High Availabilityhttp://sqlcat.com/whitepapers/archive/2010/11/03/failure-is-not-an-option-zero-data-loss-and-high-availability.aspx
6 SQL Server 2008: High Availability—Always Onhttp://www.microsoft.com/sqlserver/2008/en/us/high-availability.aspx
7 Selecting a High Availability Solutionhttp://msdn.microsoft.com/en-us/library/bb510414.aspx
8 High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Studyhttp://sqlcat.com/whitepapers/archive/2009/08/04/high-availability-and-disaster-recovery-at-serviceu-a-sql-server-2008-technical-case-study.aspx
9 bwin: Global Online Gaming Company Deploying SQL Server 2008 to support 100 Terabyteshttp://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001470
10 Using Replication for High Availability and Disaster Recoveryhttp://sqlcat.com/whitepapers/archive/2009/09/23/using-replication-for-high-availability-and-disaster-recovery.aspx
11 SQL Server High Availability and Disaster Recovery for SAP Deployment at QR: A Technical Case Studyhttp://sqlcat.com/whitepapers/archive/2010/09/20/sql-server-high-availability-and-disaster-recovery-for-sap-deployment-at-qr-a-technical-case-study.aspx
12 Getting Started with SQL Server 2008 R2 Failover Clusteringhttp://msdn.microsoft.com/en-us/library/ms189134.aspx
13 Database Mirroring Overviewhttp://msdn.microsoft.com/en-us/library/ms189852.aspx
14 Log Shippinghttp://msdn.microsoft.com/en-us/library/bb895393.aspx