The Discovery and Service Mapping Patterns application uses the MSSql DB On Windows pattern to find Microsoft SQL DB servers and clusters on your infrastructure. Discovering some of these resources requires updating the Discovery and Service Mapping Patterns application from the ServiceNow Store.

Request new or enhanced Patterns on the ServiceNow® Store

Visit the ServiceNow Store to view all the available updates and for information about submitting requests to the store. For cumulative release notes information for all released apps, see the ServiceNow Store version history release notes.

For detailed information on SQL Server supported versions see: Detailed information on products discovered by ITOM Visibility

Prerequisites

Verify that the following applications are up to date
  • Discovery and Service Mapping Patterns starting with version 1.7.0 (August 2023)
  • Visibility Content starting with version 6.12.1 (August 2023)
Create Windows credentials
After verifying that the MID Server is connected to the target Windows server, create Windows credentials on the ServiceNow AI Platform. For more information, see Windows credentials.
Create applicative credentials when using SQL authentication
For more information, see Applicative credentials.
Verify the configuration of the MID Server
Verify that the MID Server has the necessary permissions to read the Windows Registry on a remote mode. The MSSql DB On Windows pattern uses registry values to discover SQL cluster details.
Verify SQL Server discovery report is active
Verify that the SQL Server discovery report is active on your server. Discovery uses the SQL Server discovery report to discover Microsoft SQL Server components.
Verify the permissions to run WMI queries
Verify remote read-only access to the following WMI namespaces:
  • Root\CIMv2
  • Root\Microsoft\SqlServer\ComputerManagement*
Verify the SQL Server table permissions
The MSSql DB On Windows pattern requires read-only permissions to read the following tables:
  • @@version
  • sys.configurations
  • sys.dm_exec_sessions
  • sys.dm_os_schedulers
  • SERVERPROPERTY
Verify availability group table permissions
Verify the following table permissions to discover Microsoft SQL Server Always On availability groups:
  • VIEW SERVER STATE access to the sys.dm_hadr_availability_group_states table
  • Read access permission to the following tables:
    • sys.availability_groups
    • sys.availability_group_listeners
    • sys.availability_group_listener_ip_addresses

SQL failover cluster instance (FCI)

Updating the version 1.6.1 set of Patterns available from the ServiceNow® Store starting August 2023 enables an appropriate discovery of the Always On availability group details and the SQL Failover Clusters. When the failover scenario is occurring, the MSSql DB On Windows pattern discovers the failover cluster (Node 2). Then a new MSFT SQL instance and databases are created.

Node 2 is a duplicate of Node 1

The following procedure enables appropriate visibility of the SQL clusters.
  1. Set the sn_itom_pattern.discover_mssql_cluster.load_cluster_name_from_registry=true MID Server property to true to enable the Collect MSSQL Cluster info- MSSQL Cluster extension section.
  2. Ensure that an sqlservr.exe process to trigger the MSSql DB On Windows pattern is running on the server.
  3. Run a CI discovery.

    For more information, see Running discoveries in your network.

  4. Either keep duplicate instances and databases or delete them.
    • To keep the duplicate instances and databases, keep the default configuration.
      • The install status of the nodes of the passive cluster changes to Absent.
      • The install status of the nodes of the active cluster changes to Installed.
    • To delete the passive instances and databases, set the discovery.mssql.cluster.instance.postsensor.delete system property to True.

Microsoft SQL Server Always On availability groups support

Starting from Discovery and Service Mapping Patterns version 1.27.0, the MSSql DB On Windows pattern extension Collect MSSQL HADR - Availability Group Info supports the discovery of Microsoft SQL Server Always On availability groups.

Figure 1. Data model of Collect MSSQL HADR - Availability Group Info
Collect MSSQL HADR - Availability Group Info pattern extension relationships
Prerequisites
Verify availability group table permissions
Discovered resources
Resources discovered by the pattern extension: Collect MSSQL HADR - Availability Group Info
Discovered relationships
Relationships discovered by the pattern extension: Collect MSSQL HADR - Availability Group Info

Data collected by Discovery during horizontal discovery

Discovery populates the data in the CMDB when running the MSSql DB On Windows pattern.

MSFT SQL instance [cmdb_ci_db_mssql_instance]

The data collected for the running processes of the database (the actual SQL server) is referred to as the database instance

Field Description
Name [name]

The field name.

For example: MSSQLSERVER@windows_server_name
Instance Name [instance_name]

The SQL server instance name.

For example: MSSQLSERVER
Is clustered [is_clustered] Indicates the type of installation:
  • If this value is set to true,the SQL server is a part of a cluster, and the failover mechanism is enabled.
  • If this value is set to false, the SQL server is a standalone deployment and the failover mechanism is disabled.
Comments [comments] The type of instance:
  • standalone_instance
  • failover_cluster
  • always_on
Version [version] The SQL server version number. For example: 16.0.1000.6
Version Name [version_name] The SQL server version by release dates. For example: The value 2022 represents version 16.0.1000.6
Service Pack [service_pack] The service pack version. For example: SP2.
Edition [edition] The type of the edition. For example: Enterprise Edition.
TCP Port [tcp_port] The TCP port that the MSSQL instance uses to accept connections.
Install Status [install_status] The install status of the instance:

Installed- The instance is currently running.

Absent- The instance isn’t running and it’s the backup instance for a failover scenario.
MSSQL database [cmdb_ci_db_mssql_database]
Field Description
Name [name] The name of the database. For example: msdb.
Install Status [install_status] The install status of the database.
Instance Name [instance_name] The instance name.
MSSQL Cluster Node [cmdb_ci_mssql_cluster_node]
Field Description
Name [name] The server host name
IP Address [ip_address] The hosting Windows server's IP address
Cluster [cluster] Reference to the SQL cluster record.
Server [server] Reference to the hosting Windows server record.
MSSQL Cluster [cmdb_ci_mssql_cluster]
Field Description
Name [name] The SQL cluster network name​
IP Address [ip_address] The SQL cluster's virtual IP address.​
MSSQL Service Info [mssql_sqlservice_info]
Field Description

Description [description]

 Describes the MSSQL service. For example: "Provides storage, processing, and controlled access of data and rapid transaction processing."

Service Name [service_name]

 The MSSQL service name as presented on the server. For example: MSSQL$NAMEDMSSQL2016

Binary Path [binary_path]

 The file path of the service. For example: C:\Program Files\Microsoft SQL Server\MSSQL13.NAMEDMSSQL2016\MSSQL\Binn\sqlservr.exe

CI [ci]

 Reference to MSFT SQL instance record [cmdb_ci_db_mssql_instance]

Installed On [installed_on]

 Reference to the Windows server record [cmdb_ci_win_server]
Note: This field isn’t populated for standalone deployments.
MSSQL components Info [mssql_components_info]
Field Description

Install Path [install_path]

 The install path of the component service. Foe example: C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL

Service Name [service_name]

 The components service type of the server. For example: Database Engine Services

SKU Name [sku_name]

 The MSSQL version of the component service. For example: Express Edition (64-bit)

Version [version]

 The MSSQL version number of the component service

CI [ci]

 Reference to the MSFT SQL instance record.

Installed On [installed_on]

 Reference to the Windows server record [cmdb_ci_win_server].
Note: This field is populated only for SQL Clusters and not for standalone deployments.
MSSql DB On Windows pattern extension: Collect MSSQL HADR - Availability Group Info
Table 1. MSSQL Availability Group [cmdb_ci_mssql_ag]
Field Description
Cluster ID [cluster_id] Globally unique identifier (GUID) of the availability group.
Name [name] Cluster name.
IP Address [ip_address] IP address of the cluster.
Table 2. MSSQL Availability Group Replica [cmdb_ci_mssql_ag_replica]
Field Description
Replica ID [replica_id] Unique ID of the replica.
Role [role] Role of the replica.
Possible values are:
  • Primary
  • Secondary
  • Resolving
Availability Mode [availability_mode] Availability mode of the replica.
Possible values are:
  • Asynchronous Commit
  • Synchronous Commit
  • Configuration Only
Failover Mode [failover_mode] Failover mode of the availability replica.
Possible values are:
  • Automatic
  • Manual
Readable Secondary [readable_secondary] Whether an availability replica that’s performing the secondary role can accept connections from clients.
Possible values are:
  • true
  • false
Availability Group [availability_group] References the MSSQL Availability Group [cmdb_ci_mssql_ag] table.
Name [name] Name of the replica server.
Serial number [serial_number] Unique ID of the replica.
State [state] Failover state.
Possible values:
  • Pending
  • Online
  • Offline
  • Failed
  • Failed No Quorum
Table 3. MSSQL Availability Group Listener [cmdb_ci_mssql_ag_listener]
Field Description
Name [name] Name of the availability group listener.
Listener ID [listener_id] Resource globally unique identifier (GUID) from Windows Server Failover Clustering (WSFC) cluster.
IP Subnet Mask [ip_subnet_mask] IP subnet mark for the IPv4 address, if any, that’s configured for the availability group listener.
Availability Group [availability_group] References the MSSQL Availability Group [cmdb_ci_mssql_ag] table.
Primary Replica [primary_replica] References the MSSQL Availability Group Replica [cmdb_ci_mssql_ag_replica] table.

CI relationships

The MSSql DB On Windows pattern creates CI relationships to support Microsoft SQL Server and Cluster discovery.
CI Relationship CI

Windows Server

[cmdb_ci_win_server]

 Runs::Runs on

MSSQL Cluster

[cmdb_ci_mssql_cluster]

​

MSFT SQL instance​

[cmdb_ci_db_mssql_instance]​

 Runs::Runs on

MSSQL Cluster

[cmdb_ci_mssql_cluster]​

MSSQL Cluster Node​

[cmdb_ci_mssql_cluster_node]​

Cluster::Cluster of​

References [cluster]

MSSQL Cluster

[cmdb_ci_mssql_cluster]​

Windows Server​

[cmdb_ci_win_server]​

Hosts::Hosted by​

Reference [server]

MSSQL Cluster Node​

[cmdb_ci_mssql_cluster_node]​

MSFT SQL instance​

[cmdb_ci_db_mssql_instance]

 Runs on::Runs

Windows Server​

[cmdb_ci_win_server]​

MSFT SQL instance​

[cmdb_ci_db_mssql_instance]

 Contains::Contained by​

MS SQL Database​

[cmdb_ci_db_mssql_database]​

MSSQL Service Info [mssql_sqlservice_info]

Note: This relationship is created only when the Pattern uses the WMI queries.
 References

MSFT SQL instance​ [cmdb_ci_db_mssql_instance]
The MSSql DB On Windows pattern creates CI relationships using the Collect MSSQL HADR - Availability Group Info pattern extension.
CI Relationship CI
MSSQL Availability Group [cmdb_ci_mssql_ag] Extends from Cluster [cmdb_ci_cluster]
MSFT SQL instance [cmdb_ci_db_mssql_instance]​ Member of::Members MSSQL Availability Group Replica [cmdb_ci_mssql_ag_replica]
MSSQL Availability Group Listener [cmdb_ci_mssql_ag_listener] Extends from Endpoint [cmdb_ci_endpoint]
MSSQL Availability Group Listener [cmdb_ci_mssql_ag_listener] References MSSQL Availability Group [cmdb_ci_mssql_ag]
MSSQL Availability Group Listener [cmdb_ci_mssql_ag_listener] References MSSQL Availability Group Replica [cmdb_ci_mssql_ag_replica]
MSSQL Availability Group Listener [cmdb_ci_mssql_ag_listener] Used by::Uses MSSQL Availability Group [cmdb_ci_mssql_ag]
MSSQL Availability Group Replica [cmdb_ci_mssql_ag_replica] Contains::Contained by MS SQL Database [cmdb_ci_db_mssql_database]
MSSQL Availability Group Replica [cmdb_ci_mssql_ag_replica] Cluster of::Cluster MSSQL Availability Group [cmdb_ci_mssql_ag]
MSSQL Availability Group Replica [cmdb_ci_mssql_ag_replica] Extends from MSSQL Cluster Node [cmdb_ci_mssql_cluster_node]​
MSSQL Availability Group Replica [cmdb_ci_mssql_ag_replica] Hosted on::Hosts Windows Servers [cmdb_ci_win_server]​
MSSQL Availability Group Replica [cmdb_ci_mssql_ag_replica] References MSSQL Availability Group [cmdb_ci_mssql_ag]