0

I have a question about using multiple subnets in a AlwaysOn Availability group. We have a AlwaysOn setup (SQL 2012) already in production that has a listener that is configured for 2 subnets. 1 of these is local and the other is used for DR. Obviously, we want to avoid anything being routed through the DR subnet on everyday use. We had an issue where when we first did this that we experienced timeouts. The ways to overcome this is well-documented and we have implemented fixes and looking at some of the alternate ways.

Primarily, we are using “MultiSubnetFailover=True” in our connection strings, but we are looking at modifying the RegisterAllProviderIP cluster resource setting to 0 due to some limitations on the MultiSubnetFailover parameter to handle this and then just working around the slower cutover time as a result. The only other subnet we are using is DR so I think this is manageable.

My question though is if AlwaysOn changed the way connections are made in any of the newer versions of SQL (or .NET Framework) that prevents the need to do some of this. We are currently planning to upgrade the coming year to SQL 2016 and at the same time move to AlwaysOn AG. Is “MultiSubnetFailover=True” or changing the RegisterAllProviderIP still required in SQL 2016 or is any of this handled automatically\different?

Most of the information regarding this is specific to SQL 2012 and I cannot seem to find information that definitively says if this applies for all versions. I did find some articles that mentioned this “MultiSubnetFailover=True” is handled automatically, but not sure if that was just application specific or product specific. Thanks.

1

... but we are looking at modifying the RegisterAllProviderIP cluster resource setting to 0 due to some limitations on the MultiSubnetFailover parameter ...

Could you let me know what those limitations are? AFAIK there aren't any... MultiSubnetFailover is the defacto way to deal with your issue. Period. Hence why I would be interested to know what the limitations of the parameter are.

My question though is if AlwaysOn changed the way connections are made in any of the newer versions of SQL (or .NET Framework) that prevents the need to do some of this.

No, there are no changes in SQL Server. There is a change in .NET 4.6.1 that will use multisubnetfailover logic if the initial connection doesn't happen in the first 500 miliseconds if multisubnetfailover was not added to the connection string.

Is “MultiSubnetFailover=True” or changing the RegisterAllProviderIP still required in SQL 2016 or is any of this handled automatically\different?

You should ALWAYS use multisubnetfailover=true unless the client driver does not support it... in which case, upgrade the client driver and/or application... or deal with the application timing out.

|improve this answer|||||
  • Thank you. According the Microsoft, MultiSubnetFailover=True is not Supported by NET Framework 3.5 (or earlier) or OLEDB connections. I can confirm that it does not work with OLEDB connections because we ran into an issues with this. – Dustin Sep 8 '17 at 13:55
  • @Dustin Right - that's not an issue with MultiSubnetFailover that's an issue of using an out of date client driver. If something new comes out next week they aren't going to back port it to .net 2.0. Microsoft also put out a notification that the SNAC OLEDB client would no longer be worked on after the 2012 release which does support the keyword. Again, this is an issue with updated clients and not the keyword or how it works internally. – Sean Gallardy Sep 8 '17 at 15:33

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.