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

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

Leave a Reply

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