sys.dm_hadr_availability_replica_states (Transact-SQL)sys.dm_hadr_availability_replica_states (Transact-SQL)

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

傳回每個本機複本的資料列,並針對同一個 Always On 群組中當做本機複本的每一個遠端複本,各傳回一個資料列。Returns a row for each local replica and a row for each remote replica in the same Always On availability group as a local replica. 每一個資料列都包含有關給定複本狀態的資訊。Each row contains information about the state of a given replica.

重要

若要取得給定可用性群組中每個複本的相關資訊,請查詢裝載主要複本之伺服器實例上的 sys.dm_hadr_availability_replica_statesTo obtain information about every replica in a given availability group, query sys.dm_hadr_availability_replica_states on the server instance that is hosting the primary replica. 在裝載可用性群組之次要複本的伺服器執行個體上查詢時,這個動態管理檢視只會傳回此可用性群組的本機資訊。When queried on a server instance that is hosting a secondary replica of an availability group, this dynamic management view returns only local information for the availability group.

資料行名稱Column name 資料類型Data type 描述Description
replica_idreplica_id uniqueidentifieruniqueidentifier 複本的唯一識別碼。Unique identifier of the replica.
group_idgroup_id uniqueidentifieruniqueidentifier 可用性群組的唯一識別碼。Unique identifier of the availability group.
is_localis_local bitbit 複本是否為本機,下列其中一個:Whether the replica is local, one of:

0 = 表示可用性群組中的遠端次要複本,該群組的主要複本是由本機伺服器執行個體所裝載。0 = Indicates a remote secondary replica in an availability group whose primary replica is hosted by the local server instance. 這個值只會出現在主要複本位置。This value occurs only on the primary replica location.

1 = 表示本機複本。1 = Indicates a local replica. 在次要複本上,這是該複本所屬之可用性群組的唯一可用值。On secondary replicas, this is the only available value for the availability group to which the replica belongs.
rolerole tinyinttinyint Always On 可用性群組Always On availability groups本機複本或已連接遠端複本的目前角色,下列其中一個:Current Always On 可用性群組Always On availability groups role of a local replica or a connected remote replica, one of:

0 = 正在解析0 = Resolving

1 = 主要1 = Primary

2 = 次要2 = Secondary

如需 Always On 可用性群組Always On availability groups 角色的詳細資訊,請參閱 Always On 可用性群組概觀 (SQL Server)For information about Always On 可用性群組Always On availability groups roles, see Overview of Always On Availability Groups (SQL Server).
role_descrole_desc nvarchar(60)nvarchar(60) 角色 的描述,下列其中一個:Description of role, one of:

RESOLVINGRESOLVING

PRIMARYPRIMARY

SECONDARYSECONDARY
operational_stateoperational_state tinyinttinyint 複本的目前操作狀態,下列其中一個:Current operational state of the replica, one of:

0 = 暫止容錯移轉0 = Pending failover

1 = 暫止1 = Pending

2 = 線上2 = Online

3 = 離線3 = Offline

4 = 失敗4 = Failed

5 = 失敗,無仲裁5 = Failed, no quorum

NULL = 複本不是本機。NULL = Replica is not local.

如需詳細資訊,請參閱本主題稍後的 角色和操作狀態For more information, see Roles and Operational States, later in this topic.
操作 _ 狀態 _ descoperational_state_desc nvarchar(60)nvarchar(60) 操作 _ 狀態 的描述,下列其中一個:Description of operational_state, one of:

PENDING_FAILOVERPENDING_FAILOVER

PENDINGPENDING

ONLINEONLINE

OFFLINEOFFLINE

FAILEDFAILED

FAILED_NO_QUORUMFAILED_NO_QUORUM

NULLNULL
復原 _ 健全狀況recovery_health tinyinttinyint Sys.dm_hadr_database_replica_states動態管理檢視的 [資料庫 _ 狀態] 資料 行的匯總。Rollup of the database_state column of the sys.dm_hadr_database_replica_states dynamic management view. 以下是可能的值及其描述。The following are the possible values and their descriptions.

0:進行中。0 : In progress. 至少有一個聯結的資料庫具有 ONLINE 以外的資料庫狀態 (資料庫 _ 狀態 不是 0) 。At least one joined database has a database state other than ONLINE (database_state is not 0).

1:線上。1 : Online. 所有聯結資料庫的資料庫狀態都是 ONLINE (database_state 為 0) 。All the joined databases have a database state of ONLINE (database_state is 0).

Null: is_local = 0NULL : is_local = 0
recovery_health_descrecovery_health_desc nvarchar(60)nvarchar(60) Recovery_health 的描述,下列其中一個:Description of recovery_health, one of:

ONLINE_IN_PROGRESSONLINE_IN_PROGRESS

ONLINEONLINE

NULLNULL
同步處理 _ 健全狀況synchronization_health tinyinttinyint 反映資料庫同步處理狀態的匯總 (所有聯結可用性資料庫的 synchronization_state) (也稱為 複本) 和複本的可用性模式 (同步認可或非同步認可模式) 。Reflects a rollup of the database synchronization state (synchronization_state)of all joined availability databases (also known as replicas) and the availability mode of the replica (synchronous-commit or asynchronous-commit mode). 匯總會反映複本上資料庫的最不健全累積狀態。The rollup will reflect the least healthy accumulated state the databases on the replica. 以下是可能的值及其描述。Below are the possible values and their descriptions.

0:狀況不良。0 : Not healthy. 至少有一個聯結資料庫處於 NOT SYNCHRONIZING 狀態下。At least one joined database is in the NOT SYNCHRONIZING state.

1:部分狀況良好。1 : Partially healthy. 某些複本未處於目標同步處理狀態:同步認可複本應該已同步處理,而非同步認可複本應該正在同步處理。Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing.

2:狀況良好。2 : Healthy. 所有複本都處於目標同步處理狀態:同步認可複本已同步處理,而非同步認可複本正在同步處理。All replicas are in the target synchronization state: synchronous-commit replicas are synchronized, and asynchronous-commit replicas are synchronizing.
synchronization_health_descsynchronization_health_desc nvarchar(60)nvarchar(60) Synchronization_health 的描述,下列其中一個:Description of synchronization_health, one of:

NOT_HEALTHYNOT_HEALTHY

PARTIALLY_HEALTHYPARTIALLY_HEALTHY

HEALTHYHEALTHY
connected_stateconnected_state tinyinttinyint 次要複本目前是否已連接到主要複本。Whether a secondary replica is currently connected to the primary replica. 以下顯示可能的值及其描述。The possible values are shown below with their descriptions.

0:已中斷連線。0 : Disconnected. 可用性複本到中斷線上狀態的回應取決於其角色:在主要複本上,如果次要複本已中斷連接,則會在主要複本上將其次要資料庫標示為未同步處理,而這會等候次要複本重新連接;在次要複本上,偵測到它已中斷連接時,次要複本就會嘗試重新連接到主要複本。The response of an availability replica to the DISCONNECTED state depends on its role: On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as NOT SYNCHRONIZED on the primary replica, which waits for the secondary to reconnect; On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.

1:已連接。1 : Connected.

每個主要複本都會針對相同可用性群組中的每一個次要複本來追蹤連接狀態。Each primary replica tracks the connection state for every secondary replica in the same availability group. 次要複本只會追蹤主要複本的連接狀態。Secondary replicas track the connection state of only the primary replica.
connected_state_descconnected_state_desc nvarchar(60)nvarchar(60) Connection_state 的描述,下列其中一個:Description of connection_state, one of:

DISCONNECTEDDISCONNECTED

CONNECTEDCONNECTED
last_connect_error_numberlast_connect_error_number intint 上次連接錯誤的號碼。Number of the last connection error.
last_connect_error_descriptionlast_connect_error_description nvarchar(1024)nvarchar(1024) Last_connect_error_number 訊息的文字。Text of the last_connect_error_number message.
last_connect_error_timestamplast_connect_error_timestamp datetimedatetime 指出何時發生 last_connect_error_number 錯誤的日期和時間戳記。Date and time timestamp indicating when the last_connect_error_number error occurred.

角色和操作狀態Roles and Operational States

角色、 角色、反映給定可用性複本的狀態和操作狀態( operational_state)描述複本是否已準備好處理可用性複本的所有資料庫的用戶端要求。The role, role, reflects the state of a given availability replica and the operational state, operational_state, describes whether the replica is ready to process client requests for all the database of the availability replica. 以下是每個角色可能的操作狀態摘要:「正在解析」、「主要」和「次要」。The following is a summary of the operational states that are possible for each role: RESOLVING, PRIMARY, and SECONDARY.

解決: 當可用性複本處於「正在解析」角色時,可能的操作狀態如下表所示。RESOLVING: When an availability replica is in the RESOLVING role, the possible operational states are as shown in the following table.

作業狀態Operational State DescriptionDescription
PENDING_FAILOVERPENDING_FAILOVER 正在針對可用性群組處理容錯移轉命令。A failover command is being processed for the availability group.
OFFLINEOFFLINE 可用性複本的所有組態資料都已經在 WSFC 叢集和本機中繼資料中更新,但是可用性群組目前缺少主要複本。All configuration data for the availability replica has been updated on WSFC cluster and, also, in local metadata, but the availability group currently lacks a primary replica.
FAILEDFAILED 嘗試從 WSFC 叢集中擷取資訊時發生讀取失敗。A read failure has occurred during an attempt trying to retrieve information from the WSFC cluster.
FAILED_NO_QUORUMFAILED_NO_QUORUM 本機 WSFC 節點沒有仲裁。The local WSFC node does not have quorum. 這是推斷的狀態。This is an inferred state.

主要: 當可用性複本正在執行主要角色時,它目前是主要複本。PRIMARY: When an availability replica is performing the PRIMARY role, it is currently the primary replica. 可能的操作狀態如下表所示。The possible operational states are as shown in the following table.

作業狀態Operational State DescriptionDescription
PENDINGPENDING 這是暫時性狀態,但是如果沒有工作者可處理要求,則主要複本可能會陷在這個狀態中。This is a transient state, but a primary replica can be stuck in this state if workers are not available to process requests.
ONLINEONLINE 可用性群組資源在線上,而且所有資料庫工作者執行緒都已收取。The availability group resource is online, and all database worker threads have been picked up.
FAILEDFAILED 可用性複本無法從 WSFC 叢集讀取及/或將資料寫入其中。The availability replica is unable to read to and/or write from the WSFC cluster.

次要: 當可用性複本正在執行次要角色時,它目前是次要複本。SECONDARY: When an availability replica is performing the SECONDARY role, it is currently a secondary replica. 可能的操作狀態如下表所示。The possible operational states are as shown in the table below.

作業狀態Operational State DescriptionDescription
ONLINEONLINE 本機次要複本已連接到主要複本。The local secondary replica is connected to the primary replica.
FAILEDFAILED 本機次要複本無法從 WSFC 叢集讀取及/或將資料寫入其中。The local secondary replica is unable to read to and/or write from the WSFC cluster.
NULLNULL 在主要複本上,當資料列與次要複本有關時就會傳回這個值。On a primary replica, this value is returned when the row relates to a secondary replica.

安全性Security

權限Permissions

需要伺服器的 VIEW SERVER STATE 權限。Requires VIEW SERVER STATE permission on the server.

另請參閱See Also

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