High Availability and Disaster Recovery (DW)---a Technical Reference Guide for Designing Mission-Critical DW 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.
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
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 Study5 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 Terabytes6 describes how bwin uses database mirroring and log shipping.
The white paper Using Replication for High Availability and Disaster Recovery7 describes how a travel enterprise uses peer-to-peer replication and log shipping.
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.
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.
To design and adopt an HA/DR solution it is also important to understand the implications of applying maintenance to both hardware and software (including Windows security patching). Database mirroring is often adopted to minimize the service disruption to achieve this objective.
Following are the full URLs for the hyperlinked text:
1 Proven Customer Deployed Architectures & Scenarios for SQL Server HA/DR http://ecn.channel9.msdn.com/o9/te/NorthAmerica/2010/pptx/DAT401.pptx
2 Proven SQL Server Architectures for High Availability and Disaster Recovery http://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 Scalability http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/sap_sql2005_best%20practices.doc
4 Microsoft SQL Server: The Platform for SAP http://ecn.channel9.msdn.com/o9/te/NorthAmerica/2010/pptx/DAT314.pptx
5 High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study http://sqlcat.com/whitepapers/archive/2009/08/04/high-availability-and-disaster-recovery-at-serviceu-a-sql-server-2008-technical-case-study.aspx
6 bwin: Global Online Gaming Company Deploying SQL Server 2008 to support 100 Terabytes http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001470
7 Using Replication for High Availability and Disaster Recoveryhttp://sqlcat.com/whitepapers/archive/2009/09/23/using-replication-for-high-availability-and-disaster-recovery.aspx