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 Atrium 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 being the IP address the host is being scanned on, although as of Atrium Discovery 8.2 and TKU January 2011 the pattern will access the database server on a specific IP address if this has been 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 obtain the list of managed databases either by executing a WMI query, executing a command requiring local admin privileges, or, finally, reading 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.

Setting of Database Integration Point

• Set the Connection Parameters, including Username and Password.

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.

Database User Configuration

In order for Atrium Discovery to retrieve the database details via a JDBC connection, it will need to be able to access the database as a legitimate user with privileges to execute the SQL queries below.
We believe that in case of:

• SQL Server 2000 - the user Atrium Discovery uses to access the database needs to have the public role on all databases to be retrieved.
• SQL Server 2005 and later - the user Atrium Discovery uses to access the database needs to have VIEW ANY DEFINITION privilege.

SQL Queries Executed

The pattern executes the following SQL queries.

List of Managed Databases

For SQL Server 2000 the pattern runs the following query

• SELECT name AS database_name FROM master..sysdatabases

For all modern versions of SQL Server the pattern runs the following queries. Any database that appears in at least one result will have a Database node

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

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 size

• 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 given 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>

show 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 and database ID from the "SELECT name AS database_name, compatibility_level, database_id FROM sys.databases" query used to find the list of managed databases

The pattern gets physical name from the query "SELECT database_id, physical_name FROM sys.master_files". This links database_id to physical_name. We link physical_name to database name using the above query. Note that one database might have several physical names

Additional methods

The pattern attempts to obtain a list of databases using these additional methods

• executing a WMI query
• by connecting to the database through running the 'osql' utility (in 'trusted' mode), or
• locating and listing the '.mbf' files (DB files) in the Data directory

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

Availability Groups

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

Availability group supports a fail-over environment for a discrete set of user databases, known as availability databases, that fail over 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 report 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 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 host name 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

We do not process entries that do not refer to SQL Server (i.e. where the product field is not “SQL Server” and the provider field is not “SQLNCLI”)