Menu Close

How to exclude SQL Express Edition from SCOM Monitoring

You might remember I wrote about this many years ago, on how to exclude discovery and monitoring of Express editions of SQL using the “ExcludeList” property of the SQL discovery:

https://kevinholman.com/2010/02/13/stop-monitoring-sql-express-and-windows-internal-database/

 

That was cool…. but really did not go as far as we needed it to.  Excluding SQL Engine discoveries by Instance Name was great, but didn’t work if we had SQL Express editions out there using MSSQLSERVER as their instance name.  We could not include that, as that would cause all our “real” production default instances to disappear.

It is very common that we will discover SQL Express editions by default, however, these are often unwanted to be monitored in production environments:

image

 

Well, finally, this is solved in the SQL MP 7.0.20.0 (Version Agnostic).  This MP adds a new override-able property to the DB Engine discovery, for excluding by EDITION name!

image

 

Supported values (comma separated) are:  Enterprise, Standard, Web, Developer, Express

It does not appear to be case sensitive.

 

Another new update, on the ability to Exclude discovery by INSTANCE NAME – we now have Wildcard capability.  Previously we could filter our discovery by explicit name, but now we support wildcards!

image

 

So now we can exclude monitoring of SQL instances by VERSION, EDITION, and INSTANCE NAME

 

image

 

See my other SQL MP How-To posts:

https://kevinholman.com/2019/06/12/how-to-transition-to-the-sql-version-agnostic-mp/

https://kevinholman.com/2016/08/25/sql-mp-run-as-accounts-no-longer-required/

https://kevinholman.com/2020/01/31/how-to-exclude-sql-express-edition-from-scom-monitoring/

16 Comments

  1. Frederik

    When adding the exclusion for SQL Express, we get the following error on 1 of our scom management servers:

    Management Group: “uzscom16”
    Module: Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride
    Version: 7.0.20.0

    Error(s) was(were) occurred:
    Message:
    ———- Exception: ———-
    Exception Type: Microsoft.EnterpriseManagement.ContainerException
    Message: The container could not find a component with name ‘ExtensionManagement’ compatible with type ‘Microsoft.EnterpriseManagement.Configuration.IExtensionManagement, Microsoft.EnterpriseManagement.Core, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’.
    Source: Microsoft.EnterpriseManagement.Core
    Stack Trace:
    at Microsoft.EnterpriseManagement.Container.GetService[T](String name)
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerifyManagementPackElementName.GetExtensions()
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerifyManagementPackElementName.Verify(ManagementPack mp)
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.CheckPrerequisite(Boolean verifyFragment)
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.Verify(Boolean isUpdate, Boolean throwError)
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.VerifyMP(ManagementPack mp, Boolean isUpdate, Boolean throwError)
    at Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride.ModifyOrCreateOverride(OverrideSettings settings)
    at Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride.d__15.MoveNext()
    — End of stack trace from previous location where exception was thrown —
    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    at Microsoft.SQLServer.Module.Helper.Base.ModuleBasePropertyHelper`1.d__13.MoveNext()

    State:
    The configuration properties are:
    ManagementGroupName = uzscom16
    Publisher = SQLDiscoveryWindows
    Enforced = null
    ManagementServerName = null
    OverrideCategory = Property
    OverrideDisplayName = Disable filter autosetup rule
    OverrideManagementPackDisplayName = null
    OverrideManagementPackName = null
    OverrideName = Microsoft.SQLServer.Windows.RuleOverride.LocalDBEngineDiscoveryFilterSetup
    OverrideValue = false
    TargetElementId = {476C2368-BA4E-061D-CE6E-0950DC93C1D9}
    TargetModuleName = null
    TargetParameterName = Enabled
    TimeoutSeconds = 300

    Error(s):

    ———- Exception: ———-
    Exception Type: Microsoft.EnterpriseManagement.ContainerException
    Message: The container could not find a component with name ‘ExtensionManagement’ compatible with type ‘Microsoft.EnterpriseManagement.Configuration.IExtensionManagement, Microsoft.EnterpriseManagement.Core, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’.
    Source: Microsoft.EnterpriseManagement.Core
    Stack Trace:
    at Microsoft.EnterpriseManagement.Container.GetService[T](String name)
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerifyManagementPackElementName.GetExtensions()
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerifyManagementPackElementName.Verify(ManagementPack mp)
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.CheckPrerequisite(Boolean verifyFragment)
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.Verify(Boolean isUpdate, Boolean throwError)
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.VerifyMP(ManagementPack mp, Boolean isUpdate, Boolean throwError)
    at Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride.ModifyOrCreateOverride(OverrideSettings settings)
    at Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride.d__15.MoveNext()
    — End of stack trace from previous location where exception was thrown —
    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    at Microsoft.SQLServer.Module.Helper.Base.ModuleBasePropertyHelper`1.d__13.MoveNext()

    Do you have any idea why we are seeing this error on only one management server?
    We currently have 2 management servers in our environment, all patched up to scom2019 UR1
    This issue only occurs when we add the exclusion “Express” in the sql discovery.

  2. Kevin Holman

    This generally happens when you place the override in the wrong place. Did you put something else in the “versions to be excluded” ?

  3. Santi

    Even though i have EXPRESS in exclude sql versions override i can see many sql servers with express edition in sql server roles view..
    Any idea whys its happening?

    • Kevin Holman

      Thats because that’s NOT where the override goes, and will break discovery. Look at the article in detail for the correct override location.

  4. Pingback:System Center Şubat 2020 Bülten – Sertaç Topal

  5. Cyprian

    Hi Kevin,
    I have the same error like FREDERIK, but i did not add any exclusion or overrides.

    Management Group: “XXX”
    Module: Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride
    Version: 7.0.20.0

    Error(s) was(were) occurred:
    Message:
    ———- Exception: ———-
    Exception Type: Microsoft.EnterpriseManagement.ContainerException
    Message: The container could not find a component with name ‘ExtensionManagement’ compatible with type ‘Microsoft.EnterpriseManagement.Configuration.IExtensionManagement, Microsoft.EnterpriseManagement.Core, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’.
    Source: Microsoft.EnterpriseManagement.Core
    Stack Trace:
    at Microsoft.EnterpriseManagement.Container.GetService[T](String name)
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerifyManagementPackElementName.GetExtensions()
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerifyManagementPackElementName.Verify(ManagementPack mp)
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.CheckPrerequisite(Boolean verifyFragment)
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.Verify(Boolean isUpdate, Boolean throwError)
    at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.VerifyMP(ManagementPack mp, Boolean isUpdate, Boolean throwError)
    at Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride.ModifyOrCreateOverride(OverrideSettings settings)
    at Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride.d__15.MoveNext()
    — End of stack trace from previous location where exception was thrown —
    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    at Microsoft.SQLServer.Module.Helper.Base.ModuleBasePropertyHelper`1.d__13.MoveNext()

    State:
    The configuration properties are:
    ManagementGroupName = XXX
    Publisher = SQLDiscoveryWindows
    Enforced = null
    ManagementServerName = null
    OverrideCategory = Property
    OverrideDisplayName = Disable filter autosetup rule
    OverrideManagementPackDisplayName = null
    OverrideManagementPackName = null
    OverrideName = Microsoft.SQLServer.Windows.RuleOverride.LocalDBEngineDiscoveryFilterSetup
    OverrideValue = false
    TargetElementId = {476C2368-BA4E-061D-CE6E-0950DC93C1D9}
    TargetModuleName = null
    TargetParameterName = Enabled
    TimeoutSeconds = 300

    In my opinion this error could be related to override in Microsoft.SQLServer.Overrides (from SQL 2017+ 7.0.20.0)

    false

    But I nottice that target for this rule is changed:
    – Microsoft.SQLServer.Windows.Discovery – 7.0.15.0

    – Microsoft.SQLServer.Windows.Discovery – 7.0.20.0

    And there is only class def without relations or discoveries…

    PS O:\> Get-SCOMGroup -DisplayName “MSSQL on Windows: Group for disabling discovery of previous SQL Servers” |select name
    PS O:\> Get-SCOMClass -DisplayName “MSSQL on Windows: Group for disabling discovery of previous SQL Servers” |select name

    Name
    —-
    Microsoft.SQLServer.Windows.SqlVersionAgnosticGroup

    PS O:\> Get-SCOMClass -DisplayName “MSSQL on Windows: Group for disabling discovery of previous SQL Servers” | Get-SCOMClassInstance

    HealthState InMaintenanceMode DisplayName
    ———– —————– ———–
    Uninitialized False MSSQL on Windows: Group for disabling discovery of previous SQL Servers

    What do you think about it ?

  6. Cyprian

    I noticed that there is a problem with parsing XML fragments by the page …
    Below are the missing fragments of my replay:

    In my opinion this error could be related to override in Microsoft.SQLServer.Overrides (from SQL 2017+ 7.0.20.0):

    RulePropertyOverride ID=”Microsoft.SQLServer.Windows.RuleOverride.LocalDBEngineDiscoveryFilterSetup”
    Context=”MP_Ref_Microsoft_SystemCenter_Library!Microsoft.SystemCenter.ManagementServer”
    Rule=”MP_Ref_Microsoft_SQLServer_Windows_Discovery!Microsoft.SQLServer.Windows.LocalDBEngineDiscoveryFilterSetup”
    Property=”Enabled”
    Value: false

    But I nottice that target for this rule is changed: (from ManagementServer to SqlVersionAgnosticGroup)
    – Microsoft.SQLServer.Windows.Discovery – 7.0.15.0
    Rule ID=”Microsoft.SQLServer.Windows.LocalDBEngineDiscoveryFilterSetup”
    Enabled=”true”
    Target=”SC!Microsoft.SystemCenter.ManagementServer”
    ConfirmDelivery=”false” Remotable=”true” Priority=”Normal” DiscardLevel=”100″

    – Microsoft.SQLServer.Windows.Discovery – 7.0.20.0
    Rule ID=”Microsoft.SQLServer.Windows.LocalDBEngineDiscoveryFilterSetup”
    Enabled=”true”
    Target=”Microsoft.SQLServer.Windows.SqlVersionAgnosticGroup”
    ConfirmDelivery=”false” Remotable=”true” Priority=”Normal” DiscardLevel=”100″

    And there is only class def without any relations or discoveries for that class
    ClassType ID=”Microsoft.SQLServer.Windows.SqlVersionAgnosticGroup”
    Accessibility=”Internal” Abstract=”false”
    Base=”System!System.Entity”
    Hosted=”false” Singleton=”true” Extension=”false”

    I can’t find such group in Console.

    PS O:\> Get-SCOMGroup -DisplayName “MSSQL on Windows: Group for disabling discovery of previous SQL Servers” |select name

    PS O:\> Get-SCOMClass -DisplayName “MSSQL on Windows: Group for disabling discovery of previous SQL Servers” |select name
    Name: Microsoft.SQLServer.Windows.SqlVersionAgnosticGroup

    PS O:\> Get-SCOMClass -DisplayName “MSSQL on Windows: Group for disabling discovery of previous SQL Servers” | Get-SCOMClassInstance
    HealthState : Uninitialized
    DisplayName: MSSQL on Windows: Group for disabling discovery of previous SQL Servers

    This rule cannot work in my opinion
    What do you think about it ?

  7. Cyprian

    And one more remark about this.
    I wrote “This rule cannot work in my opinion” but of course is not true…
    Unhosted singleton class will be managed by All MS RP, so one of SCOM MS should take care of this class…and targeted rule…

    In the SQLServerMPWorkflowList.pdf document there is only below information:

    MSSQL on Windows: Group for disabling discovery of previous SQL Servers
    ‘This group is designed for override of version-agnostic pack to prevent SQL Servers discovered
    by previous versions of management packs from being discovered again and thus to avoid
    double monitoring.’

    MSSQL on Windows: WMI Health of DB Engine discovery filter
    ‘This rule automatically disables monitoring of SQL Server 2012, 2014, or 2016 by the new
    version-agnostic management pack if, at the moment of importing, there are still the old
    management packs for SQL Server 2012, 2014, or 2016 presented on SCOM.’

    But there is no “MSSQL on Windows: WMI Health of DB Engine discovery filter” rule (or such DisplayString) in Microsoft.SQLServer.Windows.Discovery 7.0.20.0 MP

    In this MP is below rule instead of it
    MSSQL on Windows: Automatic setup of DB Engine discovery filter (Microsoft.SQLServer.Windows.LocalDBEngineDiscoveryFilterSetup)
    Target=”Microsoft.SQLServer.Windows.SqlVersionAgnosticGroup” (MSSQL on Windows: Group for disabling discovery of previous SQL Servers)

  8. Mario

    Hi Kevin,
    I have the same problem as Cyprian after Upgrading from 7.15 to 7.20.
    I did not make any Overrides regarding SQL Edition. I do not understand what you mean by: “Thats because that’s NOT where the override goes, and will break discovery. Look at the article in detail for the correct override location.”
    Which Override in which location? I do not find this in this Article.

    Regards

    • Cyprian

      According your question: please look into SQLServerMPGuide.pdf – page 9
      How Disabling of Monitoring Works
      The version-agnostic management pack runs the “MSSQL on Windows: Automatic setup of DB Engine discovery filter” action rule after importing. The rule does the following actions:
      Search for instances of SQL Server 2012, 2014 and 2016 discovered by the old management packs. If there is at least one instance of any of those SQL Server versions, this version will be disabled for discovery by the version-agnostic management pack.
      Overrides the “MSSQL on Windows: Discover SQL Server Database Engines (Local)” discovery by filling out the “SQL Server versions to be excluded” parameter with versions to disable and saves this override in a new management pack called “Microsoft SQLServer overrides.”
      See ‘Disabling Monitoring of Specified SQL Server Versions’ to get more information.
      Disables itself and saves this override in the same management pack. Do not remove this management pack in order to keep this rule disabled.
      If the old management packs are imported after importing the version-agnostic management pack,the monitoring provided by the latter will not be disabled.

      But I didn’t make any Overrides regarding SQL 2017 + Discoveries… and still have this problem after Upgrading from 7.15 to 7.20.

      And when i try to find the root cause of this error:
      Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride – this module type is used in WriteActionModuleType ID=”Microsoft.SQLServer.Windows.WriteAction.WriteOverride” and this WA is used only in Microsoft.SQLServer.Windows.LocalDBEngineDiscoveryFilterSetup rule.
      RunAs for this WA is Microsoft.SQLServer.Core.RunAs.SDK (Microsoft SQL Server SCOM SDK Run As Profile)
      “This management pack needs the Author set of privileges on the SCOM SDK to be able to create a management pack and store overrides in it. If the default action account on SCOM does not have these permissions, make sure to have an account granted with them and map this account to the Microsoft SQL Server SCOM SDK Run As Profile”
      In my situation “Default Action Account” (like all SCOM accounts are now migrated to gMSA) is a member of grup gaving local admins rights on SCOM servers and monitored servers and member of group with Operations Manager Administrators role. So in my opinion this error is also not related to “permissions”

      I am still thinking that this could be related to changed tergetting Microsoft.SQLServer.Windows.LocalDBEngineDiscoveryFilterSetup rule from ManagementServer to SqlVersionAgnosticGroup which is not really group…

      PS O:\> Get-SCOMGroup -DisplayName (Get-SCOMClass -Name Microsoft.SQLServer.Windows.SqlVersionAgnosticGroup | select DisplayName)
      (no results)
      PS O:\> Get-SCOMClass -Name Microsoft.SQLServer.Windows.SqlVersionAgnosticGroup | select DisplayName

      DisplayName
      ———–
      MSSQL on Windows: Group for disabling discovery of previous SQL Servers

  9. Fursel

    Is there a way to disable discovery of SQL DB Engine on some windows computers with this management pack? Before it was working with disablig discovery for Windows Server objects but now it seems it doesn’t work.

        • Fursel

          Looks like that SCOM Server where this agent is assigned have the same error as for the guys above. The only thing I changed in “MSSQL on Windows: Discover SQL Server Database Engines (Local)” was to disable it for a group containing this one windows computer object I wouldnt like to have SQL discovered.

          Management Group: “CF”
          Module: Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride
          Version: 7.0.20.0

          Error(s) was(were) occurred:
          Message:
          ———- Exception: ———-
          Exception Type: Microsoft.EnterpriseManagement.ContainerException
          Message: The container could not find a component with name ‘ExtensionManagement’ compatible with type ‘Microsoft.EnterpriseManagement.Configuration.IExtensionManagement, Microsoft.EnterpriseManagement.Core, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’.
          Source: Microsoft.EnterpriseManagement.Core
          Stack Trace:
          at Microsoft.EnterpriseManagement.Container.GetService[T](String name)
          at Microsoft.EnterpriseManagement.Configuration.Verification.VerifyManagementPackElementName.GetExtensions()
          at Microsoft.EnterpriseManagement.Configuration.Verification.VerifyManagementPackElementName.Verify(ManagementPack mp)
          at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.CheckPrerequisite(Boolean verifyFragment)
          at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.Verify(Boolean isUpdate, Boolean throwError)
          at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.VerifyMP(ManagementPack mp, Boolean isUpdate, Boolean throwError)
          at Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride.ModifyOrCreateOverride(OverrideSettings settings)
          at Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride.d__15.MoveNext()
          — End of stack trace from previous location where exception was thrown —
          at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
          at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
          at Microsoft.SQLServer.Module.Helper.Base.ModuleBasePropertyHelper`1.d__13.MoveNext()

          State:
          The configuration properties are:
          ManagementGroupName = CF
          Publisher = SQLDiscoveryWindows
          Enforced = null
          ManagementServerName = null
          OverrideCategory = Property
          OverrideDisplayName = Disable filter autosetup rule
          OverrideManagementPackDisplayName = null
          OverrideManagementPackName = null
          OverrideName = Microsoft.SQLServer.Windows.RuleOverride.LocalDBEngineDiscoveryFilterSetup
          OverrideValue = false
          TargetElementId = {476C2368-BA4E-061D-CE6E-0950DC93C1D9}
          TargetModuleName = null
          TargetParameterName = Enabled
          TimeoutSeconds = 300

          Error(s):

          ———- Exception: ———-
          Exception Type: Microsoft.EnterpriseManagement.ContainerException
          Message: The container could not find a component with name ‘ExtensionManagement’ compatible with type ‘Microsoft.EnterpriseManagement.Configuration.IExtensionManagement, Microsoft.EnterpriseManagement.Core, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’.
          Source: Microsoft.EnterpriseManagement.Core
          Stack Trace:
          at Microsoft.EnterpriseManagement.Container.GetService[T](String name)
          at Microsoft.EnterpriseManagement.Configuration.Verification.VerifyManagementPackElementName.GetExtensions()
          at Microsoft.EnterpriseManagement.Configuration.Verification.VerifyManagementPackElementName.Verify(ManagementPack mp)
          at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.CheckPrerequisite(Boolean verifyFragment)
          at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.Verify(Boolean isUpdate, Boolean throwError)
          at Microsoft.EnterpriseManagement.Configuration.Verification.VerificationEngine.VerifyMP(ManagementPack mp, Boolean isUpdate, Boolean throwError)
          at Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride.ModifyOrCreateOverride(OverrideSettings settings)
          at Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride.d__15.MoveNext()
          — End of stack trace from previous location where exception was thrown —
          at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
          at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
          at Microsoft.SQLServer.Module.Helper.Base.ModuleBasePropertyHelper`1.d__13.MoveNext()

          • Cyprian

            Ovveride (enable ->false) for ‘MSSQL on Windows: Automatic setup of DB Engine discovery filter’ rule in Microsoft.SQLServer.Overrides MP (imported with SQL MP update) has target: CLASS: “Management Server”
            and this rule target is “MSSQL on Windows: Group for disabling discovery of previous SQL Servers”, so this is the root couse of this error in my opinion.
            I delete this override and create new one (enable ->false) with target “MSSQL on Windows: Group for disabling discovery of previous SQL Servers” in this Microsoft.SQLServer.Overrides MP.
            And no more 4221 events from SQL Server Discovery MP Windows in Operations Manager log on my MS (related to Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride module)
            🙂

Leave a Reply

Your email address will not be published. Required fields are marked *