Using Operations Manager to monitor a SQL server with more than 1000 databases - Catapult

I was recently faced with an interesting requirement to provide monitoring for a series of SQL 2005 servers which had several hundred databases on them. The official support limits for this management pack is at 50 databases (Per the management pack guide: "We recommend that you monitor no more than 50 databases and 150 database files per agent to avoid spikes in CPU usage that may affect the performance of monitored computers"). But if you have more than 50 databases on a SQL 2005 server you may want to read onward.

When installing the SQL 2005 management pack in environments where servers have hundreds of databases problems occur on the SQL 2005 server which is being monitored. Based upon my tests the problem is high processor utilization on the impacted system, and an inability to monitor the databases on the SQL 2005 server. The reason this occurs is that the SQL 2005 system is attempting to discover all of the databases and hitting upper bounds for Operations Manager’s send queue, allowed memory utilization and auto-restart functionality. The result is a consistent attempt to discover and failure which keeps the system consistently pegged at high processor levels since it can never complete the discovery successfully and the discovery automatically restarts when the Operations Manager agent restarts.

This blog post has been written to provide a process to allow for monitoring of SQL 2005 servers which have hundreds of databases which need to be monitored in Operations Manager 2012 R2. This blog post will discuss the following topics:

How to create a SQL 2005 server with large numbers of databases
Summary implementation steps for the workaround
Detailed implementation steps for the workaround
Screenshots for the implementation steps
Relevant technical notes for the workaround
Next steps and thoughts

How to create a SQL server with more than 1000 databases on it:

The first challenge to testing this requirement was to actually have a SQL server with a large enough number of databases. To accomplish this I created an extremely simple SQL script to just generate the databases based upon the blog available here. To create a thousand databases, I copied this into Excel into 1000 rows using this formula:

="CREATE DATABASE my_db" & ROW(A3) & ";"

This resulted in the following for the first 10 rows as an example.

CREATE DATABASE my_db1;

CREATE DATABASE my_db2;

CREATE DATABASE my_db3;

CREATE DATABASE my_db4;

CREATE DATABASE my_db5;

CREATE DATABASE my_db6;

CREATE DATABASE my_db7;

CREATE DATABASE my_db8;

CREATE DATABASE my_db9;

CREATE DATABASE my_db10;

I copied over the excel rows into SQL and executed it to create the 1000 databases on the server.

Workaround summary:

Based upon my tests the following four changes are required to provide successful discovery and monitoring for SQL 2005 servers with large numbers of databases.

Distribute the SQL credentials required for discovery to the SQL 2005 system to avoid a flurry of 1108 errors in the Operations Manager event log.
Increase the 15 MB agent queue for the discovery process on the SQL 2005 boxes to 120 MB. This is done by making a registry change on the SQL 2005 system detailed below.
Increase the private bytes threshold for the SQL 2005 boxes to a higher threshold level. This is done through an override targeted to the SQL 2005 system detailed below.
Disable the recovery which causes the Operations Manager to restart the Operations Manager service on the SQL 2005 box. This is done through an override targeted to the SQL 2005 system detailed below.

The above changes will need to remain in place for the SQL 2005 servers. This is required so that Operations Manager can discover and monitor additional databases which may be added onto the SQL 2005 systems.

Workaround implementation steps:

The following are the recommended steps to implement this approach to provide monitoring for SQL 2005 servers with large numbers of databases:

Import the SQL 2005 monitoring and discovery management packs along with the custom built management pack to disable discoveries for SQL 2005. Do not import only the SQL 2005 monitoring and discovery management packs without creating your own custom SQL discovery pack to disable the discovery of these database servers as each SQL server will attempt to discover all databases and fail as has happened in the environment historically. The SQL Discovery Overrides management pack disabled the seed discovery which is used to find if a server has SQL 2005 installed on it.

The relevant portion of the code from the management pack which disables discoveries is shown below:

<Value>false</Value>

</DiscoveryPropertyOverride>

If you need to create your own you could import the SQL management pack without agents deployed to the systems with large numbers of SQL databases. Then create an override which disables the seed discovery. The example shown below disables the discovery for the class by setting the object discovery value to false. To discover systems you can now enable them via an override setting them to true for the discovery.

Identify a SQL 2005 server with more than 500 databases which needs to be monitored by Operations Manager and can be used for an initial configuration.
Be sure to distribute the SQL credentials required for discovery to the SQL 2005 system. This is done in the administration pane, Run As Configuration, Profiles for the SQL accounts.
Increase the 15 MB agent queue for the discovery process on the SQL 2005 boxes to 120 MB. This is done by making a registry change on the SQL 2005 system and restarting the Operations Manager agent. The registry key is stored in HKLM\SYSTEM\CurrentControlSet\Services\HealthService\Parameters\Management Groups\<MG name>. Change the maximumQueueSize to 122880 in decimal. After this registry change restart the "Microsoft Monitoring Agent" service.
Increase the private bytes threshold for the SQL 2005 boxes to a higher threshold level (part 1). This is done through an override targeted to the SQL 2005 system for the Monitoring Host Private Bytes Threshold monitor which should be set to 2048000000 for the Agent Performance Monitor Type (Consecutive Samples) – Threshold. [This value was identified using the highest value currently seen for this monitor which was on the VMM system in my environment]
Increase the private bytes threshold for the SQL 2005 boxes to a higher threshold level (part 2). This is done through an override targeted to the SQL 2005 system for the Health Service Private Bytes Threshold monitor which should be set to 2048000000 for the Agent Performance Monitor Type (Consecutive Samples) – Threshold. [This value was identified using the highest value currently seen for this monitor which was on the VMM system in my environment]
Disable the recovery which causes the Operations Manager to restart the Operations Manager service on the SQL 2005 box. This is done through an override targeted to the SQL 2005 system for the System Center Management Health Service Memory Utilization Properties monitor on the recovery task called "Restart System Center Management". For the recovery, set the override value to False for the specific object (the SQL 2005 server).
Create an override to enable discovery for the SQL 2005 system. This override is targeted at the following discovery which should be disabled by the SQL Discovery Overrides management pack. Create an override to enable to discovery for the specific SQL 2005 system.
1. SQL Server 2005 DB Installation Discovery Source
Monitor the SQL 2005 server both from a performance perspective (high processor over a duration of time), an event log perspective (OperationsManager log on the SQL 2005 server), and for databases and their discovery/monitoring status.
Repeat steps 2-10 for the remaining SQL 2005 servers which match this criteria.

Screenshots for the implementation steps:

Import the SQL 2005 management packs with the override management pack for SQL.

The following discoveries is disabled in the SQL Discovery Overrides management pack through setting the discovery to False.
No screenshot is applicable.
Where the run as accounts are configured for SQL:
The registry setting to change and service to restart are shown below (done on the SQL 2005 server).
The overrides on Private Bytes overrides are shown below.
See the screenshot shown in 5.
The Health Service is configured to have the Restart System Center Management Health Service flag to false for the SQL 2005 server.
The discovery is shown in item #1. Through an override set enabled to True for the specific server which discovery needs to occur on.
Use task manager and resource explorer for utilization on the SQL server and the performance view for processor usage in Operations Manager.
No screenshot is applicable.

Relevant technical notes for the workaround:

The following were the classes which were involved in discovery for the SQL 2005 management pack which were relevant to this article and screenshots of their discoveries:

SQL Server 2005 Installation Seed
SQL Server 2005 DB Engine
SQL Server 2005 DB

To see what is discovered use the Monitoring Pane, Discovered Inventory view and target it to the classes above.

Next steps and thoughts:

Talking at TechEd 2014 with the folks at Viacode they mentioned another option which may be available to perform this type of monitoring without using the process I listed above. Their recommendation was to disable the discovery for files on the SQL 2005 management pack. Their logic is that it’s not often required to have the file level information and it would greatly decrease the amount of discovery required (by a factor of 4 I believe). I have not had cycles available to test this configuration yet but if you are ever in a situation where you need to do this type of monitoring I recommend trying this first as it would be easier than what I put together and potentially a more supportable option.