Partager via

New MP: Microsoft System Center Management Pack for SQL Server 2016

  • Article

 

 

Microsoft System Center Management Pack for SQL Server 2016 enables the discovery and monitoring of SQL Server 2016 Database Engines, Databases, SQL Server Agents, and other related components.

 

Download HERE

 

Note: There are multiple files available for this download.Once you click on the "Download" button, you will be prompted to select the files you need.

 

image

 

    • Microsoft System Center Management Pack for SQL Server 2016 enables the discovery and monitoring of SQL Server 2016 Database Engines, Databases, SQL Server Agents, and other related components. This Management Pack is designed to run by Operations Manager 2007 R2 (except dashboards), Operations Manager 2012 SP1, or Operations Manager 2012 R2.
    • The monitoring provided by this management pack includes performance, availability, and configuration monitoring, as well as performance and events data collection. All monitoring workflows have predefined thresholds and complimentary knowledge base articles. You can integrate the monitoring of SQL Server 2016 components into your service-oriented monitoring scenarios.
    • In addition to health monitoring capabilities, this management pack includes dashboards, diagram views, state views, performance views, alert views and diagnostic tasks that enable near real-time diagnostics and remediation of detected issues.
    • Note:
    • This management pack does not depend on Microsoft SQL Server Library management pack. You do not need to import it to enable the monitoring of SQL Server 2016.
    • Note:
    • If you are going to localize this management pack, keep in mind that localization installers on this page do not include files for localizing SQL Server Dashboards. Please use
    • this
    • link to download the required localization for Dashboards.
    • Feature Summary
    • The following list gives an overview of the features introduced by Microsoft System Center Operations Manager Management Pack for SQL Server 2016. Please refer to Microsoft SQL Server 2016 Management Pack Guide for more details.
<!-- end list -->

  - Discovery and monitoring of SQL Server 2016 roles like DB Engine, Integrations Services.
  - Discovery of SQL Server 2016 components: Databases, SQL Agent and SQL jobs.
  - The management pack introduces 100+ monitors and 380+ rules to provide a deep monitoring of SQL Server 2016. Please refer to Microsoft SQL Server 2016 Management Pack Guide for the complete list of monitoring scenarios and workflow inventory.
  - Monitoring of SQL Server 2016 Always On:
      - Automatic discovery and monitoring of availability groups, availability replicas, and availability databases.
      - Health roll-up from availability database to availability replicas.
      - Detailed knowledge for each related monitoring workflow.
  - Seamless integration with SQL Server 2016 policy based management (PBM):
      - Automatic discovery and monitoring of custom PBM polices targeted to Database or Database components.
      - Rollup of policy execution health to the health of related SCOM entity.
  - Monitoring of SQL Server Memory-Optimized Tables.
  - Low-privilege monitoring is supported for both stand-alone installations and clustered environments, except PBM.
  - Support for Mirroring Monitoring
      - Discover mirroring databases, witness, and mirroring group.
      - Monitor database mirror state, database mirror witness state, and mirroring partners’ state.
      - Custom diagram view to represent the primary and the mirrored databases visually.

**New Features and Fixes**

  - 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
  - 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)
  - Updated the visualization library
  - 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 issue: "Set DB offline" task does not work when the database is in Availability Group
  - Fixed issue: Always On console task does not work
  - Deprecated "Run As Account does not exist on the target system, or does not have enough permissions" rule
  - 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
  - Made "Resources Pool Memory Consumption (rollup)" enabled by default
  - Disabled Memory-Optimized Data Garbage Collection Fill Factor monitor and rule by default
  - Disabled Memory-Optimized Data workflows for all SQL Server editions that do not support Memory-Optimized Data feature
  - Fixed issue: CPU Usage monitor and 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 issue: in some cases, Set Database Online/Offline tasks did not work for databases of SQL Server cluster instance
  - 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

System Requirements

Supported Operating System

Windows Server 2012, Windows Server 2012 R2

  • For detailed system requirements refer to the management pack guide.

Install Instructions

  • For detailed instructions on setup, configuration, and monitoring refer to the management pack guide.
Files in this Management Pack

The Management Pack for Microsoft SQL Server 2016 includes the following files:

File

Description

Microsoft.SQLServer.2016.Discovery.mp

This Management Pack discovers Microsoft SQL Server 2016 and related objects. The management pack only contains the discovery logic and requires the separate monitoring management pack to be imported to monitor the discovered objects.

Microsoft.SQLServer.2016.Monitoring.mp

This Management Pack enables the monitoring of Microsoft SQL Server 2016. It depends on the Microsoft SQL 2016 (Discovery) Management Pack.

Microsoft.SQLServer.2016.Presentation.mp

This Management Pack adds SQL Server 2016 Dashboards.

Microsoft.SQLServer.2016.Views.mp

This Management Pack contains views and folder structure for Microsoft SQL Server 2016 management packs.

Microsoft.SQLServer.2016.Always On.Discovery.mp

This Management Pack discovers objects required for monitoring of Microsoft SQL Server 2016 Always On functionality. It contains only discovery logic and requires the separate monitoring management pack to be imported to enable monitoring.

Microsoft.SQLServer.2016.Always On.Monitoring.mp

This Management Pack enables the monitoring of Microsoft SQL Server 2016 Always On functionality. It depends on the Microsoft SQL 2016 Always On (Discovery) Management Pack.

Microsoft.SQLServer.2016.Always On.Views.mp

This Management Pack contains views and folder structure for Microsoft SQL Server 2016 Always On management packs.

Microsoft.SQLServer.2016.IntegrationServices.Discovery.mp

This Management Pack discovers Microsoft SQL Server 2016 Integration Services. It contains discovery logic and requires the separate monitoring management pack to be imported to monitor the discovered objects.

Microsoft.SQLServer.2016.IntegrationServices.Monitoring.mp

This Management Pack enables monitoring of Microsoft SQL Server 2016 Integration Services.

Microsoft.SQLServer.2016.IntegrationServices.Views.mp

This Management Pack contains views and folder structure for Microsoft SQL Server 2016 Integration Services management packs.

Microsoft.SQLServer.Generic.Presentation.mp

This Management Pack defines common folder structure and views.

Microsoft.SQLServer.Generic.Dashboards.mp

This Management Pack contains generic SQL Server dashboards.

Microsoft.SQLServer.Visualization.Library.mpb

This Management Pack contains base visual components required for SQL Server dashboards.

Microsoft.SQLServer.2016.Mirroring.Discovery.mp

This Management Pack discovers objects required for monitoring of Microsoft SQL Server 2016 Mirroring functionality. It contains only discovery logic and requires the separate monitoring management pack to be imported to enable monitoring.

Microsoft.SQLServer.2016.Mirroring.Monitoring.mp

This Management Pack enables the monitoring of Microsoft SQL Server 2016 Mirroring functionality. It depends on the Microsoft SQL 2016 Mirroring (Discovery) Management Pack.

Microsoft.SQLServer.2016.Mirroring.Views.mp

This Management Pack contains views and folder structure for Microsoft SQL Server 2016 Mirroring management packs.

 

 

Mandatory configuration

To configure Management Pack for Microsoft SQL Server 2016 complete following steps:

  • Review the “Configuring the Management Pack for Microsoft SQL Server 2016” section of this guide.
  • Grant required permissions as described in “Security Configuration” section of this guide.
  • Enable the Agent Proxy option on all agents that are installed on servers that are members of the cluster. It is not necessary to enable this option for standalone servers. For more information about enabling Agent Proxy option see “How to enable Agent Proxy option” section of this guide.
  • Import the Management Pack.
  • Associate SQL Server 2016 Run As profiles with accounts that have appropriate permissions. For more information about configuring Run As profiles see “How to configure Run As profile” section of this guide.