The Microsoft SQL Server Always On Availability Groups feature is often confused with the similarly named SQL Server Always On Failover Cluster instances. This is in part because Failover Cluster Instances (FCI) were rebranded some years ago under the “Always On” marketing term, but also because both features rely on the Windows Failover Cluster feature. In this article I will exclusively be talking about the availability group feature and may abbreviate it as Always On AG or just simply AG.
Note
Always On availability groups is the full, formal name for this availability feature. The abbreviation is AG, not AOAG or AAG.
Microsoft Always On availability groups: a high-availability and disaster-recovery solution
Multi-subnet Clusters
A SQL Server multi-subnet failover cluster is a configuration where each failover cluster node is connected to a different subnet or different set of subnets. There are many reasons why you may want to have a multi-subnetted cluster (or are forced to use one) including geographically dispersed sites.
When an AG is built on a Windows Server Failover Cluster (WSFC) that spans multiple subnets, a properly configured Always On AG Listener will have an IP address for each defined subnet and each will have an OR dependency in the Cluster Manager. By default, when the Listener is added, it gets registered in DNS by the Windows Cluster. The cluster will submit all of the IP addresses in the dependency list and the DNS server will register an A record for each IP address.
When a client tries to connect to the AG using the Listener name, the DNS server gets queried and all of the A records are returned. The cluster resource for the IP that is a member of the subnet for which the primary replica node of the AG is currently hosted will be online while all the other cluster resource IPs will be offline. Depending on the client, this can cause problems.
Timeout Problems
The default behavior of most supported SQL Client connectivity drivers is to try each one of the IP addresses in DNS associated with the Listener one-by-one (serially). This happens in the following order:
- Client initiates connection with Listener name specified in connection string
- Query DNS for the listener name
- DNS returns IP addresses of all A records matching the Listener name (in an indeterminate order)
- Client tries to connect to listener using the first IP returned
- Client times out after the TCP connection attempt timeout (default 21 seconds)
- If the previous connection attempt times out, attempt using the next IP address
So what is the problem? Well, the problem is that while the TCP connection timeout is 21 seconds, the default .NET client application timeout value is 15 seconds. When the application times out, the whole connection closes.
So if you have a client configured with the default timeout value, and you don’t get lucky with the first IP returned by DNS, you’ll never make it far enough to try the rest of the IPs. This often times does not manifest itself as a problem until the first time you try to failover production and manifests itself as repeated connection timeouts from your client applications trying to connect to SQL Server until you fail back to the original node. So what can you do?
Resolution: MultiSubnetFailover=True
The good news is that if you’re using at least SQL Server 2012 and your .NET application is on at least .NET 4.5 (earlier .NET libraries with hotfixes), you can take advantage of the new connection parameter that Microsoft added, MultiSubnetFailover, which should be used and set to TRUE. This changes the serial connection attempt behavior mentioned prior to what is essentially parallel connection attempts1.
Now, when you try to connect to SQL using the Listener, even if there are multiple A records, your application will try them all at once instead of one-by-one lessening the chances of a timeout.
Workaround: Older or Incompatible Clients
In a perfect world we’d just upgrade everything all the time seamlessly, immediately and without impacting the business’s bottom line. However, in reality, legacy applications exist. If you find yourself in a scenario where setting MultiSubnetFailover=True is not an option for at least one of your applications, you’ll need a work around.
Assuming that your organization is using Dynamic DNS, one option may be to modify the RegisterAllProvidersIP cluster setting. This parameter determines whether the Windows Cluster will register all of the IP addresses for the AG Listener, or only the one for the one currently online in the Cluster Manager (so the one that is a member of the subnet where the AG primary is). When set to 1(default if the AG Listener is created by SQL Server2), the AG Listener clustered resource gets created with all of the IP addresses.
With the cluster reconfigured to only register only one A record in DNS at a time, this introduces another potential issue: an outdated DNS. with default TTL on the A record, the failover has a high probability of completing much more quickly than DNS gets updated with the new IP address. This means that even after a failover your clients could still get timeout errors because the Listener A record is still pointing to the secondary node post failover. This will eventually resolve itself once the TTL expires but that may be long after your business’s SLAs. To mitigate the effects of this, we can use the HostRecordTTL cluster parameter. This parameter governs how long (in seconds) before cached DNS entries on a client OS are expired, forcing the client OS to re-query the DNS server again to obtain the current IP address. By default, this value is 1200 (20 minutes). 20 minutes is a really long time to potentially wait before clients can successfully connect again after a failover. To ensure that we can connect sooner, we should set this to something like 120 or 60.
The drawback to setting the value to a lower number is how often the client OS will query the DNS server. If you have a handful of application servers, then changing the value from 1200 to 60 would probably have no perceptible impact on the DNS server(s). However, if there are thousands of client machines that all must resolve the AG Listener name to IP, this increases the load on the DNS server(s) and could cause problems.
You will need to strike a balance between the lowest possible cache expiration time and the increased overhead for the DNS server.
1All IP addresses receive a SYN request at the TCP layer and are rapidly initiated one-by-one (so still serially) but do wait for acknowledgement before initiating the next connection attempt (so close enough to parallel for the purposes of this article). 2If a Client Access Point is created using Windows Failover Cluster Manager, the RegisterAllProvidersIP parameter is set to 0 by default.
