New High-Availability/Disaster-Recovery Solution Provided by SQL Server 2012

Introduction

The HA/DR architecture that's been widely adopted prior to SQL Server 2012 is Failover Cluster Instances + Database Mirroring, which leverages FCI for local high availability and DBM for disaster recovery (remote DR if the mirrored databases are located from a remote distance).

Besides the fact that database mirroring will be removed from the future version of SQL Server, the FCI + DBM architecture has several obvious limitations:

1. Only one mirror of the database
2. Only one active database at a time (can utilize database snapshot for offloading reporting to another server, but it won't be real-time and requires extra maintenance)

Let's take a deep dive in a similar solution provided by the SQL Server 2012 new feature Always On Availability Group.

Instead of database mirroring, availability groups (AG) is used for (remote) disaster recovery. It is referred as an FCI+AG architecture in our later introduction.

Similarly, the FCI+AG solution requires two or more nodes under the same WSFC plus at least one shared storage disk to form a failover instance. If a server level failure or even as small as a network glitch occurs, the other node will automatically take over so that the users can experience the minimal downtime without the data loss. There would also be no data synchronization overhead between two nodes since they are sharing the same storage for files and logs.

In addition to using a FCI for local high availability, Always On feature supports up to 4 secondary replicas to perform disaster recovery as well as read-only replicas for BI reporting usage. In our later illustration, another FCI will be set up as a read-only secondary replica, but keep in mind that you can always use stand-alone instances instead of FCIs for Availability Groups. I am doing so simply because I am using hyper-v virtual environment and the cost is my least consideration.

I strongly recommend you try setting up your own AlwaysOn Availability Groups for the learning purpose, as it isn't resource intensive (8G RAM and 200G disk would be more than enough) and you can acquire all the necessary products from MSDN subscription downloads (please contact ITS if you do not already have a MSDN subscription key).

Preparation

1. 1: Windows Server 2012 virtual machine for domain controller, DNS server, storage server (I combine 3 in 1 for the simplicity's sake)
2. Two same-size-storage virtually disks that are connected to DC server but consumed by two FCIs as data disks
3. One witness disks (referred as quorum disk) since we have even number of nodes so we need an extra witnesses to vote
4. 4 Windows Server 2012 virtual machines to act like four nodes to form two FCIs(1 primary 1 secondary)

Steps

The creation and configuration steps are quite straight forward. I will list all the necessary steps and highlight some noticeable point for comments.

1. Set up domain controller, DNS server and storage server. Create a domain and two virtual SCSI disks, then expose them to the four nodes.
2. Set up four node servers and make them all join the domain that's been newly created.
3. Create the WSFC with the two primary nodes and connect to the virtual storage disk 1.
4. Add the quorum disk to the WSFC and change the Quorum Configuration to Node and Disk Majority.
5. Install the SQL Server Failover Cluster Instance on the two primary nodes. Please note that you might need to manually install .NET 3.5 under Windows Server 2012 using PowerShell -dism to enable Net3.5 before SQL Cluster installation.
6. Join the two secondary nodes to the existing WSFC and install the SQL Server Failover Cluster Instance on the two secondary nodes.
7. Perform the initial backup and recovery for the databases (both full backup and log backup) in AG. Please note that WITH MOVE must be specified if different locations are used at secondary replicas.

   

8. Enable Always On Availability Group on both FCIs and create an Availability Group by joining two FCIs. Please note that there is no overlap possible owner for all for nodes, otherwise you will see the 'overlap possible owner' error in later steps. In my case, I have to manually de-select node HA1 and node HA2 for SQL Cluster DR.

   

9. Set the Readable Secondary to True if you want to offload the reporting burden to the secondary databases.
10. Validation. You should test the data synchronization, transaction performance, failover ability carefully.

References

• http://msdn.microsoft.com/en-us/library/ff877884.aspx
• http://msdn.microsoft.com/en-us/library/hh781257.aspx
• CodeProject