Time-out error and you cannot connect to a SQL Server 2012 AlwaysOn availability group listener in a multi-subnet environment

Article translations Article translations
Article ID: 2792139 - View products that this article applies to.
Expand all | Collapse all

Symptoms

After you configure the availability group listener for an AlwaysOn Availability Group in Microsoft SQL Server 2012, you may be unable to ping the listener or connect to it from an application.

For example, when you try to connect to a listener of SQL Server by using SQLCMD, the connection times out. Additionally, you receive an error message that resembles the following:
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Note These symptoms are usually intermittent, or relate to failover of the availability group resource.

The following screen shot shows an example of what occurs when you try to ping the listener for the availability of "aglisten." The screen shot also shows a successful connection to SQL Server by using the SQLCMD command when you include the multi-subnet failover parameter -M.


Collapse this imageExpand this image
Failure example


Note You can use the SQLCMD command together with the –M parameter as shown in the screen shot to connect to the listener.

Cause

This issue occurs because your application either uses a legacy data provider that does not support the new MultiSubnetFailover parameter, or is not configured to use this parameter.

This parameter is supported in newer versions of the SQLClient driver that is included with the Microsoft .NET Framework 4 and with later versions of the .NET Framework, and is back ported to the Microsoft .NET Framework 3.5.

Note The PING command is a simple connectivity testing tool that does not support the new parameter.

Resolution

You can use one of the following resolutions as applicable to your case:
  • To resolve this situation when the data providers support the MultiSubNetFailover parameter, add the MultiSubNetFailover parameter to your connection string, and set it to true.
  • To resolve this situation when your legacy clients cannot use the MultiSubnetFailover property, you can change the listener’s RegisterAllProvidersIP value to 0. To do this, run the following command from the Windows PowerShell command-line interface:
    Import-Module FailoverClusters
    Get-ClusterResource <Your listener name>|Set-ClusterParameter RegisterAllProvidersIP 0

    Collapse this imageExpand this image
    Demo
Note After you set the RegisterAllProvidersIP value to 0, the current online IP address must be un-registered from the DNS server and the offline IP address must be registered to the DNS server when a failover occurs. This may cause a connection delay for the next failover.

More information

When you try to connect to a listener that is defined on more than one subnet, the operation may fail if the client driver tries to connect by using one of the listener’s offline IP addresses.

When a listener is created, an IP address is designated for each unique subnet that an availability group replica is hosted in. For example, if a listener is created for an availability group that has replicas that exist in two subnets, two IP addresses are defined in the listener. One address is used by an application that can connect to an instance of SQL Server in subnet 1, and the other address is used when an application connects to an instance of SQL Server in subnet 2.

Behind the scenes, the listener creates a Windows cluster "Client Access Point" resource. One of its properties is RegisterAllProvidersIP. When a listener is created, this is set to 1, and all the listener’s IP addresses are registered in DNS server. This configuration provides reduced re-connection time for clients. 

Because the DNS record contains all the IP addresses, a client that tries to connect to the listener must know how to handle this situation. The MultiSubnetFailover parameter enables the client driver to try connections in parallel to all the listener’s IP addresses. Without the MultiSubnetFailover parameter, the client driver will try to connect sequentially to all IP addresses for the listener. Sequential connections may cause a long logon time or logon time-outs.

Note The problem that is mentioned in this article also affects Microsoft SharePoint environments that are configured to use an AlwaysOn Availability Group’s secondary read-only replica. To resolve this issue, perform whichever of the following actions applies to your version of SharePoint:
  • For Microsoft SharePoint 2007: This is classified as a legacy application. Therefore, SharePoint 2007 cannot be configured to use the MultiSubnetFailover parameter. Instead, you have to use the Windows PowerShell command that is described in the "Resolution" section.
  • For Microsoft SharePoint 2010: Cumulative update packages are now available that add support for the MultiSubnetFailover parameter. For more information about the update packages, click the following article numbers to view the articles in the Microsoft Knowledge Base:
    2654347 An update introduces support for the AlwaysOn features from SQL Server 2012 to the .NET Framework 3.5 SP1
    2687557 Description of the SharePoint Foundation 2010 hotfix package (Wss-x-none.msp): October 30, 2012
For more information about logon time-out issues when you specify the MultiSubnetFailover parameter, go to the following Microsoft Knowledge Base article:
2855417 Connection times out when you use AlwaysOn availability group listener with MultiSubnetFailover parameter
For more information about the SQLCMD utility, go to the following MSDN website:
Introduction for the SQLCMD utility
For more information about the SqlClient driver, go to the following MSDN website:
SqlClient support for high availability, disaster recovery
For more information about an update that introduces support for the AlwaysOn features from SQL Server 2012 to the .NET Framework 3.5 SP1, click the following article number to view the article in the Microsoft Knowledge Base article:
2654347 An update introduces support for the AlwaysOn features from SQL Server 2012 to the .NET Framework 3.5 SP1
For more information about the driver support for multi-subnet failover, see the "5.7.1 Client-Connectivity for AlwaysOn Availability Groups" section of the following MSDN website:
SQL Server 2012 release notes
For more information about how to create or configure an availability group listener, go to the following Microsoft website:
Create or configure an availability group listener in SQL Server 2012
For more information about a connection to readable secondary error when logon Security IDs (SID) are different or missing, go to the following Microsoft blog:
Connection to readable secondary fails when login SIDs are different or missing
For more information about SQL Server multi-subnet clustering, go to the following MSDN website:
Introduction for SQL Server multi-subnet clustering

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Properties

Article ID: 2792139 - Last Review: June 20, 2013 - Revision: 5.0
Applies to
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • SQL Server 2012 Enterprise Core
Keywords: 
kbsurveynew kbtshoot kbexpertiseadvanced KB2792139

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com