By: Bhaskar Sarma | Comments (25) | Related: > Availability Groups
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
![Disaster Recovery solution with AlwaysOn Availability group on SQL Failover Cluster Instance](/tipimages2/3150_1.gif)
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:
![Windows Server Failover Cluster (WSFC)](/tipimages2/3150_01.gif)
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).
![Now let's look at steps to add a standalone SQL instance and setup SQL 2012 High Availability solution](/tipimages2/3150_02.gif)
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.
![Add Failover Clustering feature to the node](/tipimages2/3150_03.gif)
Come back to your primary data center CL_1/CL_2 Failover Cluster Manager. Right click on Nodes > Add Node...
![Install SQL 2012 Enterprise Edition on SA_1 as a standalone instance](/tipimages2/3150_04.gif)
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.
![Yes, you are going to add SA_1 node to the existing WSFC on primary datacenter.](/tipimages2/3150_05.gif)
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:
![After adding SA_1 node successfully to WSFCPrimary cluster your Failover Cluster Manager will look like](/tipimages2/3150_06.gif)
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 SA_1 is added into your WSFC - but this node is not part your Failover Cluster Instanstance](/tipimages2/3150_07.gif)
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
![Now go to](/tipimages2/3150_08.gif)
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:
![you will receive below error while establishing AG Group from SQL Server Management Studio](/tipimages2/3150_09.gif)
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:
![Now you should be able to enable High Availability on SA_1 sql node](/tipimages2/3150_10.gif)
And on your Failover Cluster Manager you will see the new AG_1 group:
![And on your Failover Cluster Manager you will see new AG_1 group](/tipimages2/3150_11.gif)
Scenario 2 - Failover Cluster in Primary and Availability Group on a Failover Cluster in Secondary
Now look at the second scenario:
![Now look at second scenario: FCI in primary data center and availability group for DR on another FCI](/tipimages2/3150_2.gif)
- 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.
![Your Failover cluster Manager will look like below](/tipimages2/3150_13.gif)
Next Steps
- Learn more about SQL Server 2012 AlwaysOn Availability Group here
- Learn more about High Availability and disaster recovery design pattern here
About the author
![MSSQLTips author Bhaskar Sarma](/images/BhaskarSarma.jpg)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips