Undiscovering / Stop Monitoring SQL 2008 Express Editions

Artykuł
01/21/2013

MP UPDATE: The updated MP for all sql versions plus new explainations can be found at https://blogs.technet.com/b/emreguclu/archive/2014/09/12/undiscovering-stop-monitoring-sql-2005-2008-2012-express-editions.aspx

In this post I will try to cover an alternate method of undiscovering SQL Express editions other than using the ExcludeList Property where I have a decent reason to do so. The MP explained in detail is also attached to the post so that you can test an deploy.

Even though SQL MP has ExcludeList override property for “Discover SQL Server 2008 Database Engines (Windows Server)” this property requires manually specifying Instance Names in format of (SQLExpress;MICROSOFT##SSEE). Therefore if a 3rd party application using instance name other than the default ones SCOM Admin has to know the name and exclude it manually for the discovery. This method is very well documented in Kevins Blog.

My method is a bit more complex but more dynamic so that SCOM Admin has almost nothing else to do. For the ones who are not interested how this method works ,they can use the MP attached in this blog of course after testing and reading How to Use The MP section.

The High Level Logic;

Create a custom seed class whose base class is Microsoft.SQLServer.2008.Seed
Discover this custom class and add property of Edition using a wmi query through Microsoft.Windows.Discovery.WMISinglePropertyProvider2
Create a custom Group of Computers which contains the Custom Seed Class
Discover the Members of the Group
Disable the DB Engine Discovery in SQL MP through an Override for our Custom Group
Remove disabled instances

With the above logic you will be able stop discovering SQL DB Engines on the Computers where SQL Express editions is installed.

I am sure you are wondering what will happen to the group (in step 3 above) once the instances are removed (step 6). This a question I got from my friends that “wont the group be emptied once the instances are deleted??” The answer is a big “NO”. That’s why I created a custom seed class taking the SQL seed class as the base class which is upper than the DB engines (DB engines will be removed not the seed) in the hierarchy. I also added the edition property to my seed class (please check step 2 details below in MP Explained) so that I can group filtering the seeds of express editions.

Important notes

if a computer has SQL express edition plus the Standard or Enterprise edition all of them will be undiscovered (but I guess this is a very minor case but just wanted to point out if you have such configurations).
the discovery for the seed class has an interval of one hour (it will be ok in terms of performance since this is a basic wmi discovery). If SQL DB Engine discovery runs before our seed class discovcery than the sql express edition will be discovered. This is not going to happen frequently but once a month running Remove-SCOMDisabledClassInstance would do the trick

Recommended Reading

Extending Windows Computer Class using VSAE; I wont be starting from scratch to mp Authoring but this post will do fine for the ones who want to adapt to Visual Studio Authoring.

MP Explained

1) Create a custom seed class; First we need to declare our seed class which is based on the seed class in SQL MP.

2) Discover this custom class; The edition string (PropertyStrValue) exists in WMI of the SQL Server computer under Root\Microsoft\SQLServer\ComputerManagement10 Namespace on SqlServiceAdvancedProperty. to discover this property I used Microsoft.Windows.Discovery.WMISinglePropertyProvider2 Data Source (documented here)

<Discovery ID="SQLExpress.RemoveMP.SQLSeed.Class.Discovery" Comment="Discovers SQL Edition Seeds" Target="MS2D!Microsoft.SQLServer.2008.Seed" Enabled="true" ConfirmDelivery="false" Remotable="true" Priority="Normal"> <Category>Discovery</Category> < DiscoveryTypes> < DiscoveryClass TypeID="SQLExpress.RemoveMP.SQLSeed.Class"> <Property TypeID="SQLExpress.RemoveMP.SQLSeed.Class" PropertyID="SQLEdition" /> </DiscoveryClass> </DiscoveryTypes> < DataSource ID="DiscoveryDS" TypeID="Windows!Microsoft.Windows.Discovery.WMISinglePropertyProvider2"> < NameSpace>Root\Microsoft\SQLServer\ComputerManagement10</NameSpace> <Query>SELECT * from SqlServiceAdvancedProperty WHERE propertyname='SKUNAME' and PropertyStrValue like '%Express%' </Query> <Frequency>3600</Frequency> < ClassID>$MPElement[Name="SQLExpress.RemoveMP.SQLSeed.Class"]$</ClassID> < PropertyName>PropertyStrValue</PropertyName> < InstanceSettings> <Settings> <Setting> <Name>$MPElement[Name="SQLExpress.RemoveMP.SQLSeed.Class"]/SQLEdition$</Name> <Value>$Data/Property[@Name="PropertyStrValue"]$</Value> </Setting> <Setting> <Name>$MPElement[Name="Windows!Microsoft.Windows.Computer"]/PrincipalName$</Name> <Value>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$</Value> </Setting> </Settings> </InstanceSettings> </DataSource> < /Discovery>

Once the above discovery works you will notice this custom seed class instances in Discovered Inventory.

3. Create a custom Group; Creating a custom group is easy we just first need to declare the class like any other classes but use the Microsoft.SystemCenter.InstanceGroup class as the base class and chance the class type to Singleton.

4. Discover the Members; We will use GroupPopulator like in all other group membership discoveries but will utilize the <Contains> expression documented here and sample mps here. This discovery will populate the Microsoft.Windows.Computer instances which contains our custom seed class (step 1 - SQLExpress.RemoveMP.SQLSeed.Class)

<Discovery ID="SQLExpress.RemoveMP.SQL.Express.Group.Discovery" Enabled="true" Target="SQLExpress.RemoveMP.SQL.Express.Group" ConfirmDelivery="true" Remotable="true" Priority="Normal"> <Category>Discovery</Category> < DiscoveryTypes> < DiscoveryRelationship TypeID="SCIGL!Microsoft.SystemCenter.InstanceGroupContainsEntities" /> </DiscoveryTypes> < DataSource ID="DiscoveryDS" TypeID="SC!Microsoft.SystemCenter.GroupPopulator"> < RuleId>$MPElement$</RuleId> < GroupInstanceId>$MPElement[Name="SQLExpress.RemoveMP.SQL.Express.Group"]$</GroupInstanceId> < MembershipRules> < MembershipRule> < MonitoringClass>$MPElement[Name="Windows!Microsoft.Windows.Computer"]$</MonitoringClass> < RelationshipClass>$MPElement[Name="SCIGL!Microsoft.SystemCenter.InstanceGroupContainsEntities"]$</RelationshipClass> <Expression> <Contains> < MonitoringClass>$MPElement[Name="SQLExpress.RemoveMP.SQLSeed.Class"]$</MonitoringClass> </Contains> </Expression> </MembershipRule> </MembershipRules> </DataSource> < /Discovery>

5. Disable the DB Engine Discovery; This is just an override to DB Engine Discovery for our class created in step 3 (SQLExpress.RemoveMP.SQL.Express.Group).

<Overrides> < DiscoveryPropertyOverride ID="SQLExpress.RemoveMP.DisableDBEDiscovery.Override" Context="SQLExpress.RemoveMP.SQL.Express.Group" Enforced="false" Discovery="MS2D!Microsoft.SQLServer.2008.DBEngineDiscoveryRule.Server" Property="Enabled"> < Value>false</Value> < /DiscoveryPropertyOverride> < /Overrides>

The whole MP is attached to this post so that you can also see the references, displaystrings how the mp comes together.

6. Remove disabled instances; just run the Remove-SCOMDisabledClassInstance command on OpsMgr Shell

How to use the MP

Import the mp (like in all other mps)
Wait till you get the mp active in your sql agents and you notice the seed class is populated (check the screenshot in step 2 of MP explained section above) and also you will see the members populated for the group in powershell. Once you verify you can proceed with the next step.

Open SCOM Powershell console and run the following command.

- Remove-SCOMDisabledClassInstance

REFERENCES;

1) Stop monitoring SQL Express and Windows Internal Database
https://blogs.technet.com/b/kevinholman/archive/2010/02/13/stop-monitoring-sql-express-and-windows-internal-database.aspx

2) Microsoft.Windows.Discovery.WMISinglePropertyProvider2
https://msdn.microsoft.com/en-us/library/ee692988.aspx

3) ExpressionType (GroupPopulationSchema)
https://msdn.microsoft.com/en-us/library/ff472337.aspx

4) Creating a Group of Windows Computer and Health Service Watcher Objects
https://blogs.technet.com/b/jimmyharper/archive/2012/03/20/3487667.aspx

Comments

Anonymous
January 01, 2003
Nice article and very helpful ..
Anonymous
March 27, 2013
The exact solution I've been looking for, thanks for sharing...
Anonymous
July 02, 2013
Great Solution!
Anonymous
September 18, 2013
Excellent blog post, Great work ;)
Anonymous
November 19, 2013
Hi, Excellent, exactly what we' were looking for. Would it be hard to extend it to SQL 2012 ? We have many Express in that release now. Not sure exactly what to change, we have no dev in house. Thanks for your help
Anonymous
February 27, 2014
I've been trying to undiscover certain DBs from a particular instance of SQL, not the whole instance. The DBs have been moved to another instance and SCOM report the DBs as offline. Can you provide any help with targeting the DBs on the instance?

Thanks
Anonymous
April 21, 2014
Could you help to modify this MP to undiscovered SQL 2005 , SQL 2008 R2 , 2012 etc DB's as well.
Anonymous
September 12, 2014
This post is the updated version of http://blogs.technet.com/b/emreguclu/archive/2013/01/21/undiscovering

Udostępnij za pośrednictwem

Undiscovering / Stop Monitoring SQL 2008 Express Editions

Comments

Dodatkowe zasoby