Stop monitoring SQL Express and Windows Internal Database

The Microsoft SQL Server management packs for 2005 and 2008 will discover and monitor SQL 2005 Express edition and the Windows Internal Database by default. 

This is often not desired, and causes a lot of noise in the management group, because these are not full blown instances of SQL, and some of our default monitoring does not work on them.  Likely – these aren’t critical SQL servers, and used for one-off applications that your SQL application team isn’t even responsible for.

There is a way to override the DB engine discovery, so there never show up.  This post will describe those steps.  This article references the current SQL MP at the time of this writing, which is 6.0.6648.0

Several other good blogs posts have also covered this, but I keep seeing this issue at customer sites, so I want to re-blog and to give it some more coverage.  Tim McFadden has a good post on the topic here:  http://www.scom2k7.com/disabling-sql-express-instance-discoveries/

First – lets take a look and see if you have any of these editions in your environment.  Go to the console, monitoring, discovered inventory, and change target type to “SQL DB Engine”.  Sort by “Edition”:

As you can see – I have one SQL 2005 express edition, and one Windows 2008 internal database engine in my environment.

What you will find – is that these editions are creating noise alerts… because of missing services, or service pack compliance.  Instead of tuning each rule/monitor and disabling for these machines – what I need to do is just say “don’t discover SQL Express or Windows Internal DB”.

Here are examples of the alerts you might see for these:

Service Check Probe Module Failed Execution

Error getting state of service
Error: 0x8007007b
Details: The filename, directory name, or volume label syntax is incorrect.
One or more workflows were affected by this.
Workflow name: Microsoft.SQLServer.2005.DBEngine.FullTextSearchServiceMonitor
Instance name: MICROSOFT##SSEE
Instance ID: {95468BCA-A30C-1EC5-027B-92BAF388AB69}
Management group: PROD1

Service Check Data Source Module Failed Execution

Error getting state of service
Error: 0x8007007b
Details: The filename, directory name, or volume label syntax is incorrect.
One or more workflows were affected by this.
Workflow name: Microsoft.SQLServer.2005.DBEngine.FullTextSearchServiceMonitor
Instance name: SQLEXPRESS
Instance ID: {6E9B2468-DF8A-B7F3-6283-D26F26B11D38}
Management group: PROD1

Script or Executable Failed to Run

The process started at 10:38:59 AM failed to create System.Discovery.Data, no errors detected in the output. The process exited with 0
Command executed: “C:\Windows\system32\cscript.exe” /nologo “DiscoverSQL2005DB.vbs” {E3EDD883-4224-657C-BD61-556FD10E7A38} {95468BCA-A30C-1EC5-027B-92BAF388AB69} omterm.opsmgr.net omterm.opsmgr.net omterm\MICROSOFT##SSEE MICROSOFT##SSEE “Exclude:”
Working Directory: C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 9\3192\
One or more workflows were affected by this.
Workflow name: Microsoft.SQLServer.2005.DatabaseDiscoveryRule
Instance name: MICROSOFT##SSEE
Instance ID: {95468BCA-A30C-1EC5-027B-92BAF388AB69}
Management group: PROD1

We can (sort of) do this as a discovery property override on the SQL DB engine discovery.  In the console – go to Authoring, Object Discoveries, Change Scope, and select all classes, or at least all SQL classes.  Then find the discovery “Discover SQL 2005 Database Engines (Windows Server)”.  Override it “for all objects of class: Windows Server”.  This will bring up the override properties.

At this point, we need to check the box next to “Exclude List”.  The description of this property is:

A comma separated list of DB Engine instances that should be excluded from discovery.  You can use the wildcard * to exclude all instances.

Now, what this allows us to do is to “not discover” and subsequently “undiscover” certain SQL instances BY NAME.  I can’t say a blanket “no SQL express edition” or “no Windows Internal Database” – which would be nice.  But I can find the common names that are typical for a SQL express edition, and choose not to discover those.  Common names will be:

• SQLEXPRESS
• BKUPEXEC
• SCANMAIL
• MICROSOFT##SSEE
• EXPRESS
• and many others specific to 3rd party applications

So – look at your discovered inventory and make a list of the instance names for your SQL express and Internal editions.  Then – place them in this override, separated only by a COMMA.  As shown:

Once saved – what will happen, is that on the next time that SQL DBengine discovery runs…. these instanced will disappear.  The default frequency is 14,400 seconds, or 4 hours.  So as long as the agents are healthy, and up, and running this discovery with your new overrides, you should see all these instances disappear within 4 hours.

***Caution!!!

IF you find any SQL Express editions with the named instance of “MSSQLSERVER” do NOT exclude them in this manner.  MSSQLSERVER is the default name for a “default” SQL instance, and adding this by name will remove ANY real SQL default instances from standard and enterprise edition.

For these specific instances of SQL express – you will need to exclude these from discovery the old fashioned way, by creating an override “for a specific object of class” and choosing the Windows Server object that hosts this discovered instance of SQL Express.  Then – set the override: Enabled=False.  Then – once saved – you will need to run the powershell commandlet of “remove-disabledmonitoringobject” which will scan ALL discoveries with an explicit ENABLED=FALSE override, and remove any of those discovered instances.  The overview of this process is documented on Jonathan’s blog:  http://blogs.technet.com/jonathanalmquist/archive/2008/09/14/remove-disabledmonitoringobject.aspx

Also – check out Jimmy Harpers blog on a similar subject – excluding monitoring of the SQL Server FullText service from moniotring, on instances that do not have this server, but where you DO want to monitor the rest of the SQL instance:  http://blogs.technet.com/jimmyharper/archive/2009/07/02/sql-server-full-text-search-service-monitor.aspx