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.
AlwaysOn Availability Groups catalog views require VIEW ANY DEFINITION permission on the server instance. AlwaysOn Availability Groups dynamic management views require VIEW SERVER STATE permission on the server.
If the Windows Server Failover Clustering (WSFC) node that hosts an instance of SQL Server with AlwaysOn Availability Groups enabled has WSFC quorum, sys.dm_hadr_cluster returns a row that exposes the cluster name and information about the quorum. If the WSFC node has no quorum, no rows are returned.
Returns a row for every member that is participating in an availability group's subnet configuration. You can use this dynamic management view to validate the network virtual IP that is configured for each availability replica.
For every instance of SQL Server that hosts an availability replica that is joined to its AlwaysOn availability group, returns the name of the Windows Server Failover Clustering (WSFC) node that hosts the server instance. This dynamic management view has the following uses:
This dynamic management view is useful for detecting an availability group with multiple availability replicas that are hosted on the same WSFC node, which is an unsupported configuration that could occur after an FCI failover if the availability group is incorrectly configured.
When multiple SQL Server instances are hosted on the same WSFC node, the Resource DLL uses this dynamic management view to determine the instance of SQL Server to connect to.
Shows the mapping of AlwaysOn availability groups that the current instance of SQL Server has joined to three unique IDs: an availability group ID, a WSFC resource ID, and a WSFC Group ID. The purpose of this mapping is to handle the scenario in which the WSFC resource/group is renamed.
Contains one row for each database on the instance of SQL Server that are part of all AlwaysOn Availability Groups in the cluster, regardless of whether the local copy database has been joined to the availability group yet.
When a database is added to an availability group, the primary database is automatically joined to the group. Secondary databases must be prepared on each secondary replica before they can be joined to the availability group.
Contains one row per database in the instance of SQL Server. If a database belongs to an availability replica, the row for that database displays the GUID of the replica and the unique identifier of the database within its availability group.
AlwaysOn Availability Groups column names: replica_id, group_database_id
Returns a row for every automatic page-repair attempt on any availability database on an availability replica that is hosted for any availability group by the server instance. This view contains rows for the latest automatic page-repair attempts on a given primary or secondary database, with a maximum of 100 rows per database. As soon as a database reaches the maximum, the row for its next automatic page-repair attempt replaces one of the existing entries.
Returns a row containing information intended to provide you with insight into the health of the availability databases in each availability group on the Windows Server Failover Clustering (WSFC) cluster. This dynamic management view is useful when planning or responding to a failover or for discovering which secondary replica in an availability group is holding up log truncation on a given primary database.
The primary replica location is the authoritative source for an availability group.
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.
For a given availability group, returns either zero rows indicating that no network name is associated with the availability group, or returns a row for each availability-group listener configuration in the WSFC cluster.