Category Archives: #SQL

SCOM 2019: New version of the SQL Management Pack v7.0.24.0

The latest version of the SQL version agnostic mp is out you can get it here

What’s New

  • Added a new “Securables Configuration Status” monitor targeted to SQL Server databases
  • Updated the “Product Version Compliance” monitor with the most recent versions of public updates for SQL Server
  • Updated the “Securables Configuration Status” monitor targeted to the DB Engine when a SQL Server instance participates in Availability Groups
  • Removed the “Securables Configuration Status” monitor targeted to the Availability Replica as non-useful
  • Updated the “SQL Server Database Engines” discovery; the “Netbios Computer Name” property is now uppercased.
  • Added reports from version-specific management packs for SQL Server
  • Updated monitor “Job Duration” to add current job run’s duration to its alert description
  • Updated Web Console version of SQL MP Dashboards to support SCOM 2019 UR1
  • Updated alert description of monitor “Securables Configuration Status”
  • Added “CheckStartupType” property to SSIS Health Status monitor
  • Revised columns of SQL Agent and SQL Agent Jobs state views
  • Updated display strings

Issues Fixed

  • Fixed error “Unsupported path format” in workflows targeting Filegroups
  • Fixed discovery error on non-readable availability replicas
  • Fixed wrong Run As profile in SSIS Seed Discovery
  • Fixed issue that caused rule “Disable Discovery of Selected DB Engines” to fail
  • Fixed discovery issue for databases in recovering state
  • Fixed issue in monitor “Securables Configuration Status” when it went critical on Shared-Memory-only SQL Servers
  • Fixed the Alerting Rules data source to avoid an alert storm after exiting maintenance mode
  • Fixed the SQL Log Reader data source to support changing of the SQL Authentication method
  • Fixed the Performance Reader data source to support changing of the SQL Authentication method

Loading

SCOM 2019: Scheduled reports fail to run – The job failed. Unable to determine if the owner has access

I encountered a minor issue today which luckily proved simple to solve. Hopefully this proves useful to others.

The Issue:

In a brand new SCOM 2019 deployment the scheduled reports weren’t sending. After investigation I found the below error in the Application log on the SSRS server:

SQL Server Scheduled Job ’62A9826E-082B-4ACD-9270-6BC13FC260BE’ (0x832F33183531EF4483665BBBFCCEBD9A) – Status: Failed – Invoked on: 2020-08-05 11:00:00 – Message: The job failed. Unable to determine if the owner (DOMAIN\USER) of job 62A9826E-082B-4ACD-9270-6BC13FC260BE has server access (reason: Could not obtain information about Windows NT group/user ‘DOMAIN\USER’, error code 0x5. [SQLSTATE 42000] (Error 15404)).

The Solution

The SSRS Instance, in this case SQL 2016 SP2, was deployed using system accounts for the SQL Server and SQL agent services. Simply changing these to use a domain account with access to the SQL instance resolved the issue and reports started sending shortly after.

See the source image

Loading

SCOM 2019: Updated SQL Management Packs for SSRS and SSAS v7.0.22.0

Updated management packs for SSRS and SSAS have been released, they are available at the below links and support versions from 2012 through to 2019 and now include Power BI Report Server:

SQL Server Analysis Services

SQL Server Reporting Services

SQL Server Analysis Services MP Changelog

What’s New

  • Added support for SQL Server Analysis Services 2012, 2014, and 2016 in addition to previously supported 2017 and up
  • Added Start/Stop Analysis Services Windows Service tasks
  • Implemented Database Status monitor 
  • Updated display strings

SQL Server Reporting Services MP Changelog

What’s New

  • Added support for monitoring SQL Server Reporting Services 2012, 2014, and 2016 in addition to 2017 and up
  • Added Start/Stop Reporting Services Windows Service tasks
  • Updated Event Log Collection Target Management Server Discovery to make it use default SCOM action profile instead of SQL MP Discovery run as profile
  • Updated display name of SSRS Deployment object to display AG name as part of it instead of GUID
  • Updated logic of installation detection for local Reporting Services instance to query Windows Registry instead of WMI
  • Improved error handling for cases when error “Process with an Id is not running” is returned
  • Updated display strings

Bug Fixes

  • Fixed false alerting with status code 400 in monitor Report Manager Accessible for SSRS 2016 and PBIRS
  • Fixed alert parameter replacement failure in monitor Report Manager Accessible
  • Fixed issue with accessing DBConnectionString property of PBIRS
  • Fixed NullReferenceException error when Report Server portal being configured to have several ports

Loading

SCOM: Updated SQL Management Pack v7.0.20.0

A new version of the sql agnistoc MP is out get it here

What’s New

  • Updated MP to support SQL Server 2019 RTM
  • Added filter by edition to “Local DB Engine Discovery”
  • Redesigned DB Space monitoring to improve performance: Enabled by default
  • monitors and performance rules targeting Database which watch for disk space consumption by ROWS Filegroups and Logfiles
  • Redesigned DB Space monitoring: Added two monitors and two performance rules
  • targeting Database to watch for disk space consumption by In-Memory and
  • FILESTREAM data
  • Redesigned DB Space monitoring: Read-only filegroups now count as well
  • Redesigned DB Space monitoring: Disabled by default all workflows targeting
  • Filegroups, Files, Logfiles
  • Redesigned XTP performance counters to make them completely version-agnostic
  • Added attribute “TCP Port” to “SQL DB Engine Class” and updated “DB Engine
  • Discovery” to populate the new property
  • Added summary dashboard for SCOM 2019 Web Console (HTML5)
  • Added support for cluster nodes with disjoined namespaces
  • Added sampling to algorithm of monitor “WMI Health State” in order to eliminate
  • false alerting on cluster SQL Server instances
  • Updated alert descriptions of monitors “Availability Database,” “Availability Replica,”
  • and “Availability Group” (generating alerts still disabled by default)
  • Updated monitor “Product Version Compliance” with versions of most recent public
  • updates to SQL Server
  • Disabled by default monitor “Buffer Cache Hit Ratio” and changed its threshold
  • from 0% to 90%
  • Disabled by default monitor “Page Life Expectancy”
  • Removed monitors “Availability Database Join State” and “Availability Replica Join
  • State” as not useful
  • Updated display strings
  • Revised columns on DB Engine state views

Bug Fixes

  • Fixed: monitor “Service Principal Name Configuration Status” raises false alerts
  • because of case-sensitive comparison
  • Fixed: “Local DB Engine Discovery” crashes when Windows has Turkish locale
  • Fixed issue that caused performance degradation in workflows “General Always On
  • Discovery,” “Database Replica Discovery,” and “Always On System Policy
  • Monitoring”
  • Fixed: “General Always On Discovery” throws errors on environments with several
  • Distributed Availability Groups
  • Fixed monitoring issue in case of Database is replicated by Always On Availability
  • Group
  • Fixed empty property bag when Availability Group has cluster type NONE
  • Fixed wrong target in alerting rule “DB Backup Failed to Complete”
  • Fixed rule “MSSQL Integration Services on Windows: The package restarted from
  • checkpoint file” and its alert
  • Fixed rule “OS Error occurred while performing I/O on pages“ and its alert
  • Fixed: “DB Disk Write Latency” and “DB Disk Read Latency” monitors and
  • performance rules get wrong performance metric
  • Fixed alert description of monitor “WMI Health State”

Loading

SCOM: Updated SSRS and SSAS management pack v7.0.17.0

Updates to SQL Server Reporting Services and Analysis Services Management Packs have been released you can get them at the below links:

SSRS
SQL Server 2008 Reporting Services (Native Mode)
SQL Server 2012 Reporting Services (Native Mode)
SQL Server 2014 Reporting Services (Native Mode)
SQL Server 2016 Reporting Services (Native Mode)
SSAS
SQL Server 2008 Analysis Services
SQL Server 2012 Analysis Services
SQL Server 2014 Analysis Services
SQL Server 2016 Analysis Services


Fixes and changes for SSRS

  • SSRS 2016: Updated monitoring of Memory Consumption and CPU Usage in order to collect performance data for all subprocesses in addition to the main SSRS service process.
  • SSRS 2016: Updated monitors “Report manager accessible” and “Web service accessible” to consider specifics in how appropriate SSRS 2016 web services work
  • Added support for cases when connection string to SSRS Database is set up the way different from “MachineName\InstanceName” (e.g., <IPAddress,Port>) and SSRS Database is in Availability Group or hosted either by named instance or SQL Cluster instance
  • Improved descriptions of events thrown by management pack to make it easier to figure out error reason
  • Improved error handling and error descriptions for cases when workflows can’t get responses from WMI
  • Improved error handling in workflows that watch for memory consumption
  • Fixed Start/Stop tasks
  • Fixed issue that caused some workflows to throw error events when SQL Server instance hosting SSRS Databases happened to stop operating or refuse connections
  • Fixed issue which caused Reporting Service instance to get undiscovered when WMI didn’t respond to calls
  • Updated display strings

Fixes and changes for SSAS

  • Added performance collections for MDX Query performance counters
  • Added retry logic to some workflows in order to reduce “Category does not exist” errors thrown when WMI fail to respond
  • Improved error handling in workflows that watch for memory consumption
  • Improved descriptions of events thrown by management pack to make it easier to figure out error reason
  • Fixed Start/Stop tasks
  • Fixed issue when “SQLServerInstalled” property was set to “False” if SQL Server DB Engine was of higher version than SSAS instance
  • Updated Dashboards configuration to show tiles for new MDX performance collections
  • Updated display strings

Loading

SCOM: SSRS2017+ MP now with Power BI Reporting v7.0.12.0

An update to SQL Server Reporting Services 2017+ Management Pack is released. This update introduces Power BI Report Server monitoring capabilities to the existing SSRS MP. It considers Power BI Report Server as a special kind of SSRS and provides the same monitoring for PBIRS instances as it does for SSRS instances.

It’s great to see new features like this, Power BI is a powerful tool.

Get it here

Loading

SCOM 1801: Updates management packs for SQL Server v7.0.7.0

New versions of the SQL MPs have been released you can get them using the links below:

SQL Server

SQL Server 2017+ –  here
SQL Server 2016here
SQL Server 2014here
SQL Server 2008-2012here

SSAS

SSAS 2016 – here
SSAS 2014 – here
SSAS 2012 – here
SSAS 2008 – here

SSRS (Native Mode)

SSRS 2016 – here
SSRS 2014 – here
SSRS 2012 – here
SSRS 2008 – here

SQL Server 2017+ MP New Features and Fixes

  • Implemented an ability to monitor SQL Server Cluster instances locally; formerly, it was possible only in Agentless and Mixed modes
  • Added the SSIS monitoring
  • Added the “Exclude List” property in DB Engine Discovery in order to filter instances, which are not subject to monitoring
  • Added the “Exclude List” property in DB Discovery in order to filter databases, which are not subject to monitoring
  • Implemented a feature: both “Exclude List” properties support usage of the asterisk character to make the filter more flexible; e.g. “*temp” is used to exclude instances/databases ending with “temp” and having anything in the beginning
  • Added the “Computers” view
  • Added the “ClusterName” property to the AG class and updated AG alerts in order to display the property within
  • Updated the “SP Compliance” monitor in order to support the Modern Servicing Model for SQL Server: the monitor will check build number instead of Service Pack number
  • Updated the “SPN Status” monitor so that it requires only a single SPN record when only TCP/IP is enabled and the instance is the default one
  • Updated the “Database Backup Status” monitor: it is disabled by default now
  • Updated the DB Space monitors so that their alert descriptions include the actual value of space available
  • Updated the “Configuration Security” section in the guide
  • Fixed issue: The “Database Health Policy” monitor ignores the “Critical” state (on Windows only)
  • Fixed issue: The “Alert severity” property of the “DB File Free Space Left” monitor has incorrect Default Value
  • Fixed issue: The “DB Filegroup Fx Container” rollup monitor has an alert parameter with a wrong value within
  • Fixed issue: “Resource Pool Memory consumption” monitor may not change its state to “Critical” for the “default” resource pool
  • Fixed issue: “Number of Samples” parameter of “Resource Pool Memory consumption” alert displays incorrect data
  • Fixed issue: Missed image resources in the SQL Server 2017+ Core Library
  • Fixed issue: “The agent is suspect. No response within last minutes” alerting rule does not catch appropriate events due to the wrong source

SQL Server 2014-2016 MP New Features and Fixes

  • Updated alert severity in some monitors
  • Updated the display strings
  • Updated the “Max worker thread count” data source of the corresponding monitor and performance rule
  • Fixed issue: In some environments, DB Space workflows fail when a secondary database is non-readable
  • Fixed issue: The “Transaction Log Free Space (%)” monitor does not work
  • Fixed issue: The “The agent is suspect. No response within last minutes” alerting rule does not catch appropriate events due to a wrong source

SQL Server 2008-2012 MP New Features and Fixes

  • Fixed issue: In some environments, DB Space workflows fail when a secondary database is non-readable
  • Fixed issue: The “The agent is suspect. No response within last minutes” alerting rule does not catch appropriate events due to a wrong source
  • Fixed issue: The “DB File Space” monitor in the SQL 2008 management pack throws error events due to the unnecessary $ character in its alert configuration
  • Updated the “Max worker thread count” data source of the corresponding monitor and performance rule
  • Fixed issue: The “Transaction Log Free Space (%)” monitor does not work

SSAS 2008-2016 MP New Features and Fixes

  • Updated the Visualization Library

SSRS 2008-2016 MP New Features and Fixes

  • Added support for cases when a connection string of the SSRS instance to the SSRS Database is not in the “MachineName\InstanceName” format; e.g., “<IP Address>, <Port Number>” or “(local)”, etc. Such connection strings are fully supported for default SQL Server instances hosting the SSRS Database. If the instance is named, workflows targeted at the SSRS Instance object work properly, but those targeted at the Deployment object cannot work, as there is no possibility to learn the FQDN of the server.
  • Updated the Deployment Seed discovery so that it does not check if the SQL Server instance hosting the SSRS Database is running
  • Updated the Visualization Library

Loading