===============
Questions
===============
Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet.
===============
Guidance
===============
Always specify MultiSubnetFailover=True when connecting to an availability group listener or Failover Cluster Instance. During a multi-subnet failover, the client will attempt connections in parallel. During a subnet failover, will aggressively retry the TCP connection.
There is another importance parameter RegisterAllProvidersIP. The effect of this property value depends on the client connection string, as follows:
• Connection strings that set MultiSubnetFailover to true
Always On availability groups sets the RegisterAllProvidersIP property to 1 in order to reduce re-connection time after a failover for clients whose client connection strings specify MultiSubnetFailover = True, as recommended. Note that to take advantage of the listener multi-subnet feature, your clients might require a data provider that supports the MultiSubnetFailover keyword.
• Connection strings that do not set MultiSubnetFailover to true
When RegisterAllProvidersIP = 1, any clients whose connection strings do not use MultiSubnetFailover = True, will experience high latency connections. This occurs because these clients attempt connections to all IPs sequentially. In contrast, if RegisterAllProvidersIP is changed to 0, the active IP address is registered in the Client Access Point in the WSFC cluster, reducing latency for legacy clients. Therefore, if you have legacy clients that need to connect to an availability group listener and cannot use the MultiSubnetFailover property, we recommend that you change RegisterAllProvidersIP to 0.
Important
When you create an availability group listener through the WSFC cluster (Failover Cluster Manager GUI), RegisterAllProvidersIP will be 0 (false) by default.
===============
Reference
===============
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery#connecting-with-multisubnetfailover
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server?view=sql-server-ver15#RegisterAllProvidersIP