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;
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.
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.
1) Create a custom seed class; First we need to declare our seed class which is based on the seed class in SQL MP.
<ClassType ID="SQLExpress.RemoveMP.SQLSeed.Class" Base="MS2D!Microsoft.SQLServer.2008.Seed" Accessibility="Public" Abstract="false" Hosted="true" Singleton="false" Extension="false"> <Property ID="SQLEdition" Type="string" Key="false" CaseSensitive="false" MaxLength="256" MinLength="0" /> < /ClassType>
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) Stop monitoring SQL Express and Windows Internal Database http://blogs.technet.com/b/kevinholman/archive/2010/02/13/stop-monitoring-sql-express-and-windows-internal-database.aspx
2) Microsoft.Windows.Discovery.WMISinglePropertyProvider2 http://msdn.microsoft.com/en-us/library/ee692988.aspx
3) ExpressionType (GroupPopulationSchema) http://msdn.microsoft.com/en-us/library/ff472337.aspx
4) Creating a Group of Windows Computer and Health Service Watcher Objects http://blogs.technet.com/b/jimmyharper/archive/2012/03/20/3487667.aspx
Nice article and very helpful ..
The exact solution I've been looking for, thanks for sharing...
Excellent blog post,
Great work ;)
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
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
Could you help to modify this MP to undiscovered SQL 2005 , SQL 2008 R2 , 2012 etc DB's as well.