Proven SQL Server Architectures for High Availability and Disaster Recovery

SQL Server 2005 and SQL Server 2008 include many technologies that can be used to minimize downtime and maximize data protection so that database administrators can ensure smooth operation, continuous access to business critical data, and meet availability levels according to various service level agreements.

Sometimes high-availability and disaster-recovery architectures are unfortunately designed without considering the necessary business requirements—possibly there is already an incumbent technology, or the designers are familiar with a certain technology and choose it as the basis for a new architecture. This choice, when coupled with a lack of understanding of the capabilities of the various high-availability and disaster-recovery technologies, can lead to an architecture that fails to meet the business needs.

It is imperative that the high-availability and disaster-recovery requirements of the business are the drivers when evaluating which technologies are suitable as part of the architecture. The two major business needs to consider are:

· The duration of acceptable application downtime, whether from an unplanned outage or from scheduled maintenance/upgrades (i.e. the defined Recovery Time Objective—RTO).

· The ability to accept potential data loss from an outage (i.e. the defined Recovery Point Objective—RPO).

There is an existing whitepaper, “High-Availability with SQL Server 2008” (available at https://msdn.microsoft.com/en-us/library/ee523927.aspx), that contains information about each of the high-availability technologies in SQL Server 2008, as well as further links to other whitepapers and technical resources. It also describes how to evaluate business requirements and technical/non-technical limitations to help choose appropriate technologies.

However, there is a lack of information regarding proven architectures and real-life customer deployments, where the high-availability and disaster-recovery architecture was chosen after careful requirements analysis and technology evaluation.

We have published a new SQL Server High Availability Architecture white paper based on our largest customers worldwide https://download.microsoft.com/download/5/B/D/5BD13FFA-5E34-4AE1-9AA0-C6E6951B8FC8/SQL Server 2008 R2 High Availability Architecture White Paper.docx. This whitepaper describes five commonly-deployed architectures using SQL Server 2005 and SQL Server 2008 that are designed to meet the high-availability and disaster recovery requirements of enterprise applications.

Nick.