Undiscovering / Stop Monitoring SQL 2005/2008/2012 Express Editions

Artykuł
09/12/2014

This post is the updated version of https://blogs.technet.com/b/emreguclu/archive/2013/01/21/undiscovering-stop-monitoring-sql-2008-express-editions.aspx which adds SQL 2005 and SQL 2012 support to existing mp as per requests of my peers and customers. If you have the older version please just download and import this one. There is also a new explanation about the reason for this method.

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.

Reason for this method;

We already talked about the reason why not to use discovery exclusion override. The following method also wont work ;

1) create a group that has computers added those contain existing dbengines whose edition property is already Express

2) disable the DB Engine Discovery for this group

2) remove these disabled objects using powershell right?? Yes, but they will be deleted only for a while :)

With the above steps Since the SQL DB Engines which has Edition properties will be removed from OMDB (step 3), our group members (step 1) will also be removed. Once the group is being emptied, the discovery will rerun and rediscover the sql db engines that we removed.

The whole reason of this management pack is to eliminate the problems with these methods.

To overcome this challenge we need a new seed class that is above the DB Engine class in the class hieararchy (like the existing seed class but which doesn't have edition property).

So the idea is to create a seed class and discover edition property for each sql version, create a group of computers which contains this seed class, then disable the discovery for this computer group followed by removal of the DB Engine instances.

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

1) Import the mp (like in all other mps)

2) 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.

3) 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

SQLExpress.RemoveMP.xml

Comments

Anonymous
January 01, 2003
thanks for sharing.
Anonymous
January 01, 2003
The comment has been removed
Anonymous
January 12, 2015
first things first: many thanks for the management pack. makes life easier!
same problem here with the WMI query...
as it looks, this warning occurs on Win2K12 agents only..

any ideas?
Anonymous
January 12, 2015
update: got it on Win2K8 agents, too...
Anonymous
April 24, 2015
How long should it take for the discovery to be filled? Because mine stays empty.
Anonymous
April 24, 2015
It's starting to fill now, but also here LOADS of Failed WMI query...
Anonymous
May 07, 2015
Also here a lot of failed WMI query's. Anybody got a solution yet?
Anonymous
October 07, 2015
The comment has been removed
Anonymous
April 07, 2016
The comment has been removed

Udostępnij za pośrednictwem

Undiscovering / Stop Monitoring SQL 2005/2008/2012 Express Editions

Comments

Dodatkowe zasoby