Configure SQL Server AlwaysOn Availability Group on a Multi-Subnet Cluster

By:   |   Updated: 2016-12-16   |   Comments (19)   |   Related: > Availability Groups


Problem

Recently we had a project to configure SQL Server AlwaysOn between three nodes which were hosted on a multi-subnet environment. A “multi-subnet” environment is defined when the OS cluster used as the backbone for AlwaysOn has server nodes that are located in multiple/different subnets. Deploying SQL Server AlwaysOn Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster and each availability replica of a given availability group must reside on a different node of the same WSFC cluster. I am assuming the readers of this post have basic knowledge about AlwaysOn and its components. This tip will help you in configuring an HA and DR solution for SQL Server AlwaysOn in multi-subnet networks.

Solution

As per SQL Server Books Online "The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations."

Configuration Scenario and Setup

In our scenario, we have three machines named PRI-DB1 ( IP: 10.X.3.XXX ), PRI-DB2 ( IP: 10.X.4.XXX ) and SEC-DB2 ( IP: 172.X.15.XXX ). The IPs of the machines reflects their subnet which belongs to different series from each other. PRI-DB1 and PRI-DB2 are hosted in the corporate data center whereas SEC-DB2 is hosted on the Amazon cloud platform. All three machines are running Windows Server 2012 R2 Enterprise Edition and SQL Server 2014 Enterprise Edition. PRI-DB1 will be the primary replica and the remaining two nodes/machines will be the secondary replicas. Data replication between PRI-DB1 and PRI-DB2 will use synchronous-commit mode and the failover mode will be Automatic with no data loss which can be used for HA in case the primary replica goes down. Data replication between PRI-DB1 and SEC-DB2 will use asynchronous mode and the failover mode is Manual, which can cause some data loss in the case of a disaster.

Before going ahead, I created two databases named DRTest and Test using the FULL recovery model that will be added to the Availability Group. I took a full backup and a transaction log backup of both databases and restored them using the norecovery mode on both secondary replicas with the same database name to get ready for data synchronization using AlwaysOn.

I have not covered SQL Server installation and building a Windows Server Failover Cluster in this tip. I assume these steps will be done prior to configuring SQL Server AlwaysOn Availability Group using this tip.

NOTE : MAKE SURE TO IMPLEMENT THIS SOLUTION IN LOWER LIFE CYCLE. DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTINGS IN LOWER-LIFE CYCLE ENVIRNOMENTS.

 

Configuring SQL Server AlwaysOn Availability Group in Multi-Subnet Cluster

Step 1: Our first step is to check and validate the Windows Server Failover Cluster which needs to be installed on all machines participating in AlwaysOn. Check the Failover Cluster Manager on each node. This will not be a failover cluster instance, but the WSFC feature needs to be enabled on each participating machine.

Step 2: Next enable the SQL Server AlwaysOn feature on each instance of SQL Server to allow for SQL Server AlwaysOn Availability Group creation. Login to PRI-DB1 and open the SQL Server Configuration Manger. Right click on the SQL Server service and select properties. Select the "AlwaysOn High Availability" tab, and check the box to enable it. You will see the Windows cluster name which will be automatically fetched by this service in the "Windows failover cluster name" section. Click OK to save the changes and then stop and restart the SQL Service services to apply this change.

Enable AlwaysON Availability Groups

Repeat the same exercise on both replicas PRI-DB2 and SEC-DB2 as well.

Step 3: Now launch SQL Server Management Studio and connect to PRI-DB1 which is our primary replica which has two databases DRTest and Test on this instance. Right click on "AlwaysOn High Availability" and choose "New Availability Group Wizard..." as shown in the below screenshot.

New Availability Group Wizard...

A new window will open, "New Availability Group" to allow us to to do the configuration. Click on the Next button to proceed.

New Availability Group

Step 4: Here we need to enter the name of the Availability Group. I have entered DBAG. DBAG stands for Database Availability Group. You can choose your name as needed. Click on the Next button to proceed.

enter Availability Group name

Step 5: Here we need to choose the databases which we want to add to this Availability Group. Click the checkbox near the database name if you want to include it in the Availability Group. After selecting, you can see the status of both databases shows "Meets Prerequisites" which validates these databases are qualified for an AlwaysOn Availability Group. Click on the Next button to proceed.

Add databases in Availability Group

Step 6: This is a very important step where we need to specify the secondary replicas along with configuring the Availability Group listener details. We can see there are four tabs in this window and tab 1 (Replicas) and tab 4 (Listener) are very important and need to be configured. You can leave tab 2 and tab 3 with the default values. Let's start with tab 1 which is to configure the AlwaysOn replicas.

replicas

We can see the primary replica details are captured. Now click on the "Add Replica..." button to add both secondary replicas. Once both secondary replicas are connected they will appear in the window along with the primary replica as shown in the below screenshot.

replicas

Now we can choose the failover mode and data replication mode by clicking on the check boxes shown in the above screenshot. As I mentioned earlier, we will keep automatic failover and synchronous-commit mode data transfer between PRI-DB1 and PRI-DB2, so I checked the boxes above for both machines. For SEC-DB2 we don't want automatic failover and synchronous-commit mode, so these will not be checked.  If you want to keep your secondary replicas readable then you can choose the corresponding values as Yes.

Step 7: Now click on the Listener tab to configure the Listener for this Availability Group. A listener is an important component of a SQL Server Availability Group. It enables applications to connect to a DNS name, regardless of which Availability Group replica is the primary. Choose the "Create an availability group listener" option as shown in the below picture and enter the name of the listener along with the port number.

listener

As we are configuring AlwaysOn between three different subnets, we need to assign three different virtual IPs (one from each subnet) to this listener. When the Availability Group Listener (AGL) is configured properly, it will have an IP address for each defined subnet and have an “OR” dependency on each of the IP addresses. When a client operating system (OS) needs to resolve the AGL name to an IP by querying the DNS server, the DNS server will return multiple IP addresses – one for each subnet. The listener IP address in the subnet currently hosting the Availability Group primary replica will be online. The other listener IP address(es) will be offline. Because not all of the IP addresses returned by DNS will be online. Now click on the "Add..." button to assign IP addresses to listener "DBlistener". We can see all three IPs from each subnet which are added for listener "DBlistener" in the below screenshot.

listener

Step 8: Once the listener is configured click on the Next button to proceed. Now we need to select the appropriate option for data synchronization. There are three options:

  • Full - is to start the synchronization after creating and restoring the backups
  • Join only - is to start synchronization if you already created backups and restored on all secondary replicas
  • Skip initial data synchronization - this is used if you still need to create a backup and restore to the replicas

I chose "Join only" because I already restored both databases to both secondary replicas as mentioned above.

data synchronization

Step 9: Now validation will run to check all of the settings. All validation rules are successfully passed as shown below.

validation

Now click Next to proceed to the final page of this configuration which is a summary where you can re-check all of the details. Click on the Finish button to run this configuration.

summary

Step 10: Once the SQL Server AlwaysOn Availability Group is created successfully, you will see the below screen with each rule and status.

final wizard

Validation

Once setup is finished we can validate the AlwaysOn Availability Group in SSMS. You can see there is an icon with your Availability Group name which will appear under the AlwaysOn High Availability folder as shown on the left below. The same icon will appear on all replicas with their current state whether it's a primary replica or a secondary replica.

validate AG

When you expand the folder for the Availability Group, you can see all replicas and their state along with all of the database names and listener name. We can also check all details in the dashboard. Right click on the Availability Group name and choose "Show Dashboard". We can see the dashboard of this configuration on the right side of the below screenshot.

dashboard

We can also validate it using the Failover Cluster Manager. The new Availability Group name will be added as a role in the Failover Cluster Manager as you can see in the below picture. The listener IP address for the subnet currently hosting the Availability Group primary replica is online and the other two listener IP addresses show as offline.

FCM

SQL Server Availability Group Failover Testing

Now we will do failover testing and validate this configuration. As we can see, PRI-DB1 is the primary replica and PRI-DB2 is its secondary replica. Automatic failover is possible between PRI-DB1 and PRI-DB2, because of its configuration of automatic failover so we will run the below command to failover to PRI-DB2. Connect to the server instance that hosts the target secondary replica, which is PRI-DB2 in our case and run this command.

--Run on target secondary replica. Here our AG group name is DBAG so we used DBAG.
ALTER AVAILABILITY GROUP DBAG FAILOVER

Failover

Now check the dashboard by right clicking on Availability Group named DBAG and choose Show Dashboard.

Failover

We can now see the primary replica is PRI-DB2 and PRI-DB1 and SEC-DB2 are acting as secondary replicas.

Failover

Now we will test the listener connection post failover to make sure this configuration is working. Launch SSMS and connect using the Availability Group Listener name "DBlistener". We can see we have made a successful database connection using the Availability Group listener.

Failover
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

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

View all my tips


Article Last Updated: 2016-12-16

Comments For This Article




Tuesday, September 3, 2024 - 10:36:03 AM - Daniel Kimberlin Back To Top (92480)
I realize this is late for all the comments but it is helpful information for anyone coming to this page with the same issue. When in multi-subnet and getting multiple DNS records, this is caused by a setting at the cluster level. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server?view=sql-server-ver16#RegisterAllProvidersIP
Unless you have the ability to change all your connection strings to include MultiSubnetFailover = True (which who can really control that easily in a large org?) you will need to set RegisterAllProvidersIP to 0 on your cluster. This requires restarting your listener resources so you will need to plan this change for a maintenance window. This happens because this setting is 1 by default and causes all IPs for the listener to create a DNS record (if the CNO has permission to create/change its own DNS record). This will result in a kind of round-robin issue in DNS where we will see random client time outs when they grab the DNS of the record outside of their subnet. I think this should be 0 by default but I'm not Microsoft. Hopefully this helps anyone with this issue. In the code that is MS provided below, your just replace with your listener name (which may be listener_listener, you can see these by running Get-ClusterResource | where-object ResourceType -EQ "Network Name") . You will need to run this from an elevated (admin) PowerShell session on a node actually in the cluster as you will probably have issues trying to do this remotely.

Import-Module FailoverClusters
Get-ClusterResource yourListenerName | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource yourListenerName | Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource yourListenerName
Start-ClusterResource yourListenerName
Start-Clustergroup yourListenerGroupName

Friday, February 26, 2021 - 12:12:22 PM - Jasim Back To Top (88307)
Hi,

in multi subnet, we can keep both the subnet IP in online state for listener?

The requirement is one private IP is already associated with Listener, connectivity is working fine, now one public IP need to add as well with listener to establish connectivity from outside of customer network, so I want to add public IP with listener, and keep both IP online, user can use both concurrently.

Wednesday, February 19, 2020 - 2:31:22 PM - Krishna Back To Top (84641)

Hi. We are setting up Always on on One of our multi subnet clusters and having issue configuring Listener. Below is how our setup looks like:

Cluster Nodes:

Node1 (Subnet1)

Node2(Subnet1)

Node3(Subnet2) 

Node 4: Always On replica is added to cluster but not available for failover. 

I have given two IPs, one for each subnet. Below is the error I am getting while setting up listener. 

TITLE: Microsoft SQL Server Management Studio

------------------------------

Create failed for Availability Group Listener 'AVG_Listener'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroupListener&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The WSFC cluster could not bring the Network Name resource with DNS name 'AVG_Listener' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.

The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 19471)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5026&EvtSrc=MSSQLServer&EvtID=19471&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

Please let me know if anyone faced this issue. 

Thanks in advance!! 

Monday, December 2, 2019 - 1:16:50 PM - sanju Back To Top (83255)

good job and thanks for the post

Wednesday, September 11, 2019 - 10:17:23 AM - Sreenivas Back To Top (82365)

Hi Manvendra,

you demonstrated very well with multi-subnet cluster. 

Question:

1. In which location did you placed your Quorum?

2. Assume, PR1-DB1 ((Primary DC) and PR1-DB2 (Secondary DC) both are down for some reason. what is the state of "SEC-DB2" databases?

3. How do you bring the cluster and cluster resources are making up when 2 nodes are down?

Share your thoughts.

Regards

Sreenivas

Friday, July 26, 2019 - 10:42:43 AM - Sreeni Back To Top (81876)

Hi Manvendra,

Here is the question on your multisubnet cluster ....

what is the IP address for your cluster? and What is the IP for Node1? Are booth cluster & Node1 IPs are in samee location? 

Example:

Cluster IP :  10.x.3.128

Node1 SQL IP: 10.x.3.999

If yes, let's try this ....

RDC into Node 1 SQL and gracefuully DISABLE the network. That means, if disaster happen and Node 1 is down.

1. explain uus, How your cluster behaving?

2. What steps do you taken to bring up the databases on Secondary node (Manual failover / automatica failover)

Thanks in advancce

Tuesday, January 1, 2019 - 5:33:56 PM - Digitecht Back To Top (78592)

 Question: Let say Nodes DB1 and DB2 were in a child domain db.test.com and DB3 belonged to another child domain db3.test.com. Can we still configure AlwaysOn in this scenerio if dns/replication was configured correctly?

Friday, September 14, 2018 - 11:34:33 AM - ahmad Majeeb Back To Top (77567)

Great job!!

Thursday, May 17, 2018 - 11:25:51 AM - Timothy Weinand Back To Top (75967)

I am setting up a similar configuration.  Two synchronous nodes in one datacenter for HA and a third in a second datacenter for DR.  My issue is with the listener.  The IP seems to rotate between the virtual IP in the HA datacenter and the virtual IP in the DR datacenter.  The virtual IP in the DR datacenter is not active and the connection fails when it rotates to that IP. 

The following ping attempts were taken a few minutes apart.  Nothing changed in the interim.

 

ping sacsqlclat_agls

Pinging sacsqlclat_agls.hotwater.aosmith.org [172.24.2.14] with 32 bytes of data:

Reply from 172.24.2.14: bytes=32 time=20ms TTL=123

Reply from 172.24.2.14: bytes=32 time=20ms TTL=123

Reply from 172.24.2.14: bytes=32 time=20ms TTL=123

Reply from 172.24.2.14: bytes=32 time=20ms TTL=123

 

Ping statistics for 172.24.2.14:

    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),

Approximate round trip times in milli-seconds:

    Minimum = 20ms, Maximum = 20ms, Average = 20ms

 

ping sacsqlclat_agls

 

Pinging sacsqlclat_agls.hotwater.aosmith.org [172.16.3.234] with 32 bytes of data:

Request timed out.

Request timed out.

Request timed out.

Request timed out.

 

Ping statistics for 172.16.3.234:

    Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

 

Any idea why the IP is rotating? 

 

Wednesday, January 3, 2018 - 12:19:30 PM - Azhar iqbal Back To Top (74717)

Nice step by step explanation.

I have two questions

No 1 At Windows level we just needs to enable fail over cluster functionality or do we needs to configure quorum and needs to run cluster validation tests?

No 2. If we have primary nodes in same subnet then we will use one IP for listener?

Please confirm.

Thursday, December 21, 2017 - 11:49:47 AM - Burhan Pathan Back To Top (74311)

Have created the same setup with two different subnet. My primary server IP is 192.168.1.61 and secondary is 192.168.2.61. When have created Listener in SQL automatically two A record got created in DNS server. 1) SQLAGL  192.168.1.81 2) SQLAGL  192.168.2.81

Now my query is when I ping to SQLAGL it gets resolved with   192.168.1.81 and after few second it gets resolved with   192.168.2.81

So as per my understanding this should not happened, if this is happening then my application will send data request to both IP and it will fail on secondary IP.

 

Please help to understand and resolve this issue.

If posible please call on +919823888688

Friday, December 15, 2017 - 11:47:45 AM - Eldar Back To Top (74055)

Great document!  Thank you.

Wednesday, July 12, 2017 - 9:51:05 AM - David Cobb Back To Top (59285)

 I was able to set up a similar multi-subnet AG, but for @Mike, the missing step to point out in the Windows Failover Cluster Manager is when you add the node from the 2nd subnet, you will need to uncheck the 'Validate Cluster' option, as the validation will fail if the nodes are on separate subnets without a second NIC that has an IP on each subnet.

Once you add the node to the windows cluster, without validation, you will be able to add the node into the AG from SSMS.

Hope this helps!

 

Tuesday, March 28, 2017 - 5:58:28 PM - Gus Back To Top (53829)

 Manvendra, really nice article! 

I have a question about failover between this kind of multi-subnet environments.

1- At the moment of creating listener DNS, do you have any specific requirement like creating an A record for each subnet or you just create the virtual name with the number of virtual IPs corresponding to each subnet you have and that is all? 

2- Also, will the failover be done automatically without any DNS change?

 

Thanks in advance!

Gus

 

 

Thursday, December 29, 2016 - 12:38:47 AM - kishore Back To Top (45058)

 

Excellent  Document

 

Sunday, December 25, 2016 - 6:26:56 AM - Deepak Kumar Back To Top (45046)

Thanks Manvendra,

For such an excellent article. Its is really a helpful article for many aspirants, who really needs to increase their knowledge towards Always-On. The steps are very crystal clear.  Thanks once again for this impressive one.

 

Thanks & Regards

Deepak

Friday, December 23, 2016 - 1:57:14 PM - Manvendra Back To Top (45035)

 Hi Mike

Thank you for your comments. I did not face any issue however i would love to help you if you can share more details about your issue.

 

Hi Alex- Thank you very much for your comments. I will take your suggestion as advice in my next tips.

Monday, December 19, 2016 - 10:15:51 AM - Mike Back To Top (45004)

  Hi Manvendra,  Thanks for the excellent article.  I have tried to set up a similar solution, but I could not get past getting WSFC set up like in Step 1.  We were able to turn on WSFC on each server, but never able to get them to join the same cluster via the WSFC manager.  I am not sure if you ran into any issues there, or if any other read has, but I would like to hear how you were able to get past the issue.  The error log did not help resolve the issue, and we hit a road block.  Thanks so much, Mike.

Monday, December 19, 2016 - 9:02:51 AM - J.C.Alexandres Back To Top (45003)

 Excellent article, thank you for sharing this "how to" of some of the capabilities of SQL Server Enterprise 2012. I would just suggest including the full path and full IP addresses in your examples, as some users may get confused.

 














get free sql tips
agree to terms