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 and furthermore to obtain (optionally, disabled by default) the size of databases in KB and the list of tables within these databases.

The information is then stored within the Atrium Discovery model as DatabaseDetail nodes. In Atrium Discovery 8.3 and later, the DatabaseDetail 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 Required

The Database Detail Patterns require some additional configuration before they can be used.

Setting of Database Integration Point

• Set the Connection Parameters, including Username and Password.

Setting the port:

Atrium Discovery 8.1

The core TKU Microsoft SQL Server Pattern will attempt to obtain the listening port for the database server dynamically. In case where it fails the Database Detail pattern will use the default port which is set in its configuration parameters. If the default port is likely to be different in your environment, the pattern configuration should be altered. Changing Pattern Configuration is detailed in Atrium Discovery Documentation .

Atrium Discovery 8.2 and later

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.

Editing pattern configuration further

• User can decide whether the pattern should attempt to retrieve tables by setting the 'retrieve_tables' configuration option
• User can decide whether any specific databases should be ignored by listing them in the 'ignore_databases' array
• User can decide whether the pattern should attempt to retrieve database size by setting the 'get_database_size' option

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.

• Obtaining a list of databases being managed:
  • SELECT name AS database_name FROM SYS.SYSDATABASES
  • SELECT name AS database_name FROM master..sysdatabases (Microsoft SQL Server 2000 only)
  • ''sp_databases'' - alternative approach, unless user configured as primary by setting the 'default_to_secondary' option to 'true'

• Obtaining the size of databases in KB
  • ''sp_helpdb''
  • ''sp_databases'' - alternative approach, unless user configured as primary by setting the 'default_to_secondary' option to 'true'

• Obtaining a list of tables within a database: ''SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' AND table_catalog = <database_name>''

Alternative methods

In cases where it can fully distinguish SQL Server instances apart, the pattern attempts to obtain the list of databases managed by a particular SQL Server instance.
Three different approaches are used:

• 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

Should the pattern fail to connect to the SQL Server using a JDBC connection, it will attempt to obtain a list of managed databases by executing one of the following WMI query groups within the root\cimv2 namespace:

Where server name is known and server name is not the default server name (i.e. "MSSQLServer")

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

Command

Should the SQL and WMI query approaches fail, this pattern attempts to execute the osql.exe command.

The path to the osql.exe binary is constructed as follows:
Path to the SQLServer pattern's trigger binary is parsed using the following regular expression to get the base path:
(?i)^(\w:\\.+?\\)MSSQL(?:\.\d+)?

The base path is then placed in the base_directory attribute of the created SI for retrieval by this extended pattern module.

This path is then added to by the short version number for the product and '\Tools\Binn\osql.exe' string

The command executed is:
<path_to_osql> -S <hostname\servername> -E -Q "USE master; SELECT Name FROM sysdatabases"

The output of the command is then parsed and a DatabaseDetail node created for each database as well as adding each one to the 'db_list' (array) and 'dbs' attributes of the SI previously created by the SQLServer pattern.

Directory Listing

Should the SQL query, WMI query and command approaches fail, this pattern will attempt to read the value of a Windows Registry entry which points to a Windows folder where a directory listing can be performed.

The location of the installation path is initially search for in one of a number of registry keys:

• <registry_root>\Setup\SQLDataRoot

Should this fail, the location of the installation path is extracted from the command-line of the SQLServer pattern's triggering process using the following regular expression:

(?i)^(\w:\\.+?)\\Binn?

The path to the data directory extracted from whichever method is then placed in the data_directory attribute of the created SI for retrieval by this extended pattern module.

Once the path is extracted, a directory listing is obtained and each .mdf file collated using the following regular expression:

• (?i)\.mdf$

The collated output is then parsed ('.mdf' extension is removed) using the following regular expression:

• (?i)\b([\w\d\_\- \$]+)\.mdf\b

and a DatabaseDetail node created for each database as well as adding each one to the 'db_list' (array) and 'dbs' attributes of the SI previously created by the SQLServer pattern.