Microsoft SQL Server

Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. SQL Server is commonly used by businesses for small- to medium-sized databases, but the past five years have seen greater adoption of the product for larger enterprise databases. The codebase for MS SQL Server (before version 7.0) originated in Sybase SQL Server. It was Microsoft's entry to the enterprise-level database market, competing against Oracle, IBM, and, later, Sybase itself.

Pattern summary

Platforms supported by the pattern

The pattern identifies Microsoft SQL Server running on the Microsoft Windows and Linux platforms. 

Identification

The SQL Server is shipped with additional components besides the actual database server and supporting processes. The presence of these components is governed by the edition of the SQL Server product and by user choices made at installation.

The additional components (some of which Atrium Discovery discovers using other patterns) are the following:

• SQL Server Integration Services (SSIS) - a platform to build data integration and workflow applications. The primary use for SSIS is data warehousing, as the product features a fast and flexible data extraction, transformation, and loading (ETL) engine.
• SQL Server Analysis Services - a group of OLAP and Data Mining services provided in Microsoft SQL Server
• SQL Server Reporting Services - a server-based report generation environment
• SQL Server Notification Services - offers a scalable server engine to run notification applications
• SQL Server Management Studio - an application included with SQL Server for configuring, managing, and administering all components within Microsoft SQL Server
• SQL Server Agent - an application is doing maintenance jobs for servers.

Triggers

Software Instance type attributes created

The patterns in this module will set the following value for the type attribute on a SI:

Simple identification mappings

The following processes are identified through the use of Simple Identifiers. They are modeled using the primary and associate relationships within a full Software Instance for Adaptive Server Enterprise.

Pattern configuration options

The pattern can turn off all SQL queries by setting 'db_queries' to false. Also, this setting will impact the Externed DB discovery pattern.

Versioning

The pattern tries to get version information from a database query on both Linux and Windows. If that fails, different versioning methods are tried depending on the platform.

Database Query

The pattern can obtain version information from the following SQL query:

• SELECT SERVERPROPERTY('productversion') AS ProductVersion, SERVERPROPERTY ('productlevel') AS ProductLevel, SERVERPROPERTY ('edition') AS Edition

To enable SQL Server versioning, you need to set up valid SQL Server database credentials.

Windows operating system

Internal version information for the product is currently collected using one of several approaches described below. The pattern uses standard mappings to map the internal version to the marketing version (e.g. 13.0.5207.0 is 2014 SP2).

Active Versioning - WMI Query

The pattern attempts to obtain version information from the following WMI query:

• The WMI query executed is: select Version from CIM_DataFile where Name = '<abs_path_to_trigger_process>'

This returns a build number that the pattern maps to a version number using regular expressions. For example, a build number of 2014.120.5538.0 corresponds to a version of 12.0.5538.0.

Registry Versioning

Versioning is found for the CSDVersion or CurrentVersion registry key.

This key is always under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server or HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

If we have identified the instance we will only check registry keys whose path contains the instance name. Otherwise, we will check almost all registry subdirectories.

Package versioning

The pattern tries to obtain version information from the version field of one of the following packages:

• Microsoft SQL Server <version number> <SP number> Database Engine Services
• Microsoft SQL Server <version number> <SP number> LocalDB
• SQL Server <version number>

Linux operating system

Version information for the product is collected using one of two approaches - DB Query,  parse error log file (Microsoft provides these methods), and package versioning. The pattern executes the methods in order and uses the first successful method it can. 

Log file versioning

We extract the full version and edition from /var/opt/mssql/errorlog.

Package versioning

Pattern looking for packages with regex '^mssql-server$', and if several found packages, only one - collects its version. The package version shows only the full version without edition. 

Additional Attributes

Windows operating system

A note on Registry keys

The pattern checks for the registry key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server or HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer. If we have identified the instance, we will only check registry keys whose path contains the instance name. Otherwise, we will check almost all registry subdirectories.

Instance

Instance information is obtained from the -s argument of the trigger process.

If we cannot find instance information from the trigger process, we search the registry for keys of the form HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance name>\ MSSQLServer\SuperSocketNetLib\Tcp\TcpPort.  Suppose the port described in that registry key is the same as the port in the DiscoveredListeningPorts attribute corresponding to the trigger process. In that case, we use the <instance name> from the registry path as the SI instance. If no instance name exists, the default of MSSQLServer is used.

Edition information

Microsoft publishes details of SQL Server editions described here.

If the instance is MICROSOFT##SSEE or MICROSOFT##WID the edition is always set to "Windows Internal Database."

Otherwise, we check the Edition from:

• The database query
• The \\setup\\edtion subkey of these registry directories

Backup Directory Information

Backup Directory information is also obtained from the BackupDirectory registry key

Data Directory path information

There are three methods of obtaining the data directory.

The best method is to use the SQLDataRoot registry key.

The second best method is to use the trigger process arguments. Unfortunately, we can't find the trigger process arguments directly, but we can check the registry keys of the form <root>\MSSQLServer\Parameters\SQLArg[0-9] . If any have the form -d <filename>.mdf we can discover the data directory (e.g. -de:\DATA\master.mdf means a data directory of e:\DATA)

The third best method is to use the path to the trigger process. A trigger process of c:\Program Files\SQL Server\bin\sqlservr.exe implies a data directory of c:\Program Files\SQL Server\data

Licensing Information

The license key attribute is obtained from the Setup\ProductID registry key.

The creation date attribute is obtained by using one of the following methods:

• The database query SELECT create_date from sys.server_principals where name = 'NT AUTHORITY\\SYSTEM'".
• The file <install_root>\Setup Bootstrap\Log\summary.txt where you search for the end time : <date>.

The sample output of the summary.txt file used to find the creation date is as follows:

Overall summary:
Final result: Passed but reboot required, see logs for details
Exit code (Decimal): 3010
Start time: 2020-02-30 23:53:44
End time: 2020-02-30 23:58:13

Path to ERRORLOG

There are two methods of obtaining the path to the ERRORLOG file

The best method is to use the trigger process arguments. Unfortunately, we can't find the trigger process arguments directly, but we can check the registry keys SQLArg[0-9] . If any have the form -e <path>\ERRORLOG, you can discover the path to the error log file.

Alternatively, we can use the path to the trigger process. A trigger process of c:\Program Files\SQL Server\bin\sqlservr.exe implies an ERRORLOG file at c:\Program Files\SQL Server\LOG\ERRORLOG.

Determining Listening Port and Bind Address

If the pattern can identify the instance name and TCP is enabled, the pattern gets a list of ports from the following registry queries:

If the above fails, the pattern checks the following registry keys for port information:

• HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\<server name>\\MSSQLServer\\SuperSocketNetLib\\Tcp\\TcpPort

• HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer\\SuperSocketNetLib\\Tcp\\TcpPort

The pattern uses standard BMC Discovery functionality to obtain bind_address.

Cluster information

The pattern checks if the database is running on a cluster and, if so, what the name of the cluster is by checking one of the following registry keys:

• HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\MSSQLServer\\Cluster\\ClusterName
• HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\Microsoft SQL Server\\<server name>\\Cluster\\ClusterName
• HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\Microsoft SQL Server\\<instance name>\\Cluster\\ClusterName

The pattern models the SI as running on a Cluster if one of the following is true:

• If the "cluster name" is equal to the name of a ClusterService, the SI is modeled as hosted on the linked Cluster with a SoftwareService link to the ClusterService
• If the "cluster name" is equal to the name of a Cluster, and that cluster has a ClusterService whose name is similar to the SQL Server instance. The SI is modeled as hosted on the linked Cluster.

Clustered vs Standalone SIs

'Microsoft SQL Server' SI can be identified as clustered on MS Cluster when Cluster has 'SQL Server' ClusterResource with the same 'instance' attribute as SI.

MS SQL AlwaysOn Availability Group 

Linux operating system

Configuration File

The main configuration file of MS SQL for Linux is stored in /var/opt/mssql/mssql.conf. We get the following information from the file

• bind_address - A bind address of 0.0.0.0 means use of any available IPv4 address. So, if a bind_address of 0.0.0.0 is reported, we set the bind_address of the Software Instance to:
  
  
• listening_ports
• data_directory
• backup_directory

Instance name and instance ID

Instance name is username of triggered process owner, by default is 'mssql'
Instance ID extracted from the file: <SQL_home_directory>/.system/instance_id

Obtaining detailed SQLServer Database and table information

A different pattern has been created to query the Microsoft SQL Server to obtain the database list and (optionally) database table details. For more information about this pattern, see Microsoft SQL Server - Database Detail Pattern.

Pattern model

An SQL Server installation typically comprises one Database server which hosts several databases. The current pattern definition uses the Database server process (sqlservr.exe or sqlservr) as the trigger process. The name of the DB server is extracted from the 'servername' variable created during process identification. This name is used to create a unique SI.

Configuration Options

There is a configuration option available for this product that allows the running of database queries. To enable this option user needs to set "True" to variable db_queries .

SI Depth

The pattern produces a Deep (Instance Based) SI for Microsoft SQL Server. The key combines the DB server name, Type (Microsoft SQL Server), and the Host. If 'servername' variable is not populated through a regular expression match, it is set to a default value: 'MSSQLServer' which is a value given to default (single) MS SQL Server installations. If this occurs and additional information about the SQL Server can be obtained (i.e. version, edition, and listening port), then the Software Instance created still uses a key. In cases where some information is unavailable, a group-based Software Instance is created using the full path to the triggering process to group instances.