Multi subnet sql alwayon cluster not connecting to sql database when application or alwayon failover

Multi subnet SQL alwayon cluster not connecting to SQL database when application server restarts or alwayon failover.

In multi subnet we will have more than two ips, the active IP will be online and other ips will be offline.

https://techcommunity.microsoft.com/t5/SQL-Server-Support/Connection-Timeouts-in-Multi-subnet-Availability-Group/ba-p/318334

This Microsoft link will have more information. I just used this to give a workaround for one of our application. The application has MultiSubnetFailover=True.

Open powershell as administrator. Run the following and pass the resource name.

Get the resource name, it could be AG group name_Listener name – Get-ClusterResource

Check the ‘RegisterAllProvidersIP’ value, if it is 1 change to 0

Get-ClusterResource “Cluster Resource Name” | Get-ClusterParameter

 

Change the ‘RegisterAllProvidersIP’ from 1 to 0, this needs

Get-ClusterResource “Cluster Resource Name”| Set-ClusterParameter RegisterAllProvidersIP 0