The requirement was to prevent specific servers from being monitored by the SQL Server management pack. These were development servers where the DBAs weren’t concerned in what was going on in terms of SQL Server.

Given there was a requirement to monitor the OS and other areas, its not as easy as to just exclude these servers from monitoring by removing the agent (and using a TCP port monitor to notify if the server was up or down). Solving this challenge required diving into how the SQL Server management pack determines what systems to monitor and how to exclude specific systems. Here’s what you can do:

Create a Group to Exclude

Start by creating a group for excluded development servers. Call this group Development SQL Servers (store it in a custom management pack other than Default).  The criteria used to add members to the group can be on naming standards for development database servers. Add members to this group as shown in these two screenshots:

Exclude the Group from Discoveries

Exclude this group from discoveries using an override.

Find the discovery under

Authoring -> Management Pack Objects -> Object Discoveries –> Discover SQL 2005 Database Engines (Windows Server)

Right-click, select Properties, select the Overrides tab, and select Override to create an override For a  group.

Now select the new group name you just created from the list.

Override the Enabled setting, setting it to false, then save your changes to the management pack you created earlier.

Perform the same action for the Discover SQL 2000 Database Engines (Windows Server) and for the Discover SQL 2008 Database Engines (Windows Server) discoveries.

Exclude the Group from the SQL Computers Group

The next step is to exclude the group from the SQL Computers group. Perform the following steps:

Find the group under Authoring –> Groups -> SQL Computers. Right click, and select Properties.

Select Overrides and create an override For a group. Choose the new group name from the list. Now, override the Enabled setting and set it to false, and save your changes to a non-default management pack.

Check Discoveries

In the Monitoring space node under Discovered Inventory, choose the Change Target Type option from the Actions pane. Set the Discovered Inventory to show SQL DB Engine. Find the number of discovered SQL DB Engines that are part of the group you created.

Do this for each of the SQL DB engines:

• In the monitoring space under Discovered Inventory, choose the Change Target Type option from the Actions pane and set the Discovered Inventory to show SQL 2000 DB Engine. Find the number of discovered SQL 2000 DB that  are part of the group you created.
• In the monitoring space under Discovered Inventory, choose the Change Target Type option from the Actions pane and set the Discovered Inventory to show SQL 2005 DB Engine. Find the number of discovered SQL 2005 DB Engines that are part of the group you created.
• In the monitoring space under Discovered Inventory, choose the Change Target Type option from the Actions pane and set the Discovered Inventory to show SQL 2008 DB Engine. Find the number of discovered SQL 2008 DB Engines that are part of the group you created.

Remove the Disabled Monitoring Objects

Run the following PowerShell command (without parameters)

Remove-DisabledMonitoringObject cmdlet

Now re--check each of the discoveries to verify that the excluded systems are no longer in any of these discoveries. You will also want to verify under  Monitoring -> Microsoft SQL Server -> Computers that the excluded systems are no longer listed.

Summary

To summarize, if you want to remove your development SQL servers from being monitored by the SQL MP, try this:

• Create a group with the systems to no longer monitor with the SQL MP.
• Exclude the group from the relevant discoveries and computer group.
• Remove the disabled objects.
• Verify that the systems excluded are no longer monitored by the SQL MP.

Additional reference information

The following items may be helpful:

• http://blogs.technet.com/jonathanalmquist/archive/2008/09/14/remove-disabledmonitoringobject.aspx
• http://social.technet.microsoft.com/Forums/en-US/operationsmanagermgmtpacks/thread/226be1d4-ef44-4216-975b-7b9b202540c9

If you haven’t migrated to OpsMgr 2007 yet, you may want to consider purchasing Microsoft Operations Manager 2005 Unleashed – there’s a very nice review at http://www.left-brain.com/tabId/65/itemId/1588/Microsoft-Operations-Manager-2005-Unleashed.aspx.

(The book can be ordered at www.tinyurl.com/yxtrqd.)