Monitoring of Availability Groups (SQL Server)
Updated: June 8, 2016
Applies To: SQL Server 2016
To monitor the properties and state of an Always On availability group you can use the following tools.
|System Center Monitoring pack for SQL Server||The Monitoring pack for SQL Server (SQLMP) is the recommended solution for monitoring availability groups, availability replica and availability databases for IT administrators. Monitoring features that are particularly relevance to Always On Availability Groups include the following:|
Automatic discoverability of availability groups, availability replicas, and availability database from among hundreds of computers. This enables you to easily keep track of your Always On Availability Groups inventory.
Fully capable System Center Operations Manager (SCOM) alerting and ticketing. These features provide detailed knowledge that enables faster resolution to a problem.
A custom extension to Always On Health monitoring using Policy Based management (PBM).
Health roll ups from availability databases to availability replicas.
Custom tasks that manage Always On Availability Groups from the System Center Operations Manager console.
|To download the monitoring pack (SQLServerMP.msi) and SQL Server Management Pack Guide for System Center Operations Manager (SQLServerMPGuide.doc), see:|
System Center Monitoring pack for SQL Server
|Transact-SQL||Always On Availability Groups catalog and dynamic management views provide a wealth of information about your availability groups and their replicas, databases, listeners, and WSFC cluster environment.||Monitor Availability Groups (Transact-SQL)|
|SQL Server Management Studio||The Object Explorer Details pane displays basic information about the availability groups hosted on the instance of SQL Server to which you are connected.|
** Tip ** Use this pane to select multiple availability groups, replicas, or databases and to perform routine administrative tasks on the selected objects; for example, removing multiple availability replicas or databases from an availability group.
|Use the Object Explorer Details to Monitor Availability Groups (SQL Server Management Studio)|
|SQL Server Management Studio||Properties dialog boxes enable you to view the properties of availability groups, replicas, or listeners and, in some cases, to change their values.||- View Availability Group Properties (SQL Server)|
- View Availability Replica Properties (SQL Server)
- View Availability Group Listener Properties (SQL Server)
|System Monitor||The SQLServer:Availability Replica performance object contains performance counters that report information about availability replicas.||SQL Server, Availability Replica|
|System Monitor||The SQLServer:Database Replica performance object contains performance counters that report information about the secondary databases on a given secondary replica.|
The SQLServer:Databases object in SQL Server contains performance counters that monitor transaction log activities, among other things. The following counters are particularly relevant for monitoring transaction-log activity on availability databases: Log Flush Write Time (ms), Log Flushes/sec, Log Pool Cache Misses/sec, Log Pool Disk Reads/sec, and Log Pool Requests/sec.
|SQL Server, Database Replica and SQL Server, Databases Object|
Always On Availability Groups Catalog Views (Transact-SQL)
Always On Availability Groups Dynamic Management Views and Functions (Transact-SQL)
Flexible Failover Policy for Automatic Failover of an Availability Group (SQL Server)
Overview of Always On Availability Groups (SQL Server)
Automatic Page Repair (Availability Groups: Database Mirroring)
Use the Always On Dashboard (SQL Server Management Studio)