Category Archives: SQL

SQL Server AlwaysOn Availability Groups, SCCM and MultiSubnetFailover

EDIT: The issue below has finally been addressed in version 1906:

https://docs.microsoft.com/en-gb/sccm/core/plan-design/changes/whats-new-in-version-1906#improvements-to-support-for-sql-always-on

——————————————————————————————————-

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.