Pattern Goal

The goal of the Microsoft SQL Server Database Detail pattern is to obtain the list of databases being managed by the SQL Server.

The information is then stored within the Discovery model as Database nodes. The Database node representing each SQL Server database has additional attributes added, which permit easy mapping by the CMDB sync mechanism to the BMC_Database CIs in CMDB.

The pattern connects to the SQL Server using a JDBC connection. The IP address used to connect to the database server is determined by the pattern, with the default IP address the host is being scanned on. However, as of Discovery 8.2 and TKU January 2011, the pattern will access the database server on a specific IP address if determined and set as 'bind_address' (SQL Server 2005 and later) by the main Microsoft SQL Server pattern.

If the pattern is unable to connect to the SQL Server using a JDBC connection, it will attempt to do one of the following actions:

• obtain the list of managed databases either by executing a WMI query
• execute a command requiring local admin privileges
• read the value of a Windows Registry entry pointing to a Windows folder where a directory listing is then performed.

Configuration Options

There are several configuration options available for this Extended Discovery:

• ignore_databases := [] (""); - allows to ignore databases.
• ignore_databases_substring := [ 'ISALOG' ]; - allows to ignore selected databases.
• default_to_secondary := false; - allows to use secondary method by default to obtain list of databases.
• get_db_individ := false; - allows to obtain database size by querying individual databases.
• retrieve_tables := false; -  allows to retrieve tables in each database.
• file_db_discovery := false; - allows to use file-based method by default to obtain list of databases.
• errorlog_discovery := false; allows discovery of managed databases from the error log file.

The pattern also references the following configuration options attached to the pattern:

• db_queries - Determine if database queries are to be run via DB ABP
• use_osql - Determine if database queries are to be run via osql command

Setting the port

The option to set the default port no longer exists in the Microsoft SQL Server Database Detail pattern.

The SQL Server Database Detail pattern behavior is now as follows:

• If the core TKU Microsoft SQL Server Pattern discovers the listening port, this is the port that will be used.
• Alternatively, the SQL Server Database Detail pattern will use the port that has been set in the database credentials by the user.
• If no port was retrieved and port DB listening port was not set by the user, the SQL Server Database Detail pattern will not attempt to perform database queries.
  The reason for this change is to minimize the number of configuration steps required and prevent errors due to forgotten changes in pattern configuration.

SQL Queries Executed

The pattern executes the following SQL Queries

Basic Database information

Database Query

The pattern obtains information on running databases by running one of the following database queries:

On SQL Server 2000

• SELECT name AS database_name FROM master..sysdatabases

On later versions

• SELECT name AS database_name, compatibility_level, database_id, state_desc FROM sys.databases
• sp_databases

WMI Query

The pattern attempts to run the following WMI queries:

• select Name from Win32_PerfRawData_MSSQL<servername>_MSSQL<servername>Databases
• select Name from Win32_PerfFormattedData_MSSQL<servername>_MSSQL<servername>Databases

Where server name is not known and edition = "Express Edition"

• select Name from Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSDatabases
• select Name from Win32_PerfFormattedData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSDatabases

Otherwise

• select Name from Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases
• select Name from Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases

Should the first WMI query fail, the second one is then attempted. The name of each database is then extracted from the output, and a DatabaseDetail node is created for each as well as adding each one to the 'db_list' (array) and 'dbs' attributes of the SI previously created by the SQLServer pattern, except those in the Configuration ignore_databases list.

Should a server name contain an underscore, a dollar, or a #, the pattern will perform various substitutions to ensure the WMI query runs successfully.

Directory Listing - file-based discovery method

This is attempted only if the file_db_discovery configuration option is enabled.

The pattern obtains a list of all .mdf files in the SQL Server data directory and its sub-directories.  It then creates a Database node whose physical_name attribute is the path to the .mdf file.  The instance name is:

• The existing instance name, if a Database node already exists
• The name of the .mdf file otherwise

If we subsequently find a database with the same physical name (most probably via a SQL query) we will only update the discovered instance name and we will not create another Database node.

ERRORLOG file

If the errorlog_discovery configuration option is enabled the pattern searches the ERRORLOG file for text:

• Starting up database ['"](\S+)['"]\.

If it is found, a Database node is created.

Additional Database Information

Database Size

If the list of databases was obtained by sp_databases every database should have size information. If it doesn't (perhaps because the list was obtained by querying sys.databases) the pattern tries one of the following two methods to obtains the size:

• Run query SELECT database_id, size, physical_name FROM sys.master_files.  This requires VIEW ANY DEFINITON permission for all rows in this database.
• Run query sp_helpdb. This requires that the database user must have permissions "membership in the public role on the master database":
• Run query sp_helpdb <database name> for every database in the List of Managed Databases. This requires the database user must have "membership in the public role in the database".  This only happens if the get_db_individ configuration option is enabled.

Full information on the database permissions required is provided in the publisher's documentation.

List of Tables

This only happens if the retrieve_tables configuration option is enabled.

To obtain a list of tables within a database the pattern executes the following SQL Query:

• SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' AND table_catalog = <database_name>

Listener IP

Shows availability groups group_id, ip_configuration_string_from_cluster as listener_ip.

• SELECT group_id, ip_configuration_string_from_cluster as listener_ip FROM sys.availability_group_listeners

Other Attributes

The pattern gets compatibility level, state, and database ID from the SELECT name AS database_name, compatibility_level, database_id, state_desc FROM sys.databases query used to find the list of managed databases.

The pattern gets the physical names from the query SELECT database_id, size, physical_name FROM sys.master_files. 

Availability Groups

Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring.

The availability group supports a failover environment for a discrete set of user databases, known as availability databases, that failover together.

An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases.

Optionally, secondary databases can be made available for read-only access and/or some backup operations.

Microsoft Cluster reports all ClusterResources that are SQL Server Availability Groups.  The SQL Server extended discovery pattern adds linking and extra detail.

Linking

The pattern creates a SoftwareService link from the ClusterService that hosts the Availability Group ClusterResource to the DatabaseDetails that make up the Availability Group.  The ClusterService is the service, the DatabaseDetail is the ServiceProvider.

The pattern finds the information by running the following SQL query:

SELECT ar.group_id, name as group_name, 
 Db_name(drs.database_id) AS database_name,
 hars.role_desc, ar.replica_server_name 
 FROM sys.dm_hadr_database_replica_states DRS 
 full JOIN sys.availability_replicas AR 
 ON drs.replica_id = ar.replica_id 
 full JOIN sys.availability_groups AGS 
 ON ar.group_id = ags.group_id 
 full JOIN sys.dm_hadr_availability_replica_states HARS 
 ON ar.group_id = hars.group_id 
 AND ar.replica_id = hars.replica_id

Background to the query is represented here.

Note that due to a limitation in BMC Discovery this link will not be shown in visualizations.  To see what DatabaseDetails are part of a given Availability Group you must execute the following generic search:

• Search ClusterService where name = <name> traverse Service:SoftwareService:ServiceProvider:DatabaseDetail

If BMC Discovery was capable of producing visualizations for this model, then it would look as follows:

The pattern also adds extra details to existing nodes. This information is obtained from the database query:

• The pattern adds a always_on_availability_group attribute to the DatabaseDetail node
• The pattern adds a always_on_availability_group_role attribute to the DatabaseDetail node
• The pattern adds a listener_ipsattribute (retrieved from showListenerIP query) to ClusterService which contains related Availability Group.

OLE Linking

On every SQL Server instance that the pattern discovers it executes the following query

• select data_source, product, provider from SYS.servers where server_id > 0 and is_linked > 0

For each entry that refers to a SQL Server database, the pattern creates a Client-Server link from the triggering SQL Server Software Instance to the SQL Server Software Instance referred to in data_source. The OLE database is the client. If data_source is of the form <hostname>\<instance name> we use both the hostname and instance name to do the linking.  Otherwise, we link to a SQL Server Software Instance with instance MSSQLSERVER on the host described in data_source.