SQL Server AlwaysOn Availability Groups, SCCM and MultiSubnetFailover

At the time of writing there appear to be very little on the internet about this particular configuration.

In short, we needed to use the MultiSubnetFailover option string with our SQL AOAG making up the CM2016 back end. We have two SQL DBs, one in London and one in Slough. As one might typically expect, these exist on separate subnets.  After hunting around the internet to try to ensure the supportability of this configuration it quickly became clear that a call to Premier Support was required to confirm. In particular, we were interested to know whether the ODBC System DSN driver should remain the same on the site server. By default this uses an old version which doesn’t have the MultiSubnetFailover checkbox available. We figured this might need changing to the newer 11.x native client when AOAG were used over separate subnets. There was an awful lot of to-and-fro between ourselves and Microsoft and it was clear this was an area where there was little clear knowledge. We did finally prize an answer out of them however and the following is an edited transcript of the outcome:

  1. We wish to know if SCCM 1511 and 1602 should be able to talk to such a multi-site SQL cluster OK?

ANS: Multi-site Cluster is supported but it`s a complex solution to implement. It is heavily dependent on the Hardware Supportability and Networking. Storage should also be carefully considered:https://blogs.technet.microsoft.com/meamcs/2013/11/09/microsoft-windows-multi-site-failover-cluster-best-practices/

Configurations for the SQL Server Site Databasehttps://technet.microsoft.com/en-us/library/gg682077.aspx#BKMK_SupConfigSQLDBconfig

  1. Should we make SCCM use the “SQL server native client V 11.0” ODBC connection “system DSN” connection which has a check box that seems to have “multisubnetfailover” option or the default SCCM installed “SQL driver” for ODBC connection?

ANS: We should not be making any changes to the default configuration made as part of the ConfigMgr installation. ConfigMgr does not support SQL Server Multi-subnet clustering.

  1. If we upgrade SCCM from 1511 to 1602  (or to a later version in future) will the upgrade make the site server use the “SQL driver ODBC” connection again, needing a manual change to SQL native client again?

ANS: We should not be altering the default configuration at ODBC

  1. [PS Engineer] wrote the following “…SCCM Components make use of dll’ (some components) andSQL Server native Client both.  Native Client will get updated as per SCCM need, and we can leave sqlsvr32.dll’ as its default version.”

Our response: But slqsvr32.dll, does it have the multisubnetfailover option? We cannot see this. Where and how do we configure ALL the components to use multisubnetfailover on primary site server and other component servers as appropriate?

ANS: SQL Server Multi Subnet cluster is not supported.

 

2 thoughts on “SQL Server AlwaysOn Availability Groups, SCCM and MultiSubnetFailover”

  1. Did you ever get any movement from them on this or did you decide to not use a Multisubnet cluster. Did you simply run SQL in one site, or did you have to do a layer-2 extension in order to have a shared listener IP?

  2. From memory I think we used a stretched subnet between datacenters in the end. Really crap way of doing it but the only solution that worked at the time. Personally if it was fully up to me, I’d have said don’t bother with AOAG with SCCM. They’re more trouble than they’re really worth. If you house your SQL on the SCCM box and the whole thing is virtualised and backups taken every night, then you can usually restore anyway without issue.

Leave a Reply

Your email address will not be published. Required fields are marked *