Adding SQL Server AlwaysOn Availability Groups to existing Failover Clusters

By:   |   Updated: 2014-02-21   |   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

Let's look at the first scenario: FCI in primary data center and availability group for DR on a standalone SQL instance.

Disaster Recovery solution with AlwaysOn Availability group on SQL Failover Cluster Instance

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)

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

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

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

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.

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

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

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

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

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

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

Scenario 2 - Failover Cluster in Primary and Availability Group on a Failover Cluster in Secondary

Now look at the second scenario: FCI in primary data center and availability group for DR on another FCI:

Now look at second scenario: FCI in primary data center and availability group for DR on another FCI
  1. Add two nodes CLS_1 and CLS_2 from secondary data center into the existing WSFC
  2. Configure Shared Storage for both CLS_1 and CLS_2 nodes
  3. 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
  4. Enable Always on Availability Group on newly installed FCI
  5. Define the possible owner for each SQL FCI

Your Failover cluster Manager will look like below.

Your Failover cluster Manager will look like below
Next Steps
  • Learn more about SQL Server 2012 AlwaysOn Availability Group here
  • Learn more about High Availability and disaster recovery design pattern here


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Bhaskar Sarma Bhaskar Sarma is a MSSQL DBA working in HealthCare since 2005.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2014-02-21

Comments For This Article




Tuesday, May 1, 2018 - 11:20:35 AM - DBA_Skills Back To Top (75831)

 

I believe that the scenario 2 is just not possible. You have given a very high level explanation.

I have the same infrastucture of scenario 2 with SQL 2014 installed on it. To bring all the 4 nodes under 1 wsfc you have to break the existing 

FCI on DR site. If you break the FCI you have to uninstall the existing SQL instance. Then if you add it back to the Primary site how you going to 

install SQL and maintain the sared storage?

This is a very critical component which you have not shed any info . This is possible with distributed AG supported on SQL 2016 and above.

Thank you,

 

 

 

 

Thursday, April 12, 2018 - 2:51:07 AM - Javeed Back To Top (75681)

 Hi Bhaskar,

Thanks for the excellent article.

Just want to know your inputs for my environment.

I have a 2 Node WFC with 5 SQL instnaces in primary DC.

We have a secondary DC available with 2 Node WFC

How can i go ahead with implementing AlwaysON AG with this environment.

 

 

Thanks

Javeed

 

Thursday, March 15, 2018 - 11:24:31 PM - Peter Lee Back To Top (75436)

For scenario 1, I have a doubt that, if the Primary DataCenter is down, only the SA_1 server in the Secondary DataCenter cannot form a WSFC quorum, the whold AG will become unaccessible, then how can it serves as a disaster recovery solution?

Wednesday, February 28, 2018 - 3:05:21 PM - farhan Back To Top (75321)

 

 what happens if i failover to the always on replica when my cluster servers breaks, will the application connection connects automatically or someone will need tp change the connection strings manually. 

Friday, May 6, 2016 - 2:02:55 PM - Jeamaire Drone Back To Top (41427)

 Thank you, just what I was looking for. The only variation in our objective is to have the secondary data center be in the cloud. 

 

Sunday, May 1, 2016 - 7:20:24 AM - ALI Back To Top (41372)

Hello 

Follow your great instruction to add AG to FCI 

But I Have some Issue 

I have  

1- CLUSTER (Node1 and Node2) AS Failover Cluster 

2- AG (NODE3) AS Read only Replica in same Location 

I join Node3 AS AG from Cluster add Database (+500 GB) TO AG Database 

Quorum setting node and disk majority with no vote to NODE3

First issue Primary Availability Mode asynchronous-commit mode?

Secondary Database Readable (yes)  Not (read Intended) cannot Read from Replica I Found wait type for this Query is (HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING)

 

Pls. If Any Solution For this Issues

 

Thanks 

Thursday, January 14, 2016 - 12:20:08 PM - VRP Back To Top (40424)

 Does this configuration works? we have similar setup, failover cluster in production environment with SQL cluster instance and standalone SQL server at DR site. Need to configure Always On as DR solution. When trying to add - DR server as node in existing cluster get error.

Cluster service on node  drnode did not reach the running state. The error code is 0x5b4. For more information check the cluster log and the system event log from node  drnode. This operation returned because the timeout period expired.

 

The server 'drnode' could not be added to the cluster.
An error occurred while adding node 'drnode' to cluster 'cluster'.

This operation returned because the timeout period expired

Any help is highly appreciated. Thanks.


 

 

 

Monday, January 11, 2016 - 2:37:13 PM - Hank Kuczynski Back To Top (40396)

question about how storage should be setup in the "add a third node" option.


we intend to add a third node that will act as an always-on read only replica of our databases to our existing two node 2012 R2 SQL 2014 failover cluster. 

So what I'm seeing is that Node1/Node2 see all shared R/W disks and the new node3 will be the RO replica with its own disks not zoned to the other nodes.  The only thing zoned will be quorum?

Is that correct?  If so, how do you get beyond cluster validation failures during the third node add?  Won't it bomb out during the disk failovers?  will it allow you to add the node in this configuration or do we have to zone everything to everything?


Thanks!

 

Wednesday, October 28, 2015 - 11:53:12 AM - anton Back To Top (38979)

I need help to create always on cluster between two data centers

i have two servers one data center and one node diffrent data center. now all 3 servers are same domain. i try to create cluster with 3 nodes. but i get error TCP/IP configuration not correct.

 

do u need to create cluster in the begining with 3 notes or I need to create cluster with primary datacenter 2 notes? then later add new node

please help me

Saturday, August 8, 2015 - 1:04:41 AM - Claudinei Back To Top (38407)


Bhaskar Sarma

    How do I enable AlwaysOn high availability within the Configuration manager, it is giving the following message requires the x86 (non-Wow) or x64 Enterprise Edition of SQL Server 2012 (or later version) with WSFC hotfix KB 2494036 installed, and I am using Windows Server 2008r2 sp1 with SQL Server 2014 and applied to the requested hotfix, most did not work, one would have any idea what you can do to solve this problem?

Thursday, May 14, 2015 - 7:50:31 AM - skb Back To Top (37172)

Good Day,

 

I would like to know whether it is possible for a automatic failover in the first scenario that is from Failover cluster instance to the Disaster Recovery Site. Thanks for the post ,

 

SKB

Tuesday, April 14, 2015 - 6:42:46 PM - Ravi Back To Top (36937)

Hi,

Great post. I have one doubt. While installing the SQL in the second datacenter, do we install a named instance with name same as that of primary datacenter or do we install the default instance?

I'm doing a deployment and this would really help me clear up the confusion.

Note: Mine is SQL 2012 Sp2 on Windows 2012 R2. Primary Datacenter has a 2 node failover cluster and the second datacenter will have a standalone SQL just like your post.

Thanks,

Ravi

Saturday, December 27, 2014 - 2:26:43 PM - pchinna Back To Top (35771)

unable to connect primary replica thru listener if the primary replica holds on FCI. However if any standalone server becomes Primary replica then we able to connect thru Listener. Do we need to change any cluster configurations to access thru the listener irrespective of the place od primary replica, wether its on FCI or Stand alone server?  

 

Thursday, December 25, 2014 - 7:33:52 AM - Satish Kumar Back To Top (35761)

Scenario 1 - Failover Cluster in Primary and Availability Group in Secondary Data Center

is this option works with MS SQL Server 2014 Standard Edition 

Please let me know on this with microsoft aricle.

Tuesday, December 16, 2014 - 9:10:33 PM - pchinna Back To Top (35628)

 

Hi Bhaskar, in our shop we are planning to add FCI to the existing Stand alone server which had AlwaysON groups. I did the following Setup for POC.

    1. Created cluster and ALwaysON group on Standalone server "A"

    2. Added two seperate nodes("B" & "C") to the cluster created on "A"

    3. I build the Failover Cluster on "B" & "C"

    4. I implemented AlwaysON on FCI

    5. Added the Virtual name of cluster as a Secondary replica

Here, I am facing below error when I tried to failover the AG group from Standalone instance to to FCI. FYI. I make sure the prefered owneres configured correctly as mentioned in your post above. 

Msg 41018, Level 16, State 0, Line 4

Failed to move a Windows Server Failover Clustering (WSFC) group to the local node (Error code 5016).  The WSFC service may not be running or may not be accessible in its current state, or the specified cluster group or node handle is invalid.  For information about this error code, see "System Error Codes" in the Windows Development documentation.

Msg 41160, Level 16, State 0, Line 4

Failed to designate the local availability replica of availability group 'AG1' as the primary replica.  The operation encountered SQL Server error 41018 and has been terminated.  Check the preceding error and the SQL Server error log for more details about the error and corrective actions.

I struck in last step of this whole setup. I searched for the above errors and did not find any helpful information. It would be great if someone can help me to resolve this issue and feel free to let me know if you need any details.
 
 

Sunday, September 28, 2014 - 9:28:47 AM - hafeez Back To Top (34746)

 

What will be the connection string in primary site?

And What will be the connection string after disaster occur (DR site)

Wednesday, July 9, 2014 - 3:06:05 PM - sudhagar Back To Top (32615)

Hi Bhaskar, Nice informative.

I have one question in Scenirio 2:  is after adding CLS_1 and CLS_2 in primary WFSC , we should setup OS clsuter in secondary  and then SQL cluster?

 

Thursday, June 12, 2014 - 12:18:33 PM - VR Back To Top (32216)

Hi Bhaskar...nice work!

I do have couple of questions.

1. In the Scenerio - I, after enabling AlwaysOn inside the existing SQL cluster instance, where should I first initiate the AlwaysOn wizard first (inside SSMS---->AlwaysOn High Availability->New High Availability Group Wizard)? Is it at the SQL cluster instance or at the new standalone instance (SA_1)?

2. Since we have a 2 node fail over cluster (SQL2k8), should we enable AlwaysOn inside the properties of SQLServerService on both the active and passive nodes or enabling at the active or cluster instance is fine?

Will appreciate your quick response. Thanks in advance!

VR

Tuesday, June 3, 2014 - 3:40:30 PM - DonK Back To Top (32066)

Hi Bhaskar. That was a great writeup! Thanks for sharing it.

I was hoping to ask about the backend set up the Cluster that allows this to work.

Do you have other posts on how to setup the backend FCI as far as network and IP addresses to allow the SQL network name and server network name to resolve to the DR site in a failover situation?

Do you have a post where you perform the failover steps ?

We have a two node active/passive FCI in our Prod site, with a single node in the DR site. The FCI is running a SQL server using a network name and ip address.

We don't have any failover setup in FCI to allow the DR node to become active.  We only want to use the DR node via availability groups in a DR situation, so we would do manual failover with a forced quorum start on the DR node.

We have added new IP address resources to our FCI that include the DR node as well as our prod node.

Now we  can't get the DR node SQL service to start, which won't allow us to configure the Availability Groups.

Regards,

Don K

 

Wednesday, March 26, 2014 - 7:51:49 AM - Claude Gatto Back To Top (29882)

Thanks Bhaskar ... for either scenario is this viable for an Active/Active 2 node cluster and have AG_1 from CLP_1 and AG_2 from CLP_2? 

Monday, March 10, 2014 - 2:39:55 PM - swadesh misra Back To Top (29694)

Good information Bhaskar...

Friday, March 7, 2014 - 12:37:43 PM - Nilesh Deokar Back To Top (29680)

Nice one and thanks for sharing this useful information on AlwaysOn

Friday, March 7, 2014 - 5:37:32 AM - Jonathan Back To Top (29674)

Thanks!  Just what i'm about to try and implement myself...  Great timing.

Sunday, February 23, 2014 - 4:07:38 PM - manu Back To Top (29547)

Thanks for sharing!

Friday, February 21, 2014 - 5:47:29 AM - Raghvendra Bhanap Back To Top (29527)

Very Nice...! 

Thanks for sharing...

Raghvendra

 

 














get free sql tips
agree to terms