We recently released the SQL MP 220.127.116.11 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.
- 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
- 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:
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 18.104.22.168 version of the SQL RunAs Config Addendum:
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”
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’.
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:
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 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!