Recently, Microsoft released a new SQL MP, Version 22.214.171.124.
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 126.96.36.199 Management Packs.
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:
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.
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”
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.
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:
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:
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:
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:
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:
This will update the console views changing the Legacy MP’s to “old”:
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:
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.
- 2017+ https://www.microsoft.com/en-us/download/details.aspx?id=56204
- 2016 https://www.microsoft.com/en-us/download/details.aspx?id=53009 – This will not install without OLD SQL MP
- 2014 https://www.microsoft.com/en-us/download/details.aspx?id=47720 – This will not install without OLD SQL MP
- 2012 https://www.microsoft.com/en-us/download/details.aspx?id=47721 – This will not install without OLD SQL MP
- 2008 https://www.microsoft.com/en-us/download/details.aspx?id=47723 – This will not install without OLD SQL MP
SQL Reporting Services
- 2017+ https://www.microsoft.com/en-us/download/details.aspx?id=57381
- 2016 https://www.microsoft.com/en-us/download/details.aspx?id=53010
- 2014 https://www.microsoft.com/en-us/download/details.aspx?id=43390
- 2012 https://www.microsoft.com/en-us/download/details.aspx?id=43392
- 2008 https://www.microsoft.com/en-us/download/details.aspx?id=43391
SQL Analysis Services
- 2017+ https://www.microsoft.com/en-us/download/details.aspx?id=57382
- 2016 https://www.microsoft.com/en-us/download/details.aspx?id=53011
- 2014 https://www.microsoft.com/en-us/download/details.aspx?id=44586
- 2012 https://www.microsoft.com/en-us/download/details.aspx?id=41658
- 2008 https://www.microsoft.com/en-us/download/details.aspx?id=41659