SQL AOAG Configuration for SQL Servers on Multiple Subnets

Summary

SQL server nodes that exist on different subnets require multiple IPs registered to the listener, one for each subnet. If a failover occurs, the Adaptiva server continues trying to connect to the IP of the original Always On Available Group (AOAG) listener and cannot failover to a new IP for the new Primary in the AOAG. This issue occurs because RegisterAllProvidersIP defaults to disabled.

Use the following Solution details to change the default settings.

Solution

Enable RegisterAllProvidersIP, to allow the Adaptiva server to connect to the new SQL server in the event of a failover.

Enable RegisterAllProvidersIP, using the following actions:

• Enable RegisterAllProvidersIP on the SQL cluster.
• Set the TTL to 60 seconds.
• Restart the cluster listener.

Run the following PowerShell commandlets to enable RegisterAllProvidersIP:

Get-ClusterResource <AGNAME_LISTENER> | Set-ClusterParameter RegisterAllProvidersIP 0

Get-ClusterResource <AGNAME_LISTENER> | Set-ClusterParameter HostRecordTTL 60

Stop-clusterresource <AGNAME_LISTENER>

Start-clusterresource <AGNAME_LISTENER>

Note

Make sure to replace <AGNAME_LISTENER> with the DNS name of your registered listener. 

With ​RegisterAllProvidersIP​​ enabled, the Adaptiva server can connect to a new SQL server within 60 seconds if a SQL failover occurs.