3

With AlwaysOn, there are 3 options for the keyword MultiSubnetFailover

  • True (yes)
  • False (no) (example)
  • Not sent/used

Not all applications support it's use in connection strings. Not all vendor contacts understand it's importance to performance at failover.

As a DBA I would like to be able to validate what property is being used by the application connection. If I have not made the appropriate setting change to RegisterAllProvidersIP it can cause issues. See: Follow-up: After Creating an Availability Group Listener

It does not appear to be capture in either sys.dm_exec_sessions nor sys.dm_exec_connections

How can I capture what (if any) keyword values were used to create a connection to one of my instances?

6

I don't believe this configuration setting is sent to the server. It controls the client's interpretation of a DNS lookup returning multiple IP addresses, and there is no functional need for the server to be informed about it. And the client doesn't send arbitrary name/value pairs to the server. The fields that a client sends to the server are dictated by the TDS protocol, and it would require a protocol revision to add new ones.

Also while properly setting MultSubnetFailover at the client continues to be a best-practice, newer SQL Server client libraries have changed their behavior to mitigate the issues arising from not setting MultiSubnetFailover. See Using Transparent Network IP Resolution. Newer clients will have only a brief delay in connecting when the first IP returned from DNS is not the active AG Listener, instead of the old behavior of waiting 20-30 seconds before attempting to connect to the second IP.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.