Author: Sanjay MishraContributors: Justin Erickson, Mike WeinerReviewers: Prem Mehra, Juergen Thomas, Steve Howard, Chuck Heinzelman, Jimmy May
SQL Server 2012 AlwaysOn provides flexible design choices for selecting an appropriate high availability and disaster recovery solution for your application. SQL Server AlwaysOn was developed for applications that require high uptime, need protection against failures within a data center (high availability) and adequate redundancy against data center failures (disaster recovery). http://msdn.microsoft.com/en-us/sqlserver/gg490638 provides an overview of high availability and disaster recovery solutions available in SQL Server 2012 AlwaysOn.
Through working with customers who are evaluating and deploying SQL Server 2012 AlwaysOn currently, we have seen the following design patterns emerge as end-to-end HA+DR solution:
We expect most of the SQL Server 2012 AlwaysOn deployments to match one of these design patterns or contain slight variations.
So, how do these three design patterns compare and contrast? This blog highlights the salient features of each of these design patterns. A detailed whitepaper on each of these will be developed and published in near future.
Multi-site Failover Cluster Instance (FCI) for HA and DR
The ability to implement a multi-site FCI as a HA and DR solution has been available in the SQL Server product for a number of previous releases, and many customers have been successfully using the solution (example: http://sqlcat.com/sqlCat/b/whitepapers/archive/2010/09/20/sql-server-high-availability-and-disaster-recovery-for-sap-deployment-at-qr-a-technical-case-study.aspx). In earlier versions of SQL Server, multi-site FCI required a stretch VLAN. SQL Server 2012 removes that requirement (along with a number of other improvements to the failover cluster instance technology) enabling multi-site FCI to be more commonly adopted as a HA and DR solution.
Multi-site FCI requires storage level replication (provided by the storage vendor) to maintain a copy of the databases at the DR site. Even though there are separate storage volumes at each site, to SQL Server, this looks like a Shared Storage solution. Other important attributes of this solution are:
The whitepaper http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/12/22/sql-server-2012-alwayson_3a00_-multisite-failover-cluster-instance.aspx provides architecture details and best practices for this solution.
Availability Group for HA and DR
Using Database Mirroring for local high availability, and combining it with Log Shipping for a disaster recovery solution is a popular deployment architecture prior to SQL Server 2012 (example: http://sqlcat.com/sqlcat/b/whitepapers/archive/2010/10/29/high-availability-and-disaster-recovery-for-microsoft-s-sap-data-tier-a-sql-server-2008-technical-case-study.aspx).
With SQL Server 2012, the Database Mirroring and Log Shipping solution can be replaced with an Availability Group solution with multiple secondaries.
This is considered a non-shared storage solution, as each SQL Server in the topology has its own copy of data and does not need to share storage. Other important attributes of this solution are:
The whitepaper http://sqlcat.com/sqlcat/b/whitepapers/archive/2012/06/22/alwayson-architecture-guide-building-a-high-availability-and-disaster-recovery-solution-by-using-alwayson-availability-groups.aspx provides architecture details and best practices for this solution.
Failover Cluster Instance for local HA and Availability Group for DR
Using Failover Cluster Instance for local high availability, and combing it with database mirroring for a disaster recovery solution is a popular deployment architecture prior to SQL Server 2012 (example: http://sqlcat.com/sqlcat/b/whitepapers/archive/2009/08/04/high-availability-and-disaster-recovery-at-serviceu-a-sql-server-2008-technical-case-study.aspx).
With SQL Server 2012, the Database Mirroring can be replaced with an Availability Group for the DR solution, while continuing to use Failover Cluster instance for local HA.
This architecture is a combined Shared Storage and Non-Shared Storage solution. Other important attributes of this solution are:
The whitepaper http://sqlcat.com/sqlcat/b/whitepapers/archive/2012/06/22/alwayson-architecture-guide-building-a-high-availability-and-disaster-recovery-solution-by-using-failover-cluster-instances-and-availability-groups.aspx provides architecture details and best practices for this solution.
A brief outline and comparison of three common HA/DR design patterns with SQL Server 2012 AlwaysOn is provided above. The detailed architecture guides on each of these design patterns are published as separate whitepapers and the links are provided above.
I've been reading about the updated database mirroring features in 2012 and am particularly interested in the readable mirror. Is this option possible with only 1 replica? I would also like to know if it is possible to use infiniband (and run IP over infiniband) with synchronous database mirroring in a local HA setup.
How would the user database schema update process for a mirrored setup with automatic failover?
Would I have to:
- verify that replica is synchronized
- break the mirror between replica and primary or
simply shut down the primary instance to simulate the failover to replica (and then break
the mirror between the new primary and the previous primary database)
- apply the table, procedure, view, data updates on the previous primary database
what happens next? Based on what I have read, I'm not sure it is possible to re-establish the mirror.
My main intent is to limit the amount of database downtime for schema updates as a result of application updates.
The application updates will be handled separately by doing a rolling upgrade of several application servers as each one or several are taken off of an NLB cluster.
Right now we tried to create a new way to DR that when we are doing large deployment, the DB still up and running.
May I know if SQL 2008 (not R2) mirror can help when we are trying to upgrade the SQL 2008 Schema (for example), or upgrade the Windows the SQL server installed on top from Windows 2008 R2 to Windows 2008 R2 SP1:
1) keep the connection from application while the DB is switching over to DR site.
2) Upgrade one of the DB first (primary or DR DB),
3) Once it is verify working good, then we switch over to DR DB to keep operation.
4) then we upgrade the primary DB and verify it is working good.
5) we bring the primary DB server as a primary again and leave the DR DB as usual.
6) upgrade done.
I know SQL server rolling upgrade can solve the problem IF THERE ARE NO SCHEMA CHANGE.
However, it seems that if the SQL needs schema change, the SQL service interrupt anyway, how can the SQL server always one feature help on this ?
For Database mirroring in Sql server 2012, is it possible in Windows Server 2008 Standard Edition?