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

• 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

Each version comes in several editions, details are here

Software Pattern Summary

Platforms Supported by the Pattern

The pattern identifies Microsoft SQL Server running on the Microsoft Windows platform.

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

Version information for the product is currently collected using one of two approaches - WMI Query execution or query of Windows Registry. The pattern will execute the methods in order and use the first successful method it can.

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

Registry Versioning

Using registry queries on hosts running Microsoft Windows can at times return the version of the product a process (or processes) belongs to. This is certainly the case with Microsoft SQL Server. This approach is particularly useful in situation where only one installation of the product can be installed on a host (or OS instance) which is again the case with Microsoft SQL Server.

The keys accessed are:

If instance name has been obtained:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance name>\MSSQLServer\CurrentVersion\CurrentVersion

If server name is also known

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<server name>\MSSQLServer\CurrentVersion\CSDVersion

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<server name>\MSSQLServer\CurrentVersion\CurrentVersion

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\<server name>\CurrentVersion\CSDVersion

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\<server name>\CurrentVersion\CurrentVersion

Should a 32-bit version of SQL Server be installed on a 64-bit version of Windows, all Windows Registry call will automatically search in the Wow6432Node space as required.

Once a key corresponding to one of the keys listed above is found, the value is obtained and then a mapping is used to map the value to the SQL Server 'product_version' and 'service_pack' SI attributes.
If this initial direct version mapping does not succeed, a secondary more flexible approach is used where a shortened version (major.minor.first digit) is used against a mapping table to derive the version.

Alternative Methods to obtain version information

An alternative method to obtain SQL Server version information is detailed in a Microsoft Knowledgebase article located here: http://support.microsoft.com/kb/321185

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.

Additional Attributes

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"

If the instance is set to any other value edition information is also obtained using Windows Registry query approach.

The keys accessed are:

If instance name has been obtained:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance name>\Setup\Edition

If server name is also known:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<server name>\Setup\Edition

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<server name>\Setup\Edition

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<server name>\Setup\Edition

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.<server name>\Setup\Edition

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Setup\Edition

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup\Edition

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup\Edition

Once a key corresponding to one of the keys listed above is found, the value is obtained and an 'edition' attribute is created and populated on the SI node.

Backup Directory Information

Backup Directory information is also obtained using Windows Registry query approach.

The keys accessed are:

If instance name has been obtained:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance name>\MSSQLServer\BackupDirectory

If server name is known:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<server name>\MSSQLServer\BackupDirectory

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<server name>\MSSQLServer\BackupDirectory

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<server name>\MSSQLServer\BackupDirectory

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.<server name>\MSSQLServer\BackupDirectory

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirectory

Once a key corresponding to one of the keys listed above is found, the value is obtained and a 'backup_directory' attribute is created and populated on the SI node.

Data Directory path information

The primary method to obtain Data Directory path information (location of database files) is using Windows Registry query approach.

The keys accessed are:

If instance name has been obtained:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance name>\Setup\SQLDataRoot

If server name is also known:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<server name>\Setup\SQLDataRoot

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<server name>\Setup\SQLDataRoot

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<server name>\Setup\SQLDataRoot

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.<server name>\Setup\SQLDataRoot

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLDataRoot

Once a key corresponding to one of the keys listed above is found, the value obtained has the final part of the path '\Data' added to it to set the full data directory path and a 'data_directory' attribute is created and populated on the SI node.

If the primary method fails to obtain the data directory path, the secondary approach is used which parses the trigger process command-line.

The regular expression used is: (?i)^(\w:\\.+?)\\Binn?If a value is obtained, the final part of the path '\Data' is added to it to set the full data directory path and a 'data_directory' attribute is created and populated on the SI node.

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

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

Created by: Rebecca Shalfield 19 Oct 2007
Updated by: Rebecca Shalfield 11 Jun 2014
Reviewed by: Alex Kashkevich 23 Jun 2014