Named SQL Instances part of Availability Group not being displayed properly

[mqbk]

I have two AG's that have named SQL instances in the group ie: AG01 Node 1 = AG01\SQLINST Node 2 = AG02\SQLINST

in my configuration file, I identified the nodes using \. I am noticing that on the SQL tab, it's showing the servers in an offline state. Also when I select the the server name from the dashboard menu ( i am using bosun to display my dashboard) it doesn't show me the tabs Stats, sql instance, active SQL, and top SQL on the Status page. The same goes for my stand alone instances as well. It only appears if I am using a SQL instance with a default instance. Is there something I am missing?

[NickCraver]

Can you paste your JSON config here? It may just be a JSON escaping issue...let's see.

[mqbk]

/ Configuration for the SQL Server dashboard /

{
  // The default connection string used unless specifically provided on a node
  // $ServerName$ gets replaces with the name property of the instance
  "defaultConnectionString": "Data Source=$ServerName$;Initial Catalog=master;Integrated Security=SSPI;",
  // How often to poll all servers (defaults to 60 seconds if not configured)
  "refreshIntervalSeconds": 30,
  // Always On Availability Group Clusters
  "clusters": [
    {
      "name": "EZDISPENSEAG",
      "refreshIntervalSeconds": 20,
      "nodes": [
        { "name": "EZDSQLHAG01" },
        { "name": "EZDSQLHAG02" }
      ]
    },
    {
      "name": "EZVERIFYAG01",
      "refreshIntervalSeconds": 20,
      "nodes": [
        { "name": "EZVSQLHAG01" },
        { "name": "EZVSQLHAG02" }
      ]
    },
    {
      "name": "MCPSQLAG01",
      "refreshIntervalSeconds": 20,
      "nodes": [
        { "name": "MCPSQLHAG01" },
        { "name": "MCPSQLHAG02" }
      ]
    },
    {
      "name": "EZDUATAG",
      "refreshIntervalSeconds": 20,
      "nodes": [
        { "name": "UATSQLHAG01\\EZDUAT" },
        { "name": "UATSQLHAG02\\EZDUAT" }
      ]
    },
    {
      "name": "EZVUATAG",
      "refreshIntervalSeconds": 20,
      "nodes": [
        { "name": "UATSQLHAG01\\EZVUAT" },
        { "name": "UATSQLHAG02\\EZVUAT" }
      ]
    },
    {
      "name": "CPUATAG",
      "refreshIntervalSeconds": 20,
      "nodes": [
        { "name": "UATSQLHAG01\\CPUAT" },
        { "name": "UATSQLHAG02\\CPUAT" }
      ]
    },
    {
      // Used purely for display
      "name": "DBADMINAG",
      // How often to poll the server
      "refreshIntervalSeconds": 20,
      // The list of nodes (servers) in this AG setup
      "nodes": [
        { "name": "DBASQLHAG01\\SQL2012" },
        { "name": "DBASQLHAG02\\SQL2012" }
      ]
    }
  ],
  // Standalone instances
  "instances": [
    // An example with all the options configured
    //{
    //  "name": "",
    //  "connectionString": "Data Source=NY-DB05;Initial Catalog=bob;Integrated Security=SSPI;",
    //  "refreshIntervalSeconds": 200
    //},
    // Some standalone servers (default instance) using default refresh and connection strings:

    //Production Servers
    { "name": "AHCS-SENDSUITE" },
    { "name": "AHCS-SQLTFS" },
    { "name": "BTSSQLCLUS01" },
    { "name": "BISQL01" },
    { "name": "GEMINI01" },
    { "name": "INFRASQL01" },
    { "name": "SPSQL01" },
    { "name": "THEGUILD\\VCENTER5" },
    { "name": "TRACSQL01" },

    //VAL
    { "name": "AHCS-VAL01\\CPVAL" },
    { "name": "AHCS-VAL01\\EZDVAL" },
    { "name": "AHCS-VAL01\\MSAXVAL" },
    { "name": "AHCS-VAL02\\EZVVAL" },

    //UAT
    //{ "name": "BizTalk-UAT01\\BTSSQLUAT01" },
    { "name": "BizTalk-UAT02" },

    //QA/STG
    { "name": "AHCS-QA01\\EZVQA" },
    { "name": "AHCS-STAGE\\CPSTAGE" },
    { "name": "AHCS-STAGE\\EZDSTAGE " },
    { "name": "BTSBPMQA01\\BTSSQLQA01" },

    //DEV
    { "name": "AHCS-DEV01\\EZVDEV" },
    { "name": "AHCS-DEV02\\CPDEV" },
    { "name": "AHCS-DEV02\\EZDDEV" },
    { "name": "BIDEVSQL01" },
    { "name": "MSAX-DEVSQL01" },
    { "name": "MSAX-PRODSQL01" },
    { "name": "MSAX-RPTSQL01" },
    { "name": "SPS-DEV" },
    { "name": "SPS-DEV\\EZPORTAL_SQLUAT" }

    //{ "name": "NY-DESQL01" },
    //{ "name": "NY-RESTORESQL01" },
    //{ "name": "NY-UTILSQL01" },
    // Example of a named instance
    //{ "name": "NY-SQL05\\MYINSTANCENAME" }
  ]
}

[elvishfiend]

It looks like it could be an issue where the AG Node Names aren't being escaped properly. Your non-AG Nodes all have a double slash (e.g. SPS-DEV\\EZPORTAL_SQLUAT) but your AG Nodes all have single slashes (e.g. DBASQLHAG01\SQL2012)

[mqbk]

I am looking at my file and I do have them. not sure why it didn't show up in here.

[elvishfiend]

@mqbk fair enough - it may just be that it only treated some parts of the config that you pasted as actual code, and those were the bits that showed the double slash. It might be worth fixing the markdown for the pasted config so that it's all treated as code.

[SQLSourcerer]

I think I might have a fix for the offline state problem at least: in SQLNode.ClusterInfo.cs, it's setting m.IsLocal based on comparing m.MemberName to ServerProperties.Data.?ServerName. If it instead looked at the network name to see whether its IsLocal flag is true, it would ge the right answer for named instance AG nodes.

Here's what I changed that line of code to m.IsLocal = state.Networks.Exists(n => n.IsLocal && string.Equals(m.MemberName, n.MemberName, StringComparison.InvariantCultureIgnoreCase));

[faygate]

@SQLSourcerer Thanks for the code change, worked in my copy of the code, but not when one of my nodes in the AG is a clustered instance. I therefore also had to change the SQL called in GetFetchSQL for the AGClusterNetworkInfo class in Opserver.Core\Data\SQL\SQLNode.ClusterInfo.cs to:

Select member_name MemberName,
       network_subnet_ip NetworkSubnetIP,
       network_subnet_ipv4_mask NetworkSubnetIPMask,
       network_subnet_prefix_length NetworkSubnetPrefixLength,
       is_public IsPublic,
       is_ipv4 IsIPV4,
       Cast(Case When member_name = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') Then 1 Else 0 End as Bit) IsLocal
  From sys.dm_hadr_cluster_networks;

Just posting here in case it's useful to anyone else :)