MSSQL server discovery

Discovery identifies and classifies information about MSSQL servers on Windows machines.

A pattern performs the discovery of MSSQL instances. This pattern replaces the Windows - MSSQL probe for new instances. If you want to continue to use probes, you can deactivate the Horizontal discovery probe, which launches the pattern, and then activate the necessary probes.

Credentials

Configure these credentials:
Credential type Additional notes
Applicative credentials The credentials need public access to the following:
  • The target Windows host.
  • The Microsoft SQL Server instance on the target Windows host. You must add the user to the SQL Server configuration.
  • The MID Server host. The SMO libraries locally impersonate the credentials for the target system prior to connecting to the Microsoft SQL Server. This behavior is enforced by Active Directory. Authentication only succeeds if the domain requirements specified here are met.
  • Role: On MS SQL 2014 and 2016, the sysadmin role must be assigned to the user to obtain the instance version. The public role does not have permission to retrieve the instance version.
    Note: If there are no matching discovery credentials, probes may attempt to connect using the MID Server service credentials. The MID Server service credentials are only valid if the MID Server host is on same domain as the Microsoft SQL Server host.

Specify the CI type on the credential as MSFT SQL Instance.

Windows credentials The credentials need access to the following:
  • A database user that can access these MSSQL tables: sys.dm_exec_sessions and sys.dm_os_schedulers, SERVERPROPERTY.
  • The CI Type MSFT SQL Instance.

Classifier, pattern, and probe

Discovery uses these classifiers, probes, and patterns for IIS discovery:
Classifier Trigger probes Pattern
Microsoft SQL Server
  • Horizontal discovery probe: launches patterns
  • Windows - MSSQL*
MSSql DB On Windows Pattern
*Probes inactive on new instances.

To use patterns, verify that the correct pattern is specified in the horizontal pattern probe on the classifier. See Add the Horizontal Pattern probe to a classifier for instructions.

Data collected

The running processes of the database (the actual SQL server) is referred to as the database instance. The following data is collected in the MSFT SQL Instance [cmdb_ci_db_mssql_instance] table:

Label Field name
Name name
Instance instance_name
TCP port tcp_port
Portdynamic port_dynamic
Version name version_name
Edition edition
Version version
Engine edition engine_edition
Install directory* install_directory
CPU sockets* cpu_sockets
Service Pack* service_pack
*These fields are populated by the pattern only.
The database is referred to as the catalog. The following data is collected in the MSFT SQL Catalog [cmdb_ci_db_mssql_catalog] table:
Note: Only the Windows - MSSQL probe, not the MSSql DB On Windows pattern, populates this table.
Label Field name
Name name
Status status
Note: You can find the data for the actual server on which the MSSQL instance and catalogs resides in the Windows computer [cmdb_ci_win_server].
Discovery populates the MSSQL database [cmdb_ci_db_mssql_database] table with the MSSql DB on Windows Pattern:
Label Field name
Name name
Operational status operational_status

For Microsoft SQL2000 servers, Discovery can match instance names to their process ID.

When Software Asset Management is activated, all discovered software is saved in at least two places in the CMDB: in the Software Installation [cmdb_sam_sw_install] table and the tables specific to the application type. A reference field is provided on these application-specific tables to the corresponding record in the Software Installation table.

Microsoft SQL Server cluster Discovery

Discovery identifies Microsoft SQL Server instances that are part of a cluster in the CMDB by the cluster name rather than as individual configuration items (CI). Discovery identifies Microsoft SQL Server instances in the CMDB that are part of a cluster by creating a Runs::Runs on relationship between the instance and its associated cluster, as well as the Runs::Runs on relationship between the instance and the actual host on which it runs.

Viewing discovered relationships

By default, a CI record for a server (node) hosting an SQL Server instance that is part of a cluster does not display the instance or the cluster as related items.

To see the cluster information on a CI record:
  1. Add the Cluster Nodes related list to the form. This list displays the name of the node and the parent cluster.
    Figure 1. Windows server and cluster
  2. Click the cluster name in the related list to drill into the CI record for that cluster. Individual SQL Server instances running on that cluster are listed in the Related Items hierarchy. The names in the list contain the instance name prepended to the host name with the @ symbol. In this example, MSSQLCLUSTER is the SQL Server instance name, and cluster-node2 is the name of the Windows host.
    Figure 2. Cluster and related SQL instance name
  3. Click the name of the SQL Server instance in the Related Items list to open the record for that CI. The instance name is expressed in the format instance@host name and displays the relationship hierarchy to the configured number of levels, including the system databases that the instance uses, the cluster on which the instance runs, and the server on which the cluster runs.
    Figure 3. SQL instance CI record

Defining a clusterable process

  1. Open the desired process classifier (Discovery Definition > CI Classification > Process).
  2. Add the Parameters related list (if not visible).
  3. Click New in the Parameters related list and add a new parameter, using the following values:
    • Name: Unique name of your choosing.
    • Type: Enter Cluster.
    • Value: Enter the following statement.
    resourceType: "Resource Type",
    isMatch: function(process, resource) {
        //javascript function that returns true if a resource matches the classified process
    }
    Table 1. Defining Clusterable Processes
    Parameter Description
    resourceType Resource type the platform should look for in the cluster's Windows Cluster Resources related list.
    isMatch Determines if the cluster resource that is found, based on the type, is a match for the classified process being examined.
    Figure 4. SQL Cluster Parameter