For monitoring availability groups and replicas and the associated databases by using Transact-SQL, AlwaysOn Availability Groups provides a set of catalog and dynamic management views and server properties. Using Transact-SQL SELECT statements, you can use the views to monitor availability groups and their replicas and databases. The information returned for a given availability group depends on whether you are connected to the instance of SQL Server that is hosting the primary replica or a secondary replica.
Many of these views can be joined using their ID columns to return information from multiple views in a single query.
In This Topic:
Using Transact-SQL to monitor:
AlwaysOn Availability Groups feature on a server instance
Availability groups on the WSFC cluster
Availability group listeners
To monitor the Windows Server Failover Clustering (WSFC) cluster that hosts a local server instance that is enabled for AlwaysOn Availability Groups, use the following views:
Also see sys.dm_hadr_availability_replica_cluster_nodes and sys.dm_hadr_availability_replica_cluster_states in the Monitoring Availability Replicas section and sys.availability_databases_cluster and sys.dm_hadr_database_replica_cluster_states in the Monitoring Availability Databases section, later in this topic.
For information about WSFC clusters and AlwaysOn Availability Groups, see Windows Server Failover Clustering (WSFC) with SQL Server and Failover Clustering and AlwaysOn Availability Groups (SQL Server).
To monitor availability replicas, use the following views and system function:
For information about performance counters for availability replicas (the SQLServer:Availability Replica performance object), see SQL Server, Availability Replica.
To monitor availability databases, use the following views:
For information about the AlwaysOn Availability Groups performance counters for availability databases (the SQLServer:Database Replica performance object), see SQL Server, Database Replica. Also, to monitor transaction-log activity on availability databases, use the following counters of the SQLServer:Databases performance object: Log Flush Write Time (ms), Log Flushes/sec, Log Pool Cache Misses/sec, Log Pool Disk Reads/sec, and Log Pool Requests/sec. For more information, see SQL Server, Databases Object.
To monitor the availability group listeners on subnets of the WSFC cluster, use the following views:
For information about availability group listeners, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).
AlwaysOn Availability Groups monitoring tasks:
AlwaysOn Availability Groups monitoring reference (Transact-SQL):
AlwaysOn performance counters:
Policy-based management for AlwaysOn Availability Groups