Kevin Holman's System Center Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

Stop monitoring SQL Express and Windows Internal Database

Stop monitoring SQL Express and Windows Internal Database

  • Comments 15
  • Likes

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”:

 

image

 

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:

 

image

 

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

Comments
  • oh thanks thats very handy didnt know you do that.

  • I think they've changed the name of the Discovery in the latest SQL MP, it's now 'Discover SQL Server 2005 Database Engines (Windows Server)' with the extra 'Server' in there?    All my SQL Express and Windows Internal Databases started showing up again in our alerts, so I guess you have to redo your exclusions for this new Discovery rule.

  • Steve - I will look into this.  This should not have changed.  The override is by ID, not by display name.... so it should still apply unless they broke exclusion by name in the changes to the discovery script.

  • I looked - the discoveries are identical....  The only thing that changed is displayname, and the script for the datasource.  I cannot imagine why these would populate again if your override was in place.

  • I've few sqlexpress edition with instance name as MSSQLSERVER, i created one group based on windows computer and override in the discovery to enable as False for this group. But now DBA team installed standard edition in the same server,now the server has both express edition and standard edition. it is not discovered due to disable the discovery, now i want to discover that standard edition instance but SQLEXPRESS should not be discovered.

    How can i achieve this?

  • Hello Kevin, 13 Jun 2012 4:32 AM

    I've few sqlexpress edition with instance name as MSSQLSERVER, i created one group based on windows computer and override in the discovery to enable as False for this group. But now DBA team installed standard edition in the same server,now the server has both express edition and standard edition. it is not discovered due to disable the discovery, now i want to discover that standard edition instance but SQLEXPRESS should not be discovered.

    How can i achieve this?

  • @vijayh -

    You can a few options.  Uninstall SQL express.  Or - create an override to disable discovery of the Express instance BY NAME - as posted above - ONLY for this specific server.  Then any other SQL instance will be discovered.

  • So when I do this only ~10% disappear.  I've waited 24hr and run remove-disabledmonitoringobject as well (multiple times).  I'm running SCOM 2007 R2 infra, SCOM 2012 agents, SQL MP 6.3.173.1.  Targeting overrides to Windows Server and the SQL Discovery Seed actually excluded more than just targeting Windows server.  Below is one of my overrides - pls tell me I've just made a simple mistake!

    ID="OverrideForDiscoveryMicrosoftSQLServer2005DBEngineDiscoveryRuleServerForContextMicrosoftWindowsServerComputer44870d24b20d485f9c96ae9f74ceaf45" Context="Windows!Microsoft.Windows.Server.Computer" Enforced="false" Discovery="SQLServer!Microsoft.SQLServer.2005.DBEngineDiscoveryRule.Server" Parameter="ExcludeList" Module="DS">

    <Value>ECSQLEXPRESS,MICROSOFT##SSEE,SQLEXPRESS,SQLEXPRESS1,SQLEXPRESSTEST,AUTODESKVAULT,BKUPEXEC,QUADRATE,BPASERVER8,SQLExpress,HPWJA,MICROSOFT$VMM$,VIM_SQLEXP,WINTOOL</Value>

  • First - when using the parameter override for discovery "ExcludeList", running Remove-DisabledMonitoringObject will not do anything.  This is by design.  When we are using ExcludeList - we need the discovery to run, and to UNDISCOVER the SQL instance by name.  So we must ensure that the discovery is not "disabled" on the host server.

    Your list looks good.  However - you might need to ensure you are putting these same exclusions in on your SQL server 2008 discovery rule as well.

    Additionally - one thing to point out - some workflows don't handle any test with a "$" in there - because it thinks it is a variable.  You might consider removing the one with the $ signs and see if that fixes it.  Also - if you have overridden the frequency of the SQL DB engine discovery in the past - you might want to consider changing that back for the purposes of testing your changes.  Or just restarting the agent to force it to run all discoveries at startup.

  • Oh - and Nicole - feel free to ping me directly in email anytime.

  • Kevin - thanks for the quick reply!  I removed the instance with the $ and confirmed my SQL2005/2008 exceptions match.  Unfortuantely, no errors are generated and Express instances still appear.  I've even reset the health store and restarted a few agents.  I've also tried shortening my exclusion list - in case there is a max field size -- but there's no change.  Any other thoughts?

  • Hi,

    I'm experiencing the same as Nicole, I've added a number of instances to exclude, but my already discovered instances are still there. The only way I've been able to make them disappear, were by re-installing the agent.

    My theory is that the DB engine is no longer directly discovered with the Windows Server as target, rather than SQL Server installation seed. Even after making the server disappear from that discovery the SQL instances still hang around.

    I'm doing some more tests, and will hopefully find an easy workaround.

    /Per

  • Hi Kevin,

    I've followed the steps you described above to the letter, but it seems I cannot get the same result.

    Not a single SQLEXPRESS is undiscovered; they still appear in the discovered inventory (even after 24+ hour). The discovery is set for 4 hours.

    In your override screenshot i noticed the Enabled property set to False (grey), so i assume you've set it in another override?

    Is that what I'm missing, or shouldn't it be of any influence?

    Thx,

    Dennis

  • the target for these discoveries is currently "Sql Server 200x Installation Seed" even though the names of the discoveries have "(windows server)" in them. there is another discovery called "sql server 2005 db installation discovery source" that targets windows server and discovers the "seed." should we be targeting our overrides at all objects of type "sql server 200x installation seed" instead of windows server now?

  • Should your method still work for SQL 2012 Express? Or should I use a different discovery?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs