Configuring Microsoft SQL Server monitoring profile

This topic describes how to configure BMC PATROL for Microsoft SQL Server.


 Click here to see how to configure Microsoft SQL Server for monitoring

This topic describes how to configure BMC PATROL for Microsoft SQL Server for monitoring.

Add Monitoring Configuration for PATROL for Microsoft SQL server

  1. Select Monitoring from the Create Monitoring Policy window. Click onAdd Monitoring Configuration.


  2. Select Microsoft SQL Server as Monitoring Solution, select the installed Version. 
    By default the Monitor Profile will be Microsoft SQL Server and Monitor Type will be the MS SQL Server. ClickAdd for Environment configuration.
  3. Specify your Environment name and for Hosts configuration clickAdd.

    Note

    In order to configure a MS SQL server environment at least one host should be added.


     

  4. Specify the Host name/IP address and for MS SQL Server instances configuration clickAdd.

    Note

    In order to configure a MS SQL server environment at least one MS SQL server instance should be added.


  5. Specify regex pattern that matches the Microsoft SQL server instances that needs to be monitored.

     
     

  6. Once you configure the environment, you can see the hierarchy of the monitoring configuration in the figure below.
  7. After configuring an environment you need to save, then all the changes are automatically pushed to the PATROL Agents on which the policy is already applied.


On the Add Monitor Types dialog, with the Monitoring Profile set to Microsoft SQL Server, and the Monitor Type set to Microsoft SQL Server, provide the following details: 

FieldDescription
Environment nameEnter a unique logical name for the environment. A container will be created with the environment name. This environment will hold all the SQL Server databases. BMC recommends you to provide only alphanumeric characters in environment name.
Windows user

Windows user credentials are used for discovery and to collect host related data, such as disk size or MS SQL Server service status.

UserName should contain the domain name (DOMAIN_NAME\USER_NAME).

User Name

Enter a unique, user-defined alias name, host name.

User Name should contain a domain name (DOMAIN_NAME\USER_NAME).

PasswordEnter the password for the specified user.
Confirm PasswordRe-enter the password for confirmation.
MS SQL Server login
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).

PasswordEnter the password for the specified user.
Confirm PasswordRe-enter the password for confirmation.
Hosts configuration
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)

MS SQL server instance configuration
MS SQL Server instance name (regex)

Enter the name of the MS SQL server instance. Enter .* to discover all the available instances.

Monitor databases only

Enable this option to monitor only database related metrics. Monitoring of any other SQL Server general metrics will be disabled.

Note

This feature is disabled if Monitor availability only feature is enabled. 

Monitor availability only

Enable this option to monitor only availability related metrics. Monitoring of any other SQL Server general metrics will be disabled.

Databases

Filtering mode

Specify the filtering type for the filtering the database.

  • None - Selects all databases for filtering
  • Include - Includes only the databases entered in the SQL Server databases field for monitoring
  • Exclude - Excludes the databases entered in the SQL Server databases field from monitoring
Include/Exclude from monitoring (regex)

Enter a regular expression of the relevant database names to either include or exclude from filtering.

Monitor files and file groups

Enable monitoring of DB files and file groups.

Ignore file 'Autogrowth'

Configuration for Space Used Percentage Metrics

MS SQL Agent
Jobs considered as failed as long as its last run was failed

Select this check box if you want to consider a job as failed as long as its last run was failed. Such values are stored in the SQL Agent Job Failures parameter. For example, when one job fails in a collection interval, the value of the SQL Agent Job Failures parameter is set to 1 and the value remains so until next run. If the check box is clear, the value of the SQL Agent Job Failures parameter is reset to 0 in the new collection interval if the job did not run during the new collection interval. If you want to retain the number of jobs that failed until the same job runs successfully during any collection interval, select this check box.

Jobs filtering options
Jobs by logins filtering options
Filtering mode

Specify the filtering type for the filtering the MS SQL Agent jobs by logins.

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

Jobs by categories filtering options
Filtering mode

Specify the filtering type for the filtering the MS SQL Agent jobs by categories.

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

Jobs by names filtering options
Filtering mode

Specify the filtering type for filtering the MS SQL Agent jobs by names.

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

Perfmon objects filtering
Perfmon monitoring level
  • None - Disables the monitoring of all perfmon objects
  • Most Important - (Recommended) Monitors all important perfomon objects

     Click here to see the list of most important perfmon objects

    Availability Replica

    Columnstore

    Databases

    Database Mirroring

    Database Replica

    General Statistics

    Locks

    Memory Manager

    Plan Cache

    Replication Dist

    Replication Logreader

    Replication Snapshot

    Resource Pool Stats

    SQL Errors

    SQL Statistics

    Transactions

    XTP Storage

    XTP Transactions


    Note: The Most Important perfmon objects are specified in the %PATROL_HOME%mssql\configuration\PerfmonMapping.xml file. The most important perfmon objects are marked as is_important. You can change these perfmons in the PerfmonMapping.xml file. After you update the file, restart PATROL Agent.

  • Full - Enables the monitoring of all perfmon objects
  • Custom - Choose manually the perfmon objects to monitor
Click Custom filtering settings to filter perfmon objects
Filtering mode

Specify the filtering type for filtering the perfmon objects.

  • Include - Will include only the perfmon objects entered in the List - Custom perfmon filtering list for filtering
  • Exclude - Will exclude the perfmon objects entered in the List - Custom perfmon filtering list from filtering
Perfmon objects

Select the list of perfmon objects to add to the List - Custom perfmon filtering list to Include/Exclude from filtering.

  • Access Methods                              
  • Availability Replica
  • Backup Device
  • Batch Resp Statistics
  • Broker Activation
  • Broker/DBM Transport
  • Broker Statistics
  • Broker To Statistics
  • Buffer Manager
  • Buffer Node
  • Buffer Partition
  • Catalog Metadata
  • CLR
  • Columnstore
  • Cursor Manager By Type
  • Cursor Manager Total
  • Databases
  • Database Mirroring
  • Database Replica
  • Deprecated Features
  • Exec Statistics
  • File Table
  • General Statistics
  • HTTP Storage
  • Latches
  • Locks
  • LogPool
  • Memory Broker Clerks
  • Memory Manager
  • Memory Node
  • Plan Cache
  • Query Execution
  • Query Store
  • Replication Agents
  • Replication Dist
  • Replication Logreader
  • Replication Merge
  • Replication Snapshot
  • Resource Pool Stats
  • SQL Errors
  • SQL Statistics
  • Transactions
  • User Settable
  • Wait Statistics
  • Workload Group Stats
  • XTP Cursors
  • XTP Garbage Collection
  • XTP IO Governor
  • XTP Phantom Processor
  • XTP Storage
  • XTP Transaction Log
  • XTP Transactions
Add to listClick this option to add the selected perfmon object to the List - Custom perfmon filtering list.
Modify selectionClick this option to modify the configuration details.
Remove from listClick this option to remove the selected perfmon object from the List - Custom perfmon filtering list.
List - Custom perfmon filteringDisplays a list of perfmon objects added to Include/Exclude from filtering.
CloseClick this option to close the dialog box.
Custom SQL queries
Name

Enter the logical name for the SQL query.

SQL query

Enter the SQL query.

Collection interval (min)Specify the collection interval in minutes
Add to ListClick this option to add the selected perfmon object to the List - Custom SQL queries list.
Modify SelectionClick this option to modify the configuration details.
Remove from ListClick this option to remove the selected perfmon object from the List - Custom SQL queries list.
List - Custom SQL queriesDisplays a list of SQL queries you have entered.
Time based parameters configuration
Long Running Transactions criteria (min)

Specify the amount of time the transaction has been running before it is considered as a long running transaction. All long running transactions are reported by the Long Running Transactions parameter.

SQL Agent Hung Jobs criteria (min)

Specify the amount of time the job has been running before it is considered as a hung job. All the hung jobs are reported by the SQL Agent Hung Jobs parameter.

Idle Procs criteria (min)

Specify the amount of time the process has not been consuming any resources before it is considered as an idle process. All the idle processes are reported by the Idle Procs parameter.

Number of Blocking Processes criteria (min)

Specify the amount of time the process has been blocked by the blocking process before it is considered as blocking process. All the blocker processes are reported by the Number of Blocking Processes parameter

Advanced Configuration
Unique port number (per instance)

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.

Add to ListClick this option to add the configuration to the List - MSSQL Server instance configuration list.
Modify SelectionClick this option to modify the configuration details.
Remove from ListClick this option to remove the configuration from the List - MSSQL Server instance configuration list.
List - MS SQL Server instances configurationDisplays a list of configurations you have entered.
Add to ListClick this option to add the configuration to the List - Hosts configuration list.
Modify SelectionClick this option to modify the configuration details.
Remove from ListClick this option to remove the configuration from the List - Hosts configuration list.
List - Hosts configurationDisplays a list of hosts you have configured.
KM administration
Availability collection interval (min)

Enter the availability collection interval in minutes.

Data collection interval (min)Enter the data collection interval in minutes
Device mappingSelect to enable device mapping to consolidate the configured devices at the device level
LoggingSelect to enable debug logging for the environment
Generate detailed events (requires MRL)

Select to enable to send an event that contains the detailed annotation for the following metrics:

Note

In order to support this feature suited mrl needs to be applied on TrueSight cell side. For more details, see steps to apply mrl script.

Query timeout (sec)Specify the number of seconds allowed for a query to complete before going into timeout. This time affects all queries executed by the KM to collect data.
Add to ListClick this option to add the configuration to the List - Environment configuration list.
Modify SelectionClick this option to modify the configuration details.
Remove from ListClick this option to remove the configuration from the List - Environment configuration list.
List - Environment configurationDisplays a list of environments you have configured.
Global administration settings
LoggingSelect to enable debug logging only at a global level
ResetClick this option to reset the monitor configuration to its default values.
AddClick this option to add the configuration.
CloseClick this option to close the Add Monitor Types configuration screen.
 


Was this page helpful? Yes No Submitting... Thank you

Comments