Category Archives: #SQL

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

Leave a reply

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

Loading

SCOM: SQL Server MP Community Technical Preview 1

2 Replies

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

Loading

SCOM: SQL Dashboards workaround for slow performance

2 Replies

Microsoft has finally officially recommended a workaround that some of us have been using for some time to keep the SQL dashboards in a usable state.

Dashboards may work slowly if used rarely

Issue: When used rarely or after a long break, the dashboards may work rather slowly due to large amounts of the collected data to be processed; especially, it is related to large environments (2000+ objects).

Resolution: Below is a “warming up” script, which may be used to create an SQL job to run on some schedule. Before scheduling it as an SQL job, please test how long these queries will be executing (if you will schedule it to run too often or execution time is too long, that may kill the performance). If you have dashboards with thousands of objects to load, then time to load the content will be 10+ seconds anyway. It was tested with 600 000 objects, and the dashboard loading time was 1-2 minutes.

USE [OperationsManagerDW]

EXECUTE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateLastValues]

EXECUTE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateHierarchy]

It is also worth noting that the following versions of SQL Server Management Pack are considered as deprecated and suspended:

  • 1.314.35
  • 1.400.0
  • 3.173.0
  • 3.173.1
  • 4.0.0
  • 4.1.0
  • 5.1.0
  • 5.4.0
  • 6.0.0
  • 6.2.0
  • 6.3.0

Loading

SCOM: Updated SQL MPs for Dashboards, SSAS and SSRS version 6.6.7.6

1 Reply

Several SQL Management packs have been updated, version 6.6.7.6 for each of them can be found at the following locations:

As usual treat new management packs with caution, especially considering the recent issues with SQL and Base OS ones and test, test, test!

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

SQL Server 2014 Reporting Services (Native Mode) – here
SQL Server 2012 Reporting Services (Native Mode) – here
SQL Server 2008 Reporting Services (Native Mode) – here

Management Pack for SQL Server Dashboards – here

 

Updated in this version

Dashboards

  • Fixed “The client has been disconnected from the server. Please call ManagementGroup.Reconnect() to reestablish the connection” exception.
  • Replaced 999+ presentation with a new one (26.2k)
  • Added configurable rule to pre-aggregate data in the DW (see the corresponding Known Issue)
  • Added second row in the instance view to show Path of the object, and allow searching by Path.
  • Fixed tooltip presentation on the Instance view

“Known issues and Troubleshooting” section of the guide is updated

SQL Server Reporting Services

  • 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

SQL Server Analysis Services

  • 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

 

Loading

SCOM: Updated SQL MP 6.6.4.0

Leave a reply

Microsoft released another update for the SQL management packs version 6.6.4.0 can be downloaded from the below links:

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

This version refers to additional updates to the visualization library:

  • Fixed an installation issue on SQL Server 2008
  • Fixed permission grants for Alert aggregation table

With the previous issues with these management packs I strongly recommend testing  thoroughly before importing them into production and also having a good rollback plan in the case of an issue.

Loading

SCOM: Updated MPs for SQL Server v 6.6.3.0

Leave a reply

Microsoft released updates for the SQL Server management packs:

It is strongly recommended to test thoroughly before importing into live due to the sever issues some people have experienced with previous versions of 6.6.x.

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

This update:

  • Updated the visualization library
  • Implemented batching to all data aggregation mechanisms to ensure low temp database space and log space usage
  • Implemented a control bit to divide group from non-group references to save space in aggregated data storage.
  • Fixed the error message in SQL DB discovery script

Loading

SCOM: SQL Server MP updates v6.6.2.0

7 Replies

Microsoft has just released version 6.6.2.0 of the SQL management packs listed below. This updated contains fixes to many of the issues plaguing the previous version, of course test before using in your production environments.

SQL Server 2014 Replication – download here
SQL Server 2012 Replication – download here
SQL Server 2008 Replication – download here
SQL Server 2014 – download here
SQL Server 2012/2008/2003 – download here

Updates and Fixes in this version:

First the dashboards which had many issues in the original release.

  • Improved performance
  • Objects with selected monitors are now displayed on the top of the tiles list while editing
  • If there are no child elements, Related Objects tile is not displayed in Instance View.
  • Added Dark, Light and Contrast themes.
  • Introduced an interface upgrade allowing to display the Dashboard errors
  • User rights are now considered in the Dashboard display: the user can see the groups according to his/her access level only; Read-Only mode is implemented, it provides basic functions only: navigation, changing of the personalization settings
  • Fixed some UI errors, UI design upgraded
  • Fixed synchronization errors of the personalization settings.
  • Introduced a Dashboard feature allowing to display data from any nesting level within the widgets

SQL Server 2012/2008/2003

  • Added a support for disabled TCP/IP protocol
  • Fixed performance metrics error that may occur on some localized versions of Windows
  • Fixed bugs in monitor tiles on SQL Server Summary Dashboard
  • Fixed incorrect performance of Transaction log free space monitor
  • Added new type of events from failed discoveries; added a new rule that collects such events
  • Added overrides to prevent various scripts timeout failure
  • Removed some 1X1 tiles from Summary Dashboards
  • Filestream filegroups are excluded from discovery for now
  • 2008/2012 Summary Dashboards tiles were reorganized

SQL Server 2014

  • Fixed performance
  • Added a support for disabled TCP/IP protocol
  • Fixed performance metrics error that may occur on some localized versions of Windows
  • Fixed incorrect performance of Transaction log free space monitor
  • Added new type of events from failed discoveries; added a new reporting rule that collects such events
  • Added overrides to prevent various scripts timeout failure
  • Removed some 1X1 tiles from Summary Dashboards
  • Filestream filegroups are excluded from discovery for now
  • 2008/2012 Summary Dashboards tiles were reorganized
  • Added KB for Microsoft.SQLServer.2014.Mirroring.MirroringCommonGroupDiscovery
  • Summary Dashboard: added monitor/performance tiles to class “SQL Server 2014 Mirroring Groups”
  • Some minor fixes

SQL Server Replication MPs

  • Preset dashboards are updated

Loading

XPost: What SQL maintenance should I perform on my SCOM 2012 databases?

Leave a reply

Kevin Holman has updated his article on which SQL maintenance to perform on your SQL databases to now cater for the SCOM 2012 data structures.

The article which is available here and makes special mention of the types of maintenance which is run by SCOM itself which does not need to be handled by a SQL DBA.

Loading

SCOM: Updated SQL management packs v 6.6.0.0

Leave a reply

Microsoft has released updates of all of their SQL management packs to version 6.6.0.0, they are available for download at the links below, a brief what’s new is below the download links:

SQL Server 2005, 2008, 2008 R2, and SQL Server 2012 – Here
SQL Server 2014 – Here
SQL Server 2008 Analysis Services – Here
SQL Server 2012 Analysis Services – Here
SQL Server 2014 Analysis Services – Here
SQL Server 2014 Reporting Services (Native Mode) – Here
SQL Server 2012 Reporting Services (Native Mode) – Here
SQL Server 2008 Reporting Services (Native Mode) – Here

What’s new in 6.6.0.0

All of the management packs have had their dashboards replaced with new ones and the SQL Server management packs have the following additional updates:

newsqldashboard

SQL Server 2005, 2008, 2008 R2, and SQL Server 2012 v 6.6.0.0

  • Dashboards were replaced with the new ones
  • Components of replication functionality are deprecated and disabled by default
  • SPN monitor now correctly handles disjoined namespaces
  • Added support for filegroups containing filestreams and partition schemes
  • Memory Consumption monitor has been fixed
  • Upgradeability from 6.4.1.0 version is supported
  • Added CPU Usage monitor and rule for SQL Server 2005
  • Added ConsecutiveSamples Condition to the Buffer Cache Hit Ratio and Page Life Expectancy monitors
  • AlwaysOn discovery was reworked
  • Minor fixes.

SQL Server 2014 v 6.6.0.0

  • Dashboards were replaced with the new ones
  • SPN monitor now correctly handles disjoined namespaces
  • Added support for filegroups containing filestreams and partition schemes
  • Localization packs now works with the MP
  • Memory Consumption monitor has been fixed
  • Upgradeability from 6.4.1.0 version is supported
  • Added ConsecutiveSamples Condition to the Buffer Cache Hit Ratio, Page Life Expectancy, Transaction Log Free Space (%) and Resource Pool Memory Consumption monitors
  • AlwaysOn discovery was changed
  • Minor fixes.

sqlserver

Loading

SCOM 2012 R2: Support for SQL Server 2008 R2 SP3

Leave a reply

Over at the MS SCOM engineering Blog it’s been newly released that SCOM 2012 R2 now supports SQL Server 2008 R2 SP3.

There is a catch though see below for what to be aware of:

Issue: Fresh Install of SCOM 2012 R2 on SQL 2008 R2 SP3 will not work
Workaround: Deploy SCOM 2012 R2 on to SQL 2008 R2 SP2 and after installation upgrade the DB to SQL 2008 R2 SP3

Loading