SQL Server 2012 AlwaysOn High Availability and Disaster Recovery Design Patterns


The following design patterns emerge as end-to-end HA+DR solution:

  • Using Multi-site Failover Cluster Instance (FCI) for local high availability and disaster recovery solution
  • Using Availability Groups (AG) for local high availability and disaster recovery solution
  • Using Failover Cluster Instance (FCI) for local high availability, and Availability Groups (AG) for disaster recovery solution

(1) 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 unit of failover for both local HA, and remote DR is SQL Server instance.
  • No requirement on database recovery model, as storage level replication is used for maintaining the remote copy of the data.
  • The DR copy of the data is not readable.

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.

(2) 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 unit of failover for local HA, and DR is the Availability Group (a group of one or more databases).
  • The database is required to be in the FULL recovery model.
  • The DR replica can be utilized as an Active Secondary (Readable Secondary ReplicasBackup on Secondary Replicas).

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.

(3) 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 unit of failover for local HA is the SQL Server instance.
  • The unit of failover for DR is the Availability Group (a group of one or more databases).
  • The database is required to be in the FULL recovery model.
  • The DR replica can be utilized as an Active Secondary (Readable Secondary ReplicasBackup on Secondary Replicas).

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.

 

 

 

Advertisements
This entry was posted in SQL SERVER 2012. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s