Microsoft SQL Server and Cluster discovery

Microsoft SQL Server and Cluster discovery Microsoft SQL Server and Cluster discovery

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.

The following procedure enables appropriate visibility of the SQL clusters. 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. Ensure that an sqlservr.exe process to trigger the MSSql DB On Windows pattern is running on the server. Run a CI discovery. For more information, see Running discoveries in your network .

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

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 using the Collect MSSQL Components Info pattern extension. 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

Reference [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.

Reference 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]

Parent Topic: Database discovery

Related concepts <a class="link" href="https://docs.servicenow.com/bundle/washingtondc-it-operations-management/page/product/discovery/concept/dba-report-discovery-pattern.html" title="Discovery and Service Mapping uses the Patterns extension sections to provide a Database Administrator report (DBA report) for the Apache Cassandra, Microsoft SQL, MySQL, MongoDB, and Oracle databases. The extension sections for each DB pattern populate the related entries tables. Discovering some of these resources requires updating the Discovery and Service Mapping Patterns application from the ServiceNow Store.">Database Administrator (DBA) report discovery

Related tasks Add the Horizontal Pattern probe to a classifier

Related reference Horizontal Pattern probe