Monitor Availability Groups (Transact-SQL)

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.

Tip

Many of these views can be joined using their ID columns to return information from multiple views in a single query.

In This Topic:

• Permissions

• Using Transact-SQL to monitor:  

  AlwaysOn Availability Groups feature on a server instance

  Availability groups on the WSFC cluster

  Availability groups

  Availability replicas

  Availability databases

  Availability group listeners

• Related Tasks

Permissions

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.

Monitoring the AlwaysOn Availability Groups Feature on a Server Instance

To monitor the AlwaysOn Availability Groups feature on a server instance, use the following built-in function:

• SERVERPROPERTY function
  Returns server property information about whether AlwaysOn Availability Groups is enabled and, if so, whether it has started on the server instance.

  Column names:  IsHadrEnabled, HadrManagerStatus

Monitoring Availability Groups on the WSFC Cluster

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:

• sys.dm_hadr_cluster
  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.

  Column names:  cluster_name, quorum_type, quorum_type_desc, quorum_state, quorum_state_desc

• sys.dm_hadr_cluster_members
  If the WSFC node that hosts the local AlwaysOn-enabled instance of SQL Server has WSFC quorum, returns a row for each of the members that constitute the quorum and the state of each of them.

  Column names:  member_name, member_type, member_type_desc, member_state, member_state_desc, number_of_quorum_votes

• sys.dm_hadr_cluster_networks
  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.

  Column names:  member_name, network_subnet_ip, network_subnet_ipv4_mask, network_subnet_prefix_length, is_public, is_ipv4

  Primary key:  member_name + network_subnet_IP + network_subnet_prefix_length

• sys.dm_hadr_instance_node_map
  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.

  Column names:  ag_resource_id, instance_name, node_name

• sys.dm_hadr_name_id_map
  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.

  Column names:  ag_name, ag_id, ag_resource_id, ag_group_id

Note

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

[Top]

Monitoring Availability Groups

To monitor the availability groups for which the server instance hosts an availability replica, use the following views:

• sys.availability_groups
  Returns a row for each availability group for which the local instance of SQL Server hosts an availability replica. Each row contains a cached copy of the availability group metadata.

  Column names:  group_id, name, resource_id, resource_group_id, failure_condition_level, health_check_timeout, automated_backup_preference, automated_backup_preference_desc

• sys.availability_groups_cluster
  Returns a row for each availability group in the WSFC cluster. Each row contains the availability group metadata from the Windows Server Failover Clustering (WSFC) cluster.

  Column names:  group_id, name, resource_id, resource_group_id, failure_condition_level, health_check_timeout, automated_backup_preference, automated_backup_preference_desc

• sys.dm_hadr_availability_group_states
  Returns a row for each availability group that possesses an availability replica on the local instance of SQL Server. Each row displays the states that define the health of a given availability group.

  Column names:  group_id, primary_replica, primary_recovery_health, primary_recovery_health_desc, secondary_recovery_health, secondary_recovery_health_desc, synchronization_health, synchronization_health_desc

[Top]

Monitoring Availability Replicas

To monitor availability replicas, use the following views and system function:

• sys.availability_replicas
  Returns a row for every availability replica in each availability group for which the local instance of SQL Server hosts an availability replica.

  Column names:  replica_id, group_id, replica_metadata_id, replica_server_name, owner_sid, endpoint_url, availability_mode, availability_mode_desc, failover_mode, failover_mode_desc, session_timeout, primary_role_allow_connections, primary_role_allow_connections_desc, secondary_role_allow_connections, secondary_role_allow_connections_desc, create_date, modify_date, backup_priority, read_only_routing_url

• sys.availability_read_only_routing_lists
  Returns a row for the read only routing list of each availability replica in an AlwaysOn availability group in the WSFC failover cluster.

  Column names:  replica_id, routing_priority, read_only_replica_id

• sys.dm_hadr_availability_replica_cluster_nodes
  Returns a row for every availability replica (regardless of join state) of the AlwaysOn availability groups in the Windows Server Failover Clustering (WSFC) cluster.

  Column names:  group_name, replica_server_name, node_name

• sys.dm_hadr_availability_replica_cluster_states
  Returns a row for each replica (regardless of join state) of all AlwaysOn availability groups (regardless of replica location) in the Windows Server Failover Clustering (WSFC) cluster.

  Column names:  replica_id, replica_server_name, group_id, join_state, join_state_desc

• sys.dm_hadr_availability_replica_states
  Returns a row showing the state of each local availability replica and a row for each remote availability replica in the same availability group.

  Column names:  replica_id, group_id, is_local, role, role_desc, operational_state, operational_state_desc, connected_state, connected_state_desc, recovery_health, recovery_health_desc, synchronization_health, synchronization_health_desc, last_connect_error_number, last_connect_error_description, and last_connect_error_timestamp

• sys.fn_hadr_backup_is_preferred_replica
  Determines whether the current replica is the preferred backup replica.

Note

For information about performance counters for availability replicas (the SQLServer:Availability Replica performance object), see SQL Server, Availability Replica.

[Top]

Monitoring Availability Databases

To monitor availability databases, use the following views:

• sys.availability_databases_cluster
  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.

  Note

  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.

  Column names:  group_id, group_database_id, database_name

• sys.databases
  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

• sys.dm_hadr_auto_page_repair
  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.

  Column names:  database_id, file_id, page_id, error_type, page_status, modification_time

• sys.dm_hadr_database_replica_states
  Returns a row for each database that is participating in any availability group for which the local instance of SQL Server is hosting an availability replica.

  Column names:  database_id, group_id, replica_id, group_database_id, is_local, synchronization_state, synchronization_state_desc, is_commit_participant, synchronization_health, synchronization_health_desc, database_state, database_state_desc, is_suspended, suspend_reason, suspend_reason_desc, recovery_lsn, truncation_lsn, last_sent_lsn, last_sent_time, last_received_lsn, last_received_time, last_hardened_lsn, last_hardened_time, last_redone_lsn, last_redone_time, log_send_queue_size, log_send_rate, redo_queue_size, redo_rate, filestream_send_rate, end_of_log_lsn, last_commit_lsn, last_commit_time, low_water_mark_for_ghosts

• sys.dm_hadr_database_replica_cluster_states
  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.

  Column names:  replica_id, group_database_id, database_name, is_failover_ready, is_pending_secondary_suspend, is_database_joined, recovery_lsn, truncation_lsn

  Note

  The primary replica location is the authoritative source for an availability group.

Note

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.

[Top]

Monitoring Availability Group Listeners

To monitor the availability group listeners on subnets of the WSFC cluster, use the following views:

• sys.availability_group_listener_ip_addresses
  Returns a row for every conformant virtual IP address that is currently online for an availability group listener.

  Column names:  listener_id, ip_address, ip_subnet_mask, is_dhcp, network_subnet_ip, network_subnet_prefix_length, network_subnet_ipv4_mask, state, state_desc

• sys.availability_group_listeners
  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.

  Column names:  group_id, listener_id, dns_name, port, is_conformant, ip_configuration_string_from_cluster

• sys.dm_tcp_listener_states
  Returns a row containing dynamic-state information for each TCP listener.

  Column names:  listener_id, ip_address, is_ipv4, port, type, type_desc, state, state_desc, start_time

  Primary key:  listener_id

For information about availability group listeners, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

[Top]

Related Tasks

AlwaysOn Availability Groups monitoring tasks:

• Use the Object Explorer Details to Monitor Availability Groups (SQL Server Management Studio)

• View Availability Group Properties (SQL Server)

• View Availability Replica Properties (SQL Server)

• View Availability Group Listener Properties (SQL Server)

AlwaysOn Availability Groups monitoring reference (Transact-SQL):

• SERVERPROPERTY (Transact-SQL)

• sys.availability_group_listener_ip_addresses (Transact-SQL)

• sys.availability_group_listeners (Transact-SQL)

• sys.availability_databases_cluster (Transact-SQL)

• sys.availability_groups (Transact-SQL)

• sys.availability_read_only_routing_lists (Transact-SQL)

• sys.availability_replicas (Transact-SQL)

• sys.dm_hadr_availability_replica_cluster_nodes (Transact-SQL)

• sys.dm_hadr_availability_replica_cluster_states (Transact-SQL)

• sys.database_mirroring_endpoints (Transact-SQL)

• sys.dm_hadr_auto_page_repair (Transact-SQL)

• sys.dm_hadr_availability_group_states (Transact-SQL)

• sys.dm_hadr_availability_replica_cluster_states (Transact-SQL)

• sys.dm_hadr_availability_replica_states (Transact-SQL)

• sys.dm_hadr_database_replica_states (Transact-SQL)

• sys.dm_hadr_database_replica_cluster_states (Transact-SQL)

• sys.dm_hadr_cluster (Transact-SQL)

• sys.dm_hadr_cluster_members (Transact-SQL)

• sys.dm_hadr_cluster_networks (Transact-SQL)

• sys.dm_hadr_database_replica_cluster_states (Transact-SQL)

• sys.dm_hadr_database_replica_states (Transact-SQL)

• sys.dm_hadr_instance_node_map (Transact-SQL)

• sys.dm_hadr_name_id_map (Transact-SQL)

• sys.dm_os_performance_counters (Transact-SQL)

• sys.dm_tcp_listener_states (Transact-SQL)

• sys.fn_hadr_backup_is_preferred_replica (Transact-SQL)

AlwaysOn performance counters:

• SQL Server, Availability Replica

• SQL Server, Database Replica

• SQL Server, Databases Object

Policy-based management for AlwaysOn Availability Groups

• Use AlwaysOn Policies to View the Health of an Availability Group (SQL Server)

• Use the AlwaysOn Dashboard (SQL Server Management Studio)

[Top]

See Also

Concepts

AlwaysOn Availability Groups (SQL Server)

Overview of AlwaysOn Availability Groups (SQL Server)

Monitoring of Availability Groups (SQL Server)