Share via

Can multisubnetfailover be configured in scom

ronald van den berg 241 Reputation points
2020-12-10T10:43:42.317+00:00

I'm building a new managementgroup and we want to use sql in an always on configuration using multisite.
SQL registers all it's ip's on which it might listen in dns that causes 50% of the connection lookups to fail.
To resolve this i find 2 options on the big internet.

1 is to configure sql to register only the active ip's and set ttl very short so you only have a short period of possible issues after a failover.

2 is to configure multisubnetfailover in the client causing database lookups will always be tried on all ip's parallel.

Option 2 seems to be prefered for our situation but i cannot find out if and how this can be configured, there's no option in the ConfigService.config file for this.

Is option 2 possible, and if true then how?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments
Accepted answer
  1. ronald van den berg 241 Reputation points
    2020-12-11T13:09:50.64+00:00

    Ok, i've ask MS support the same question and multisubnetfailover is indeed not supported, also not for the 2019 version according to this document
    https://learn.microsoft.com/en-us/system-center/scom/plan-sqlserver-design?view=sc-om-2019#multisubnet-string

    So we will change the sql side to only register the active ip.

    Thanks all for thinking with me.

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,721 Reputation points
    2020-12-10T12:37:50.697+00:00
    0 comments
  2. ronald van den berg 241 Reputation points
    2020-12-10T12:46:34.6+00:00

    Thanks Tom, it is a nice document describing our situation, but it doesn't answer my question.

    As far as i know scom uses modern libraries, .net4.5 to connect to sql so the option multisubnetfailover should be an option to use, but how/where can i configure this in scom...or doesn't scom support this and should i change the ip registration config of sql?

    Gr,
    Ronald

    0 comments
  3. David Browne - msft 3,771 Reputation points
    2020-12-10T14:37:15.843+00:00

    If SCOM, or any application, is using the modern drivers, MultiSubnetFailover is only recommended, not required. The newer drivers for .NET, ODBC, OleDb and JDBC have implemented a feature Transparent Network IP Resolution that enables connectivity to multi-subnet clusters and to clusters using Distributed Network Names without specifying MultiSubnetFailover.

    See:

    .NET Framework SqlClient
    ODBC
    OleDb
    JDBC

    Under this new behavior, which is enabled by default, clients will only experience a very brief delay when connecting to a multi-IP SQL Server endpoint, if the first-returned IP is not online. Under the old behavior the clients waited for 20sec or so trying to connect to the first IP, which typically caused an error in the client app.

    So if you can find a way to set MultiSubnetFailvoer in SCOM, do it. But if not you should be fine, and there's no need to turn off RegisterAllProvidersIP on the AG Listener's Client Access Point, which was the previous mitigation for this issue.

    0 comments
  4. Cris Zhan-MSFT 6,616 Reputation points
    2020-12-11T10:05:01.713+00:00

    Hi @ronald van den berg ,

    Seems the Operations Manager does not support setting the MultiSubnetFailover parameter. This parameter is not used in Operations Manager connection strings.
    But I can only find the documentation of SCOM2012 that record this. Maybe you need to consult an expert in SCOM.
    https://learn.microsoft.com/en-us/previous-versions/system-center/system-center-2012-R2/jj899851(v=sc.12)?redirectedfrom=MSDN

    So you may need to set the RegisterAllProvidersIP to 0, and then set the HostRecordsTTL parameter.

    Best Regards,
    Cris

    0 comments