If the member Servers are deployed across 2 subnets, the DNS Entry for the Listener has 2 IP Addresses, one for each subnet. As a result it is possible for AppInsight applied to the Listener to pick up the wrong IP Address and indicate the SQL as down, when in fact it is simply assigned to the wrong IP Address.
Let’s say you have configured SQL Listener with two IP address, one for DC and another for DR subnet. Suddenly due to some network issues, the Availability group fails over to the Secondary replica in DR. Previously in the failover cluster manager, the DC Listener IP address was online but after failover the DR Listener IP address came online. The application tries to connect the primary replica using the Listener. It connects with DNS to resolve its virtual network name and gets a list of multiple IP addresses in return. The application tries to connect with the first IP address and could not connect to it. Once it gets a timeout issue, it checks for another IP address, and the connection is established. It is a trial and error approach. If we have multiple subnets, the application connection might get delayed further.
We can use the configuration MultiSubnetFailover=True, in the connection string, to resolve this issue. Once we enable this parameter, the Application tries to connect both IP address simultaneously. Whichever IP address connection is successful, the application connects with the Primary replica using that IP address and routes read-write queries on that replica.
Please add this functionality to AppInsight for SQL so that the Virtual Server can be monitored in this scenario.
