監視可用性群組 (Transact-SQL)Monitor Availability Groups (Transact-SQL)

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions)

為了透過 Transact-SQLTransact-SQL監視可用性群組和複本,以及相關聯的資料庫, Always On 可用性群組Always On availability groups 提供一組目錄和動態管理檢視與伺服器屬性。For monitoring availability groups and replicas and the associated databases by using Transact-SQLTransact-SQL, Always On 可用性群組Always On availability groups provides a set of catalog and dynamic management views and server properties. 您可以透過 Transact-SQLTransact-SQL SELECT 陳述式使用這些檢視來監視可用性群組及其複本和資料庫。Using Transact-SQLTransact-SQL SELECT statements, you can use the views to monitor availability groups and their replicas and databases. 針對給定可用性群組所傳回的資訊取決於連接到的是裝載主要複本或次要複本的 SQL ServerSQL Server 執行個體。The information returned for a given availability group depends on whether you are connected to the instance of SQL ServerSQL 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.

權限Permissions

Always On 可用性群組Always On availability groups 目錄檢視需要伺服器執行個體的 VIEW ANY DEFINITION 權限。catalog views require VIEW ANY DEFINITION permission on the server instance. Always On 可用性群組Always On availability groups 動態管理檢視需要伺服器的 VIEW SERVER STATE 權限。dynamic management views require VIEW SERVER STATE permission on the server.

監視伺服器執行個體上的 AlwaysOn 可用性群組功能Monitoring the Always On Availability Groups Feature on a Server Instance

若要監視伺服器執行個體上的 Always On 可用性群組Always On availability groups 功能,請使用下列內建函數:To monitor the Always On 可用性群組Always On availability groups feature on a server instance, use the following built-in function:

SERVERPROPERTY 函數SERVERPROPERTY function
傳回有關 Always On 可用性群組Always On availability groups 是否已啟用的伺服器屬性資訊,如果已啟用,也指出它是否已在伺服器執行個體上啟動。Returns server property information about whether Always On 可用性群組Always On availability groups is enabled and, if so, whether it has started on the server instance.

資料行名稱: IsHadrEnabled、HadrManagerStatusColumn names: IsHadrEnabled, HadrManagerStatus

監視 WSFC 叢集中的可用性群組Monitoring Availability Groups on the WSFC Cluster

若要監視裝載已啟用 Always On 可用性群組Always On availability groups之本機伺服器執行個體的 Windows Server 容錯移轉叢集 (WSFC) 叢集,請使用下列檢視:To monitor the Windows Server Failover Clustering (WSFC) cluster that hosts a local server instance that is enabled for Always On 可用性群組Always On availability groups, use the following views:

sys.dm_hadr_clustersys.dm_hadr_cluster
如果裝載已啟用 Always On 可用性群組Always On availability groups 之 SQL Server 執行個體的 Windows Server 容錯移轉叢集 (WSFC) 節點有 WSFC 仲裁,則 sys.dm_hadr_cluster 會傳回一個資料列,該資料列會公開叢集名稱以及有關此仲裁的相關資訊。If the Windows Server Failover Clustering (WSFC) node that hosts an instance of SQL Server with Always On 可用性群組Always On availability groups enabled has WSFC quorum, sys.dm_hadr_cluster returns a row that exposes the cluster name and information about the quorum. 如果 WSFC 節點沒有仲裁,則不傳回任何資料列。If the WSFC node has no quorum, no rows are returned.

資料行名稱: cluster_name、quorum_type、quorum_type_desc、quorum_state、quorum_state_descColumn names: cluster_name, quorum_type, quorum_type_desc, quorum_state, quorum_state_desc

sys.dm_hadr_cluster_memberssys.dm_hadr_cluster_members
如果裝載已啟用 AlwaysOn 之 SQL Server 本機執行個體的 WSFC 節點有 WSFC 仲裁,則針對構成仲裁的每個成員及其狀態各傳回一個資料列。If the WSFC node that hosts the local Always On-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.

資料行名稱: member_name、member_type、member_type_desc、member_state、member_state_desc、number_of_quorum_votesColumn names: member_name, member_type, member_type_desc, member_state, member_state_desc, number_of_quorum_votes

sys.dm_hadr_cluster_networkssys.dm_hadr_cluster_networks
針對參與可用性群組之子網路組態的每個成員,各傳回一個資料列。Returns a row for every member that is participating in an availability group's subnet configuration. 您可以使用此動態管理檢視來驗證為每個可用性複本所設定的網路虛擬 IP。You can use this dynamic management view to validate the network virtual IP that is configured for each availability replica.

資料行名稱: member_name、network_subnet_ip、network_subnet_ipv4_mask、network_subnet_prefix_length、is_public、is_ipv4Column names: member_name, network_subnet_ip, network_subnet_ipv4_mask, network_subnet_prefix_length, is_public, is_ipv4

主索引鍵: member_name + network_subnet_IP + network_subnet_prefix_lengthPrimary key: member_name + network_subnet_IP + network_subnet_prefix_length

sys.dm_hadr_instance_node_mapsys.dm_hadr_instance_node_map
對於裝載已聯結其 AlwaysOn 可用性群組之可用性複本的每個 SQL Server 執行個體,傳回裝載伺服器執行個體的 Windows Server 容錯移轉叢集 (WSFC) 節點名稱。For every instance of SQL Server that hosts an availability replica that is joined to its Always On 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:

  • 這個動態管理檢視適用於偵測有多個可用性複本裝載於同一個 WSFC 節點的可用性群組,如果可用性群組不正確地設定,在 FCI 容錯移轉後可能會發生此不支援的組態狀況。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.

  • 當多個 SQL Server 執行個體裝載於同一個 WSFC 節點時,資源 DLL 會使用此動態管理檢視,判斷要連接的 SQL Server 執行個體。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.

資料行名稱: ag_resource_id、instance_name、node_nameColumn names: ag_resource_id, instance_name, node_name

sys.dm_hadr_name_id_mapsys.dm_hadr_name_id_map
顯示 SQL Server 目前執行個體已聯結之 AlwaysOn 可用性群組與三個唯一識別碼的對應:可用性群組識別碼、WSFC 資源識別碼和 WSFC 群組識別碼。Shows the mapping of Always On 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. 此對應的目的是要處理重新命名 WSFC 資源/群組的案例。The purpose of this mapping is to handle the scenario in which the WSFC resource/group is renamed.

資料行名稱: ag_name、ag_id、ag_resource_id、ag_group_idColumn names: ag_name, ag_id, ag_resource_id, ag_group_id

注意

另請參閱本主題稍後 監視可用性複本一節中的 sys.dm_hadr_availability_replica_cluster_nodessys.dm_hadr_availability_replica_cluster_states,以及 監視可用性資料庫一節中的 sys.availability_databases_clustersys.dm_hadr_database_replica_cluster_statesAlso 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.

如需 WSFC 叢集和 Always On 可用性群組Always On availability groups 的相關資訊,請參閱 SQL Server 的 Windows Server 容錯移轉叢集 (WSFC)容錯移轉叢集和 AlwaysOn 可用性群組 (SQL Server)For information about WSFC clusters and Always On 可用性群組Always On availability groups, see Windows Server Failover Clustering (WSFC) with SQL Server and Failover Clustering and Always On Availability Groups (SQL Server).

監視可用性群組Monitoring Availability Groups

若要監視伺服器執行個體裝載其可用性複本的可用性群組,請使用下列檢視:To monitor the availability groups for which the server instance hosts an availability replica, use the following views:

sys.availability_groupssys.availability_groups
針對裝載可用性複本的 SQL ServerSQL Server 本機執行個體的每一個可用性群組,各傳回一個資料列。Returns a row for each availability group for which the local instance of SQL ServerSQL Server hosts an availability replica. 每一個資料列都包含可用性群組中繼資料的快取副本。Each row contains a cached copy of the availability group metadata.

資料行名稱: group_id、name、resource_id、resource_group_id、failure_condition_level、health_check_timeout、automated_backup_preference、automated_backup_preference_descColumn 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_clustersys.availability_groups_cluster
針對 WSFC 叢集中的每一個可用性群組,各傳回一個資料列。Returns a row for each availability group in the WSFC cluster. 每個資料列都包含 Windows Server 容錯移轉叢集 (WSFC) 叢集中的可用性群組中繼資料。Each row contains the availability group metadata from the Windows Server Failover Clustering (WSFC) cluster.

資料行名稱: group_id、name、resource_id、resource_group_id、failure_condition_level、health_check_timeout、automated_backup_preference、automated_backup_preference_descColumn 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_statessys.dm_hadr_availability_group_states
針對擁有 SQL ServerSQL Server本機執行個體之可用性複本的每一個可用性群組,各傳回一個資料列。Returns a row for each availability group that possesses an availability replica on the local instance of SQL ServerSQL Server. 每個資料列會顯示定義給定之可用性群組健全狀況的狀態。Each row displays the states that define the health of a given availability group.

資料行名稱: group_id、primary_replica、primary_recovery_health、primary_recovery_health_desc、secondary_recovery_health、secondary_recovery_health_desc、synchronization_health、synchronization_health_descColumn names: group_id, primary_replica, primary_recovery_health, primary_recovery_health_desc, secondary_recovery_health, secondary_recovery_health_desc, synchronization_health, synchronization_health_desc

sys.dm_hadr_availability_replica_cluster_statesMonitoring Availability Replicas

若要監視可用性複本,請使用下列檢視和系統函數:To monitor availability replicas, use the following views and system function:

sys.availability_replicassys.availability_replicas
針對每一個可用性群組中的每一個可用性複本 ( SQL ServerSQL Server 本機執行個體裝載此群組的可用性複本),各傳回一個資料列。Returns a row for every availability replica in each availability group for which the local instance of SQL ServerSQL Server hosts an availability replica.

資料行名稱: 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_urlColumn 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_listssys.availability_read_only_routing_lists
針對 WSFC 容錯移轉叢集中 AlwaysOn 可用性群組內每個可用性複本的唯讀路由清單,各傳回一個資料列。Returns a row for the read only routing list of each availability replica in an Always On availability group in the WSFC failover cluster.

資料行名稱: replica_id、routing_priority、read_only_replica_idColumn names: replica_id, routing_priority, read_only_replica_id

sys.dm_hadr_availability_replica_cluster_nodessys.dm_hadr_availability_replica_cluster_nodes
針對 Windows Server 容錯移轉叢集 (WSFC) 叢集中 AlwaysOn 可用性群組的每一個可用性複本 (不論聯結狀態為何),各傳回一個資料列。Returns a row for every availability replica (regardless of join state) of the Always On availability groups in the Windows Server Failover Clustering (WSFC) cluster.

資料行名稱: group_name、replica_server_name、node_nameColumn names: group_name, replica_server_name, node_name

sys.dm_hadr_availability_replica_cluster_statessys.dm_hadr_availability_replica_cluster_states
針對 Windows Server 容錯移轉叢集 (WSFC) 叢集中所有 AlwaysOn 可用性群組 (不論複本位置為何) 的每一個複本 (不論聯結狀態為何),各傳回一個資料列。Returns a row for each replica (regardless of join state) of all Always On availability groups (regardless of replica location) in the Windows Server Failover Clustering (WSFC) cluster.

資料行名稱: replica_id、replica_server_name、group_id、join_state、join_state_descColumn names: replica_id, replica_server_name, group_id, join_state, join_state_desc

sys.dm_hadr_availability_replica_statessys.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.

資料行名稱: 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 和 last_connect_error_timestampColumn 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_replicasys.fn_hadr_backup_is_preferred_replica
判斷目前的複本是否為慣用的備份複本。Determines whether the current replica is the preferred backup replica.

注意

如需可用性複本效能計數器 ( SQLServer:Availability Replica 效能物件) 的相關資訊,請參閱 SQL Server、可用性複本For information about performance counters for availability replicas (the SQLServer:Availability Replica performance object), see SQL Server, Availability Replica.

sys.dm_hadr_database_replica_cluster_statesMonitoring Availability Databases

若要監視可用性資料庫,請使用下列檢視:To monitor availability databases, use the following views:

監視可用性資料庫sys.availability_databases_cluster
針對屬於叢集中所有 AlwaysOn 可用性群組的 SQL Server 執行個體上的每一個資料庫,各包含一個資料列,無論本機資料庫複本是否已聯結至可用性群組。Contains one row for each database on the instance of SQL Server that are part of all Always On 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.

資料行名稱: group_id、group_database_id、database_nameColumn names: group_id, group_database_id, database_name

sys.databasessys.databases
針對 SQL ServerSQL Server執行個體中的每個資料庫,各包含一個資料列。Contains one row per database in the instance of SQL ServerSQL Server. 如果資料庫屬於某個可用性複本,該資料庫的資料列會顯示複本的 GUID,以及資料庫在其可用性群組內的唯一識別碼。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.

Always On 可用性群組Always On availability groups 資料行名稱: replica_id、group_database_idAlways On 可用性群組Always On availability groups column names: replica_id, group_database_id

sys.dm_hadr_auto_page_repairsys.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. 這個檢視包含在給定之主要或次要資料庫上進行最新自動修復頁面嘗試行為的資料列,而且每個資料庫最多 100 個資料列。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.

資料行名稱: database_id、file_id、page_id、error_type、page_status、modification_timeColumn names: database_id, file_id, page_id, error_type, page_status, modification_time

sys.dm_hadr_database_replica_statessys.dm_hadr_database_replica_states
針對參與任何可用性群組的每一個資料庫 ( SQL ServerSQL Server 本機執行個體正在裝載此群組的可用性複本),各傳回一個資料列。Returns a row for each database that is participating in any availability group for which the local instance of SQL ServerSQL Server is hosting an availability replica.

資料行名稱: 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_ghostsColumn 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.availability_databases_clustersys.dm_hadr_database_replica_cluster_states
傳回包含資訊的資料列,該資訊的目的是為了讓您深入了解 Windows Server 容錯移轉叢集 (WSFC) 叢集中每個可用性群組內可用性資料庫的健全狀況。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.

資料行名稱: replica_id、group_database_id、database_name、is_failover_ready、is_pending_secondary_suspend、is_database_joined、recovery_lsn、truncation_lsnColumn names: replica_id, group_database_id, database_name, is_failover_ready, is_pending_secondary_suspend, is_database_joined, recovery_lsn, truncation_lsn

注意

主要複本位置是可用性群組的授權來源。The primary replica location is the authoritative source for an availability group.

注意

如需可用性資料庫 Always On 可用性群組Always On availability groups 效能計數器 ( SQLServer:Database Replica 效能物件) 的相關資訊,請參閱 SQL Server、資料庫複本For information about the Always On 可用性群組Always On availability groups performance counters for availability databases (the SQLServer:Database Replica performance object), see SQL Server, Database Replica. 此外,若要監視可用性資料庫上的交易記錄活動,請使用 SQLServer:Databases 效能物件的下列計數器:Log Flush Write Time (ms)Log Flushes/secLog Pool Cache Misses/secLog Pool Disk Reads/secLog Pool Requests/sec。如需詳細資訊,請參閱 SQL Server, Databases ObjectAlso, 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.

監視可用性群組接聽程式Monitoring Availability Group Listeners

若要監視 WSFC 叢集子網路上的可用性群組接聽程式,請使用下列檢視:To monitor the availability group listeners on subnets of the WSFC cluster, use the following views:

sys.availability_group_listener_ip_addressessys.availability_group_listener_ip_addresses
針對目前上線供可用性群組接聽程式使用的每個符合標準虛擬 IP 位址傳回一個資料列。Returns a row for every conformant virtual IP address that is currently online for an availability group listener.

資料行名稱 :listener_id、ip_address、ip_subnet_mask、is_dhcp、network_subnet_ip、network_subnet_prefix_length、network_subnet_ipv4_mask、state、state_descColumn 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_listenerssys.availability_group_listeners
若為給定的可用性群組,傳回零個資料列,表示沒有網路名稱與可用性群組相關聯,或針對 WSFC 叢集中的每個可用性群組接聽程式組態傳回一個資料列。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.

資料行名稱 :group_id、listener_id、dns_name、port、is_conformant、ip_configuration_string_from_clusterColumn names: group_id, listener_id, dns_name, port, is_conformant, ip_configuration_string_from_cluster

sys.dm_tcp_listener_statessys.dm_tcp_listener_states
針對每個 TCP 接聽程式傳回一個包含動態狀態資訊的資料列。Returns a row containing dynamic-state information for each TCP listener.

資料行名稱: listener_id、ip_address、is_ipv4、port、type、type_desc、state、state_desc、start_timeColumn names: listener_id, ip_address, is_ipv4, port, type, type_desc, state, state_desc, start_time

主索引鍵: listener_idPrimary key: listener_id

如需可用性群組接聽程式的相關資訊,請參閱可用性群組接聽程式、用戶端連接性及應用程式容錯移轉 (SQL Server)For information about availability group listeners, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

AlwaysOn 可用性群組監視工作:Always On Availability Groups monitoring tasks:

AlwaysOn 可用性群組監視參考 (Transact-SQL):Always On Availability Groups monitoring reference (Transact-SQL):

AlwaysOn 效能計數器:Always On performance counters:

AlwaysOn 可用性群組的原則式管理Policy-based management for Always On Availability Groups

另請參閱See Also

AlwaysOn 可用性群組 (SQL Server) Always On Availability Groups (SQL Server)
AlwaysOn 可用性群組概觀 (SQL Server) Overview of Always On Availability Groups (SQL Server)
監視可用性群組 (SQL Server)Monitoring of Availability Groups (SQL Server)