Product Description

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 code base for MS SQL Server (prior to version 7.0) originated in Sybase SQL Server, and was Microsoft's entry to the enterprise-level database market, competing against Oracle, IBM, and, later, Sybase itself.

Known Versions

Each version comes in several editions, details are here

Windows

• 6.0
• 6.5 (including SP1, SP2, SP3, SP5, SP5a, SP5a update)
• 7.0 (including SP1, SP2, SP3, SP4)
• 2000 (including SP1, SP2, SP3, SP3a, SP4)
• 2005 (including SP1, SP2)
• 2008 (including SP1, SP2, SP3, SP4)
• 2008 R2 (including SP2, SP3)
• 2012
• 2014
• 2016
• 2017

Linux

•  2017

Linux versioning table here

Software Pattern Summary

Platforms Supported by the Pattern

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

Identification

Apart from the actual Database server and supporting processes, SQL Server is shipped with some additional components. The presence of these components is governed by the edition of SQL Server product and on user choices made at installation.

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

• 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 on which 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.

Software Instance 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 and are modelled within a full Software Instance for Adaptive Server Enterprise using the primary and associate relationships (See #Application Model Produced by Software Pattern for more details about modelling this product).

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

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 to 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 the package Microsoft SQL Server <version number> <SP number> Database Engine Services

If that fails the pattern tries to obtain version information from the name field of any package that starts SQL Server <version number>

Linux

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

Log file versioning

Error log file is stored in the MS SQL user home directory. Pattern obtains user's home using command 'echo ~<username_triggered_process_owner>'. If command was failed or output was unexpected, pattern trying to use default home directory '/var/opt/mssql/'. Errolog is read by pattern using command:

   cat <home_directory>/log/errorlog | grep 'Microsoft SQL Server'
Output example:
   2017-06-15 13:29:32.40 Server      Microsoft SQL Server 2017 (CTP2.1) - 14.0.600.250 (X64)

Extract full version using regex 'Microsoft SQL Server \d{4} \(\S+\) - (\d+(?:\.\d+)+)'

Extract edition using regex '(?i)(\S+\s+Editions?\s*\S*)\s+on\s+'

Package versioning

Pattern looking for packages with regex '^mssql-server$' and if number of found packages only one - collects it's version

Package version shows only full version, without edition. 

Additional Attributes

Windows

A Note on Registry Keys

The pattern checks for 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 no instance name exists the default of MSSQLServer is used.

Edition Information

Microsoft publishes details of SQL Server editions here

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

Otherwise we check the Edition registry key

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 the 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 from the database query "SELECT create_date from sys.server_principals where name = 'NT AUTHORITY\\SYSTEM'"

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 we can discover the path to the errorlog file from that

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 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 modelled 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 equal to the SQL Server instance. The SI is modelled as hosted on the linked Cluster

MS SQL AlwaysOn Availability Group

Linux

The main configuration file of MS SQL for Linux is stored in the SQL user home directory. Pattern obtains user's home using command 'echo ~<username_triggered_process_owner>'. If command was failed or output was unexpected, pattern trying to use default home directory '/var/opt/mssql/'. If pattern can not obtain some parameters, it trying to use default values

MSSQL Config file

Config file location : <SQL_home_directory>/mssql.conf
Default location : /var/opt/mssql/mssql.conf

Content example:
        [EULA]
        accepteula = Y
        [network]
        ; tcpport = 5555
        tcpport = 5544
        # old server address # ipaddress = 10.0.0.54
        ipaddress = 192.168.133.139 -- main address
        ## some comment ipaddress = 0.0.0.0
       
        [filelocation]
        defaultdatadir = /sql/data
        defaultdumpdir = /sql/dump

Сommented lines starts with symbols: '#', ';' '--', so patterns looks only actual values

Bind address

Default: none
From config file content: regex '(?im)^\s*ipaddress\s*=\s*(\d+(?:\.\d+){3})'

Listening port

Default: 1433
From config file content: regex '(?im)^\s*tcpport\s*=\s*(\d+)'

Data directory

Default: <SQL_home_directory>/data
From config file content: regex '(?im)^\s*defaultdatadir\s*=\s*(\S+)'

Backup directory

Default: <SQL_home_directory>/data
From config file content: regex '(?im)^\s*defaultbackupdir\s*=\s*(\S+)'

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 separate pattern has been created to query the Microsoft SQL Server in order to obtain database list and (optionally) database table details. For more information about this pattern, please refer to the relevant page

Application Model Produced by Software Pattern

Software 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 that has been created during process identification. This name is used to create a unique SI.

NOTE: In cases where this variable was not populated during identification (e.g. on Windows 2000 Server systems command-line arguments cannot be obtained by Atrium Discovery), a default name 'MSSQLServer' is set in 'servername' and therefore used within the pattern. This can lead to the SI not being unique which will lead to 'normalizing' of non-unique instances to a single instance.

Configuration Options

There is a configuration option available for this product which allows to run database queries.

To enable this option user just needs to set "True" to variable db_queries .

SI Depth

The pattern produce a Deep (Instance Based) SI for Microsoft SQL Server. The key used is a unique combination of the DB server name, Type (Microsoft SQL Server) and the Host.

NOTE: 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 occurrs 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 of the information is not available, a group-based Software Instance is created using full path to the triggering process to group instances

Subject Matter Expertise

The simple identifiers and pattern definitions for Microsoft SQL Server were developed with assistance from BMC ADDM R&D in-house SQL Server SME. They have been tested on various versions and configurations of SQL Server (v7, 2000, 2005, 2008, 2008 R2) running in Windows 2003 Server, Windows 2008 Server and Windows XP.

Testing

Testing to ensure the processes related to Microsoft SQL Server components have been correctly identified has been performed using both ADDM record data and in-house SQL Server installations running on Windows XP Pro, Windows 2000 Server, Windows 2003 Server and Windows 2008 / 2008 R2 Server installations.

Registry versioning approach was tested against in-house Microsoft SQL Server installations and was deemed to work well unless constrained by the data returned from the hosts (e.g. ADDM discovery typically cannot obtain process command-line on hosts running Windows NT or Windows 2000 Server. This limitation no longer exists on hosts running Windows XP or Windows 2003 server).

WMI Query versioning approach was tested against in-house Microsoft SQL Server installations and was deemed to work well unless constrained by the data returned from the hosts (e.g. ADDM discovery user privileges do not allow execution of WMI queries). WMI Query is now used in preference to Registry query approach for this product as it yields more accurate version information - 'full_version' attribute contains the build information that indicates the cumulative updates (hotfixes) applied

Information Sources

List of SQL Server build numbers

Open Issues

N/A