Category Archives: #SQL

SCOM 2016: Updated MPs for SQL Server v7.0.0.0

The new management packs for SQL Server have been released get them at the links below:

SQL Server 2016

SQL Server 2014

SQL Server (2008-2012)

SQL Server Dashboards

 

New SQL Server 2008-2012 MP Features and Fixes

  • The version of the management packs was significantly increased as the SQL Server MP product family was extended with brand new management packs for SQL Server 2017+ and SQL Server 2017+ Replication. The new management packs are designed to monitor SQL Server 2017 and the upcoming versions.
  • Reimplemented Always On workflows to enable monitoring of Availability Groups hosting over 200 databases
  • Updated alert descriptions of Availability Group monitors: added cluster name and primary replica name
  • Implemented 3 alerting rules for events #5105 (error with physical file access), #833 (IO request has taken longer than 15 seconds), and #41144 (AO availability group failed); they are disabled by default
  • Added debug information to Always On monitoring scripts
  • Disabled the alerting rule for event #18456 by default
  • Fixed issue: Invalid encoding of SQL names in Always On console tasks
  • Fixed issue: Invalid encoding of SQL instance names in Always On console tasks

 New SQL Server 2014 MP Features and Fixes

  • The version of the management packs was significantly increased as the SQL Server MP product family was extended with brand new management packs for SQL Server 2017+ and SQL Server 2017+ Replication. The new management packs are designed to monitor SQL Server 2017 and the upcoming versions.
  • Reimplemented Always On workflows to enable monitoring of Availability Groups hosting over 200 databases
  • Updated alert descriptions of Availability Group monitors: added cluster name and primary replica name
  • Implemented 3 alerting rules for events #5105 (error with physical file access), #833 (IO request has taken longer than 15 seconds), and #41144 (AO availability group failed); they are disabled by default
  • Implemented “MSSQL 2014/16: HTTP Storage: Avg. Bytes/Transfer” performance rule
  • Added debug information to Always On monitoring scripts
  • Disabled the alerting rule for event #18456 by default
  • Fixed issue: Invalid encoding of SQL names in Always On console tasks
  • Fixed issue: Invalid encoding of SQL instance names in Always On console tasks

New SQL Server 2016 MP Features and Fixes

  • The version of the management packs was significantly increased as the SQL Server MP product family was extended with brand new management packs for SQL Server 2017+ and SQL Server 2017+ Replication. The new management packs are designed to monitor SQL Server 2017 and the upcoming versions.
  • Reimplemented Always On workflows to enable monitoring of Availability Groups hosting over 200 databases
  • Updated alert descriptions of Availability Group monitors: added cluster name and primary replica name
  • Implemented 3 alerting rules for events #5105 (error with physical file access), #833 (IO request has taken longer than 15 seconds), and #41144 (AO availability group failed); they are disabled by default
  • Added debug information to Always On monitoring scripts
  • Implemented “MSSQL 2014/16: HTTP Storage: Avg. Bytes/Transfer” performance rule
  • Enabled Memory-Optimized workflows for all editions of SQL Server 2016 by default, so far as all editions now support Memory-Optimized Data since SP1
  • Disabled the alerting rule for event #18456 by default
  • Optimized ProbeAction for User Resource Pool Memory workflows
  • Fixed issue: Invalid encoding of SQL names in Always On console tasks
  • Fixed issue: “XTP Configuration” monitor is in healthy state when XTP filegroup container and the transaction log are placed on different volumes of a physical disk
  • Fixed issue: Incorrect publishers’ names for SSIS EventCollection rules (SQL2016)
  • Fixed issue: GetSQL2016DBEngineWinServState.js crashes when getting neither “true” nor “false” as the second parameter (CheckStartupType)
  • Fixed issue: Invalid encoding of SQL instance names in Always On console tasks
  • Fixed issue: XTP Configuration monitor is always in Warning state for SQL Server 2016 Standard edition

SQL Server Dashboards MP

  • No changes introduced; increased the version number to 7.0.0.0 to comply with the current version of SQL Server Management Packs

SCOM 2016: Updated MP for SQL 2017+

Hot off the press an updated management pack for monitoring of SQL 2017 and all future versions of SQL get version 6.7.65.0 here

New in this version:

  • The management pack was reimplemented in order to enable monitoring SQL Server 2017 and all upcoming SQL Server versions
  • Introduced a number of fixes and improvements to functionality, performance, and display strings
  • Reduced the number of files in the management pack
  • Removed dependency on SQL Server Dashboards management pack

SCOM 2016: Updated SQL Server Management Packs v6.7.31.0

New versions of the SQL management packs were released a short while ago links for all of the versions 6.7.31.0 MPs can be found below:

SQL Server

SQL Server 2008/2012 – here
SQL Server 2014 – here
SQL Server 2016 – here

SQL Server Dashboards

SQL Server Dashboards – here

SQL Server Replication

SQL Server 2008 Replication – here
SQL Server 2012 Replication – here
SQL Server 2014 Replication – here
SQL Server 2014 Replication – here

SQL Server Analysis Services

SQL Server 2008 Analysis Services – here
SQL Server 2012 Analysis Services – here
SQL Server 2014 Analysis Services – here
SQL Server 2016 Analysis Services – here

SQL Server Reporting Services (Native Mode)

SQL Server 2008 Replication(Native Mode) – here
SQL Server 2012 Replication(Native Mode) – here
SQL Server 2014 Replication(Native Mode) – here
SQL Server 2014 Replication(Native Mode) – here

 

New in this version:

Important Notes About This Release

  • Please note that we implemented a new alerting rule for SQL Server event #18456 “Login failed” in this release. However, this rule may become rather noisy due to the fact that the target events appear quite frequently in some environments. The rule is enabled by default; thus, if it creates too many alerts in your environment, consider disabling it via the override. The ideal would be to resolve the underlying issues causing the alert but we understand this is not always a priority.
  • We are introducing a new monitor in this release (see the first bullet below) based on customer feedback. You have now a better solution for monitoring backups of AG databases.

SQL Server 2008-2016

  • Added new “Availability Database Backup Status” monitor in Availability Group to check the existence and age of the availability database backups (this monitor is disabled by default)
  • “Database Backup Status” monitor has been changed to return only “Healthy” state for the databases that are Always On replicas, since availability database backups are now watched by the dedicated monitor
  • Improved performance of DB Space monitoring workflows
  • Added new “Login failed” alerting rule for SQL Server event #18456
  • Fixed issue: “Active Alerts” view does not show all alerts
  • Fixed issue: DB space monitoring scripts fail with “Cannot connect to database” error.
  • Fixed issue: PowerShell scripts fail with “Cannot process argument because the value of argument ‘obj’ is null” error
  • Fixed issue: Alert description of “Disk Ready Latency” and “Disk Write Latency” monitors displays the sample count instead of the performance value that was measured
  • Fixed issue: Different file location info from “sys.master_files” and “sysfiles” causes error when Availability Group secondary database files are in different path
  • Fixed issue: “DB Transaction Log Free Space Total” rules return wrong data
  • Introduced minor updates to the display strings
  • Deprecated “Garbage Collection” monitor and the appropriate performance rule
  • Resource Pool Discovery is disabled by default for pools not containing databases with Memory-Optimized Tables
  • “XTP Configuration” monitor now supports different file path types (not only those starting with C:, D:, etc.)
  • Fixed issue: “Resource Pool State” view shows incorrect set of objects
  • Fixed issue: Invalid group discovery in SQL Server 2016 Always On
  • Updated the visualization library

SQL Server Dashboards

  • Increased the version number to comply with the current version of SQL Server MPs

SQL Server 2008-2016 Replication

  • Added Distributor name caching to Subscription discovery
  • Restricted the length of some string class properties
  • Improved the internal structure of SQL scripts storage
  • Fixed variable types in SQL scripts
  • Fixed connectivity issues in SmartConnect module
  • Introduced minor updates to the display strings
  • Updated the visualization library

SQL Server 2008-2016 Reporting Services

  • Reimplemented Instance seed discovery: replaced the managed module with a PowerShell script
  • Reimplemented Deployment seed discovery: added a retry policy and improved error handling
  • Updated the visualization library

SQL Server 2008-2016 Analysis Services

  • Restricted length of some string class properties
  • Updated the visualization library

SCOM 2016: Updated SQL MPs 6.7.20.0

New SQL management packs were released recently, get them at the links below:

SQL Server 2008/2012 – here
SQL Server 2014  – here
SQL Server 2016 – here

Updates and fixes

SQL Server 2008/2012

  • Fixed issue: GetSQL20XXSPNState.vbs fails when domain controller is Read-Only
  • Fixed issue: SQL ADODB “IsServiceRunning” function always uses localhost instead of server name

Dashboards

  • No changes introduced; the version is increased to comply with the current version of SQL Server MP

XPost: SCOM 2016 Now supports SQL 2012 SP3

Additional support is now available for SCOM 2016 for SQL 2012 SP3. this will more easily facilitate the in-place upgrade path from SCOM 2012 as previously you would need your environment to be using a SQL version supported by both SCOM versions  (SQL 2014 SP2)

Kevin Holman has a nice article hi-lighting the different supported versions you can find it here

SCOM: Updated SQL Server Management Packs v6.7.7.0

New versions of the SQL Server management packs are out! They are available from the below links:

Note: Be aware that there have been some serious issues with previous versions of the SQL management packs this year. I cannot stress enough the need for testing before using these in a live environment.

SQL Server 2016: here
SQL Server 2014: here
SQL Server 2008 / 2012: here
SQL Dashboards: here

New in this version

SQL Server 2008 / 2012

  • Fixed issue: SQL Server 2012 Always On discoveries fail after stopping WSFC service
  • Fixed issue: “Set DB offline” task does not work when the database is in Availability Group
  • Fixed issue: user policy discovery script fails with “Invalid namespace “ROOT\Microsoft\SqlServer\ComputerManagement12″‘ error
  • Fixed issue: Always On console task does not work
  • Updated the visualization library

SQL Server 2014 / 2016

  • Fixed issue: “Set DB offline” task does not work when the database is in Availability Group
  • Fixed issue: Always On console task does not work
  • Updated the visualization library

Dashboards

  • Fixed issue: expanding “arrow” has low-contrast color when a health group is collapsed in Instance view (High-Contrast #2 color scheme)
  • Fixed issue: in Web console, Dashboards continuously send requests to the database
  • Fixed issue: not the first object gets selected in the object list after drill-down
  • Fixed issue: horizontal scroll position resets after refreshing the Instance view
  • Fixed issue: “No Data” message is displayed on some tiles after upgrading Dashboards management pack from version 6.7.2.0 to 6.7.4.0 or later version
  • Fixed issue: on Web console, Dashboards crash when drilling down from Datacenter view to Instance view
  • Restored the correct group order in SQL Server Summary dashboard views

SCOM: MP update SQL (Community Technical Preview 2) v6.7.5.0

Technical preview 2 of the new SQL management packs are out for preview, get them here.

Updates:

Dashboards

  • Improved Dashboards performance
  • Fixed issue: Regular\Virtual group tile will show 0 objects if user adds a new group before the previous one has been saved
  • Fixed issue: virtual group filtering for Generic Distributor does not work
  • Fixed issue: tooltips on some menus appear in unexpected places
  • Fixed issue: Web Console is crashing upon right-clicking the Hamburger menu
  • Fixed issue: button captions are cropped in some dashboard localization packs
  • Fixed issue: instance dashboard displays data for the item that is the first in the list if no items appear after applying a filter
  • Fixed issue: the first group object is not in focus after clearing “Filter” field if there were no search results
  • Fixed issue: Summary dashboards appear to be in a strange state if one installs only Dashboards MP without SQL MPs
  • Fixed issue: Bulk Add Tiles does not trigger the refresh action
  • Fixed issue: object and alert counters of Virtual groups containing real groups display zeros
  • Fixed issue: an error appears in Web Console when using keyboard for navigation in “Add Aggregated Monitor” dialog
  • Fixed issue: unexpected scrolling behavior in the instance view when using mouse scroll
  • Fixed issue: markup of the dialog for adding classes gets broken when the scrollbars are displayed
  • Fixed issue: wrong width value of the Edit dialog
  • Fixed issue: there is no refresh action after saving the edited Virtual Group configuration
  • Fixed issue: white-screen exception when working with a dialog in Web Console
  • Fixed issue: datacenter and instance queries fail when a special set of classes is selected

SQL Server 2016 / 2014 / 2012 /2008 / 2005

  • Added support for configurations where computer host names are longer than 15 symbols
  • Added “Event ID” to descriptions of all the alerts generated by the alerting rules
  • Deprecated “Run As Account does not exist on the target system, or does not have enough permissions” rule
  • Added 2 rules for alerts generating when there are problems with execution of the monitoring workflows scripts on the following agents: “MSSQL: Monitoring failed” and “MSSQL: Monitoring warning”
  • Added “MSSQL 2016: Discovery warning” rules to generate alerts when there are non-critical problems with execution of the discovery scripts (warning events in the Operations Manager log)
  • Changed “MSSQL 2016: Discovery failed” rules to generate alerts for only critical errors during executing discovery scripts
  • Improved error logging in the MP scripts
  • Fixed some issues in the scripts, which could lead to unstable work with WMI
  • Fixed the issue when not all available performance counters were presented at “All Performance Data” view in Memory-Optimized Data sub-folder
  • Added a new overridable parameter to monitor “Stale Checkpoint File Pairs Ratio” in order to ignore databases having the number of checkpoint file pairs less than the threshold (300 by default)
  • Made “Resources Pool Memory Consumption (rollup)” enabled by default
  • Updated the visualization library

SCOM: SQL Server MP (Community Technical Preview 1) v6.7.3.0

Another update for the SQL Community Technical Preview management packs this time we have updates for the SQL Server MPs and no changes to the dashboards. Get the MPs here.

SQL Server 2014 / 2016:

  • Disabled Memory-Optimized Data Garbage Collection Fill Factor monitor and rule by default
  • Disabled Memory-Optimized Data workflows for all SQL Server instances, except for Developer, Enterprise, and Evaluation editions
  • Fixed issue: CPU Usage monitor & rule did not work for SQL Server cluster instance
  • Fixed issue: connection to an SQL Server instance was not closing when the destination was wrong
  • Made detection condition stricter for DB User Policy event-based discovery: added management group name
  • Made detection condition stricter for Script Failed alerting rule: added management group name

SQL Server 2008 / 2012

  • Added support for databases stored on SMB Shares
  • Fixed error logging in a script for Blocking Sessions monitor
  • Removed from the installer SQL 2005 MP files as no longer supported
  • Fixed issue: CPU Usage monitor & rule did not work for SQL Server cluster instance
  • Fixed issue: connection to an SQL Server instance was not closing when the destination was wrong
  • Fixed Non-Readable Replica detection (Always On)
  • Made detection condition stricter for DB User Policy event-based discovery: added management group name
  • Made detection condition stricter for Script Failed alerting rule: added management group name

SCOM: SQL MP (Community Technical Preview 2) v6.7.0.0

Version 6.7.0.0 of the Community Technical Preview 2 for the SQL management packs have been released, they are available for download here.

Microsoft seems to be very cautions now with releases of the SQL MP which is good considering the issues with the last 4 live versions,

What’s new?

New Releases
MP for SSAS 2016
MP for SSRS 2016 (Native Mode)

Updates and fixes

SQL Server Dashboards

  • Added a feature to support Virtual Groups (groups defined by classes, not by real group instance). This will help users with partial access to use our predefined dashboards.
  • Added a feature to Bulk Add tiles from the class definition.
  • Added a feature to ignore some states while calculating the worst state for the Datacenter View State Tile; added a feature to set this ignorance up for each group
  • Added a feature to show/hide Instance path in the list on the Instance view
  • Added instance path to the Instance Details
  • Implemented deferred loading of the instance tiles to make the instance view operation faster
  • Implemented a new format for Dashboards configuration providing a smaller size and a single binding
  • Implemented a converter from old to new format providing preservation of user changes
  • Improved error reporting
  • Improved performance of initial load process
  • Improved data sources performance
  • Improved performance of instance selection (made it asynchronous)
  • Improved performance of Datacenter and Instance View Tiles, loading and loaded animations
  • Fixed animation issues and minor visual glitches (positioning, size of elements, fonts, main loading animation)
  • Updated Summary dashboard
  • “Known issues and Troubleshooting” section of the guide is updated

SQL Server 2016 / 2014

  • Fixed Smart Admin policies monitoring
  • Fixed Always On Database replica discovery incorrect behavior; fixed Always On policies discovery and monitoring
  • Fixed Database policies discovery and monitoring
  • Fixed and optimized CPU Usage monitoring scripts (the issue appeared when only one core was assigned)
  • Added support for more than 32 processors count in CPU Usage monitoring.
  • SQLPS module is now used for the tasks instead of deprecated SQLPS.EXE
  • Implemented FILESTREAM filegroup monitoring
  • FILESTREAM is now supported on the summary dashboard
  • Multiple Ports are now supported in SQL Server TCP/IP parameters
  • Fixed error occurring when no port is specified in SQL Server TCP/IP parameters
  • Fix filegroup read-only state discovery
  • Fixed RunAs profiles mapping for some workflows
  • Added support for new SQL Server 2016 Memory-Optimized Data implementation
  • Implemented support for TLS 1.2 in connection logic
  • Implemented support for different client drivers in connection logic
  • Updated connection logic error logging
  • Updated Availability Group Automatic Failover monitor
  • Added rules to collect new Always On performance counters
  • Updated SMO usage in Always On workflows to support the new connection logic
  • Upgraded the used PowerShell version to 3+, since SQL Server 2016 uses .Net 4 Runtime
  • Fixed issue: CPU usage monitor ignored SQL server limitations on CPU core count
  • Fixed display strings and Knowledge Base articles
  • Fixed error reporting in the scripts
  • Fixed threshold percentage comparison of deactivated subscriptions workflow in Distributor.

SQL Server 2016 / 2014 / 2012 / 2008 Replication

  • Reviewed threading implementation
  • Fixed issue: Distributor Discovery could fail for Replication
  • Fixed error logging, added more details
  • Fixed “Availability of the Distribution database” monitor issue: added missing overridable parameter
  • Assigned SDK profile to the Replication Database Health Discovery
  • Fixed replication monitoring issue: compatibility with SQL 2005 as a part of Replication
  • Fixed Publisher Discovery issue: unable to cast object of type ‘System.DBNull’ to type ‘System.String’
  • Added timeout support for .Net modules
  • Fixed issue: SQL Server Event Log Collection Target was not discovered Associate due to invalid path in DataSource
  • Fixed Replication Agent State monitor type cookdown issue
  • Fixed issue: failing replacement warning in “One or more of the Replication Agents are retrying on the Distributor” monitor
  • Dashboards fixes: added bindings to the tiles; added Datacenter Classes (L1 dashboard for Replication)
  • Fixed upgradeability
  • Fixed Knowledge Base articles and display strings
  • Updated Known Issues and Troubleshooting section of the guide

SCOM: SQL Server MP Community Technical Preview 1

A Community Technical Preview has been released for all of the SQL server versions and components they are available for download here. This MP is accompanied by a refresh of the SQL MPs released earlier this month v6.6.7.6, could this be an attempt to improve QA and testing on management pack releases, especially considering the issues with the SQL MP over the past year.

The new features and fixes introduced in CTP1 management packs are as follows:

    New Analysis Services 2016 MP Features and Fixes

    • Updated references and removed deprecated elements to support 6.6.4.0+ visualization library
    • Fixed bug when SSAS Monitoring Pack could not collect OS performance counters with localized names
    • Updated discoveries to throw errors in case of some problems detected during the discovery
    • Fixed module error collection on cluster instances to reduce the noise
    • Changed defaults for CPU Usage monitor, added sampling to Memory Usage on the server monitor
    • Reduced inner complexity of modules
    • Fixed the discoveries; now the last items can be undiscovered
    • Simplified the dashboard to make it quicker and more informative
    • Reviewed and updated the Knowledge Bases
    • Added timeout support for every non-native workflow
    • Fixed Blocking Session Monitor – in some situations it could calculate blocking sessions incorrectly
    • Win10 support: fixed OS version detection issue
    • Fixed CPU usage alert description
    • “Known Issues and Troubleshooting” section of the guide is updated

    New Reporting Services 2016 MP Features and Fixes

    • Fixed issue: Microsoft System Center Management Pack for SQL Server Reporting Services doesn’t discover SSRS instance when the instance name starts from some certain symbols (0-9, A-F)
    • Instance configuration monitor is introduced
    • Implemented a better logging system
    • Improved the Report Manager availability monitor: now it parses human readable message to get the real status of the Report Manager
    • Improved the Report Manager and Web Service availability monitors: now the user can treat some status codes as healthy via the override.
    • Fixed the discoveries, now the last items can be undiscovered
    • Added timeout support for every non-native workflow
    • Simplified the dashboard to make it quicker and more informative
    • Reviewed and updated the Knowledge Base articles
    • Improved Run As mappings
    • Updated the discoveries to throw errors in case of some problems detected during the discovery
    • Updated the references and removed deprecated elements to support 6.6.4.0+ visualization library
    • Changed the performance rules write actions accounts to “Default Action account”
    • “Known Issues and Release Notes” section of the guide is updated
    • Fixed “Memory consumed by other processes (%)” rule having no Run As profile for DS; added SQL MP default Run As to the data source
    • Fixed “Failing replacement: $Data/Context/Property[@Name='<MonitorName>’]$” issue in OM log

    New SQL Server 2005-2012 MP Features and Fixes

    • Fixed intermittent “Cannot login to database” alert with some rules
    • Added support for SQL Express Instances
    • Updated Knowledge Base articles
    • Microsoft SQL Server 2012 x86 on Windows 2008 R2: fixed the issue when DB file groups cannot be discovered
    • Win10 support: fixed “Cannot bind argument to parameter ‘Path’ because it is an empty string.” issue
    • Fixed issue when SQL Configuration Manager starts snap-in of wrong version
    • Fixed invalid Always On non-readable replica detection

    New SQL Server 2014-2016 MP Features and Fixes

    • Fixed intermittent “Cannot login to database” alert with some rules
    • Fixed display string for AgentsGroup.Discovery
    • Added support of SQL Server Express Instances
    • Always On issue: Events “967” are no longer fired for File Group and Files discoveries when server has a non-readable DB
    • DB Discovery issue fixed: masterDB is to be used if the target is inaccessible
    • DBSize issue: filter < 0 values in provider, return data for fileSize
    • Win10 support: fixed “Cannot bind argument to parameter ‘Path’ because it is an empty string.” issue
    • Agent Job Discovery is now disabled by default
    • Fixed issue when SQL Configuration Manager starts snap-in of wrong version
    • Fixed invalid Always On non-readable replica detection
    • Updated summary dashboards