Sql-server – SQL Server Multi-Site HA/DR Solution

availability-groupsclusteringdisaster recoveryhigh-availabilitysql server

I want to deploy Multi Site SQL Server HA/DR Solution. I am not sure if I use FCI/AG. In my case whenever the wan link is down at Site1 my ISP is redirecting my wan traffic to Site2.

I was thinking of deploying Node1 at Site1 and Node2 at Site2 and File Share Witness at Site3/Cloud, also making sure SAN – SAN Async Replication in place. So whenever my Site1 wan is failing I want the SQL at DR site to be active.

So Kindly Suggest what kind of deployment will be best for this case.

I have SQL Server 2012 Enterprise and Windows Server 2012 R2 Standard. I have to chose whether AOFCI or AOAG.

I have experience with 2 Node SQL Cluster but in single site. But this is new application and I want to have multi-site (multi-subnet) deployment. I have very less knowledge about AOAG but willing to learn of course.

Suppose if I chose to implement FCI i will have Node1 at Site1 with one shared disk1 from SAN1 and Node2 at Site2 with same disk1 from SAN2 but off-line. There will be SAN-SAN asynchronous replication in place and I will use Site3/Cloud for File Share Witness. Will this be the good solution ? Or you suggest AOAG assuming Node1(primary) and Node2(secondary replica – synchronous) at Site1 and Node3(secondary replica – asynchronous) at Site2.

Best Answer

Lets make sure we understand some of the terms involved.

  • WSFC=Windows Server failover cluster
  • FCI=failover clustering instance
  • AOAG=Always On Availability Groups

I suggest you make you requirement absolutely clear before you start implementing. As @John suggested version of SQL Server and Windows Operating system can detect most of your decision.

You definitely need WSFC, does not matter what configuration you chose. For multi site (assuming multi subnet) WSFC follow this document.

Then you have 3 options:

  1. FCI
  2. AOAG
  3. FCI+ AOAG

For creating multi-subnet Failover cluster instance follow this document.

Creating AOAG between multi subnet is not an issue as long as WSFC is configured correctly. Issue mostly arises because listener is not configured correctly. See this for details.

Now you need to decide if you need to combined solution of step 1 and 2 meaning FCI+AOAG. This document will give a good start.

For storage level replication you definitely want to discuss with your vendor. In general you can do that when you are deploying AOAG. Because AOAG will read transaction log and write to secondaries. Most storage level replication that I am aware off, you will need disks to be offline for implementing block level changes.

Here are few more resources that can help you.

  1. https://www.sqlhammer.com/ has bunch of example of different configuration by Derik Hammer.
  2. https://www.mssqltips.com/sqlservertip/4597/configure-sql-server-alwayson-availability-group-on-a-multisubnet-cluster/
Related Question