Maintenance outage for upgrade on Sunday, September 22

This site, docs.bmc.com, will be inaccessible for two hours starting at 8 AM CDT, Sunday, September 22, for a platform upgrade.

    Page tree
    Skip to end of metadata
    Go to start of metadata


    Discover with BMC Discovery
    download

    This product can be discovered by any edition of BMC Discovery. Download our free Community Edition to try it out, or [see what else it can discover] !

    What is this?
    This is a product information page, containing details of the information that BMC Discovery gathers about a product and how it is obtained.
    Product Name
    SQL Server - Database Detail Pattern
    Publisher Page
    Microsoft
    Category
    Relational Database Management Systems
    Release
    TKU-2019-Sep-1
    Change History
    Microsoft SQL Server - Database Detail Pattern - Change History
    Reports & Attributes
    Microsoft SQL Server - Database Detail Pattern - Reports & Attributes
    Publisher Link
    [Microsoft]

    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

    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

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

    Setting of Database Integration Point

    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.

    Note

    If the SQL Server has been configured to allow only Windows domain users, then the jTDS driver should be used when creating database credentials and the additional parameters section populated with 'domain=Actual Windows Domain Name'. Furthermore, if the domain controller requires NTLM v2 authentication add the parameter: 'useNTLMv2=true'.
    Example: domain=MY_DOMAIN;useNTLMv2=true

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

    Note

    Enabling the get_db_individ configuration option can adversely affect pattern performance. You should only enable this option if database permissions require this

    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>


    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 additinoal 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 attemps 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 servername contain an underscore, a dollar or a # the pattern will perform various substitutions to ensure the WMI query runs successfully


    Command

    The pattern attempts to execute the following osql.exe command.

    • <path_to_osql> -S <hostname\servername> -w 65000 -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 - 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 subdirectories.  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

    Note

    The errorlog_discovery configuration option is disabled by default. We recommend you do not enable it unless you are having major difficulties with other discovery methods. This method will create Database nodes for databases that have been started and subsequently stopped, not just databases that are started and still running

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

     

    Note that due to a limitation in BMC Discovery this link will not be shown in visualisations.  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