question

rohinik avatar image
rohinik asked

I am able to connect to SQL Server successfully without adding MultiSubnetFailover= True parameter in connection string in a multi sub environment with SQL AO. Unable to test the impact of this parameter.

Hi all,

I have setup SQL AO in multi subnet environment in a windows failover cluster. As per documentation, we need to add MultiSubnetFailover=True parameter in connection string to get the successful connection.

Refer the links below -

https://docs.microsoft.com/en-us/sql/connect/jdbc/jdbc-driver-support-for-high-availability-disaster-recovery?view=sql-server-ver15

https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/

However I am able to connect to SQL AO listener and get connection successfully without adding this parameter. I have simulated the multi subnet in cloud environment where Primary and one secondary are running in same subnet and one secondary is running in different subnet. I also tried removing primary and secondary VM's from network to test the multi Subnet failover. In that case as well, I am able to get the connection

I am using SQL server 2016

Can someone tell me how can I test the impact of MultiSubnetFailover parameter? Is it really needed in case on Windows failover cluster?

sql server
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Out of interest that is the TTL on your DNS? Sounds like this is very low so your not seeing the need for the option.

Do you also have register all providers set on your availability group / cluster configuration?

If so you should see the listener name in DNS multiple times with the different registered IP addresses 10.10.10.#, 10.10.11.# etc.

The param is basically acting like a round robin, so if your TTL is to high to make the switch seamlessly, MultiSubNet enabled will allow it to try 1 address, if no response try the next etc.

0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.