I am using a 2 node SQL AG (2014 EE) (each on a different subnet) and the Listener is configured with 2 Static IPs. The client application uses SQL Native Client 11.4.
My understanding is that this driver is legacy and hence should not support failover with RegisterAllProvidersIP as 1 and default HostRecordTTL( 20 minutes).
Although I am unable to failover when RegsiterAllProvidersIP is 0 and TTL is 20 minutes (I am able to when TTL is lowered to say, 2 minutes), the application successfully fails over with RegisterAllProvidersIP set as 1.
According to the blog here, https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/
It is recommended to set RegisterAllProvidersIP to 0 and HostRecordTTL to 2 minutes to achieve failover, but in my case I found that it is not needed as I am able to failover with RegisterAllProvidersIP set as 1.
Am I completely missing the point of recommended settings from MS blog or my multi subnet setup has any issues?
SQL Server – MultiSubnet Failover with RegisterAllProvidersIP 1
availability-groupsfailoverhadrsql server
Related Question
- Sql-server – Much slower SQL application performance after upgrading from Windows Server 2008 R2 to Windows Server 2012 R2
- SQL Server – Availability Groups Using Multi-Subnet Clustering
- Sql-server – Mixing 2 and 3 node AG on a single WSFC
- SQL Server MultiSubnetFailover – Performance Gain for Single Subnet
- SQL Server – Listener IP Not Changing After Failover
- Entity Framework Core Failover with SQL Server Basic Availability Groups
Best Answer
Microsoft has deprecated the native client, however, they have released a new OLE DB driver which supports all the native client features plus adds MultiSubnetFailover and TLS1.2 support. See the announcement here. It may be worth looking at upgrading OLE DB to take advantage.
With the RegisterAllProvidersIP = 0 and TTL = 20 combo, you would need to wait up to twenty minutes post-failover for the new listener IP to be registered in DNS and picked up by your clients. It is still technically 'working' it's just very slow.
With RegisterAllProvidersIP set to 1, the native client will try and connect to each IP address returned by DNS serially. If the first IP returned by DNS is the correct one it connects, if not it takes ~20 seconds to timeout, then waits ~20 seconds then tries the next IP.
What this means is if your connection timeout is greater than ~40 seconds, its quite possible that you're seeing the behaviour in question but a high connection timeout value is masking it.
Either way, the true fix here is to start using MultiSubnetFailover to make the best use of the rapid failover capabilities provided by RegisterAllProvidersIP=1. If you don't want upgrade the OLE DB driver, you should set a low TTL for the listener DNS entry and set RegisterAllProvidersIP=0. It may work for you now with it set to 1, but conditions in your environment could change and cause this to suddenly stop working.