Connection Timeouts in Multi-Subnets Availability Group Listener

This month blog post I would like to share a connection timeout issue that one of my client encountered and fixed by me. The client has some Crystal Reports connect to a MS SQL database in an availability group, through the availability group listener, which has multi-subnets IP addresses. The client found the reports intermittently connection timeout in the MS SQL database. According to this Microsoft documentation, 1) 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. 2) To resolve this situation when your legacy clients cannot use the MultiSubnetFailover property, you can change the listener's RegisterAllProvidersIP value to 0 (and lower the HostRecordTTL value if required). As the client found the application's data provider doesn't support the MultiSubNetFailover connection string parameter, I can only help them by disabling the RegisterAllProvidersIP in the AAG listener. Here comes the steps to do:

1.  Open PowerShell command window using Administrator privilege.

2. Run command Get-ClusterResource to get the AAG listener cluster resource name (it should be AagGroupName_ListenerName).

3. Run command Get-ClusterResource <AAG listener cluster resource name> | Get-ClusterParameter RegisterAllProvidersIP to get the current value of this parameter. It should be 1 if you create the AAG listener by using SSMS.

4. Cross check that the AAG listener registered all its multi-subnets IP addresses in DNS, by running nslookup <listener name>. Below screenshot illustrates steps 1 to 4:

5. Run command Get-ClusterResource <AAG listener cluster resource name> | Set-ClusterParameter RegisterAllProvidersIP 0 to disable the register all IP cluster parameter in the listener cluster resource.

6. For simplicity, failover the AAG to make this change takes effect.

7. Run command Get-ClusterResource <AAG listener cluster resource name> | Get-ClusterParameter RegisterAllProvidersIP to double check the parameter set to 0, and nslookup <listener name> to double check the listener now only registered one active IP in DNS. Below screenshot illustrates steps 5 to 7:

Happy Labour Day!