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.
Note
The patterns will not by default return the list of all tables within the discovered databases as this can have a marked impact on discovery of hosts with large database installations.
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.
The pattern executes the following SQL Queries
| Query | Reason | Permission | More details |
|---|---|---|---|
| SELECT name AS database_name, compatibility_level, database_id, state_desc FROM sys.databases | Get database name, compatibility level, ID and state. | Public role | Docs |
| SELECT database_id, physical_name FROM sys.master_files | Map Database ID to physical name | VIEW ANY DEFINITON permission for all rows in this database | Docs |
| sp_helpdb | Get database sizes | Public role in the master database | Docs |
| sp_helpdb <database name> | Get database sizes | Public role | Docs |
| sp_databases | Get database name and sizes | Requires VIEW ANY DEFINITION or higher | Docs |
| SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = %db_name%" | Get information on tables in a database | grant select on master.information_schema.tables to [a_db_user] | SQL Server Central Forum |
| 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 | Get details of Availability Groups |
| Docs Docs Docs Docs |
| SELECT group_id, ip_configuration_string_from_cluster as listener_ip FROM sys.availability_group_listeners | Add Listener Ips to relevant ClusterService node | Need to be granted some permission for all securables you wish to discover deeply | Docs |
| select data_source, product, provider from SYS.servers where server_id > 0 and is_linked > 0 | Get OLE records | Public role, but see notes in the link | Docs |
