We recently released the SQL MP 7.0.32.0 available here: Download
This MP brings some features requested by users, but also will require updating the security configuration on all your SQL servers to support these features.
What’s New
- Added a new exclude list option for the “SQL Server Agent Job” discovery
- Updated the “Database Status” monitor. Added a new override ‘Disable if Availability Group is offline’ to avoid false positives in high-availability environments
- Updated the “Database Backup Status” monitor. Added a new override ‘Track Availability Group Backup Preferences’ to instruct the monitor to track the backup location configured in the Availability Group backup preferences
- Updated override for the “Service Principal Name Configuration Status” monitor. The ‘Interval’ value is now set to 3600 (1 hour) to avoid an alert storm in multiple domain controller environments
- Updated overrides for the “WMI Health State” monitor. The ‘Interval’ value is now set to 3600 (1 hour) and the ‘Samples count’ value is set to 2
- Updated the “Virtual Log File Count” monitor. Now it uses the ‘sys.dm_db_log_info’ view instead of DBCC on SQL Server 2016 and higher
- Renamed some Dashboard tiles
- Added new rules targeted to the SQL Server Agent Job: “SQL Server Agent Job Duration Alert Rule” and “SQL Server Agent Job Duration” performance rule
- Updated the following space related discoveries: “DB Filegroups”, “DB Files”, “Transaction Log File”, “FILESTREAM Filegroups”, “Memory-Optimized Data Filegroup”, and “Memory-Optimized Data Filegroup Containers”. Now they are disabled by default to reduce the load on the environment
- Updated the “Summary Dashboard” views. Now they also contain tiles for the SQL Server Integration Services
- Updated the “Product Version Compliance” monitor with the most recent version of the public updates for SQL Server
- Updated display strings
Bug Fixes
- Fixed an issue with continuous login attempts from passive SQL Server cluster node after the failover
- Fixed an issue with the failing “SQL Server Agent Jobs” discovery in cases of unsupported ASCII characters in job names—added proper error handling
- Fixed performance data collection for SQL Server DB Engines with Latin1_General_CP850_BIN collations
- Fixed an issue with the incorrect alert name for the “LOG Free Space Left” monitor
- Fixed an issue with the “Blocking Sessions” monitor that was enabled by default
- Fixed an issue with the lost ‘Timeout’ override for the “WMI Health State” monitor
- Fixed an issue with the wrong alert parameter reference in the “Summary Dashboard” view tiles
The new feature of being able to filter the Agent Job discovery by name, does bring some new security requirements for your action account in the Low Priv model (a best practice). Specifically:
USE [msdb]
GO
GRANT EXECUTE ON SQLAGENT_SUSER_SNAME TO [LowPrivAccount]
GRANT SELECT ON sysjobs_view TO [LowPrivAccount]
GRANT SELECT ON syscategories TO [LowPrivAccount]
I have added these to the updated 7.0.32.0 version of the SQL RunAs Config Addendum:
SQL MP Run As Accounts – NO LONGER REQUIRED
If you do not make these changes, the SQL agent job discovery will start failing and you will see alerts in SCOM:
MSSQL on Windows: Discovery error
Management Group: “OM”
Module: Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.AgentJobDiscovery
Version: 7.0.32.0
Error(s) was(were) occurred:
Message: An error occurred during discovery.———- Exception: ———-
Exception Type: System.Data.SqlClient.SqlException
Message: The EXECUTE permission was denied on the object ‘SQLAGENT_SUSER_SNAME’, database ‘msdb’, schema ‘dbo’.
Number: 229
Source: .Net SqlClient Data Provider
SQL Agent Job Discovery
SQL Agent Jobs are not discovered by default. We monitor SQL Agent Jobs through the SQL Agent object out of the box. Sometimes customers wish to monitor each job separately and want different settings on a per-job basis. I explain this process in detail here: Monitoring SQL Agent Jobs in SCOM – Kevin Holman’s Blog
In this updated MP, there is a new override-able parameter that allows further filtering of SQL Agent Jobs.
The discovery is named “MSSQL on Windows: Discover SQL Server Agent Jobs”.
The new override parameter is named “Exclude List”
Here is the description:
Parameter Description:
A comma-separated list of SQL Server agent jobs that must be excluded from discovery. Use a wildcard character ‘*’ to exclude all agent jobs. Otherwise, use conditions like ‘*_test’ to exclude agent jobs, the names of which end with _test, ‘Test*’ to exclude agent jobs, the names of which start with Test, or a ‘*test*’ condition to exclude agent jobs that have a test entry in any part of the job’s name.
It actually goes beyond that – you can also use multiple wildcards, something like *-*-*-*
I’ll demonstrate.
I have 186 SQL Agent Jobs in my lab. However, 116 of these agent jobs are default jobs created by the SCOM Reporting role, and I do not wish to discover nor monitor these.
Since they are defined with a GUID as a name… the hyphens are the only static characters. Therefore – I can use the following filter that contains 4 hyphens” *-*-*-*-*
That *undiscovered* any SQL Agent Job with 4 hyphens in it. Cool!
If you have any SQL agent jobs that you do not wish to monitor, you should consider undiscovering (excluding) them. Lots of agent job monitoring adds load to the agent machine and the SCOM management group. This is a great new feature!
Hi Kevin,
We’ve tried the task Creat HealthService Login as Low Priv Task, but the task output only shows errors. Is there something else we can try?
Script is starting.
Running as (domain\sqladmin).
SQLServer: (mysqlserver.domain\INST01).
Opening Connection using connect string: (mysqlserver.domain\INST01).
Connected to SQL Server: (mysqlserver.domain\INST01).
Running SQL command to Create SQL Login and assign permissions for Healthservice.
ERROR executing query on: (mysqlserver.domain\INST01).
TERMINATING.
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.
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.
Changed database context to ‘master’.
Changed database context to ‘master’.
Changed database context to ‘msdb’.”
At line:134 char:1
+ $SQLCmd.ExecuteNonQuery() | Out-Null
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
This is normal. These are simply error messages because you have already run the script before, and the login exists already. If you check, you can see the new permissions were most likely added.
Thanks for listing out the changes. Not sure why there isn’t a guide available for this version.
There is. ALL SQL MP’s will ship with online guides from now on to make the content more updateable and current.
https://docs.microsoft.com/en-us/system-center/scom/sql-server-management-pack-changes-history?view=sc-om-2019
Hello Kevin
There is a new SQL MP Version: 7.0.34.0 have you tested it?
Im having an issue with SQL 2019 server, it throws a Event ID 4221
Module: Microsoft.SQLServer.Core.Module.Helper.ScomModules.Monitoring.MultiValueSpaceHealthCalculator
Error(s) was(were) occurred:
Message:
———- Exception: ———-
Exception Type: Microsoft.EnterpriseManagement.HealthService.MalformedDataItemException
Message: Exception has been thrown by the target of an invocation.
Source: Microsoft.Mom.Modules.DataTypes
Stack Trace:
at Microsoft.EnterpriseManagement.Mom.Modules.DataItems.PropertyBagDataItem..ctor(XmlReader reader)
at Microsoft.SQLServer.Core.Module.Helper.ScomModules.Monitoring.MultiValueSpaceHealthCalculator.GetPropertyBag(DataItemBase[] inputDataItems, CancellationToken token)
at System.Threading.Tasks.Task`1.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
— 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.Helpers.AsyncHelper.d__0`1.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.DataItemHelper.c__DisplayClass4_0`1.<b__0>d.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.DataItemHelper.d__5`1.MoveNext()
Same issues here, have been trying to solve this for a week without any success. Not a lot of information regarding this issue out there.
Hope Kevin can shed any light on this 🙂
Same, appears to be a bug – even in 7.0.34.0.
I am not seeing these errors. I have the latest MP. I’m sure they are happening but not in my environments. Has anyone raised a support case on this issue?
I’m running the latest MP as well. This post: https://docs.microsoft.com/en-us/answers/questions/693664/sql-server-mp-bug.html says someone is on to it.
I am puzzled though, in alert details at the bottom to the left it says:
Summary
The rule traces monitoring workflows errors and generates error alerts.
Causes
The most common cause of monitoring workflow fails is lack of permissions to monitor SQL Server.
Resolutions
Make sure that all necessary SQL Server monitoring privileges are obtained.
Overridable Parameters
Name
Description
Default Value
Enabled
Enables or disables the workflow.
Yes
Priority
Defines Alert Priority.
1
Severity
Defines Alert Severity.
2
Is it a permission error without saying so in the event viewer, Ops Man events or SQL logs, no clue anywhere?