This is based on the SQL MP Version 7.0.20.0 and later.
https://www.microsoft.com/en-us/download/details.aspx?id=56203
This 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 Management Packs:
You might see an error on the Microsoft SQL Server Web Dashboards. This MP is only applicable to SCOM 2019 and later. If you are running any older version of SCOM you will need to remove this from the import list.
If you had a previous version of this SQL MP imported (Previously named SQL 2017+) then you might find that the Integration MP’s will not import:
The MP Guide discusses this. These are not updateable, and must be deleted from your existing management group 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 SQL Server Database Engines (Local)”
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
Next, 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
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:
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, Microsoft recommends that you use AlwaysOn. AlwaysOn is fully monitored by the SQL management packs discussed above. However, if you still depend on Database Mirroring in SQL 2012 and later, I have published a SQL Version Agnostic management pack for mirroring here: https://github.com/thekevinholman/Microsoft.SQLServer.Windows.Mirroring
- 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 2012-2019+ https://www.microsoft.com/en-us/download/details.aspx?id=56204
- Legacy 2008 https://www.microsoft.com/en-us/download/details.aspx?id=47723
SQL Reporting Services
- SQL 2012-2019+ https://www.microsoft.com/en-us/download/details.aspx?id=57381
- Legacy 2008 https://www.microsoft.com/en-us/download/details.aspx?id=43391
SQL Analysis Services
- SQL 2012-2019+ https://www.microsoft.com/en-us/download/details.aspx?id=57382
- Legacy 2008 https://www.microsoft.com/en-us/download/details.aspx?id=41659
See my other SQL MP How-To posts:
https://kevinholman.com/2016/08/25/sql-mp-run-as-accounts-no-longer-required/
https://kevinholman.com/2020/03/15/monitoring-sql-agent-jobs-in-scom/
https://kevinholman.com/2020/01/31/how-to-exclude-sql-express-edition-from-scom-monitoring/
Known Issues:
1. If you upgrade from the SQL MP version 7.0.15.0 or older, to a newer version of the MP, you will see Alerts, and event errors on one of your management servers such as:
Alert Name: MSSQL on Windows: Discovery error
Log Name: Operations Manager
Source: SQL Server Discovery MP Windows
Event ID: 4221
Level: Error
Description:
Management Group: “SCOM”
Module: Microsoft.SQLServer.Core.Module.Helper.Tasks.WriteOverride
Version: 7.0.20.0Error(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.<GetModuleDataAsync>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.<GetOutputDataAsync>d__13.MoveNext()
State:
The configuration properties are:
ManagementGroupName = SCOM
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
This is caused by a change in the MP from 7.0.15.0 to 7.0.20.0. There is a rule in “Microsoft SQL Server on Windows (Discovery)” MP named: “MSSQL on Windows: Automatic setup of DB Engine discovery filter” (Microsoft.SQLServer.Windows.LocalDBEngineDiscoveryFilterSetup). This rule runs once per hour and has two purposes:
(1) It creates an override in the “Microsoft SQLServer overrides” (Microsoft.SQLServer.Overrides) management pack which will disable discovery of 2012, 2014, and 2016 instances in the VersionList property, IF those older legacy MP’s are detected to also be installed. It will create this management pack if it does not exist.
(2) It then creates another override to disable itself. This stops it from running again.
The problem is that the rule TARGET changed from “Microsoft.SystemCenter.ManagementServer” in 7.0.15.0 and older versions, to “Microsoft.SQLServer.Windows.SqlVersionAgnosticGroup”. The issue that causes this event/alert, is because the original override has “Management server” in the scope of the override context.
To resolve this issue, simply find the rule “MSSQL on Windows: Automatic setup of DB Engine discovery filter” and delete the override, then re-create a new one and set enabled to false for all objects of type, on this rule:
After recreating the override correctly:
This will resolve the 4221 event and discovery error alerts, as the override now has the correct target scope.
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.
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.”
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.
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
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.
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.
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?
Hello does anyone have a solution for this?
We have just imported this and found out this problem as well. We use Principal Name in the alert in Orchestrator to send alerts to ServiceNow. So we are have to look how to work around this. Interestingly the alerts from the Discovery MP does use Principal Name in the alerts it generates.
I opened MS case and asked Microsoft if there is any solution for this, and there isn’t from their side.
I solved the problem by a script that assigns server names to “custom field”, and in the orchestrator is the value assigned to the principal name parameter.
I have most of the SCOM environments connected directly to the ServiceNow(via ServiceNow connector) and with the custom field, only new rule in the SN is needed.
Hi Jan,
How do you limit collection of alerts with the SNOW connector? Are you using groups, or do you just send all alerts to SNOW, then use SNOW event management rules to ticket/ignore?
If you are using SCORCH to update the custom field, how do you ensure the custom field is updated with FQDN *before* the alert is sent to SNOW?
We branched off based on the alert coming from the name of the Version Agnostic MP (Microsoft SQL Server on Windows (Monitoring)) and Microsoft SQL Server Integration Services on Windows MP as that has the same problem. We found that MonitoringObjectFullName has the FQDN in the middle of this field and so with Regex we were able to extract that and put it in the field where the PrinciplaName should be.
@Kevin we only send Warning and critical alerts. If the alert is not needed then it is disabled or changed to Information if we want to keep an eye on it without creating ServiceNow tickets. We have a Runbook that checks that the names are FQDN.
Hi Ian,
Are you able to share the Regex you used to extract the PrinciplaName?
Sorry for the delay but I did not realised there was a reply to my post.
We use Orchestrator as part of the process and use the Match Pattern activity.
From the alert we use MonitoringObjectFullName and for the FQDN the regex is
[0-9a-zA-Z-]*(.domain.com)
Change to your domain.
And for NetBios Name
(?<=:)([a-zA-Z0-9-]*)
We don't have many servers in the DMZ but I did test it and worked.
I have also found that the MonitoringObjectDisplayName at least for backup failure alerts, is now the database name. Whereas the legacy packs it was the instance name which we pass along to the ticketing system.
I have the same opinion, The Alert´s in the new SQL Mp 7.0.24.0 is very bad formatted, There is many common properties that is empty. NetbiosComputerName, NetbiosDomainName, PrincipalName, Path, the only value that you can use is MonitoringObjectName or MonitoringObjectDisplayName but this is formated like HOSTNAME.SQLINSTANCE, – why!!
I have a support Case on this.
A support case and asking for escalation is the best thing to do – this MP Alerts is not optimal, I agree, and this is a common complaint. Let’s hope they address it with the customer escalations.
What I just found is that I am not receiving any alerts on failed backups at all, and the rules are enbled. What can I be missing?
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.
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?
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
*next morning* all discovered objects that i saw yesterday are gone again in the reporting part… im so confused right now.
does anyone have a copy of the new SQL Server RunAs Addendum Management Pack, the TechNet link seems to be broken ?
TechNet download Blog https://kevinholman.com/2016/08/25/sql-mp-run-as-accounts-no-longer-required/
Blog https://kevinholman.com/2016/08/25/sql-mp-run-as-accounts-no-longer-required/
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
I need to provide more details on that. All the SQL 2017 stuff should carry over. But all the stuff pointing to 2012, 2014, and 2016 needs to be recreated as the workflow ID’s are different.
Thanks Kevin – understood.
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
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.
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…
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.
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?
I’m also facing same issue, did you get any solution for that ?
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
I dont know. It depends on what you found inside the Default MP that was being referenced.
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”>
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?
Did you manage to resolve this? I can also only see the “Database Files Space Usage” report since deploying the SQL version agnostic MP
Anyone managed to solve this problem?
It isnt a “problem” more than it is a change. The old SQL MP’s contains about 8 reports, but they were all linked reports so they can be recreated using the core foundational reports pretty easily. The new one only contains 1 report which is a new and custom report. I am not sure how much the old reports were even used. Do you have examples of specific reports you depended on?
Hi Kevin, not really dependent on any of the reports, was just wondering where they are gone.
Thanks for clarification on this, I will recreate reports as I go.
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?
Hi Kevin,
Thanks for this! I am sure it will be very helpful. My challenge right now is that there is a worrying (for us) note in the docs:
“We recommend that you monitor no more than 50 databases and 150 database files per
System Center Operations Manager agent to avoid spikes in CPU usage that may affect the
performance of monitored servers.”
We have some SQL clusters that have more than 100 databases on an instance. We have experienced some monitoring performance issues with the existing MP already so I am wondering if it is going to be more severe with this MP.
That statement is not new. It’s been true since our very first SQL MP’s for SCOM 2007 back over 10 years ago.
I have customers go well over the 50 DB mark…. I have even seen up to 300 DB’s and seem to work ok. However, I have also seen many DB’s get to the point that monitors fail to initialize, timeouts, resource consumption, etc. You just need to pay attention to those, monitor them closely, look at CPU/memory consumption for the SCOM processes, and look for errors in the SCOM Agent event logs. If you cannot support these large instances, you can take actions like disable DB file/logfile discovery, and try monitoring at the DB level, or instance level only, etc.
Thanks Kevin for replying so quickly. I appreciate your insight.
Since this new agnostic version was built from scratch did an ‘Express’ engine exclusion get built in, or can it monitor them properly?
self-found answer. (RTFM) Yes it can!
It does discover express, however you can exclude by name, additionally there is a dynamic group included that can be used to disable specific workflows if desired. I dont know of any errors specifically around express, as I almost always work to exclude them.
Hi Kevin,
great instruction on how to remove the “duplicated” SQL Views from SCOM. But unfortunately we did not get the instances for SQL 2012, 2014 or 2016 to be shown in the “SQL Server Roles” nor the “SQL DB Eingine on Windows ” from your “RunAs Config MP”.
We did remove the exclusion from the override package like written in your post but after waiting for round about 12 hours we did not get more then 3 instances
Hi Kevin
Any details on the correct MS SQL Reporting service management pack to use with the new agnostic SQL mp versions, as I need monitor SQL 2012 RS.
The only non version labelled MP I can find for MS SQL RS is 7.0.15.0 which actually is the 2017+Reporting services one once you import it, which you said should be installed.
That should say “Shouldn’t be installed”
FYI – take care to note the differences in the Version Agnostic MP vs the legacy MPs. I just completed a multi-version SQL farm migration from SCOM 2016 with legacy MP’s to SCOM 2019 with only the Version Agnostic MP. I was happy to turn over to my DBAs a nice and “clean” SCOM view, only to then immediately be asked “where’s the ‘Tcp Port’ property for the DB Engines?”. Had I noticed this wasn’t available I might have chosen not to use this MP. Instead of configuring SCOM 2019 to also use the legacy MP, I am just accepting the DBA criticism and hoping it can be included in a future update…
I believe we are planning on adding this back in a future update of the MP. This was removed, because it was no longer needed because of changes to the MP in how we connect to SQL. Since it wasn’t needed by the MP, it was removed. However, we understand customers may have liked that feature for visibility, grouping, and custom workflows, so this feedback has been taken.
Hi – I just wanted to mention that the latest version published on 26th December has indeed brought this feature back in. Version 7.0.20.0
It is included in the What’ New section of the guide… “Added attribute “TCP Port” to “SQL DB Engine Class” and updated “DB Engine Discovery” to populate the new property”
Kevin,
Might you be able to direct me on how to monitor what used to be called “Service Pack Compliance” in the old individual MPs and what now I believe is called “Product Version Compliance” in the new MP? Different versions of SQL have a different compliance version in our environment.
The new monitor evaluates for each version separately – this is described in the Product knowledge.
If you wish to override this – also covered in the product knowledge – you override for groups. You will have to create custom groups for each version of SQL instance. Such as an “SQL 2012 DB Engines Group”, etc.etc. and use the version property to create these groups.
Hi!
Trying to go over to SQL Agnostic MP from the Legacy SQL MP´s. Have read this Guide for hours now, in case i´ve been missing something important. 1.I have removed all the overrides based on the Legacy SQL MP´s. 2. Uninstalled all the Legacy MP´s and the earlier Run as MP for SQL. 3. Installed the Agnostic SQL MP. 4. Installed the Run as SQL MP 7.15.1. I dont now why, but where missing many of the SQL Server Roles. Everything shows up under the SQL Run as MP under Seed. Under DB Engine over 50% SQL engines missing. Same in the SQL Server Roles there are missing 50 % of the SQL Server Roles. Any tip what to search for?
If you are missing DB Engines, this means you have a discovery problem.
1. The most common issue is that you do not have rights. You did not set up Service SID monitoring using the RunAs Helper MP correctly, or you did not grant rights to the service SID on the instance as needed. This will be evidenced by discovery failure alerts that I guarantee you – you have.
2. The other common issue would be that the override is set to exclude specific versions of SQL, as described in the post and images above.
It is going to be one, or the other, or both.
Ok number 2 is out beqause i can see different kind of SQL versions under the SQL view… But your right about the discovery problem, i can see thoose failure alerts you talked about. The thing is that for now the SCOM agents are in a multihome scenario.
I have checked on a missing SQL Server Instance and the NT Service\HealthService is there and are mapped to the SA role. Can it be the cause that the service SID was created and granted with the older version of the the RunAs Helper MP from the old SCOM 2012 R2 environment? Maybe i have to delete SQL-logins and create them again from SCOM 2019 with the new RunAs Helper MP? But it is a little bit strange why some of SQL instances works and other not.
I can see now in the eventlog for one of the SQL Servers that there is a error with eventid: “4221” Source: “Error SQL Server Integration Services MP”
There is a Exception with message: “SQL Namespace is empty or version less than minimum version”
In the Inner Exception the message is: “Unable to execute query ‘Select * FROM ClientSettings’ for the path ‘\\SERVER.Domain.com\root\Microsoft\SQLServer\ComputerManagement13’ 32 bit: False. Error code -2147217392. Error message: Invalid class.
I have no idea whats is wrong…?
Invalid class means your SQL server is missing WMI entries that the management pack is expecting.
Do you have the following WMI class present? root\Microsoft\SQLServer\ComputerManagement13
Yes i have that WMI class present…
The same Error “4221” are consistent for every SQL Server that have Discovery problems. Looked on another SQL server and the WMI class “root\Microsoft\SQLServer\ComputerManagement13” are also present there, so i really dont know what to do…
It worked very well with the Legacy SQL MP’s so this is strange. It also feels wrong to give up and go back to the Legacy MP´s.
Hello Benny!
We had the same issue with some of or SQL Servers in the Discovery process.
Try this one on the SQL Servers that have problem with the WMI questions.
From an Elevated CMD prompt
mofcomp “%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof”
Remember to change the number for your current SQL version.
Thank you Martin, now it works. No more discovery problems. 🙂
This version agnostic management pack (v7.0.15.0) appears to have issues discovering clustered SQL Server instances. I deployed it alongside a fresh SCOM 2019 build without any of the old SQL Server management packs imported, and only some instances/databases are being discovered – it’s a very strange mix of results. We have a SQL 2014 instance that gets discovered fully (including databases), a SQL 2016 instance which is discovered with its databases but are not monitored, and SQL 2016/2017 instances that aren’t discovered at all.
I then imported the SQL 2016 management pack (v7.0.15.0) and it discovered all the clustered instances/databases with no issues whatsoever. I haven’t tested other versions yet but I’d expect it would likely be the same result.
I see this error appearing in the Operations Manager log on the management servers (ID 10801):
Discovery data couldn’t be inserted to the database. This could have happened because of one of the following reasons:
– Discovery data is stale. The discovery data is generated by an MP recently deleted.
– Database connectivity problems or database running out of space.
– Discovery data received is not valid.
The following details should help to further diagnose:
DiscoveryId: 435a766d-decb-3d54-7d0d-08ca1142e6eb
HealthServiceId: 330d8eeb-218d-4956-16df-e6a89aa7e061
Microsoft.EnterpriseManagement.Common.DiscoveryDataInvalidRelationshipSourceException,The relationship source specified in the discovery data item is not valid.
Relationship source ID: c1c83d48-b71f-296c-25be-80fd56d89cb2
Rule ID: 435a766d-decb-3d54-7d0d-08ca1142e6eb
Instance:
5c324096-d928-76db-e9e7-e629dcc261b1HOSTNAME.domain.com8e36e48d-79a9-25f6-03f4-a83dbabfb7c5SQLVIRTUALNETWORKNAME.site.domain.com0ad613ed-b2ba-4bd3-05d2-102dc4d8e756INSTANCENAME.
What’s interesting is that the 1st value contains only a partial domain name which doesn’t include the site, whereas the 2nd value contains the FQDN.
I’ve ruled out cluster/SQL instance permissions, proxy agent settings, RunAs profiles, NIC settings, DNS, NetBIOS/default domain on the cluster nodes.
Standalone SQL instances are discovered fine!
Same issue here. Seems that to monitor SQL cluster instances the SCOM management servers that are included in the SQL 2017 resource pool needs to have RPC remote access permissions to the SQL instances. This would be a big drawback I think.
This problem happens so often that we have created 2 rules. One for SQL 2012 servers and the other for SQL 2016. Use PowerShell Script:
For SQL 2012, check event 4101
Start-Process mofcomp “C: \ Program Files (x86) \ Microsoft SQL Server \ 110 \ Shared \ sqlmgmproviderxpsp2up.mof”
For SQL 2016, check event 4212
Start-Process mofcomp “C: \ Program Files (x86) \ Microsoft SQL Server \ 130 \ Shared \ sqlmgmproviderxpsp2up.mof”
Hi All, I saw some issues after import New version mp for SQL. I’m using too other version MpS for SQL 2012-2016. After imported I disabled discovery in Agnostic MP for previous version SQL but now, from date import I See more events 2115 from data discovery. The Time delay is from 20000 to 40000 s. On database I See one task which is lock, and another tasks must waiting for this locking task… Anybody know where it will be problem? And how can I check and find root cause?
We have the Replication MP for 2016 and the database team want to monitor 2017 replication. When it says it needs the old replication MP does that mean it just needs the MP installed or does it need discovery running as well to get the objects?
Kevin,
Thanks for putting this together. Quick question: I have this new management pack, but I still need to monitor SSRS 2016. I added the “SQL Reporting Services 2016” MP. This results in a “Microsoft SQL Server (old)” folder being created. Aside from the unfortunate visual of an “old” folder, is there any other conflict with using “SQL Reporting Services 2016” MP alongside the v7.0.15.0 version agnostic SQL Server MP? Thanks!
If it will import and discover, there is no “conflict”.
Hello Kevin,
As some other people commented, i notice that the reporting part of the new SQL MP only has one report type, no monitoring report as before. Is it something that we do wrong while importing this MP? Are the reports available somewhere? I need some of these reports for SQL performance etc.
Thx a lot for your help,
Hi Kevin
The Management Pack appears to be discovery only stand-alone sql servers. None on my 2012, 2014 and 2016 clustered instances are being discovered. The 2017 instance where discovered but not monitored. Not sure where I should go from here any assistance would be appreciated.
Thank you.
I don’t repro that. I have clusters (traditional shared storage failover clusters and Always-On replicated clusters) and all discover without issue.
Have you opened a Microsoft support case on this issue?
Are you seeing any WMI errors in the Application event log? Are you seeing discovery errors in the agents OperationsManager event log?
Thank you for the feedback.
My SQL clusters did come in eventually. Discoveries are currently extremely slow.
Hi Mr. Master Kevin. Regarding Run As Addendum MPs and these new versions you said “It will “work”, however, the new MP simplified the security configuration required to be the same across all versions.” Do you recommend to use SID accounts or standard Run As accounts? Many thanks for your great support!
SID. Always. RunAs accounts are good when they enable something that cannot be done otherwise, but they are a real pain to manage. Using Service SIDs is so much easier to maintain, and more secure.
Hi Kevin, we are testing the new agnostic SQL mp in our scom dev at the moment.
First we deleted all our old SQL mps and waited for some minutes (no more SQL views and profiles were present).
After that we imported the “new” SQL mp 7.0.15.0 and placed our SQL admin (sa and local admin) in the discovery and monitor run-as profile.
After some minutes only the SQL 2016 on our scom reporting server was discovered.
We are missing three more testservers with SQL 2016 and 2017 but none is showing up.
any idea why ? no error or warning in scom.
Those missing SQL Servers worked with the old mp without a problem.
First off, I am not a fan of using RunAs accounts for these, especially not ones with SQL Admin rights. I have much better solutions posted, using lowest priv and service SID’s.
Second – if you have rights, did you distribute these RunAs accounts to the agents? Did you inspect the event log on the agents to look for discovery attempts or errors? Did you restart the agents to speed up the discovery process? Did you check to see if you discovered the SEED class on the expected servers?
How to exclude servers with Express Edition from this MP ? I have created group with (( Object is MSSQL on Windows: Local DB Engine AND ( Edition Contains Express ) AND True ) ) and created Override on Local DB Engine object discovery for that group but I can still see Express servers poluating in server roles Dashboard.
could you please suggest why this is happening ?
I imported the new MP version 7.0.20 into SCOM 2019. I do not see a dsicovery “MSSQL on Windows: Discover Local SQL Server Database Engines” . I only see MSSQL on Windows: Discover Local SQL Server Database Engines Should Manage Relationship. Is there something I am missing?
No – we renamed it (ugh!) – I need to update this blog post. Search for “SQL” and you will find it in the list.
Thanks, That worked! However now I am flooded with discovery fails. It looks like my runas accounts are having trouble logging into SQL now. I configured the run-as profiles correctly and I check the SQL servers tand they are showing Login failed: Could not find a logon matching the name provided : Error 18456 . These are the same runas accounts that the older MPs were using just fine. The systems are still using SCOM 2016 agent on SCOM 2019. I looked at the SCOM MP guide and don’t see any security changes. Did something change with permissions that I am not aware of?
Sounds like a misconfiguration. Using RunAs profiles and RunAs accounts?? Ewww. Why not move to Service SID’s and make your life easier, and more secure?
I looked at your MP once before but I think we didn’t do it because it required rebooting every single sql server (We have a lot) . It seems like there is a bug in the new MP that is sending the credentials the wrong way or something. I can login remotely using SSMS with the domain credentials no issue.
Do not put your domain Runas account into the “Microsoft SQL Server SQL Credentials Run As Profile”
This profile is used for authentication against Microsoft SQL Server instances using SQL Credentials. Do not bind a domain account to this Run As profile.
You do not have to reboot anything! It just requires an agent service restart, which is a non-issue. 🙂
There’s a side note: “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.”
Any development for a Version Agnostic SQL Replication MP that doesn’t depend on the old legacy discovery MPs?
I’m in the process of changing to SQL agnostic mp. The issue is that I have about 100+ Distributed Application dependencies to the old SQL MPs. Do I need to open every DA and replace the old discovered DB-object or is there someway to replace the old discovered DB-objects with the “newly” discrovered MPs?
You’d have to manually edit them, or figure out how to do a bulk find/replace if they are in different MP’s. If they are statically assigned by ID in the XML, manual edit is the only feasible way.
I was never a fan of distributed applications. They create a big load and manual work, and most people just dont use visualizations like them.
I am not the fan too. However, my boss is! My boss’s boss is! What can I do? Could MS remove this feature then I can speak out “NO” loudly?
Generally, because this has to do with visualizations, I would recommend looking at investing in Squaredup, or Live-Maps. You can still use Distributed Apps for free, but it gets to be a manual tedious process to keep them up to date with a dynamic changing landscape.
Hello Kevin/All,
I am trying to migrate SQL monitoring to agnostic MP from legacy SQL MPs..before i proceed to remove legacy SQL MPs how can i migrate overrides from older MPs to agnostic MPs in easier way ?
Please suggest.
No, unfortunately not. This is a new MP, with new workflows, new design, and gets rid of multiple MP’s requiring version specific overrides. So the investment NOW will pay dividends later.
Hi Kevin & Community,
When using the “create login as low priv” script from the SCOM console it fails to execute the creation of the SCOM_HealthService role and NT SERVICE\HealthService account. We are getting the following return (Output 1).
However, When we excecute the script locally on the same server with the same account, the script runs succesfully (Output 2).
Notes: Logon as a service is enabled for the User Account.
The SQL ports (1433 – 1434) are open on the server and the SCOM port (5723) is able to communicate with the Gateway Server.
The user logs on succesfully and is able to execute the powershell script locally on the server. All “SQL server network Configuration” protocols are enabled for the instances.
Does someone experience the same issue or have an idea what could be causing this difference?
Kind Regards,
Niels
Output 1
////////////////////////////////////////////////////////////////////
Script is starting.
Running as (Contoso\SomeUser).
Attempting to Create SQL Login and assign permissions for Healthservice.
ERROR returned from SQL server (SomeServer.Contoso\INST01).
Terminating script.
Error is: (Exception calling “ExecuteNonQuery” with “0” argument(s): “ExecuteNonQuery requires an open and available Connection. The connection’s current state is closed.” Exception calling “Open” with “0” argument(s): “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol)”).
Exception calling “Open” with “0” argument(s): “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol)”
At line:89 char:1
+ $SQLConnection.Open()
+ ~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
Exception calling “ExecuteNonQuery” with “0” argument(s): “ExecuteNonQuery requires an open and available Connection. The connection’s current state is closed
.”
At line:93 char:1
+ $SQLCmd.ExecuteNonQuery() | Out-Null
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException
////////////////////////////////////////////////////////////////////
Output 2
////////////////////////////////////////////////////////////////////
Script is starting.
Running as (Contoso\SomeUser).
Attempting to Create SQL Login and assign permissions for Healthservice.
ERROR returned from SQL server (SomeServer.Contoso\INST01).
Terminating script.
Error is: (Exception calling “ExecuteNonQuery” with “0” argument(s): “The server principal ‘SCOM_HealthService’ already exists.
The server principal ‘NT SERVICE\HealthService’ already exists.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
Changed database context to ‘master’.
Changed database context to ‘master’.
Changed database context to ‘msdb’.”).
Exception calling “ExecuteNonQuery” with “0” argument(s): “The server principal
‘SCOM_HealthService’ already exists.
The server principal ‘NT SERVICE\HealthService’ already exists.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
User, group, or role ‘SCOM_HealthService’ already exists in the current database.
Changed database context to ‘master’.
Changed database context to ‘master’.
Changed database context to ‘msdb’.”
At line:93 char:1
+ $SQLCmd.ExecuteNonQuery() | Out-Null
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
////////////////////////////////////////////////////////////////////
Are your SQL instances using a custom port? I believe that’s a known issue with this solution and I was going to enhance it to try and discover the port.
If you get already exists error – that’s normal when you run it a second time.
I think so, we use ports; 1433, 1435, 1439 and 1441
Hi Kevin,
Are there any caveats to know before I upgrade from Agnostic SQL MP 7.0.15.0 to 7.0.20.0.?
I note that there are quite a number of changes between these versions, for example;
7.0.15.0 – MSSQL on Windows: Discover Local SQL Server Database Engines
>became 7.0.20.0 – MSSQL on Windows: Discover SQL Server Database Engines (Local)
I currently have overrides for this discovery for the “Exclude List” and “Versions of SQL…” and so I am wondering if these will carry across given the name change or if these will require adding again?
I am also overriding “SQL on Windows: Discover Installation Source (seed)” based on groups to include / exclude the SQL servers I want to monitor – the guide suggests overrides on this discovery have to be removed before importing, again is that relevant to a 7.0.15.0 > 7.0.20.0 upgrade?
I also have overrides in place for a significant number of rules and I am wondering if these will seamlessly transition when upgrading to 7.0.20.0?
Apologies for the many questions as I may have misunderstood, but from reading the MP guide it does suggest there will be major issues, though it is not clear if that also applies to importing over v7.0.15.0 (or only from earlier SQL MP’s).
Your clarification would be greatly appreciated.
Regards,
Paul
Regards,
Paul
Nothing should be lost, the update should be seamless for any overrides you already have. If there was an override issue, then the MP would not be backwards compatible.
The major difference for someone to know in upgrading – is to read the “whats changed” section in the MP guide. It will give you hints of things to review. In this version, the biggest change is how DB space monitoring is done. We no longer use DB file/filegroup in the calculation.
Many thanks for clarifying.
Hello Kevin,
After importing the MP, we try to view the summary dashboard from the web console but we are getting a pop up that says Error!… no details are mentioned.
It’s working fine on the operations console.
One of the errors related to this is the below:
{“errorMessage”:”The required anti-forgery cookie \”__RequestVerificationToken_L09wZXJhdGlvbnNNYW5hZ2Vy0\” is not present.”,”errorTrace”:” at System.Web.Helpers.AntiXsrf.TokenValidator.ValidateTokens(HttpContextBase httpContext, IIdentity identity, AntiForgeryToken sessionToken, AntiForgeryToken fieldToken)\r\n at System.Web.Helpers.AntiXsrf.AntiForgeryWorker.Validate(HttpContextBase httpContext, String cookieToken, String formToken)\r\n at Microsoft.EnterpriseManagement.OMDataService.Filters.ValidateAntiForgeryTokenAttribute.OnActionExecuting(HttpActionContext actionContext)\r\n at System.Web.Http.Filters.ActionFilterAttribute.OnActionExecutingAsync(HttpActionContext actionContext, CancellationToken cancellationToken)\r\n— End of stack trace from previous location where exception was thrown —\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Filters.ActionFilterAttribute.d__0.MoveNext()\r\n— End of stack trace from previous location where exception was thrown —\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext()\r\n— End of stack trace from previous location where exception was thrown —\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext()\r\n— End of stack trace from previous location where exception was thrown —\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Filters.ActionFilterAttribute.d__0.MoveNext()\r\n— End of stack trace from previous location where exception was thrown —\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Controllers.ActionFilterResult.d__2.MoveNext()\r\n— End of stack trace from previous location where exception was thrown —\r\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Controllers.ExceptionFilterResult.d__0.MoveNext()”}
Hi Kevin,
I have imported this the SQL MP version 7.0.20.0 (updating from 7.0.15.0) in our DEV environment. After importing I have seen the following error relating to the SCOM Management Server RANOMG09 (with Repeat Count over 100++ regularly popping up). I am not sure why there is a discovery error for the management server itself. We have not seen this before with the previous version and I am at a loss as to determining the cause / resolution.
I would appreciate your suggestion on how we may go about resolving this issue.
Regards,
Paul
Alert>>>>
Rule name: MSSQL on Windows: Discovery error
Rule target: MSSQL on Windows: Monitoring Pool Alert Collection
Log Name: Operations Manager
Source: SQL Server Discovery MP Windows
Event Number: 4221
Level: 1
Logging Computer: RANOMG09.lan.xxxxxxxxxxxxx
User: N/A
Description:
Management Group: “SESI-OM-DEV” 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 = SESI-OM-DEV 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()
Event Data:
Management Group: “SESI-OM-DEV” 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 = SESI-OM-DEV 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()
Thoughts on why this might be occurring and whether it can safely be ignored would also be appreciated.
I added the resolution to this in the “Known Issues” section.
Thank you Kevin. That is awesome.
Hi Kevin,
If i do not see the Integration MP’s for the new MP, and cannot find them in the import managements search. Where do i find these to try and import them in? The other MP’s have been imported.
Download them from the link I posted at the top.
*NEVER* use the catalog in-console experience for MP import. In my opinion, we do not do a good job of keeping it current, nor correct. MP’s are too complex and this in-console solution is overly simplified, and often results in unsupported configurations.
I have tied using that installer but the change/repair options are greyed out, the only option i have is remove?
Never mind, found the Management Packs from a other SCOM instance. Thanks for the help
For this new MP there is a way to create groups based on SQL Version for the Database Replica and Availability Replica, but i can not see a way to link Availability Groups back to the SQL Version. Is there a way, Team like to look at environments by SQL Version, did not have to worry about this in the Legacy MP
Hello Kevin,
Do we need SQL addendum pack as pre-requisite for agnostic MP to monitor sql 2017+ or it could still work with old process of Run as Profile ? I did see some monitors in Agnostic discovery MP mentioning addendum Microsoft.SQLServer.RunAs.2017.Addendum.OLEDB.CheckForSysadmin.Monitor..
Please suggest.
You can use old-school Windows run-as accounts just fine. There is no requirement to use my addendum. My addendum is just a tool to make using Healthservice SID accounts, and setting SQL permissions, easier to manage.
We have “Microsoft System Center Management Packs (Community Technology Preview) for SQL Server Reporting Services and Analysis Services” v7.0.19.0 Agnostic MP available now .. Will there be any impact on current agnostic MP if we import this MP ?
It shouldn’t.
Keep in mind, CTP releases of MP’s are not yet supported and designed to be used in test environments, to gather customer feedback before releasing a production ready MP.
Hi Kevin,
Is there a way to adjust the default behavior for databases marked with IsAlwaysOnReplicas True showing a status of “offline/recovery pending/suspect/emergency” when they are the secondary?
Hi Kevin,
10 months back we had upgraded the SQL MP version from 7.0.7.0
to 7.0.15 as per the MS SQL MP guide.
We see two different SQL views in the SCOM console.
please confirm if we need to perform the above activity for the SQL MP version 7.0.15 as well.
If Yes, can we perform this activity now and will there be no impact?
do we loose the old data (reports, overrides etc.)
You should be using 7.0.20.0, this is the latest MP. You should transition to this MP only, and remove your old legacy MP’s for SQL – as these are no longer being developed, updated, nor supported.
You wont lose old data – that is kept in the Warehouse.
You will need to set overrides again, if they are still applicable to the new MP. There are so many new monitoring methods, it is likely they will not apply anymore.
Kevin,
Preface: We are setup to use Low Priv monitoring for SQL.
After importing 7.0.20.0 our console flooded with “System Center Management Health Service Credentials Not Found Alert Message” complaining “An account specified in the Run As profile “Microsoft.SQLServer.Core.RunAs.Discovery” cannot be resolved.”
In our Prod environment, where this is happening, we only distribute the SQLDiscovery account to known SQL servers.
The error is sourcing from servers on which SQL isn’t installed (from what I can tell).
Any ideas?
Why are you using RunAs accounts at all? Why not use Service SID in conjunction with low priv?
This will happen when a workflow is told to use a RunAs profile, and that profile is associated with a RunAs account. The question really is – why is a RunAs profile associated with a workflow, running on computers where SQL server is not installed? The answer will lie in “which workflow is causing this”. Do you have any other clues as to which workflow?
Does anyone know an easy way to resolve this issue: The alert comes in as the database name instead of the servername.
Go to Personalize view and add Path. This will then have the server name.
Kevin,
I am getting mixed messages about the Overrides for previous versions installed for SQL 2008, 2012 etc…
What will need to be be redone in terms of Overrides?
Thank you again for your help.
All tuning will need to be recreated – as these are new MP’s, new classes, and new instances. Anything you had for SQL 2012 – 2016 will need to be recreated for the version agnostic MP.
SQL 2008 will remain, as the Agnostic MP does not support that.
Thank you.
Is there a way to do Overrides to discoveries-(Disable discoveries for older SQL Classes) to only target SQL 2019 servers to be monitored by the new MP?
That information is covered in this article. Please review it. By default the Agnostic MP will only discover SQL 2017 and later.
Thank you as always. Needed to triple check. 🙂
Is there a way to disable discovery of Integration Services or any other component of SQL Services with this MP?
The DB Engine Override has worked well so far.
You can disable discovery of anything that uses an independent discovery:
Root level discovery of SEED class:
MSSQL on Windows: Discover Installation Source (seed)
DBEngine discovery:
MSSQL on Windows: Discover SQL Server Database Engines (Local)
IS has its own discovery in the IS management pack which is include din the bundle. You can disable IS discovery if you don’t want it:
MSSQL on Windows Integration Services: Discover Installation Source (seed)
MSSQL on Windows Integration Services: Discover Local SQL Server Integration Services
Thank you.
Was wondering if there was a way to limit on version like the SQL DB Engine with versions and a comma….2012, 2014, 2016 etc…
Need help with MSSQL Replicartion Discovery:
We have implemented the new method of SID, but we are having a discovary error for MSSQL Replication for Distibuter role fails, and try to run with defualt action acount (Local System)
Management Group: “OM-TETSRV”
Module: Microsoft.SQLServer.Replication.Windows.Module.Discovery.Discoveries.DistributorDiscovery
Version: 7.0.15.0
Error(s) was(were) occurred:
State:
The configuration properties are:
ManagementGroupName = OM-TESTSRV
Publisher = SQLReplicationDiscoveryWindows
ConnectionString = XYZ307.mydomain.local
InstanceName = MSSQLSERVER
MachineName = XYZ307.mydomain.local
MonitoringType = Local
NetbiosComputerName = XYZ307
Login = <>
AgentServiceName = SQLSERVERAGENT
ClassId = 465e9601-be5f-ad8f-0b96-85e338a872fa
DataBaseClassId = 722594ba-bc11-45d5-81a2-a6059ada4682
DiscoverySourceManagedEntityId = f794aa43-b199-7172-4b64-3805c2728264
DiscoverySourceObjectId = fe1fb7ea-6a11-8011-889e-57d646085fe7
DistributorInstanceGroupClassId = e86a4378-8ee5-53be-16ae-cc0ad3a80bad
DistributorInstanceGroupContainsInstanceRelationId = 88dff527-e469-6e4b-7d4c-48c387e340f3
DistributorRefersDataBaseRelationId = 7d1bcdaa-26d8-5c78-c1d0-358d8e3b22bf
InstanceClassId = 8def7ccc-ca28-c7ef-796f-644160e0b22a
PerformanceCounterObject = SQLSERVER
ServiceName = MSSQLSERVER
SqlTimeoutSeconds = 15
TimeoutSeconds = 300
Password = ********
Is anyone else having issues importing these MP’s? I’m running SCOM 2019 UR2 and I removed all previous SQL Server MP’s before attempting to install the agnostic ones. I’m getting “This assembly is not fully signed. Cannot verify the strong name signature of the file” on some of the agnostic SQL MPs but not all so I’m now somewhat stuck in limbo. Note that I ran the installer on my primary MS then tried to import the MP’s from their C:\Program Files (x86) location. I’ve since tried downloading the individual MP’s from the catalogue but same result.
Sorry, forgot to mention that I’m trying to install the latest version of the agnostic MP’s – 7.0.24.0. Cheers
When I go to updates and recommendations, I see that sql 2016, etc is not completely installed. How do i remove this?
Our catalog/updates/recommendations is not very good. I’d ignore it. I actually remove the “updates and recommendations” MP from my customer environments, because it causes more harm than good in my opinion.
Hello Kelvin,
I have excluded the SQL version of 2012 and 2016 however SQL 2016 SQL cluster are still getting discovered inspite of exlcuding 2016 version. Do we have any way exclude SQL cluster that is being discovered under Agnostic pack ?
In Dev Environment the Monitoring > Microsoft SQL Server > Summary Dashboard is loading fine. however in Prod environment its not loading throwing errors as
An exception was thrown while processing GetDataWarehouseStoredProcedureResult for session ID uuid:b84e633c-5f94-4836-8fc7-6f440bad93fd;id=3947.
Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Timed out stored procedure: sdk.Microsoft_SQLServer_Visualization_Library_GetDataCenterDashboardData
Full Exception: Microsoft.EnterpriseManagement.DataWarehouse.DataAccess.SqlTimeoutException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Timed out stored procedure: sdk.Microsoft_SQLServer_Visualization_Library_GetDataCenterDashboardData —> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. —> System.ComponentModel.Win32Exception
Installed SQL MP ver 7.0.24.0.
How many SQL server instances do you monitor in this environment?
32 instances (only SQL 2017 and 2019) and reset we have excluded from discovery
Why do SQL 2019 Pack (7.0.24) version has 2 Summary out of which one shows popup error as
Couldnt load file or assembly ‘NotUsed….
System.IO.FileNotFoundException: Could not load file or assembly ‘NotUsed’ or one of its dependencies. The system cannot find the file specified.
File name: ‘NotUsed’
at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.EnterpriseManagement.ConsoleFramework.Wpf.ConsoleViewDisplayService.OpenViewJobArguments.LoadType()
at Microsoft.EnterpriseManagement.ConsoleFramework.Wpf.ConsoleViewDisplayService.OpenViewJob(Object sender, ConsoleJobEventArgs args)
at Microsoft.EnterpriseManagement.Mom.Internal.UI.Console.ConsoleJobExceptionHandler.ExecuteJob(IComponent component, EventHandler`1 job, Object sender, ConsoleJobEventArgs args)
Hi Kevin,
Thank you for the detailed explanation.
Currently we are in SCOM 2019 and installed SQL Agnostic MP. We are facing some canned pre defined SQL reports under SQL reporting. Only one report is visible but the other MP canned reports are missing in Agnostic MP. Do we need to enable something from MP or what i am missing. Please suggest. Only one SQL report is visible under Reporting pane – Microsoft SQL server on windows – Database Files Space Usage Forecast.
Please Suggest.
Thanks,
Sharanya
Hello Kevin,
We are using SCOM 2016 in our environment and recently started monitoring SQL 2017 servers through the new MSSQL MP. All the db instances are discovered and getting monitored but with a naming convention server1.abc.com.MSSQLSERVER for every instance. We have integrated SCOM with ServiceNow for auto ticketing and only SQL server name is sent as server1.domain.com.MSSQLSERVER for the sql related alerts because of which the server name which was supposed to be auto assigned from the CMDB list is not picking the server name and creating incident leaving the CI Item empty. I did go through the object discovery and couldn’t find anything. The servers names were sent exactly the same when I was using the old SQL MP but is now facing issue with the new one.
Please Suggest.
Example:
Server Name: Server1.abc.com
Server Name Sent to ServiceNow (auto ticketing) : Server1.abc.com.MSSQLSERVER
Regards,
Jyotsna Adapa
Morning, I have followed the procedures. It has discovered all but sql 2019. However, I can no longer find this: “MSSQL on Windows: Discover SQL Server Database Engines (Local)”. may be I am searching wrong or something. I am not on the 7.20 version yet. Any assistance or pointers, even to my glaring stupid errors, at all, at this point is greatly appreciated. I am at the point where I am want to pull this out and all the sql MPs and just install the agnostic version itself!
Thank you,
Anthony Strother
Upgrade to the latest MP first please. The discovery was renamed. There is no point in using an older version.
Hi kevin,
great article – i have one problem…. I cannot delete the managementpacks: SQL Server 2014 (Monitoring) and SQL Server 2014 (Discovery). I have disabled the MSSQL 2014: DIscover SQL Server 2014 DB Installation Source (seed) and then ran the Remove-SCOMDisabledClassInstance several times until it completed without error.
I can also see in the old SQL management pack folder, that I have 0 DB Engine discovered items from SQL 2014. when I try to delete the MP in the SCOM console there is no error about dependend management packs – so it just tries to delete it – but after a long time, it just times out.
and I also tried the powershell command:
Get-SCOMManagementPack -DisplayName “Microsoft SQL Server 2014 (Mo*” | Remove-SCOMManagementPack
I added the (Mo* so it will only attempt to delete the (Monitoring) MP – because I still have an override on the discovery
but the powershell command also fails:
Remove-SCOMManagementPack : The requested management pack could not be deleted. See inner exception for details.
At line:1 char:72
+ … playName “Microsoft SQL Server 2014 (Mo*” | Remove-SCOMManagementPack
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (Microsoft.Syste…mentPackCommand:RemoveSCManagementPackCommand) [Remove-SCManagementPack], InvalidOperationException
+ FullyQualifiedErrorId : ExecutionError,Microsoft.SystemCenter.Core.Commands.RemoveSCManagementPackCommand
I have seen this before.
Especially when we have a lot of SQL servers. It can be really hard to delete these. I like to load up SQL Activity Monitor and watch for blocked sessions, look at the head blockers, and often it is the delete of the MP doing this. I worked with a customer recently where deleting an MP with a lot of relationships took about 20 minutes. The default timeout is around 30 minutes I believe.
Question for you – how long have you had the discoveries disabled? The reason I ask – is that if you are removing an MP – you have to remove all the data collected by that MP as well, and this can be incredibly time consuming. If you keep 7 days of perf data, and statechange data for example, you might have more luck waiting for this data to groom out before attempting to remove the MP. When we delete objects in SCOM, we really just mark them deleted, then it takes 2-3 days before we purge the deleted data. I’d say – wait a week, then attempt this again. Or, manually set your retention lower, and manually run things like the purge of deleted data, grooming, run a database reindex, then attempt again.
Hi Kevin
We have update to 7.0.24.0 sql mp
The alerts from many of the sql instance is missing correct fullpath name (path), I see a new attribute in many of the alerts ”machineName” but for sql Always On alert this new is missing, as many companies we forward alerts to ticket system, there must be a generic attribute for all sql alert that is pointing on the computer. Not a sql instance name (computerhostname.instance) just the clean hostname
This is a common complaint. I recommend logging this request in great detail at aka.ms/SCOM
Hi Kevin,
Is Run as Addendum Management Pack 7.0.20.1″ works fine with SQL MP Version 7.0.24 ?
We have newly installed SCOM 2019 environment, after importing SQL MP Version 7.0.24 (fresh SQL MP installation) is there any other steps to be followed.
Kindly let us know.
Regards,
Kumar B