Was working with a client who had an existing Availability Group, but did not have the RegisterAllProvidersIP setting enabled on their cluster. So, recent changes to their connection strings to take advantage of MultiSubnetFailover to minimize failover re-connection delay wasn't helping. Multisubnet failover needs to see both Listener IP's to help, so RegisterAllProviderIP's is required to be ON.
Here's why.
With RegisterAllProvidersIP disabled on the client access point (the cluster network), only one IP address is made available for the listener. When enabled, all site IPs for the listener are simultaneously listed. Connection strings using MultiSubnetFailover will try out all IPs simultaneously, providing for the fastest possible transition after an availability group failover.
Caveat remains that any connection strings that aren't or can't use MultiSubnetFailover will have problems. So RegisterAllProvidersIP should be enabled only when MultiSubnetFailover can be used in all application connection strings. More information here. You really do need to understand the applications and their connection strings here, this has little to do with the SQL Server and more to do with apps and DNS aliases.
In the case of the client above, enabling RegisterAllProvidersIP on an existing, in-use cluster is possible, but does require a brief outage.
Here is the script to use via PowerShell.
Comments guide along the way.
nslookup AAGListenerName #With RegisterAllProvidersIP = 0, this will only return one IP, the IP of the current primary.
Get-ClusterResource -Cluster "FC_Name" #Get Cluster Network name
Get-ClusterResource "ClusterNetworkName" -Cluster "FC_Name" | set-clusterparameter RegisterAllProvidersIP 1 -Cluster "FC_Name"
#You'll get a warning, all changes will take effect until ClusterNetworkName is taken offline and then online again.
Stop-clusterresource "ClusterNetworkName" -Cluster "FC_Name" #Take Offline
Start-clusterresource "ClusterNetworkName" -Cluster "FC_Name" #Right Back Online
Start-clusterresource "AAGName" -Cluster "FC_Name" #This step is important. The AAG is offline, must bring the AAG Back online
#Wait a moment, then verify
Get-ClusterResource -Cluster "FC_Name" #Verify
nslookup AAGListenerName #This should now return Two IPs, for both the primary and secondary. Now, MultiSubnetFailover can help.
UPDATE: Thanks to a commenter, updated the 0 to 1 in the set-clusterparameter line.
