SQL Server – Availability Groups Using Multiple Subnets

availability-groupssql server

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.

Best Answer

... 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.

Related Question