Before we start the discussion on SQL 2012 AlwaysON let me define the terminologies introduced with SQL 2012. As we all know with SQL 2012, we have AlwaysON solution which provides us with a HADR Solution.
SQL 2012 AlwaysON can be configured in 2 modes viz
- AlwaysON Failover Clustering Instance (FCI): This is the traditional Failover Clustering Instance which we had with SQL Server since SQL 6.5. However the name is changed to AlwaysON FCI. There are lot of enhancements introduced in FCI as well which is can be read in this blog .
- AlwaysON Availability Groups: This is introduced in SQL 2012 which allows us to mirror a group of database to another instance which allows us to have upto 4 secondary some of which can be configured in Read-Intent mode thereby allowing to route the readable workload to secondary.
So with SQL 2012, we have the following High Availability (HA) and Disaster Recovery (DR) Solutions and I have drawn the following comparison matrix which might help us to choose the best solution to setup HADR solution for your environment
You might have seen this matrix quite no. of time but I have specific intent of the matrix to explain the rest of the post.
Note: I am not including replication here as a HADR solution due to the complexity of replication architecture
|AlwaysON FCI||AlwaysON Availability Groups||Database Mirroring||Log Shipping|
|Provides High Availability||
|Provides Disaster Recovery||X||X||
|Zero Data Loss Possible (RPO)||
From the above matrix AlwaysON Availability Group is a clear winner here, since it meets all the criteria which one would need to for HADR solution and to utilize the secondary for read/reporting workloads.
However one of the major concerns with Synchronous AlwaysON Availability Group and an important one is the performance overhead it introduces due to synchronous commit on the remote secondary replica and the dependency of the performance on network. The only technique which doesn’t impact the performance of the sql server with high availability is AlwaysOn FCI but it doesn’t serve as a DR solution and doesn’t provide readable solution.
Now, if there is a requirement to have a HA solution without performance impact and a DR solution which provides readable secondary can be achieved by the combination of AlwaysON Faliover Clustering Instance (HA Solution) and Asynchronous AlwaysON Availability Group (DR Solution) with readable secondary.
The above configuration gives us best of both the worlds, AlwaysON FCI serving a no performance overhead High Availability Solution while async readable secondary availability replica serves as a DR solution with readable secondary useful to offload reporting workloads.
However the configuration of AlwaysON FCI in conjuction with AlwaysON Availability Groups is not as simple as the above diagram looks. This is because to achieve above configuration we have the following considerations
- All the 3 nodes should be part of the same windows server failover cluster and should be part of the same domain. This is a mandate both for AlwaysON FCI and Always Availability groups.
- Node 1 and Node 2 are in the same site while Node 3 is at geographically dispersed site.
- Node 1 and Node 2 should have automatic failover while failover to Node 3 should be manual since it is an async secondary replica.
- AlwaysON FCI Group should be part of the same sql instance while AlwaysOn Availability Group requires a separate SQL instance.
- The shared storage between Node 1 and Node 2 shouldn’t be visible to Node 3 while the storage of Node 3 shouldn’t be visible to Node 1 and Node 2.
To summarize we need the following
- One Windows Server failover Cluster consisting of 3 nodes in same domain.
- Shared Storage between Node 1 and Node 2 while Node 3 requires a separate storage.
- Automatic failover between Node 1 and Node 2 but no automatic failover to Node 3.
- Two SQL Server Instances.
Points 2 and 3 was something which was difficult to achieve with traditional windows server failover clustering. This is because with Traditional WSFC we could have only 2 types of configuration
Shared Storage visible to all the Nodes
Majority Node set or Geographical dispersed clustering
However, for our requirement we need the following setup. Shared Storage between Node 1 and Node 2 while separate storage for Node 3 –> Asymmetric storage
The above configuration is called Asymmetric Storage since the shared storage is partially visible to subset of the nodes. This setup was not possible with traditional clustering but starting Windows 2008 R2 SP1 and Windows 2008 (Hotfix) the asymmetric storage is supported by Windows Server Failover Clustering.
This support was introduced by Windows Team to support such kind of scenarios as required by SQL AlwaysON setup discussed above.
I hope after reading this post, you would have clarity on why the asymmetric storage concept is introduced by WSFC and when will it be required for AlwaysON configuration.
If you like my posts as much as I like writing it for you, please considering sharing it with others 🙂
Hope this helps !!!
Premier Field Engineer
very informative article…thanks for sharing your experience.
Very useful Article. Thank you very much for sharing.try to share more Article..
Great article and diagram. I want to set something up similar but want 2 secondary readable replicas. One replica would reside at the main site for queries and reporting and the second replica would be in a different data center for DR purposes. Would I have to install a 3rd instance of SQL server on node 2 of the WSFC (main site) to achieve this? What’s the optimal configuration to have this type of setup?
I forgot to include I also want to nodes 1 & 2 to be FCI.