SQL 2017 Standard in SQL always on two node Basic AG. There are two DNS records for the listener with the different subnet IP's

The servers are across subnets and we have hit issues with an application seeing to randomly time out when talking to the database, other times it responds in milliseconds. After running Wireshark we can see the application is trying to talk to the database via the failover partner listener IP address, the none readable replica.

This information I have read explains the 21 second timeout.


MultiSubnetFailover=TRUE seemed like the resolution but the application is using an older version of .NET is there anything else I can do to resolve or work around this?

1 Answer 1


As mentioned in the docs here the issue is explained:

MultiSubnetFailover=True Not Supported by NET Framework 3.5 or OLEDB

Issue: If your Availability Group or Failover Cluster Instance has a listener name (known as the network name or Client Access Point in the WSFC Cluster Manager) depending on multiple IP addresses from different subnets, and you are using either ADO.NET with .NET Framework 3.5SP1 or SQL Native Client 11.0 OLEDB, potentially 50% of your client-connection requests to the availability group listener will hit a connection timeout.

And there are two main workarounds:

Workarounds: We recommend that you do one of the following tasks.

If do not have the permission to manipulate cluster resources, change your connection timeout to 30 seconds (this value results in a 20-second TCP timeout period plus a 10-second buffer).

Pros: If a cross-subnet failover occurs, client recovery time is short.

Cons: Half of the client connections will take more than 20 seconds

If you have the permission to manipulate cluster resources, the more recommended approach is to set the network name of your availability group listener to RegisterAllProvidersIP=0. For more information, see "RegisterAllProvidersIP Setting" later in this section.

Pros: You do not need to increase your client-connection timeout value.

Cons: If a cross-subnet failover occurs, the client recovery time could be 15 minutes or longer, depending on your HostRecordTTL setting and the setting of your cross-site DNS/AD replication schedule.

In my opinion half of the connections taking more than 20 seconds is far from ideal and I would look into changing the RegisterAllProvidersIP to 0 and HostRecordTTL settings according to your needs. Know that lowering this value too much will increase DNS lookup requests. You could also look into monitoring failovers here would help with connection issues after failover.

  • Randi, thank you for the information I have been reading up on the RegisterAllProvidersIP settings and think this is the way to go. Appreciate the help!
    – Stockburn
    Commented Nov 11, 2020 at 20:47
  • @Stockburn You're welcome! I have had the same issue in the past, unfortunately on live production when adding a DRS node to my Always On AG Setup ;). Commented Nov 12, 2020 at 8:47
  • 1
    Randi wanted to say thanks again, I made the changes to the RegisterAllProvidersIP and HostRecordTTL one night this week and its looks good, this was on live as well, it was odd we had an existing app server that has been fine but a new server heighted the problem.
    – Stockburn
    Commented Nov 19, 2020 at 1:00
  • @Stockburn Great! Thank you for taking the time for the update. Have a great day :) Commented Nov 19, 2020 at 8:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.