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 may require updating to the latest version of 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.

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.

The Collect MSSQL HADR - Availability Group Info pattern extension introduces the following CI classes that extend existing CMDB classes:

• 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

• 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]	References the MSFT SQL instance​ [cmdb_ci_db_mssql_instance] table.

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

• MSSQL Components Info [mssql_components_info]

• MSSql DB On Windows pattern extension: Collect MSSQL HADR - Availability Group Info

  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.

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

CI relationships

The MSSql DB On Windows pattern creates the following relationships and references to support Microsoft SQL Server and Cluster discovery. References link to records in other tables and don't appear in the CI Relationship [cmdb_rel_ci] table.

*This reference is created only when the pattern uses the WMI queries.

The MSSql DB On Windows pattern creates the following relationships and references using the Collect MSSQL HADR - Availability Group Info pattern extension. References link to records in other tables and don't appear in the CI Relationship [cmdb_rel_ci] table.

Parent Topic:Database discovery

Related topics

Add the Horizontal Pattern probe to a classifier

Horizontal Pattern probe

Database Administrator (DBA) report discovery