Menu Close

How to transition to the SQL Version Agnostic MP

image

Recently, Microsoft released a new SQL MP, Version 7.0.15.0.

https://www.microsoft.com/en-us/download/details.aspx?id=56203

This new MP supports discovery and monitoring of SQL 2012, 2014, 2016, 2017, 2019, and later versions of SQL Server.  It is completely different than all previously released SQL Management Packs, the older ones I will refer to as “Legacy” MP’s.  The new MP is much improved in that all DB Engines once again share a common base class, and you no longer have duplicated rules and monitors for each SQL version, with dozens of MP’s to keep up with and maintain.

This MP is the only supported MP for the above versions moving forward for monitoring the SQL DB engines.  If you were previously using the older MP’s for 2012-2016, there is a process you will need to plan for, in transitioning.  This article is a step by step plan on how to make the transition from the Legacy MP’s to the new Agnostic MP’s with the least duplication of alerting, and load on SCOM.

The first step, will be to import the 7.0.15.0 Management Packs.

image

If you had a previous version of this MP imported (Previously named SQL 2017+) then you might find that these MP’s will not import:

image

The MP Guide discusses this.  These are not updateable, and must be deleted first before importing the new version of this MP.

Once imported, you may see two “Microsoft SQL Server” folders in the console, due to the new and “old” MP’s sharing the same display name.  This will be corrected later in the process.

image

The DEFAULT settings for discovery for this MP is to discover ALL SQL versions SQL 2012 and later. If you do not want DULPICATE discovery of ALL your SQL computers, engines, DB’s, etc….   There is a rule in the management pack that attempts to inspect to see of the older 2012-2016 Legacy MP’s exist in the management group, and if they do, it will try to set an override on the primary DB Engine discovery to not discover 2012-2016, so no duplicates would be discovered.

There are two problems with this:

1.  The rule didn’t work in my testing, so it isn’t 100% reliable.

2.  The MP it tries to create is Microsoft.SQLServer.Overrides (Microsoft SQLServer overrides) and this might not fit in with your corporate override MP naming standards. 

For this reason, I recommend checking the discovery IMMEDIATELY after import, and setting the overrides you want in your own SQL Overrides MP.  The discovery Display Name is “MSSQL on Windows: Discover Local SQL Server Database Engines

image

First check to see if any overrides are set.  I recommend placing them in your own SQL Overrides MP that aligns to your override MP naming standards.

image

Now, at this stage, we should have the new MP fully implemented, and it should be discovering and monitoring on SQL 2017 and later.

The next step, is to onboard (discover and monitor) one SQL version at a time.  I will start with SQL 2016.  To make this work, I will delete SQL 2016 from the discovery override above, leaving SQL 2012 and 2014 in place:

image

Give this some time for the agents to get the new override, and you should start to see your SQL 2016 servers trickling into the SQL Server Roles view:

image

You need to verify that all your instances discovered as expected, and there are no monitoring configuration and security issues.  The new MP does a good job of alerting you if the security configuration is not sufficient.

Next, to avoid duplicate monitoring, you can delete your older SQL 2016 MP’s from the management group:

image

Note*** Deleting Management Packs is VERY expensive in the SCOM Database.  It is very common for MP’s to fail a delete attempt, or lock up the console for an extended period, if there is a very large amount of discovered instances and monitoring data from an imported MP.  If this is the case, you can always attempt to delete the MP’s, but if they will not delete, then try to delete them using PowerShell:

Get-SCOMManagementPack -DisplayName “Microsoft SQL Server 2016*” | Remove-SCOMManagementPack

Obviously you will need to fix/cleanup any unsealed MP’s which depend on (reference) the SQL 2016 MP’s first, such as removing overrides, custom views, etc.

If all of these fail, then the course of action would be to create a temporary override MP, and disable the SQL 2016 seed class discovery:

image

Once you disable this discovery, you can attempt to cleanup/delete SQL 2016 instances in SCOM using Remove-SCOMDisabledClassInstance.  Sometimes you must run this multiple times to remove all your Legacy SQL 2016 instances, as it may time out or fail with an error.  You can look in the legacy MP views to see if it is deleting them.  Once you have no more instances, removing the MP should be easy to complete.

If this goes well, you can move on to SQL 2014, then SQL 2012.  Repeat the removal of the override for that version, verify they get discovered, and then remove the legacy MP’s:

Get-SCOMManagementPack -DisplayName “Microsoft SQL Server 2014*” | Remove-SCOMManagementPack

Get-SCOMManagementPack -DisplayName “Microsoft SQL Server 2012*” | Remove-SCOMManagementPack

Lastly, if you are going to retain monitoring for SQL 2008 (which is not supported by this SQL Version Agnostic MP) then you will want to update to the most current version:

https://www.microsoft.com/en-us/download/details.aspx?id=10631

image

This will update the console views changing the Legacy MP’s to “old”:

image

Lastly – if you were using my SQL RunAs Helper MP, it has been updated for the SQL Version Agnostic MP, and is ready for deployment as well:

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

The security model has changed in the newest MP’s, so you might want to verify security is correct first.  See the above article for examples.

 

Some side notes:

  • SQL Mirroring is no longer supported, nor monitored in this SQL Version Agnostic MP.  For SQL 2012 and later, you should use AlwaysOn.
  • The Legacy SQL Replication MP’s are not supported with this MP for SQL 2012-2016.  The old Replication MP’s have dependencies on the Legacy SQL DB Engine MP’s and will need to be removed in order to remove all the old Legacy MP’s.
  • In general – NEVER import any of the below MP’s unless you determine their monitoring is critical to your business requirements.  Just because a MP exists does not mean you should import it.

SQL Replication

SQL Reporting Services

SQL Analysis Services

image

27 Comments

  1. EricGS

    Kevin, thank you for this guide. Excellent as usual. How well does the new MP cope with SQL 2008? We’re still in the process of migrating off this and it’s taking a while.

    • Techiedude

      Hi Ericgs – As SQL 2008 will EOL in less than four weeks (July 9th to be exact), the new agnostic version release does not support it. Please note the following in the download details: “This version-agnostic management pack was thoroughly built from the ground up in accordance with Best Practices for SQL Server; it is intended to monitor SQL Server 2012—2017 and all upcoming SQL Server versions.”

    • Kevin Holman

      Ericgs – I expect that customers will unfortunately run for a long time on SQL 2008, even understanding there will be no support for it soon, and this puts the environments at risk. This MP does not discover/monitor SQL 2008, so you can continue to use the old “legacy” MP’s for SQL 2008 to continue monitoring it, you will simply see it in the “old” views as I documented above already.

  2. Rick Bywalski

    We are using your Run AS MP will we need to rerun it on the existing servers that we have run it on with the old MP our should it just work. My guess is we will not need to run it and permissions already set will still work

    • Kevin Holman

      It will “work”, however, the new MP simplified the security configuration required to be the same across all versions. It is possible you might see the new monitor “Securables Configuration Status” showing an unhealthy state, and this will tell you if you are missing any recommended/required security rights. If this monitor shows unhealthy, you can review the list and determine if you are missing something.

      • Rick Bywalski

        Is the new run as back words compatible with the legacy mp? I was thinking if I get the new one installed and use it going forward I would have the proper rights in place when we can migrate to the new MP we would already have the proper permissions in place. We are only about half way through deploying to SQL. We do have one 3rd party software blocking us from deploying the upgraded MP for a few weeks.

  3. Chris Wood

    I noticed that the new SQL 2017+ MP changes the PrincipalName for DBEngines. It now shows the Netbios type instance name instead of the Parent FQDN of the host. Also the alerts no longer have PrincipalName populated. Is this intentional? Is there any way to get these attributes like they used to be?

  4. Rick Bywalski

    One word of caution if you are using any 3rd party add on for things like dash boarding check with your vendor if they support this pack or not before proceeding. I did and was glad I did as it would have broken some key functions in the product and they are busy updating their product to support the new build. This is a complete rewrite of the code and changes a lot of things like counters etc.

  5. Edwin

    Hey Kevin,
    Thanks for the guide helped me a bunch! However i used to have a daily scheduled report on a MSSQLSERVER object to report average wait time and cpu load to a specific group of people by e-mail.
    Now this (naturally) stopped working as i uninstalled the old versions of the SQL MP.
    However when i wanted to fix this i noticed i can’t find any mssqlserver objects or any rules that are in the new MP, i do see them under monitoring and i do see the performance rules there too and they show data. But when i want a detailed performance report (where i used to see them) i just can’t find them. Do you have any clue on what might be going on here? is this by design maybe, or is something wrong in my environment?

    • Edwin

      after removing the entire MP and reinstalling it, i now do see the new discovered objects in reporting aswell.
      now to see if the reporting works again tomorrow

  6. Edwin

    *next morning* all discovered objects that i saw yesterday are gone again in the reporting part… im so confused right now.

  7. richard scott

    does anyone have a copy of the new SQL Server RunAs Addendum Management Pack‎, the TechNet link seems to be broken ?

  8. Kiwifulla

    Probably an obvious answer, but we have created a lot of overrides against the SQL 2012, 2014, 2017 Management Packs over the years. These are against discoveries, monitors and rules. By following this article to remove those MP versions, I assume I have to recreate all of those overrides again against the equivalent discoveries, rules and monitors (if they are applicable) within the new agnostic Management Pack?
    Thanks in advance

  9. Rick Bywalski

    Is the new run as back words compatible with the legacy mp? I was thinking we could start using it now and have the proper rights there when we are able to deploy the new MP

    • Kevin Holman

      The new RunAs config SQL statement is backwards compatible with the 2012, 2014, and 2016 scripts in the old RunAs config – with one exception. The old script added:

      GRANT SELECT on sys.database_mirroring_witnesses to [SCOM_HealthService]

      Since the new SQL MP does not monitor database mirroring, this permission would be missing if you only used the new one. You could easily add this back into your custom copy.

  10. Gerald Versluis

    Hello Kevin,

    How about retaining historical data for capacity reports? What if we want to look at teh historical growth of a specific database over, say, the past six months? I assume that data will be lost…

    • Kevin Holman

      The data is not lost. We do not delete data from the DW when a managed entity is deleted. We simply sync a new managed entity to the DW and associate the new perfmon data with that. A single contiguous chart would be hard, but not impossible, depending on how it was queried.

  11. Michael Daffner

    Hi Kevin
    some SQL 2017 Servers will not be discovered in my environment (SCOM 2016 UR5) with the new agnostic SQL MP. The message I get in the eventlog
    “”Microsoft.SQLServer.Windows.Discovery.LocalDiscoverySeed”” running for instance “”xxx”” with id:””{1E89D4D7-46C2-D5D6-87E7-9D36EE6ECC75}”” cannot be initialized and will not be loaded. Management group “”ARZ”””,”System.String[]”

    Do you have any ideas for this problem?

  12. Paul

    Hi Kevin,

    To my surprise when reviewing my existing SQL Server 2016 MP(s) in prep for the new 2017+ MP I have found that they all have a dependency on the Default MP.

    During implementation of the SQL 2016 MP (on SCOM 2016) I was very careful to follow best practice and ensure that no overrides etc were saved to the Default MP so I am left wondering how this occurred.

    Your article implies that removing the ‘old’ SQL MP(s) would be straight forward (suggesting to me that there should not be any dependencies on the default MP under normal circumstances).

    Would you happen to know if this is a common situation (MS SQL MP’s becoming dependent on the default MP) and if so what is the most likely cause and the best way to fix this situation?

    Many Thanks,

    Paul

      • Paul

        Summary of References are:-
        >

        Microsoft.SQLServer.2016.Presentation

        Microsoft.SQLServer.Generic.Dashboards

        Microsoft.SQLServer.Visualization.Library

        Microsoft.SQLServer.2016.Discovery

        Microsoft.SQLServer.Generic.Presentation

        ….And I have found some sections in the default MP called “ComponentOverride” below which I don’t know what they are for exactly. I ran the overrides report against the default MP and there is nothing listed for SQL at all.

        >
        ComponentOverride ID=”Microsoft.SQLServer.2016.Presentation.UniversalDashboardOverride_cb9b0787_4668_4a2b_88ad_e0f894fe4c18″ TypeID=”SQLServer!Microsoft.SQLServer.2016.Presentation.UniversalDashboard”>

  13. Damian

    Hi Kevin,
    Fairly new to SCOM, but I’ve followed the steps and got this working on SCOM 2019, with just the new SQL agnostic pack installed, albeit with one issue.

    There is now only one SQL Server report listed
    Microsoft SQL Server on Windows (Views) -> Database Files Space Usage Forecast

    I was expecting to have a Microsoft SQL Server (Monitoring) folder with reports listed. Any obvious reasons what I’ve misconfigured?

  14. Raoul

    Hi Kevin, when I apply the PowerShell commands you provided, that will also delete SQL Reporting MP’s. Is this included in the new MP’s or not?

Leave a Reply

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