Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to resolve connectivity issues with SQL Server Availability Groups


By:   |   Updated: 2014-11-21   |   Comments (7)   |   Related: More > Availability Groups

Problem

After you have completed your AlwaysOn setup and configuration in a multi-subnet environment, you get a number of calls from the application team that they are having intermittent connectivity issues with the database server. You verify that the AlwaysOn IP address is pingable and you are able to connect via SSMS to the Availability Group (AG) name. You advise the application team to issue the command to flush their DNS cache via ipconfig in a command window. This solves the problem for a while and while you are on your next coffee break you get a call again from the application development team that they are having the same problem again.

Solution

This intermittent connectivity time out problem might be caused by the multiple IP addresses provided during the setup of the AG listener for the multi-subnet cluster.

During the configuration of your AG listener, you would have to define an IP address for each subnet in your environment. This will have ať dependency for each of the subnet IP addresses.

When your AG is up and running, it is registered in your DNS by the Windows Cluster Service for each subnet. Most organizations have 2 subnets, but there is also the possibility that there may be more. The Windows Cluster will submit all of the IP addresses for the subnet to the DNS server. The DNS server then creates a record for each subnet. I am referring to a Microsoft Windows DNS server, if you have another kind of server used in your organization, it may have another implementation or behavior.

Now from your application, when a client connection is made to the AG listener, the Windows Cluster needs to resolve the AG name to an IP address by searching the DNS server. The DNS server will then return multiple IP addresses registered previously during the configuration of your AG. Remember that there are multiple IP addresses in your DNS list, one for each subnet that you have. The IP address for your primary replica server is online, the rest of the IP addresses for your secondary replicas are offline. Now here lies the connectivity timeout issue. Because not all the IP addresses are online, the IP address that may be returned by the DNS server may be an offline one. Then the client application experiences the timeout problem.

Depending on your DNS server configuration, the client application may have to wait for some time before it returns another IP address that may or may not be online. The default TCP connection attempt is usually 21 seconds. So if the first IP address returned by the DNS server is offline, it will have to wait for another 21 seconds to return another IP address to try to connect. Again, the new IP address returned may or may not be online. Until it has exhausted all the IP addresses, it will be a trial and error approach, until you get that call again from the application team.

To correct and finally solve this intermittent connectivity timeout problem, you need to revisit your AG listener properties. In SSMS, open your AG properties window (as shown below).

In your SSMS, go and open your AG properties window.

From the properties window dialog box above, we zoom in at the RegisterAllProviderIP property. During the configuration of the AG listener via the Windows Cluster, this property is 0 by default. This property simply means that all IP addresses for each of your subnets will be registered in the DNS server.

When the property RegisterAllProvidersIP is set to 1, any client application whose connection string does not use the setting multi-subnetFailover = True, will experience high latency connections and subsequently experience the dreaded connection timeout problem. This occurs because these clients attempt to connect to all the registered IP addresses sequentially.

Note that to take advantage of the AG listener multi-subnet feature, the application will need a data provider that supports the multi-subnetFailover keyword in the connection string property.

In contrast, if RegisterAllProvidersIP is changed to 0, the active IP address is registered in the Client Access Point in the WSFC cluster, reducing latency for legacy applications.

The recommendation here is that, if you have legacy client applications that need to connect to an availability group listener and cannot use the multi-subnetFailover keyword, the property RegisterAllProvidersIP should be set to 0. If the property RegisterAllProvidersIP is set to 1, the client application connection string should add the multi-subnetFailover = True setting.

To view the same property in PowerShell, you may issue the following command (see figure below)

To view the same property in powershell, you may issue the following command.

Then you will get the following result as below.

Then you will get the following result.

I hope the above recommendations solve the intermittent connectivity problem and makes your application failover test a success.

Next Steps
  • Perform a more thorough client application test, by setting the RegisterAllProviderIP property for your scenario.
  • For more help on the configuration of the AG listener, click here.
  • For more articles on SQL Server 2012 AlwaysOn, click here.


Last Updated: 2014-11-21


get scripts

next tip button



About the author
MSSQLTips author Carla Abanes Carla Abanes works for a private bank in Singapore as a SQL Server DBA.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, July 25, 2016 - 3:20:39 AM - ManoShankar Back To Top

Hi,

 

As per the business requirement, i need to copy datas from AlwaysOn secondary replica AG database to non AG database through agent job. By default agent account cannot use the parameter “ApplicationIntent=Readonly”.Please advice how to read the datas from secondary replica.

 

Thanks,

ManoShankar


Wednesday, December 03, 2014 - 9:12:58 AM - abdul Back To Top

 

 

thanks you 

it helps a lot 


Thursday, November 27, 2014 - 8:01:30 PM - Carla Abanes Back To Top

Hi Yasin,

 

The error usually encountered is connection timeout message. Which could be very broad and you need to look at a thousand things probably. But if you have the similar scenario as i described in this tip, then you can apply the same in your test environment and try to replicate the same error.

I hope this helps.

 


Wednesday, November 26, 2014 - 7:06:14 AM - Yasin Back To Top

I am interested in what errors were seen for these connectivity issues. Thanks


Wednesday, November 26, 2014 - 2:32:29 AM - Hany Helmy Back To Top

Hi Carla,

Thanx 4 ur feedback, most probably we are talking about 2 different versions of windows, mine is 2012 standard edition which I have Roles instead of Services and Applications (doesn`t exist in my version), beside my AG properties window includes only 2 tabs: General & Failover, in the meanwhile, the Cluster properties window has the same tabs shown in ur screenshot except the last one: Properties.


Monday, November 24, 2014 - 9:29:23 PM - Carla Abanes Back To Top

Hi Hany Helmy,

 

I apologize, but the AG property from above screenshot should be seen from your failover cluster manager. In your windows server manager, go to failover cluster manager> services and applications>your ag name>properties. From this properties window you should see properties tab and the properties described above can be seen from this tab.

Again i apologize but i do hope it works well for you.


Monday, November 24, 2014 - 6:17:21 AM - Hany Helmy Back To Top

Thank u 4 the article, I `ve a concern, the screenshot u provided is from which version exactly?! I am confused as my version is Microsoft SQL Server Enterprise (64-bit) 2012 SP2, 11.0.5058, but I have only the General tab in the AG listener properties in SSMS, so how I will review those properties window?! plz help.

Thanx a lot.


Learn more about SQL Server tools