sys.availability_replicas (Transact-SQL)sys.availability_replicas (Transact-SQL)

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

針對屬於 WSFC 容錯移轉叢集中 Always On 可用性群組的每個可用性複本傳回一個資料列。Returns a row for each of the availability replicas that belong to any Always On availability group in the WSFC failover cluster.

如果本機伺服器執行個體無法與 WSFC 容錯移轉叢集聯繫,例如由於叢集已關閉或仲裁已遺失,則只會傳回本機可用性複本的資料列。If the local server instance is unable to talk to the WSFC failover cluster, for example because the cluster is down or quorum has been lost, only rows for local availability replicas are returned. 這些資料列只會包含在本機快取於中繼資料內的資料行。These rows will contain only the columns of data that are cached locally in metadata.

資料行名稱Column name 資料類型Data type 描述Description
replica_idreplica_id uniqueidentifieruniqueidentifier 複本的唯一識別碼。Unique ID of the replica.
group_idgroup_id uniqueidentifieruniqueidentifier 複本所屬之可用性群組的唯一識別碼。Unique ID of the availability group to which the replica belongs.
replica_metadata_idreplica_metadata_id intint Database Engine 中可用性複本之本機中繼資料物件的識別碼。ID for the local metadata object for availability replicas in the Database Engine.
replica_server_namereplica_server_name nvarchar(256)nvarchar(256) 裝載這個複本之 SQL ServerSQL Server 執行個體的伺服器名稱,如果是非預設執行個體,則是它的執行個體名稱。Server name of the instance of SQL ServerSQL Server that is hosting this replica and, for a non-default instance, its instance name.
owner_sidowner_sid varbinary(85)varbinary(85) 針對這個可用性複本的外部擁有者,註冊給這個伺服器執行個體的安全性識別碼 (SID)。Security identifier (SID) registered to this server instance for the external owner of this availability replica.

非本機可用性複本為 NULL。NULL for non-local availability replicas.
endpoint_urlendpoint_url nvarchar(128)nvarchar(128) 使用者指定之資料庫鏡像端點的字串表示法,該端點是由主要與次要複本之間的資料同步處理連接所使用。String representation of the user-specified database mirroring endpoint that is used by connections between primary and secondary replicas for data synchronization. 如需這些端點 URL 語法的相關資訊,請參閱在加入或修改可用性複本時指定端點 URL (SQL Server)For information about the syntax of endpoint URLs, see Specify the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server).

NULL = 無法聯繫 WSFC 容錯移轉叢集。NULL = Unable to talk to the WSFC failover cluster.

若要變更這個端點,請使用ALTER AVAILABILITY GROUP語句的 ENDPOINT_URL 選項 Transact-SQLTransact-SQLTo change this endpoint, use the ENDPOINT_URL option of ALTER AVAILABILITY GROUPTransact-SQLTransact-SQL statement.
availability_modeavailability_mode tinyinttinyint 複本的可用性模式,下列其中一項:The availability mode of the replica, one of:

0 | 非同步認可。0 | Asynchronous commit. 主要複本可認可交易,而不需要等候次要複本將記錄寫入磁碟中。The primary replica can commit transactions without waiting for the secondary to write the log to disk.

1 | 同步認可。1 | Synchronous commit. 主要複本會等候認可給定交易,直到次要複本將交易寫入磁碟為止。The primary replica waits to commit a given transaction until the secondary replica has written the transaction to disk.

4 | 僅限設定。4 | Configuration only. 主要複本會以同步方式將可用性群組設定中繼資料傳送至複本。The primary replica sends availability group configuration metadata to the replica synchronously. 使用者資料不會傳送至複本。User data is not transmitted to the replica. 可在 SQL Server 2017 CU1 和更新版本中使用。Available in SQL Server 2017 CU1 and later.

如需詳細資訊,請參閱 可用性模式 (AlwaysOn 可用性群組)或 PowerShell,針對 AlwaysOn 可用性群組執行規劃的手動容錯移轉或強制手動容錯移轉 (強制容錯移轉)。For more information, see Availability Modes (Always On Availability Groups).
availability_mode_descavailability_mode_desc nvarchar(60)nvarchar(60) 可用性 _ 模式 的描述,下列其中一個:Description of availability_mode, one of:

非同步 _ 認可ASYNCHRONOUS_COMMIT

同步 _ 認可SYNCHRONOUS_COMMIT

_僅限設定CONFIGURATION_ONLY

若要變更可用性複本的可用性模式,請使用ALTER AVAILABILITY GROUP語句的 AVAILABILITY_MODE 選項 Transact-SQLTransact-SQLTo change this the availability mode of an availability replica, use the AVAILABILITY_MODE option of ALTER AVAILABILITY GROUPTransact-SQLTransact-SQL statement.

您無法將複本的可用性模式變更為 [ _ 僅限設定]。You cannot change the availability mode of a replica to CONFIGURATION_ONLY. 您無法將 _ 僅限設定複本變更為次要或主要複本。You cannot change a CONFIGURATION_ONLY replica to a secondary or primary replica.
容錯移轉 _ 模式failover_mode tinyinttinyint 可用性複本的 容錯移轉模式 ,下列其中一個:The failover mode of the availability replica, one of:

0 | 自動容錯移轉。0 | Automatic failover. 此複本可能是自動容錯移轉的目標。The replica is a potential target for automatic failovers. 只有當可用性模式設定為同步認可 (可用性 _ 模式 = 1) 且可用性複本目前已同步處理時,才支援自動容錯移轉。Automatic failover is supported only if the availability mode is set to synchronous commit (availability_mode = 1) and the availability replica is currently synchronized.

1 | 手動容錯移轉。1 | Manual failover. 如果容錯移轉到次要複本的程序設定為手動容錯移轉,則必須由資料庫管理員手動起始。A failover to a secondary replica set to manual failover must be manually initiated by the database administrator. 執行的容錯移轉類型將取決於次要複本是否同步處理,如下所示:The type of failover that is performed will depend on whether the secondary replica is synchronized, as follows:

如果可用性複本並未同步處理或者依然在同步處理,只會發生強制容錯移轉 (可能會遺失資料)。If the availability replica is not synchronizing or is still synchronizing, only forced failover (with possible data loss) can occur.

如果可用性模式設定為同步認可 (可用性 _ 模式 = 1) 且可用性複本目前已同步處理,則不會遺失資料的手動容錯移轉。If the availability mode is set to synchronous commit (availability_mode = 1) and the availability replica is currently synchronized, manual failover without data loss can occur.

若要在可用性複本中查看每個可用性資料庫的資料庫同步處理健全狀況匯總,請使用 sys.dm_hadr_availability_replica_states動態管理檢視的 [同步處理 _ 健全 狀況] 和 [同步處理健全狀況 _ _ desc ] 資料行。To view a rollup of the database synchronization health of every availability database in an availability replica, use the synchronization_health and synchronization_health_desc columns of the sys.dm_hadr_availability_replica_states dynamic management view. 此積存會考量每個可用性資料庫的同步處理狀態及其可用性複本的可用性模式。The rollup considers the synchronization state of every availability database and the availability mode of its availability replica.

注意: 若要查看給定可用性資料庫的同步處理健全狀況,請查詢 sys.dm_hadr_database_replica_states動態管理檢視的 [同步處理 _ 狀態] 和 [同步處理 _ 健全狀況] 資料行。Note: To view the synchronization health of a given availability database, query the synchronization_state and synchronization_health columns of the sys.dm_hadr_database_replica_states dynamic management view.
容錯移轉 _ 模式 _ descfailover_mode_desc nvarchar(60)nvarchar(60) 容錯移轉 _ 模式 的描述,下列其中一個:Description of failover_mode, one of:

MANUALMANUAL

AUTOMATICAUTOMATIC

若要變更容錯移轉模式,請使用 _ ALTER AVAILABILITY GROUP語句的 [容錯移轉模式] 選項 Transact-SQLTransact-SQLTo change the failover mode, use the FAILOVER_MODE option of ALTER AVAILABILITY GROUPTransact-SQLTransact-SQL statement.
會話 _ 超時session_timeout intint 逾時期間 (以秒為單位)。The time-out period, in seconds. 逾時期間是將主要複本與次要複本之間的連接視為失敗之前,複本等待接收另一個複本之訊息的時間上限。The time-out period is the maximum time that the replica waits to receive a message from another replica before considering connection between the primary and secondary replica have failed. 工作階段逾時會偵測次要複本是否連接到主要複本。Session timeout detects whether secondaries are connected the primary replica.

當偵測到與次要複本之間的連接失敗時,主要複本會將次要複本視為未 _ 同步處理。On detecting a failed connection with a secondary replica, the primary replica considers the secondary replica to be NOT_SYNCHRONIZED. 一旦偵測到與主要複本之間的連接失敗時,次要複本只會嘗試重新連接。On detecting a failed connection with the primary replica, a secondary replica simply attempts to reconnect.

注意: 會話超時不會造成自動容錯移轉。Note: Session timeouts do not cause automatic failovers.

若要變更這個值,請使用ALTER AVAILABILITY GROUP語句的 SESSION_TIMEOUT 選項 Transact-SQLTransact-SQLTo change this value, use the SESSION_TIMEOUT option of ALTER AVAILABILITY GROUPTransact-SQLTransact-SQL statement.
主要 _ 角色 _ 允許 _ 連接primary_role_allow_connections tinyinttinyint 可用性允許所有連接還是只允許讀寫連接,下列其中一項:Whether the availability allows all connections or only read-write connections, one of:

2 = 所有連接 (預設值)2 = All (default)

3 = 讀寫連接3 = Read write
主要 _ 角色 _ 允許 _ 連接 _ descprimary_role_allow_connections_desc nvarchar(60)nvarchar(60) 主要 _ 角色 _ 允許 _ 連接 的描述,下列其中一個:Description of primary_role_allow_connections, one of:

ALLALL

讀 _ 寫READ_WRITE
次要 _ 角色 _ 允許 _ 連接secondary_role_allow_connections tinyinttinyint 執行次要角色的可用性複本 (也就是次要複本) 是否可接受來自用戶端的連接,下列其中一個值:Whether an availability replica that is performing the secondary role (that is, a secondary replica) can accept connections from clients, one of:

0 = 否。0 = No. 不允許連接次要複本的資料庫,這些資料庫也不可用於讀取存取。No connections are allowed to the databases in the secondary replica, and the databases are not available for read access. 這是預設值。This is the default setting.

1 = 唯讀。1 = Read only. 只允許與次要複本的資料庫進行唯讀連接。Only read-only connections are allowed to the databases in the secondary replica. 可讀取複本中的所有資料庫。All database(s) in the replica are available for read access.

2 = 全部。2 = All. 次要複本的資料庫允許所有連接進行唯讀存取。All connections are allowed to the databases in the secondary replica for read-only access.

如需詳細資訊,請參閱使用中次要:可讀取的次要複本 (Always On 可用性群組)For more information, see Active Secondaries: Readable Secondary Replicas (Always On Availability Groups).
secondary_role_allow_connections_descsecondary_role_allow_connections_desc nvarchar(60)nvarchar(60) Secondary_role_allow_connections 的描述,下列其中一個:Description of secondary_role_allow_connections, one of:

NO

READ_ONLYREAD_ONLY

ALLALL
create_datecreate_date datetimedatetime 建立複本的日期。Date that the replica was created.

NULL = 複本不在這個伺服器執行個體上。NULL = Replica not on this server instance.
modify_datemodify_date datetimedatetime 上次修改複本的日期。Date that the replica was last modified.

NULL = 複本不在這個伺服器執行個體上。NULL = Replica not on this server instance.
backup_prioritybackup_priority intint 表示使用者為了在這個複本上執行備份所指定的優先權 (相對於相同可用性群組中的其他複本)。Represents the user-specified priority for performing backups on this replica relative to the other replicas in the same availability group. 這個值是 0 到 100 範圍之間的整數。The value is an integer in the range of 0..100.

如需詳細資訊,請參閱使用中次要:在次要複本上備份 (Always On 可用性群組)For more information, see Active Secondaries: Backup on Secondary Replicas (Always On Availability Groups).
read_only_routing_urlread_only_routing_url nvarchar(256)nvarchar(256) 唯讀可用性複本的連接端點 (URL)。Connectivity endpoint (URL) of the read only availability replica. 如需詳細資訊,請參閱本主題稍後的 設定可用性群組的唯讀路由 (SQL Server))。For more information, see Configure Read-Only Routing for an Availability Group (SQL Server).
seeding_modeseeding_mode tinyinttinyint 值為下列其中之一:One of:
0:自動0: Automatic

1:手動1: Manual
seeding_mode_descseeding_mode_desc nvarchar(60)nvarchar(60) 說明植入模式。Describes seeding mode.
AUTOMATICAUTOMATIC

MANUALMANUAL

安全性Security

權限Permissions

需要伺服器執行個體的 VIEW ANY DEFINITION 權限。Requires VIEW ANY DEFINITION permission on the server instance.

另請參閱See Also

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