Problem
AlwaysOn is the new high availability feature introduced in SQL Server 2012 which allows us to create multiple copies of a highly available databases using Windows Server Failover Cluster (WSFC). We wanted to implement this solution for our Disaster Recovery data center. We already have a SQL Failover Cluster Instance in our primary data center and management wants us to implement a second 2 node Failover Cluster Instance in our secondary data center as well. A new two node cluster located in our secondary data center was setup and handed over to me to setup an AlwaysOn availability group, but I could not create a new High Availability Group between the two failover cluster instances. What went wrong?
Solution
In order to implement SQL Server 2012 Availability Group on a Failover Cluster Instance (FCI) you don’t need to create two separate Windows Server Failover Clusters (WSFC). All nodes for an availability group must exist on a single WSFC within the same Active Directory domain, even between data centers.
In this tip we will look at how you can setup a disaster recovery solution between two datacenters combining your existing SQL Failover Cluster Instance and a standalone or a Failover Cluster Instance in a secondary data center.
Scenario 1 – Failover Cluster in Primary and Availability Group in Secondary Data Center
In the Primary Datacenter SQL_PV is the FCI instance and CL_1 and CL_2 are the two nodes. These two nodes have already joined the Windows Server Failover Cluster (WSFC). Our goal is to setup the SA_1 standalone SQL instance in the AG_1 group. For the Failover Cluster Manager on CL_1/CL_2 node you will typically see the below setup:
Now let’s look at steps to add a standalone SQL instance and setup a SQL 2012 High Availability solution:
Enable AlwaysOn High Availability on the existing FCI instance on CL_1/CL_2. Go to SQL Server Configuration Manager > SQL Server Services > SQL Server > Properties and on the “AlwaysOn High Availability” tab check the enable option. (After this step plan to restart the SQL Server service in failover cluster manager during a maintenance window).
Add the Failover Clustering feature to node (SA_1) that is located in the secondary data center that you are planning to add to the High Availability solution. You MUST connect to the server as a Windows domain user. Go to Start > Administrative Tools > Server Manager > Features and check the Failover Clustering option and follow the wizard.
Come back to your primary data center CL_1/CL_2 Failover Cluster Manager. Right click on Nodes > Add Node…
Yes, you are going to add the SA_1 node to the existing WSFC in the primary datacenter. That means that you need to have your secondary server or replica in the same active directory domain. After you add the replica into your primary datacenter WSFC it will look like a 3 node cluster.
Select the SA_1 node – click Next and follow the Cluster Validation option and add the SA_1 node to the existing WSFCPrimary cluster.
After adding the SA_1 node successfully to WSFC primary cluster your Failover Cluster Manager will look like this:
Now SA_1 is added into your WSFC – but this node should not be part of your Failover Cluster Instance. Right click on SQL Cluster group (SQL_PV), go to Properties and select Preferred Owner. Only CL_1 and CL_2 are preferred owners for SQL_PV.
Now go to “SQL Server” services on the Cluster instance under SQL_PV group and go to SQL Properties. Under Advanced Policies select only CL1_1 and CL_2 pair as a possible Owner of SQL Failover Cluster Instance
If you don’t remove the secondary node SA_1 as a possible owner of SQL FCI you will receive the below error while establishing the AG Group from SQL Server Management Studio:
Now you should be able to enable High Availability on the SA_1 SQL node in the secondary data center. Go to SQL Server Configuration Manager > SQL Server Services > SQL Server > Properties and under “AlwaysOn High Availability” tab check the enable option. (Plan to restart the SA_1 instance of SQL Server after enabling HA.)
You are now ready to setup the AG_1 availability group between the two data centers. (I don’t want to go through the steps on how to setup Availability Groups since this has already been discussed in detail in this earlier tip). After creating the AG_1 group on your primary SQL FCI “SQL_PV” you will see the below in SQL Server Management Studio:
And on your Failover Cluster Manager you will see the new AG_1 group:
Scenario 2 – Failover Cluster in Primary and Availability Group on a Failover Cluster in Secondary
Now look at the second scenario:
- Add two nodes CLS_1 and CLS_2 from secondary data center into the existing WSFC
- Configure Shared Storage for both CLS_1 and CLS_2 nodes
- Install FCI instance of SQL 2012 Enterprise edition using “New SQL Server Failover Cluster Installation” and join the other secondary node using Add node to a SQL Server failover Cluster
- Enable Always on Availability Group on newly installed FCI
- Define the possible owner for each SQL FCI
Your Failover cluster Manager will look like below.
Next Steps
