In this how-to tutorial, we will look at implementing multi-node, multi-site SQL 2012 AlwaysOn Windows Server Failover Cluster (WSFC) with two Failover Cluster Instance (FCI) across two geographically dispersed datacenters for High Availability (HA) and Disaster Recovery (DR). I am currently working on a project where we’re going to upgrade from previous version of SQL to SQL 2012 and take advantage of some of the new features introduced in SQL 2012, such as AlwaysOn Failover Cluster Instances, AlwaysOn Availability Groups, enhanced Windows PowerShell support, Replication and many others which the DBAs and other IT professionals will appreciate.
Before moving forward, there are number of hardware and software requirements which must be met in order to setup AlwaysOn cluster. For complete list of hardware and software requirements for installing SQL Server 20102, check out Microsoft Library and this technet article for Multi-Site failover cluster requirements/recommendations. In this article we will be using Windows 2008 R2 Enterprise with Service Pack 1 and SQL 2012 Enterprise Service Pack 1. For hardware requirements, in datacenter 01 hardare consists of HP blades with shared storage from EMC VMAX and in datacenter 02, we’re going to use Dell blades with Dell Compellent SAN shared storage. Your hardware may differ but the goal here is to show that you can certainly use different blades and storage solutions to set this up and do not need same hardware and have to depend on storage-array based replication.
I am not going to get into the basics of how to setup Windows Server and SQL failover cluster… this article assumes you’ve already done that many times and can setup, configure and deploy standalone Windows Server and SQL failover cluster and provision storage for the cluster without guidance.
Design and Architecture
This is a demo setup in lab. The names of servers, VNN, ip addresses, domain name, etc. have been changed to help you better understand the pattern and relationships between the resources and provisioned storage/clustered disks are temporary.
Both datacenters have their own domain controllers in same Active Directory domain, running windows 2008 R2 Enterprise edition. It is not a requirement but If you haven’t done so already, extend your Active Directory domain to datacenter 02. It will certainly help speed things up and allow local servers at secondary site to use local domain controllers.
DATACENTER 01 (Atlanta, GA)
- Active Directory Domain: domain.local
- Node A
- Name: SQL2012-02A
- IP Address: 192.168.1.141
- Node B
- Name: SQL2012-02B
- IP Address: 192.168.1.142
- Windows Server Failover Cluster
- Name: SQL2012-02-ABCD
- IP Address: 192.168.1.145
- Quorum: Node and File Share Majority (a clustered or non-clustered File Share at primary datacenter)
- SQL Failover Instance
- Name: SQL02AB
- IP Address: 192.168.1.146
- SQL Dual-Subnet AVG Listener
- Name: SQL2012-02L
- IP Address 192.168.1.147
- Storage/Clustered Disks
- AB Cluster Disk
Volume: S
- AB Cluster Disk
DATACENTER 02 (NYC, NY)
- Active Directory Domain: domain.local
- Node C
- Name: SQL2012-02C
- IP Address: 10.10.0.143
- Node D
- Name: SQL2012-02D
- IP Address: 10.10.0.144
- Windows Server Failover Cluster
- Name: SQL2012-02-ABCD
- IP Address: 10.10.0.145
- SQL Failover Instance
- Name: SQL02CD
- IP Address: 10.10.0.146
- SQL Dual-Subnet AVG Listener
- Name: SQL2012-02L
- IP Address: 10.10.0.147
- Storage/Clustered Disks
- CD Cluster Disk
Volume: S
- CD Cluster Disk
Notice the pattern between the two datacenters? I don’t know about you but I like it that way and makes it easy to retrieve information from memory. Here is what it looks like on a visio diagram
Setup the Environment
Once you have installed Windows 2008 R2 on all nodes, configured static ip addresses and patched. In each datacenter, make sure both nodes can see appropriate disks. On each node, click on Server Manager > Features and install “Failover Clustering” feature and “.NET Framework 3.5.1” features.
From primary datacenter, in this case, Node A, open Failover Cluster Manager > right click and “Create A Cluster” to setup Windows Server Failover Cluster, we’re going to name it SQL2012-02-ABCD with an IP Address of 192.168.1.145. I configured and added all the nodes to the cluster at the same time. If you have primary site nodes ready, you can just add those and add the nodes in secondary datacenter later (often network/WAN connectivity isn’t there so you may not be able to add all nodes) by going into Failover Cluster Manager > Nodes > right click and “Add Node” to add more nodes to the cluster.
Now that you have the Windows Failover Cluster ready, click on the Cluster name, SQL2012-02ABCD and expand “Cluster Core Resources,” here you have only one IP address listed for primary datacenter. Now we add the cluster ip address for the secondary datacenter. Right click on the “Name: SQL2012-02ABCD” and click “Properties” click on “Add” and specify 10.10.0.145 as the IP address for datacenter 02.
Next, you will need to click on each IP Address and change possible owners. Right click > Properties > Advanced Policies > and check only the nodes which belong to that subnet.
Do the same for Nodes A and B IP address. With geographically dispersed datacenters, network latency is always a concern. You can adjust Cluster Heart Beat Timeout settings to address connection and latency issues. Windows Failover cluster, by default, will fail when 5 pings are lost. You can use cluster.exe command to adjust the threshold. More details here at MS Technet site.
For shared storage, you should have two available disks under “Storage” which we will add to the cluster during the SQL 2012 failover cluster setup.
On Node A, lets install SQL. Insert SQL 2012 installation media, click on Installation > “New SQL Server failover cluster installation” go through the wizard. The SQL features you select will vary from organization to organization but most important things to keep in mind are:
- Cluster Network Configuration: Make sure to name it appropriately with the relevant datacenter’s IP address/subnet.
- Server Configuration: In my setup, we’re going to use named instances, yours maybe different. So, here we ll have SQL02AB as the name
- Database Engine Configuration: Dataroot directory should be the S drive and path for user database directory will need to change to: S: \ SQL02ABCD \ MSSQL \ Data
We MUST use same directory path in both datacenters otherwise AlwaysOn AVG will not work.
After you finish installing SQL on Node A, go ahead and start installation on Node B and “Add node to a SQL Server failover cluster” to add Node B to the SQL02AB SQL instance.
Now that you have SQL Failover Clustered Instance installed, its time to change some important items so that FCI in datacenter 01 does not try to failover to nodes in datacenter 02. Select “SQL02AB” instance under “Service and applications” right click and click on “Properties,” under General tab, change preferred owners: to Node A and B only, move them up. You will need to do this for each resources under the SQL02AB instance. So, “Server Name” and “Disk Drives” and “Other resources” items will need to be adjusted.
Next steps is to enable AlwaysOn High Availability. Open SQL Server Configuration Manager > click on > SQL Server Services > SQL Server (SQL02AB) > right click > Properties > “AlwaysOn High Availability” tab. Once enabled, restart the SQL Server service.
With primary site, datacenter 01 ready, you will need to repeat these steps for Node C and Node D at secondary site, in datacenter 02. You then end up with 4 nodes and 2 SQL failover clustered instances (SQL02AB and SQL02CD).
Configure Availability Group
For sake of simplicity, we’re going to start from Node A, as it is the current owner of SQL02AB SQL instance in primary datacenter. Open Microsoft SQL Server Management Studio, connect to the local instance “SQL02AB\SQL02AB” select the “AlwaysOn High Availability” folder and right click and then click on “New Availability Group Wizard…” Here are some screenshots to walk you through the wizard:
For dual-subnet AVG Listener to work properly, create a DNS “A” record and specify name and IP address and Check the Box “Allow any authenticated user to update DNS Records with the same owner name“so that when AVG fails over to datacenter 02, the cluster can update DNS records to point to the appropriate IP address. Here is AVG failed over and responding to new IP address screenshot:
Of course, it would not be complete without a final successful validation of the new AVG:
You now have an AVG setup within SQL. To manage AlwaysOn AVG, you must do it from SSMS. Right Click on the “AlwaysOn High Availability” folder and click on “Show Dashboard” and you end up with this interface:
Failover
The replication between the two datacenters will be asynchronous commit mode. When its time to failover to the secondary datacenter, we will switch to “Synchronous commit” mode so that transactions have chance to catch up with the secondary replica and there is no data loss [warnings]. To change the availability mode, right click on the replica and click on “Properties.” To failover, open AVG Dashboard and click on “Start Failover Wizard” and go through the prompts:
Once you’ve completed the the failover wizard, AVG will failover to the secondary SQL instance in datacenter 02.
This was just a very brief how-to guide for quickly setting up multi-datacenter SQL failover cluster instance using AlwaysOn Availability groups. The multi-site/datacenter cluster with multiple FCIs is just one of the many possible scenarios. The introduction of Availability Groups in SQL 2012 has made it possible to have number of different deployment variations of AlwaysOn. In future articles, we may get into troubleshooting AVG issues and diagnostics.
If you are implementing this for your organization, make sure you follow hardware and software requirements and best practices when setting up the cluster. If you have any questions or comments, be sure to post in our forums or contact me directly.