locked
Monitoring SQL Server with SCOM 2012 R2 RRS feed

  • Question

  • Hi All,

    We are using SQL server Management Packs version 7.0.15.0 to monitor SQL servers in SCOM 2012 R2.

    We have couple of SQL Servers which host more than 50 Databases (200+ databases), as per SQL Server MP guide we should not monitor more than 50 SQL Databases per SCOM agent. Due to this, we are noticing many Monitored Objects like Logical Disks, Operating System, DB Engine etc.. running on these SQL Servers go under not monitored state (i guess due to SCOM Agent overload). We try to perform clearing Health Service State cache, but most of the time that too dosen't help. Since there are so many SQL databases being discovered and monitored, we cannot override and disable these Databases from the SQL DB Engine discovery using the Override Parameter EXCLUDE, as we need to manually add these Databases one at a time and override. There are chances in future new Databases being added and they get discovered hence it is not a practicle way.

    Do we have any option to decrease the load on the SCOM agents running on these SQL Servers or any other way to exclude large number od Databases from Discovery without using the Override Parameter EXCLUDE?

    Thanks,

    Sreejeet

    Tuesday, January 28, 2020 11:51 AM

All replies

  • HI Sreejeet,

    the best way would be to create groups with the databases that you don't won't to monitor and disable the SQL discoveries for those. Here a nice example on how to this with one difference - the group from the example consists of serevrs and your group will contain teh databases you want to explude:

    Disable SQL discovery for a group

    Here another reference:

    Disable Discovery of certain Databases

    P.S. Please note that the databases will be exckuded afetr the dioscvery runs, so at least after 4 hours. You can also force the dioscvery to run quicker, using the simple script I published some years ago:

    Forcing a SCOM discovery with PowerShell (the easy way)

    And last, but not least here are some tuning recommendations. They are about the management group in general, not for the paricular agents, but some of them can help you increase your SCOM performance:

    Fly Your SCOM Management Group to the sky – SCOM Performance Optimizations

    Hope this helps you out. 

    Regards,


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov


    Tuesday, January 28, 2020 12:12 PM
  • Hi Stoyan,

    Thank you for sharing the links!

    I checked the links,  the first link (Disable SQL discovery for a group) is to disable the SQL Server installation seed, we have some overrides on SQL Seed discovery as we have some unwanted SQL servers and it works perfect. So this link will not help, as we need to only remove Databases from discovery.

    The second link Disable Discovery of certain Databases describes about how we can disable few Databases using the Override Parameter EXCLUDE. And also I noticed a soluton from Elton in this article, but the Discovery Rule he has mentioned has been upgraded with new Rule with new Version of SQL Server MPs and now we no longer have the option to use a Group with Unwanted Databases as its member and directly override and disable the discovery using that Group. As the new discovery has introduced a New Override Parameter EXCLUDE (I have attached the screenshot of that in the initial query above). We cannot use EXCLUDE as we have more that 200 Databases to be excluded which is not practicle. So the second link aslo cannot help. :(

    Thanks,

    Sreejeet

    Tuesday, January 28, 2020 12:53 PM
  • Hi Sreejeet,

    You are correct about the Seed Discovery, but it was just an example. The discovery you would like to overrite for your group of databases is the one Elton referenced in the screenshot  - "Discover Databases for a Database Engine". Applogies if I wasn't clear enough. 

    Please try to group your unwanted databases, located the discovery "Discover Databases for a Database Engine" and override it (Enabled = False) for your custom group. Afterwards force the discovery to run and at the end execute:

    Remove-SCOMDisabledClassInstance

    Regards,


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov


    Tuesday, January 28, 2020 1:01 PM
  • Thank you Stoyan, I will surely check on that and update you.

    Regards,

    Sreejeet

    Tuesday, January 28, 2020 1:07 PM
  • Hi Sreejeet,

    may I also ask about the status of this one? As always, your feedback is appreciated!

    Regards,


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    Wednesday, February 19, 2020 2:09 PM
  • I tried it but it did not remove the unwanted Databases from Discovery. So I guess the only option then remains is using the Exclude parameter. But when we have many hundred Databases to be excluded from discovery this becomes a not so practicle way. I wish Microsoft should have retained the old way of overriding Databases from SQL discovery rule using Groups rather than bringing a new Override parameter  EXCLUDE.
    Wednesday, February 19, 2020 2:32 PM
  • Hi All,

    Has anyone found a way to disable monitoring of multiple unwanted Databases (100 + databases) from the SQL Server Management Pack discovery?

    Using the Exclude List Override parameter can be used if we have few databases to be disabled from monitoring.

    Thanks,

    Sreejeet

    Monday, May 11, 2020 8:55 AM
  • Hi,

    As far as I know the exclusion is the only official way really, unless you want to author an own custom solution.

    You can apply overrides to the following discoveries to specify an "Exclude List" (in comma delimited format) of the names of SQL Server Database Engine instances that the discovery should overlook.


    Best regards,
    Leon

    Blog: https://thesystemcenterblog.com LinkedIn:

    Monday, May 11, 2020 9:42 AM