Opsmgr: How to Exclude SQL Components from Monitoring (and Remove Discovery Data)
Below are some procedures for excluding SQL database engine instances, SQL databases and SQL Agent jobs from discovery and/or monitoring. To exclude these components from monitoring, we need to:
- Override the Object Discoveries
- Delete any existing discovery data
1. DB Engine Instances:
If you want to exclude specific DB Engine instances by name then one or more overrides can be applied to the “Discover SQL 2005 Database Engines (Windows Server)” (SQL 2005) or “Discover SQL 2000 Database Engines (Windows Server)” (SQL 2000) discoveries to provide an “Exclude List”.
Exclude List is a comma-delimited list of SQL DB Engine instance names as shown in figure 1 below. If you want to exclude whole servers entirely from having their DB Engine instances discovered then you could right-click on the discovery select “Overrides” -> “Disable the object discovery” -> “For a specific object of type: Windows Server” and select the systems they want excluded.
Figure 1 Override for Exclude List on “SQL 2005 Database Engine”.
2. SQL Databases:
The process for SQL databases is quite similar to that of the SQL DB engine. One or more overrides can be applied to the “Discover Databases for a Database Engine” (SQL 2005) or “Discover SQL 2000 Databases” (SQL 2000) discoveries to provide an Exclude List which is a comma-delimited list of DB names to exclude, which is shown in figure 2 below.
Likewise if you wanted to exclude whole SQL DB engine instances from having their databases discovered then they could right-click on the discovery select “Overrides” -> “Disable the object discovery” -> “For a specific object of type: SQL 2005 DB Engine” and select the DB Engine instances they want excluded.
Figure 2 Override for exclude list on “Discover Databases for a Database Engine”.
3. Long Running Jobs:
There are two (technically three) ways this monitoring is provided: On a per-SQL agent basis (SQL 2005 only) and on a per-job basis (SQL 2000 and SQL 2005).
On a per-”SQL agent instances” basis (SQL 2005 only):
This is the only monitoring that is “turned on” by default and it’s only provided in the SQL 2005 MP. I put turned on in quotes because all of the monitors are enabled but the job specific monitors are targeted at classes which are not discovered by default in the MP. The behavior of this monitoring is that every so often a script runs against each SQL agent and identifies of all the jobs that are run by that agent, what the duration of the longest job was. That value is then compared against the threshold (defaults to 120 minutes) and an alert is raised is the threshold is exceeded.
So regardless of how many jobs are hosted on an instance of SQL agent, if one or more of them deviate from the threshold then an alert will be raised for the given SQL agent by the “Long Running Jobs” monitor. Exclusions here are handled by standard overrides on the “Long Running Jobs” monitor as shown in figure 3 and should consist of instances of the “SQL 2005 Agent” class.
On a “per-job basis” (SQL 2000 and SQL 2005):
This monitoring is available in both the SQL 2005 and SQL 2000 MPs via the “Job Duration” monitor associated with the “SQL 2005 Agent Job” and “SQL 2000 Agent Job” classes respectively. In order to get this monitoring the user needs to enable the discovery of individual jobs, which can be done via overrides on the “Discover SQL 2000 Agent Jobs” and “Discover SQL 20005 Agent Jobs” object discoveries. Once jobs are being discovered, then each job will have this monitor associated with it. The behavior of the monitor is rather straight forward in that it looks at the most recent duration for a given job and will evaluates that duration against two values; a lower threshold and an upper threshold. If the duration is below the lower threshold then the state of the monitor for that job is set to green. If the duration is above the lower and below the upper then state is turned to yellow/warning. If the duration exceeds the upper threshold then the state changes to red/error.
Exclusions for the “Job Duration” monitor would be done via standard overrides to that monitor, providing a list of “SQL 2005 Agent Job” or “SQL 2000 Agent Job” instances that the override would apply to.
Figure 3 Configuring overrides on the “Long Running Jobs” monitor for specific SQL 2005 agent instances.
To Delete Discovery Data
Instructions for deleting discovery data using the remove-disabledmonitoringobject cmdlet can be found HERE.