Monitoring of Availability Groups (SQL Server)

  • Article

To monitor the properties and state of an AlwaysOn availability group you can use the following tools.

Tool

Brief Description

Links

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 AlwaysOn 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 AlwaysOn 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 AlwaysOn Health monitoring using Policy Based management (PBM).

  • Health roll ups from availability databases to availability replicas.

  • Custom tasks that manage AlwaysOn 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

AlwaysOn 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.

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

Related Content

Arrow icon used with Back to Top link[Top]

See Also

Concepts

Flexible Failover Policy for Automatic Failover of an Availability Group (SQL Server)

Overview of AlwaysOn Availability Groups (SQL Server)

Automatic Page Repair (Availability Groups/Database Mirroring)

Use the AlwaysOn Dashboard (SQL Server Management Studio)

Other Resources

AlwaysOn Availability Groups Catalog Views (Transact-SQL)

AlwaysOn Availability Groups Dynamic Management Views and Functions (Transact-SQL)