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

• 4.21
• 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)
• 2008 R2

Editions

Microsoft SQL Server is available in a number of different editions. The editions are seemingly based on two different code bases (SQL Server Enterprise and SQL Server Mobile) and are likely provided for marketing and licensing reasons.
Some editions based on the main SQL Server Enterprise codebase are available on x86 (32-bit and 64-bit - x64) as well as IA64 processor platforms running Microsoft Windows Operating System.
Known editions of the current version of the product (SQL Server 2005) are:

• Enterprise Edition - offers largest scale performance and scalability. No limits applied to the maximum number of CPUs, RAM usage or Database Size (other than OS limits). Supports multicore processors. Bundled with a suite of Business Intelligence tools.
• Standard Edition - Limit on a number of CPUs (maximum 4). No support for patitioning, Indexed views. Smaller bundle of Business Intelligence tools.
• Workgroup Edition - aimed at smaller businesses. Limit on a number of CPUs set to 2 and RAM usage set to 3GB. Available on x86 (32-bit) processor platforms only.
• Developer Edition - offers the same features as the Enterprise Edition but restricted through licensing.
• Express Edition - An embedded version of SQL Server Database Engine. Designed to be bundled (freely) with other applications. Since it is compatible with the Standard and Enterprise Editions, it can be used as a development environment prior to migration. Limited to a single CPU and 1GB of RAM. The Database size is also limited to 4GB. Available on x86 (32-bit) processor platform only.
• Compact Edition - Based on SQL Server Mobile Edition code base, but has now had platform restrictions removed. An embedded database for single-user client applications for Microsoft Windows-based desktops as well as PocketPC and smart phone platforms. Freely available and supersedes the Microsoft SQL Server Mobile edition.

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 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 2005 for configuring, managing, and administering all components within Microsoft SQL Server.

Software Instance Triggers

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

Provided the pattern can obtain via the process command-line the full path to the trigger process the pattern will attempt to run a WMI query to obtain the product version.

The WMI namespace accessed is: 'root\CIMV2'
The WMI query executed is: select Version from CIM_DataFile where Name = '<abs_path_to_trigger_process>'

The output is then parsed using the following regular expression to extract the version: ^(\d+\.\d+\.\d+\.\d+)

Following this, any trailing zeros are removed from the bottom of the version, and the resulting value is subsequently entered in mapping table, which translates that numeric version into that naming that Microsoft gives to its products – ie, 2008 SP 1.
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.

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

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' SI attribute.
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
This method however requires direct interaction with the database and execution of queries against the database. This versioning approach has not as yet been implemented since it would require the user to also set up SQL Server database credentials.

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 Foundation), 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.

Edition Information

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\MSSQLServer\Setup\Edition
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<server name>\Setup\Edition
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\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 also 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\MSSQLServer\MSSQLServer\BackupDirectory
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<server name>\MSSQLServer\BackupDirectory
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\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.

Determining Listening Port and Listening IP address (if set)

Each SQL Server instance can have one or more listening ports allocated to it to respond to connections from remote clients.
Furthermore from SQL Server 2005 and later the database server can be configured to listen to connections on a specific IP address.
The pattern attempts to retrieve the port information through two approaches, via registry query, and if that fails, by analysis of process to port mapping information obtained by Discovery. Listening IP address information is retrieved if present via registry query.

In order to determine the listening port and possibly the specific address the database server is listening on, the pattern initially attempts to determine whether the database server is configured to listen via TCP/IP.

The registry key accessed is:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\MSSQLServer\SuperSocketNetLib\Tcp\Enabled

Database Server listening on all IP addresses

If the pattern has determined that the database server is listening via TCP/IP, then it accesses the following registry key to determine whether the database server is listening on all IP addresses:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\MSSQLServer\SuperSocketNetLib\Tcp\ListenOnAllIPs

If the pattern has determined that the database server is listening on all IP addresses, it will execute the following queries to determine the listening port:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\MSSQLServer\SuperSocketNetLib\Tcp\IPAll\TcpPort
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\MSSQLServer\SuperSocketNetLib\Tcp\IPAll\TcpDynamicPorts

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

If a comma-separated list of ports is returned, only the first port in the list is actively used by the pattern, extracted via the following regular expression, although all ports may be viewed via the port_list attribute of the Software Instance:

• ^(\d+)

NOTE: In cases where the instance name of the server cannot be obtained (server versions earlier than 2005), the pattern assumes that the server is listening on all IP addresses.

The pattern will attempt to obtain the port information using the following registry queries:

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

If a comma-separated list of ports is returned, only the first port in the list is actively used by the pattern, extracted via the following regular expression, although all ports may be viewed via the port_list attribute of the Software Instance:

• ^(\d+)

Database Server listening on a specific IP address

If the pattern has determined that the database server is listening on a specific IP address then in order to determine the listening IP address and port the pattern performs the following:

• Lists the registry structure below the following:
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\MSSQLServer\SuperSocketNetLib\Tcp
• Within each registry directory, access the following registry keys to determine whether the database server is listening on that IP address
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\MSSQLServer\SuperSocketNetLib\Tcp\IP<n>\Active
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\MSSQLServer\SuperSocketNetLib\Tcp\IP<n>\Enabled
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\MSSQLServer\SuperSocketNetLib\Tcp\IP<n>\IpAddress
• If the pattern has determined that the database server is listening on a specific IP address, the value is obtained and a 'bind_address' attribute is created and populated on the SI node.
• The pattern then attempts to obtain the port information by performing the following registry queries:
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\MSSQLServer\SuperSocketNetLib\Tcp\IP<n>\TcpPort
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\MSSQLServer\SuperSocketNetLib\Tcp\IP<n>\TcpDynamicPorts

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

If a comma-separated list of ports is returned, only the first port in the list is actively used by the pattern, extracted via the following regular expression, although all ports may be viewed via the port_list attribute of the Software Instance:

• ^(\d+)

Database Server running as part of SQL Server cluster - cluster specific IP address

Database servers that are running as part of SQL Server cluster listen for connections on a specific IP address (this IP address is in the list of IP addresses discovered for the host). This IP address cannot be obtained from a registry query and the configuration in the registry is set as if the database server is listening on all IP addresses.

If the pattern has determined via registry query that the database server is listening on all IP addresses then it will also attempt to determine whether the SQL server is part of a cluster by performing the following registry query:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instancename>\Cluster\ClusterName

If a value for this registry key is obtained this both confirms that the SQL Server is in an SQL Server cluster and gives the pattern the information it needs to match the cluster name against one of the IP addresses that have been discovered for the host.

An alternative approach to check whether the server is part of an SQL cluster and get the listening IP address, although this is likely to only be true for SQL Server 2000, is by performing the following registry query:

• HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\MSSQLServer\\Cluster\\ClusterIpAddr

If either match is successful, the 'bind_address' attribute is created and populated on the SI node.

Alternative method of obtaining listening port information

If pattern was unable to obtain the listening port information via registry queries it attempts to obtain the port information by analysing process to port mapping obtained by Discovery.
If the data is obtained, a 'port' attribute is created and populated on the SI node.

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

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

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 19 Mar 2012
Reviewed by: Nikola Vukovljak 22 Nov 2011