次の方法で共有

Undiscovering / Stop Monitoring SQL 2008 Express Editions

  • [アーティクル]

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;

  1. Create a custom seed class whose base class is Microsoft.SQLServer.2008.Seed
  2. Discover this custom class and add property of Edition using a wmi query through Microsoft.Windows.Discovery.WMISinglePropertyProvider2
  3. Create a custom Group of Computers which contains the Custom Seed Class
  4. Discover the Members of the Group
  5. Disable the DB Engine Discovery in SQL MP through an Override for our Custom Group
  6. 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 

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

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

 

image

 

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.

<ClassType ID="SQLExpress.RemoveMP.SQL.Express.Group" Accessibility="Public" Abstract="false" Base="SCIGL!Microsoft.SystemCenter.InstanceGroup" Hosted="false" Singleton="true" />

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.
  • image
  • 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