Configuration for large systems

By default. when the hosts are added to an environment, PATROL for Microsoft SQL Server automatically discovers all MS SQL Server instances installed on the host and adds the hosts for monitoring. Similarly, when the MS SQL Server instances are discovered by PATROL for Microsoft SQL Server all its databases are automatically added for monitoring, by default.

Because of this automated process large systems should be configured carefully. Also, the systems should be split between few environments and between few PATROL agents (if required).

PATROL for Microsoft SQL Server supports monitoring of large systems. Configuration for such systems should to be done correctly, else, you can face load issues and monitoring will not work as expected.

Based on internal testing, BMC recommends some guidelines in the following areas:

Important

The figures mentioned here is just a sample data to give you a general idea of what should be the range. The performance and scalability of the systems depend on many other inputs like CPU/memory size, load on MS SQL server, load on host, load on PATROL Agent and other parameters. It might be possible that lower numbers might not work well and on the other hand bigger numbers might work well.

Planning monitoring configuration for large systems

Consider the following points when you are planning the configuration for a large system:

No.Consideration scanarioTested values
1

Environments for PATROL Agent

  1. For the first time, PATROL for Microsoft SQL 9.6.00 enables remote monitoring. BMC recommends (not mandatory) to use a dedicated PATROL Agent for monitoring MS SQL Servers only.
  2. We have successfully tested dedicated PATROL Agent with ten environments
  3. The following parameters of the MS SQL Server (MSSQL_SERVER_MAIN) application class indicate the load on the PATROL Agent:
    1. Instance Count (InstanceCount) - Successfully tested dedicated PATROL Agent with Instance Count = ~ 5,000 
    2. Parameter Count (ParameterCount) - Successfully tested dedicated PATROL Agent with Parameter Count = ~ 50,000

These statistics are suggested considering that BMC PATROL for Microsoft SQL Server has a dedicated PATROL Agent. Regards not a dedicated PATROL Agent, the number of environments that can be configured on each PATROL Agent depends on the load and the number of other KMs running on the PATROL Agent.

2

Hosts in the environment 

Successfully tested the environment with five hosts
3

MS SQL Server instances in the host 

Successfully tested the host with five MS SQL Server instances

4

Total MS SQL Server instances for an environment

Successfully tested the environment with ten MS SQL Sever instances with all its hosts.

Splitting the monitoring of Microsoft SQL Servers between environments:

In Host configuration, you can filter the MS SQL Server instances. These instances will be discovered in the MS SQL server instance name (regex) field.

Example

Ignore the databases consideration in the following example.

If you want to configure a MS SQL Server system with three hosts and 20 MS SQL Server instances divided as,

  1. Host 1: SQLInst1-SQLInst7 (seven instances)
  2. Host 2: SQLInst8-SQLInst14 (seven instances)
  3. Host 3: SQLInst15-SQLInst20 (six instances)

BMC recommends to configure the MS SQL Server system by configuring two environments.

  1. Environment 1 with total ten MS SQL Server instances:
    1. Host1 and MS SQL server instance name (regex) = .*


    2. Host2 and MS SQL server instance name (regex) = ^SQLInst8$|^SQLInst9$|^SQLInst10$ (To include SQLInst8- SQLInst10)

       
  2.  Environment 2 with total ten MS SQL Server instances:
    1. Host2 and MS SQL server instance name (regex) = ^SQLInst11$|^SQLInst12$|^SQLInst13$|^SQLInst14$ (To include only SQLInst11- SQLInst14)

    2. Host3 and MS SQL server instance name (regex) = .*

5

Total databases for the environment

Successfully tested an environment with total 750 databases

If you have SQL Server instances with large number of databases, we recommend to add the SQL Server instances in succession to the environment until you reach up to approximately 750 databases with all MS SQL Server instances for environment.

For a single MS SQL Server environment with more than 750 databases, we recommend to split the databases between two or more environments. 

Splitting the monitoring of a single MS SQL Server with a large number of databases between environments:

For example, if you want to configure monitoring for a MS SQL Server SQLInst1 with 2000 databases DB1-DB2000, the recommendation is as below (This example uses dummy regular expressions):

  1. Environment1, SQLInst1 and Include/Exclude from monitoring (regex) = dummy_regex1-750

    This configuration will monitor SQLInst1 general metrics and also metrics for databases DB1- DB750

  2. Environment2, SQLInst1 and Include/Exclude from monitoring (regex) = dummy_regex751-1500

    Enable Monitor databases only option to avoid duplicate monitoring of SQLInst1 general metrics

    This configuration will monitor only metrics for databases DB751- DB1500 without entire SQLInst1 general metrics.

  3. Environment3, SQLInst1 and Include/Exclude from monitoring (regex) = dummy_regex1501-2000

    Enable Monitor databases only option to avoid duplicate monitoring of SQLInst1 general metrics

    This configuration will monitor only metrics for databases DB1501- DB2000 without entire SQLInst1 general metrics

In case of a MS SQL Server with large number of databases, discovery and start of monitoring might take long time than usual (1 hour or more).

The tests are done with the following configuration:

    1. Data collection interval (min) = 30
      Increase in the interval will decrease the load, and vice versa.
    2. Availability collection interval (min) = 2
      Increase in the interval will decrease the load
    3. Perfmon objects filtering = Full
      Setting other Perfmon filtering mode except Full will decrease the load 
    4. Monitor file and file groups = True
      Disabling the monitoring of files and file groups will decrease the load
    5. Monitor availability only = False
      Enable Monitor availability only mode will decrease the load, and allow you to monitor more MS SQL instances for host/environment
      The way to configure it should be by trial and error technique. BMC has no recommended numbers for this mode. Total databases for environment (consideration number 5) will become irrelevant in this mode.
    6. No Custom SQL query configured - Configuring custom SQL queries will increase the load
Was this page helpful? Yes No Submitting... Thank you

Comments