Configuring Microsoft SQL Server environment

This topic provides instructions to configure a Microsoft SQL Server environment.

To configure a Microsoft SQL Server environment

  1. From the PATROL Console work area, right-click MSSQL node (MSSQL_SERVER_MAIN application class)
  2. Access the KM menu commands as described in Accessing-KM-menu-commands.
  3. Choose the Environment Configuration KM menu command to display the Environment Configuration dialog box.
  4. From the Environment Configuration dialog box, 
    • To add a new Microsoft SQL Server environment, select Add and click Next.
    • To edit an existing Microsoft SQL Server environment, select it in the list, then select Modify, and click Next.
    • To delete an existing Microsoft SQL Server environment, select it in the list, then select Delete, and click Next. Then click OK to confirm.
  1. If defining or editing, enter the information, as follows:
    1. Environment name - Enter a unique environment name for monitoring the Microsoft SQL Server environment. A container will be created with the environment name. This environment will hold all the SQL Server instances. BMC recommends you to provide only alphanumeric characters in environment name.
    2. For a Windows User, 
      1. User Name - Enter a unique, user-defined alias name, host name. User Name should contain domain name (DOMAIN_NAME\USER_NAME).
      2. Password - Enter the password for the specified user.
    3. Enter the MS SQL Server login credentials,
      1. User Name - Enter a unique username. This username is the MS SQL Server login that will be used for monitoring. If this field is left blank then, the Windows User credentials will be used to connect to the MS SQL server (Integrated Security).
      2. Password - Enter the password for the specified user.
    1. Enter the Administration details,
      1. Availability collection interval (min) - Set the availability collection interval time.
      2. Data collection interval (min) - Set the data collection interval time.
      3. Device mapping - Select this checkbox to enable device mapping to consolidate the configured devices at the device level.
      4. Logging - Select this checkbox to enable debug logging for the external collector used by the environment.
  1. To continue with the configuration and to add the Host / IP address details, click Next.
  2. In the Host configuration of environment dialog box, define or edit the information as follows,
    • Host name / IP address - Enter the FQDN or IP Address where the SQL Server is installed. $HOSTNAME refers to the host on which Patrol Agent is installed. (Default value is $HOSTNAME)
  1. To continue with the configuration and to add the MS SQL server instance configuration details, click Next.
  2. In the MS SQL server instance configuration dialog box, define or edit the information as follows,
    1. MS SQL Server instance name (regex) - Enter a regular expression of the MS SQL server instances to be discovered and monitored. Enter .* (dot asterisk) to discover all the available instances on host hostName.
    2. Monitor databases only - Select this checkbox to monitor only database related metrics. Monitoring of any other SQL server general metrics will be disabled.This feature is disabled when 'Monitoring availability only' is enabled.
    3. Monitor availability only - Select this checkbox to monitor only availability related metrics. Monitoring of any other SQL server general metrics will be disabled.
    4. MS SQL Agent Jobs By Logins Filtering Options will filter the 
      1. Filtering mode -

          • None - Selects all available MS SQL Agent jobs by logins for filtering
          • Include - Includes only the MS SQL Agent jobs by logins entered in the Include/Exclude from Monitoring field
          • Exclude - Excludes the MS SQL Agent jobs by logins entered in the Include/Exclude from Monitoring field
      2. Include/Exclude from monitoring (regex) - Enter a regular expression of the relevant MS SQL Agent jobs by logins to either include or exclude from filtering.
    5. MS SQL Agent Jobs By Categories Filtering Options will filter the
      1. Filtering mode - 

          • None - Selects all available MS SQL Agent jobs by categories for filtering
          • Include - Includes only MS SQL Agent jobs by categories entered in the Include/Exclude from Monitoring field
          • Exclude - Excludes the MS SQL Agent jobs by categories entered in the Include/Exclude from Monitoring field
      2. Include/Exclude from monitoring (regex) - Enter a regular expression of the relevant MS SQL Agent jobs by categories to either include or exclude from filtering.
    6. MS SQL Agent Jobs By Names Filtering Options will filter the
      1. Filtering mode - 

          • None - Selects all available MS SQL Agent jobs by names for filtering
          • Include - Includes only the MS SQL Agent jobs by names entered in the Include/Exclude from Monitoring field
          • Exclude - Excludes the MS SQL Agent jobs by names entered in the Include/Exclude from Monitoring field
      2. Include/Exclude from monitoring (regex) - Specify a regular expression of the relevant MS SQL Agent jobs by names to either include or exclude from filtering
    7. Databases
      1. Filtering mode - 
        • None - Selects all available databases for filtering
        • Include - Includes only the databases entered in the Include/Exclude from Monitoring field
        • Exclude - Excludes the databases entered in the Include/Exclude from Monitoring field
      2. Include/Exclude from monitoring (regex) - Specify a regular expression of the relevant databases to either include or exclude from filtering
    1. Advanced configuration

      1. Unique port number (optional) -  Enter a unique port number.

        Note

        Leave this field blank. System defined ports are used by default in an empty field. If the System DBA has overwritten the system definitions, enter a unique port number for each instance.


    2. Monitor files and file groups - Select this checkbox to enable monitoring of DB files and file groups.
    3. Ignore file autogrowth - Select this checkbox to Configuration for Space Used Percentage Metrics
    4. To continue with the configuration and to add more MS SQL server instances configuration details or add more hosts configuration details, click Done.
    5. Click Cancel to exit without saving the configuration details
  2. In the Host configuration of environment dialog box, select add, modify or delete a server and click Accept. Select Save & Exit and click Accept to save the configuration details. Click Prev to go back to Environment Configuration dialog box.

Note

To configure the SQL cluster in a Microsoft SQL Server environment, see Monitoring-SQL-Server-failover-cluster-environment.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*