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.
Each version comes in several editions, details are described here.
Linux versioning table is displayed here.
Product Component | OS Type | Versioning | Pattern Depth |
---|---|---|---|
SQLServer | Windows | Active (WMI Query), Registry | Instance-based |
SQL Server | Linux | DB query, Log file, Package | Instance-based |
The pattern identifies Microsoft SQL Server running on the Microsoft Windows and Linux platforms.
Linux supported platforms are described here.
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:
Trigger Node | Attribute | Condition | Argument |
---|---|---|---|
DiscoveredProcess | cmd | matches | regex '(?i)(\\(?:MSSQL\.\d+)\\.*|)\bsqlservr\.exe$' |
or | |||
unix_cmd 'sqlservr' |
The patterns in this module will set the following value for the type attribute on a SI:
Pattern Name | SI type |
---|---|
SQLServer | Microsoft SQL Server |
MSSQLServer_Linux | Microsoft SQL Server |
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).
Name | Command |
---|---|
SQL Server Database server | sqlservr.exe |
sqlservr | |
SQL Server Agent | sqlagent.exe or sqlagent90.exe |
SQL Browser Service | sqlbrowser.exe |
Full Text Engine for SQL Server | msftesql.exe |
SQL VSS Writer | sqlwriter.exe |
DTS (Data Transformation Services) Package Executer | DTSRun.exe |
SQL ODBC Server Communication process | osql.exe |
SQL Service Manager - Not present in SQL Server 2005 | sqlmangr.exe |
SQL sqlwtsn process | sqlwtsn.exe |
Replication Distribution Agent | distrib.exe |
Replication Log Reader Agent | logread.exe |
Replication Queue Reader Agent | qrdrsvc.exe |
Replication Merge Agent | replmerg.exe |
SQL Server Profiler | profiler.exe or profiler90.exe |
Database Engine Tuning Advisor | DTASHELL.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 Studio | SqlWb.exe |
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.
The pattern can obtain version information from the following SQL query:
To enable SQL Server versioning, you need to set up valid SQL Server database credentials.
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).
The pattern attempts to obtain version information from the following WMI query:
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.
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.
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>
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.
We extract full version and edition from /var/opt/mssql/errorlog
Pattern looking for packages with regex '^mssql-server$' and if number of found packages only one - collects its version.
Package version shows only full version, without edition.
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 information is obtained from the -s argument of the trigger process. If no instance name exists the default of MSSQLServer is used.
Microsoft publishes details of SQL Server editions are 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:
Backup Directory information is also obtained from the BackupDirectory registry key
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
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'"
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.
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 Status | Registry Keys to check |
---|---|
If Listen On All IPs is enabled |
|
|
If the above fails, the pattern checks the following registry keys for port information:
The pattern uses standard BMC Discovery functionality to obtain bind_address.
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:
The pattern models the SI as running on a Cluster if one of the following is true:
Extended Discovery pattern identify and model Availability Groups.
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
Instance name is username of triggered process owner, by default is 'mssql'
Instance ID extracted from the file: <SQL_home_directory>/.system/instance_id
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, see this page.
Note
In due course, the attributes, dbs and db_list, will be phased out in favour of Database Detail and Detail nodes.
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.
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 .
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.
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 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.
List of SQL Server build numbers
N/A
Created by: Rebecca Shalfield 19 Oct 2007
Updated by: Rebecca Shalfield 11 Jun 2014
Reviewed by: Alex Kashkevich 23 Jun 2014