Microsoft SQL Server
- Product name
- Publisher page
-
- [Microsoft|Microsoft]
- Category
- Relational Database Management Systems
- Release
- TKU 2024-Mar-1
- More information
- Publisher link
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 codebase for MS SQL Server (before version 7.0) originated in Sybase SQL Server. It was Microsoft's entry to the enterprise-level database market, competing against Oracle, IBM, and, later, Sybase itself.
An extended Discovery pattern, which enables model Database Detail Nodes to be managed by the SQL Server, is available for this product.
Pattern summary
Pattern name | Operating system | Versioning | Pattern depth |
---|---|---|---|
SQLServer | Windows | Active (WMI Query), Registry | Instance-based |
SQL Server | Linux | DB query, Log file, Package | Instance-based |
Platforms supported by the pattern
The pattern identifies Microsoft SQL Server running on the Microsoft Windows and Linux platforms.
Identification
The SQL Server is shipped with additional components besides the actual database server and supporting processes. The presence of these components is governed by the edition of the SQL Server product and by user choices made at installation.
The additional components (some of which Atrium Discovery discovers using other patterns) are the following:
- 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 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
- SQL Server Agent - an application is doing maintenance jobs for servers.
Triggers
Trigger node | Attribute | Condition | Argument |
---|---|---|---|
DiscoveredProcess | cmd | matches | 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 name | SI type |
---|---|
SQLServer | Microsoft SQL Server |
MSSQLServer_Linux | Microsoft SQL Server |
Simple identification mappings
The following processes are identified through the use of Simple Identifiers. They are modeled using the primary and associate relationships within a full Software Instance for Adaptive Server Enterprise.
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 |
Pattern configuration options
The pattern can turn off all SQL queries by setting 'db_queries' to false. Also, this setting will impact the Externed DB discovery pattern.
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 operating system
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 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 one of the following packages:
- Microsoft SQL Server <version number> <SP number> Database Engine Services
- Microsoft SQL Server <version number> <SP number> LocalDB
- SQL Server <version number>
Linux operating system
Version information for the product is collected using one of two approaches - DB Query, parse error log file (Microsoft provides these methods), and package versioning. The pattern executes the methods in order and uses the first successful method it can.
Log file versioning
We extract the full version and edition from /var/opt/mssql/errorlog.
Package versioning
Pattern looking for packages with regex '^mssql-server$', and if several found packages, only one - collects its version. The package version shows only the full version without edition.
Additional Attributes
Windows operating system
A note on Registry keys
The pattern checks for the 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 we cannot find instance information from the trigger process, we search the registry for keys of the form HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance name>\ MSSQLServer\SuperSocketNetLib\Tcp\TcpPort
. Suppose the port described in that registry key is the same as the port in the DiscoveredListeningPorts attribute corresponding to the trigger process. In that case, we use the <instance name> from the registry path as the SI instance. If no instance name exists, the default of MSSQLServer is used.
Edition information
Microsoft publishes details of SQL Server editions 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:
- The database query
- The \\setup\\edtion subkey of these registry directories
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 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 by using one of the following methods:
- The database query
SELECT create_date
from sys.server_principals where name = 'NT AUTHORITY\\SYSTEM'". - The file <install_root>\Setup Bootstrap\Log\summary.txt where you search for the end time : <date>.
The sample output of the summary.txt file used to find the creation date is as follows:
Overall summary:
Final result: Passed but reboot required, see logs for details
Exit code (Decimal): 3010
Start time: 2020-02-30 23:53:44
End time: 2020-02-30 23:58:13
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, you can discover the path to the error log file.
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 the 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:
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 the 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\\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 modeled 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 similar to the SQL Server instance. The SI is modeled as hosted on the linked Cluster.
Clustered vs Standalone SIs
'Microsoft SQL Server' SI can be identified as clustered on MS Cluster when Cluster has 'SQL Server' ClusterResource with the same 'instance' attribute as SI.
MS SQL AlwaysOn Availability Group
Extended Discovery pattern identifies and models Availability Groups.
Linux operating system
Configuration File
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
- bind_address - A bind address of 0.0.0.0 means use of any available IPv4 address. So, if a bind_address of 0.0.0.0 is reported, we set the bind_address of the Software Instance to:
- listening_ports
- data_directory
- backup_directory
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 different pattern has been created to query the Microsoft SQL Server to obtain the database list and (optionally) database table details. For more information about this pattern, see Microsoft SQL Server - Database Detail Pattern.
Important
In due course, the attributes, dbs, and db_list, will be phased out in favor of Database Detail and Detail nodes.
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 created during process identification. This name is used to create a unique SI.
Important
In cases where this variable was not populated during identification (e.g. on Windows 2000 Server systems command-line arguments cannot be obtained by BMC 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 that allows the running of database queries. To enable this option user needs to set "True" to variable db_queries .
SI Depth
The pattern produces a Deep (Instance Based) SI for Microsoft SQL Server. The key combines the DB server name, Type (Microsoft SQL Server), and the Host. 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 occurs 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 information is unavailable, a group-based Software Instance is created using the full path to the triggering process to group instances.
Comments
Log in or register to comment.