TransparentNetworkIPResolution in SQLClient for .NET 4.6.1

  • Article

A new Connection String property has been added for SqlConnection in .NET 4.6.1.  See

SqlConnection.ConnectionString

But as of this writing the property has not been properly documented.  And the new property is set to true by default:

            var con = new SqlConnection("server=.;Integrated Security=true");
var cb = new SqlConnectionStringBuilder(con.ConnectionString);
Console.WriteLine($"TransparentNetworkIPResolution= {cb.TransparentNetworkIPResolution}");

So this post is a quick preview of the enhanced behavior.

The background to this is that when you deploy an AlwaysOn Availaibility Group with an AG Listener with IPs on two different subnets, then the clients need to be configured with MultiSubnetFailover=true, or else they may experience connection timeouts when the AG Listener is not active on the IP that is returned first by your DNS server.

The initial plan was to change the default for MultiSubnetFailvoer to true, so that the SqlClient attempted a simultaneous connection to both IPs by default.   This was mentioned here. At some point this plan was changed, and instead the default connection behavior was modified to prevent timeouts even without MultiSubnetFailover.

The reason for the timeout before was that the without MultiSubnetFailover, SqlClient would attempt to connect to the first IP returned from the name lookout, and only after that failed would it attempt the other addresses.  This timeout was something like 20sec, and the default connection timeout for SqlConnection is only 15sec.  So the client would either see extreme delays or connection failures.

From spelunking the Reference Source here, here's what it looks like the new behavior is: An initial connection attempt to the first-returned IP address is still made, but that attempt is timed-out after only 500ms, and then connection attempts to all the IP addresses are attempted in parallel.

So this new behavior should eliminate the long delays and connection failures for clients who have neglected to set MultiSubnetFailover=true.  They will still suffer a 500ms delay when the AG Listener is not on the first-returned IP address, but once the connection is made it will be pooled and so this should only happen rarely.

It's still a best-practice to set MultiSubnetFailover=true when connection to an AG Listener, but it appears that it's no longer required.

I'll update this thread if I get any more official confirmation or documentation.

David