Discover with BMC Discovery
download

This product can be discovered by any edition of BMC Discovery. Download our free Community Edition to try it out, or [see what else it can discover] !

What is this?
This is a product information page, containing details of the information that BMC Discovery gathers about a product and how it is obtained.
Product Name
SQL Server
Publisher Page
Microsoft
Category
Relational Database Management Systems
Release
TKU 2017-Oct-1
Change History
Microsoft SQL Server - Change History
Reports & Attributes
Microsoft SQL Server - Reports & Attributes
Publisher Link
Microsoft

 

Product Description

 

Extended Discovery pattern which allows to model Database Detail Nodes being managed by the SQL Server  is available for this product.

 

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

Product ComponentOS TypeVersioningPattern Depth
SQLServerWindowsActive (WMI Query), Registry

Instance-based

SQL ServerLinuxDB query, Log file, PackageInstance-based

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

Trigger NodeAttributeConditionArgument
DiscoveredProcesscmdmatches

regex '(?i)(\\(?:MSSQL\.\d+)\\.*|)\bsqlservr\.exe$'


or

unix_cmd 'sqlservr'

Software Instance type attributes created

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

 

 Pattern NameSI type
SQLServerMicrosoft SQL Server
MSSQLServer_LinuxMicrosoft SQL Server

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

NameCommand
SQL Server Database serversqlservr.exe
sqlservr
SQL Server Agentsqlagent.exe or sqlagent90.exe
SQL Browser Servicesqlbrowser.exe
Full Text Engine for SQL Servermsftesql.exe
SQL VSS Writersqlwriter.exe
DTS (Data Transformation Services) Package ExecuterDTSRun.exe
SQL ODBC Server Communication processosql.exe
SQL Service Manager - Not present in SQL Server 2005sqlmangr.exe
SQL sqlwtsn processsqlwtsn.exe
Replication Distribution Agentdistrib.exe
Replication Log Reader Agentlogread.exe
Replication Queue Reader Agentqrdrsvc.exe
Replication Merge Agentreplmerg.exe
SQL Server Profilerprofiler.exe or profiler90.exe
Database Engine Tuning AdvisorDTASHELL.EXE
SQL Integration Services (SQL Server Integration Services)msdtssrvr.exe
SQL Analysis Services Server (SQL Server Analysis Services)msmdsrv.exe
SQL Server Reporting Service (SQL Server Reporting Services)reportingservicesservice.exe
SQL Notification Services (SQL Server Notification Services)nsservice.exe
SQL Server Management StudioSqlWb.exe

Versioning

Windows

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.

Note

'servername' and/or 'instancename' variables that have been obtained through the regular expression match during the identification process are substituted in the key values before these values are looked up in the Registry.

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. 

Database query versioning

Version information obtained using SQL query. This method the same as for Windows based MS SQL, described at Alternative Methods to obtain version information

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. 

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

Windows

Instance

Instance information is obtained from the -s argument of the trigger process. If no instance name exists the default of MSSQLServer is used.


Using the Registry

Many additional attributes can be found in the registry. SQL Server information can be found in the following registry directories, listed in order of preference:

  • HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\<instance>
  • HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10.<instance>
  • HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.<instance>
  • HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL11.<instance>
  • HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\100\\Tools
  • HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\110\\Tools
  • HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\120\\Tools
  • HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\110\\DQ


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 from registry key <root>\Setup\Edition (details of the registry root are above)

Backup Directory Information

Backup Directory information is also obtained from registry key <root>\MSSQLServer\BackupDirectory (details of the registry root are above)


Data Directory path information

There are three methods of obtaining the data directory

The best method is to use the SQLDataRoot registry key. We use the contents of key <root>\Setup\SQLDataRoot (details of the registry root are above). We add "\Data" to the path before reporting it on the SI

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


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 of the form <root>\MSSQLServer\Parameters\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:

Listen On All IPs StatusRegistry Keys to check
If Listen On All IPs is enabled
  • HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\<instance name>\\MSSQLServer\\SuperSocketNetLib\\Tcp\\IPAll\\TcpPort
  • HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\<instance name>\\MSSQLServer\\SuperSocketNetLib\\Tcp\\IPAll\\TcpDynamicPorts
 
  • HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\<instance name>\\MSSQLServer\\SuperSocketNetLib\\Tcp\*\\TcpPort'
  • HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\<instance name>\\MSSQLServer\\SuperSocketNetLib\\Tcp\\*\\TcpDynamicPorts

 

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

Details of whether TCP is enabled can be found at registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\\Microsoft SQL Server\\<instance name>\\MSSQLServer\\SuperSocketNetLib\\Tcp\\Enabled. Details of whether SQL Server is Listening On All IPs can be found at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\\Microsoft SQL Server\\<instance name>\\MSSQLServer\\SuperSocketNetLib\\Tcp\\ListenOnAllIPs

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

In all other cases the SI is modeled as running on a host.

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

Note

In due course, the attributes, dbs and db_list, will be phased out in favour of Database Detail and Detail nodes.

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



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

Was this page helpful? Yes No Submitting... Thank you

2 Comments

  1.  

    1.  
      This 

© Copyright 2017 BMC Software, Inc.