sys.availability_groups (Transact-SQL)sys.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)

針對裝載可用性複本的 SQL Server 主機本機執行個體的每一個可用性群組,各傳回一個資料列。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 name 資料類型Data type 描述Description
group_idgroup_id uniqueidentifieruniqueidentifier 可用性群組的唯一識別碼 (GUID)。Unique identifier (GUID) of the availability group.
namename sysnamesysname 可用性群組的名稱。Name of the availability group. 這是使用者指定的名稱,它在 Windows Server 容錯移轉叢集 (WSFC) 內必須是唯一的。This is a user-specified name that must be unique within the Windows Server Failover Cluster (WSFC).
resource_idresource_id nvarchar(40)nvarchar(40) WSFC 叢集資源的資源識別碼。Resource ID for the WSFC cluster resource.
resource_group_idresource_group_id nvarchar(40)nvarchar(40) 可用性群組之 WSFC 叢集資源群組的資源群組識別碼。Resource Group ID for the WSFC cluster resource group of the availability group.
failure_condition_levelfailure_condition_level intint 必須觸發自動容錯移轉的使用者定義失敗狀況層級,這是在此資料表正下方的表格中所顯示的其中一個整數值。User-defined failure condition level under which an automatic failover must be triggered, one of the integer values shown in the table immediately below this table.

失敗狀況層級 (1-5) 的範圍從最低限制 (層級 1) 到最高限制 (層級 5)。The failure-condition levels (1-5) range from the least restrictive, level 1, to the most restrictive, level 5. 給定的狀況層級包含所有較少限制的層級。A given condition level encompasses all of the less restrictive levels. 因此,最嚴格的狀況層級 5 包含四個較少限制的狀況層級 (1-4),層級 4 則包含層級 1-3,依此類推。Thus, the strictest condition level, 5, includes the four less restrictive condition levels (1-4), level 4 includes levels 1-3, and so forth.

若要變更這個值,請使用ALTER AVAILABILITY GROUP語句的 FAILURE_CONDITION_LEVEL 選項 Transact-SQLTransact-SQLTo change this value, use the FAILURE_CONDITION_LEVEL option of the ALTER AVAILABILITY GROUPTransact-SQLTransact-SQL statement.
health_check_timeouthealth_check_timeout intint 在假設伺服器實例變慢或沒有回應之前, sp_server_diagnostics 系統預存程式傳回伺服器健全狀況資訊的等候時間 (以毫秒為單位) 。Wait time (in milliseconds) for the sp_server_diagnostics system stored procedure to return server-health information, before the server instance is assumed to be slow or not responding. 預設值為 30000 毫秒 (30 秒)。The default value is 30000 milliseconds (30 seconds).

若要變更這個值,請使用ALTER AVAILABILITY GROUP語句的 HEALTH_CHECK_TIMEOUT 選項 Transact-SQLTransact-SQLTo change this value, use the HEALTH_CHECK_TIMEOUT option of the ALTER AVAILABILITY GROUPTransact-SQLTransact-SQL statement.
automated_backup_preferenceautomated_backup_preference tinyinttinyint 針對此可用性群組中的可用性資料庫執行備份的慣用位置。Preferred location for performing backups on the availability databases in this availability group. 以下是可能的值及其描述。The following are the possible values and their descriptions.



0:主要。0 : Primary. 備份一定要在主要複本上進行。Backups should always occur on the primary replica.

1:僅限次要。1 : Secondary only. 偏好針對次要複本執行備份。Performing backups on a secondary replica is preferable.

2:偏好次要。2 : Prefer Secondary. 偏好針對次要複本執行備份,但是如果沒有次要複本可用來執行備份作業,可以接受針對主要複本執行備份。Performing backups on a secondary replica preferable, but performing backups on the primary replica is acceptable if no secondary replica is available for backup operations. 這是預設行為。This is the default behavior.

3:任何複本。3 : Any Replica. 針對主要複本或次要複本執行備份沒有任何偏好。No preference about whether backups are performed on the primary replica or on a secondary replica.



如需詳細資訊,請參閱使用中次要:在次要複本上備份 (Always On 可用性群組)For more information, see Active Secondaries: Backup on Secondary Replicas (Always On Availability Groups).
automated_backup_preference_descautomated_backup_preference_desc nvarchar(60)nvarchar(60) Automated_backup_preference 的描述,下列其中一個:Description of automated_backup_preference, one of:

PRIMARYPRIMARY

SECONDARY_ONLYSECONDARY_ONLY

SECONDARYSECONDARY

NONE
versionversion smallintsmallint 儲存在 Windows 容錯移轉叢集中的可用性群組中繼資料版本。The version of the availability group metadata stored in the Windows Failover Cluster. 新增功能時,此版本號碼會遞增。This version number is incremented when new features are added.
basic_featuresbasic_features bitbit 指定這是否為基本可用性群組。Specifies whether this is a Basic availability group. 如需詳細資訊,請參閱基本可用性群組 (AlwaysOn 可用性群組)For more information, see Basic Availability Groups (Always On Availability Groups).
dtc_supportdtc_support bitbit 指定是否已啟用此可用性群組的 DTC 支援。Specifies whether DTC support has been enabled for this availability group. CREATE AVAILABILITY GROUPDTC_SUPPORT 選項控制此設定。The DTC_SUPPORT option of CREATE AVAILABILITY GROUP controls this setting.
db_failoverdb_failover bitbit 指定可用性群組是否支援資料庫健康情況的容錯移轉。Specifies whether the availability group supports failover for database health conditions. CREATE AVAILABILITY GROUPDB_FAILOVER 選項控制此設定。The DB_FAILOVER option of CREATE AVAILABILITY GROUP controls this setting.
is_distributedis_distributed bitbit 指定這是否為分散式可用性群組。Specifies whether this is a distributed availability group. 如需詳細資訊,請參閱分散式可用性群組 (AlwaysOn 可用性群組)For more information, see Distributed Availability Groups (Always On Availability Groups).
cluster_typecluster_type tinyinttinyint 0: Windows Server 容錯移轉叢集0: Windows Server failover cluster

1: External cluster (例如 Linux Pacemaker) 1: External cluster (for example, Linux Pacemaker)

2:無2: None
cluster_type_desccluster_type_desc nvarchar(60)nvarchar(60) 叢集類型的文字描述Text description of cluster type
required_synchronized_secondaries_to_commitrequired_synchronized_secondaries_to_commit intint 必須處於已同步狀態才能完成認可的次要複本數目The number of secondary replicas that must be in a synchronized state for a commit to complete
sequence_numbersequence_number bigintbigint 識別可用性群組設定順序。Identifies the availability group configuration sequence. 每次可用性群組主要複本更新群組的設定時,以累加方式增加。Incrementally increases every time the availability group primary replica updates the configuration of the group.
is_containedis_contained bitbit 1:設定為高可用性的 Big data cluster 主要實例。1: Big data cluster master instance configured for high-availability.

0:其他。0: all other.

失敗狀況層級值Failure condition level values

下表描述 failure_condition_level 資料行可能的失敗狀況層級。The following table describes the possible failure condition levels for the failure_condition_level column.

Value 失敗狀況Failure condition
11 指定在發生以下任何情況時應該起始自動容錯移轉:Specifies that an automatic failover should be initiated when any of the following occurs:



- SQL ServerSQL Server 服務已關閉。- The SQL ServerSQL Server service is down.

-用於連接到 WSFC 容錯移轉叢集的可用性群組租用已過期,因為未從伺服器實例收到 ACK。- The lease of the availability group for connecting to the WSFC failover cluster expires because no ACK is received from the server instance. 如需詳細資訊,請參閱 How It Works:SQL Server Always On Lease Timeout (運作方式:SQL Server Always On 租用逾時)。For more information, see How It Works: SQL Server Always On Lease Timeout.
22 指定在發生以下任何情況時應該起始自動容錯移轉:Specifies that an automatic failover should be initiated when any of the following occurs:



-實例不 SQL ServerSQL Server 會連接到叢集,而且會超過可用性群組的使用者指定 health_check_timeout 臨界值。- The instance of SQL ServerSQL Server does not connect to cluster, and the user-specified health_check_timeout threshold of the availability group is exceeded.

-可用性複本處於失敗狀態。- The availability replica is in failed state.
33 指定應該在嚴重 SQL ServerSQL Server 內部錯誤發生時起始自動容錯移轉,例如執行緒同步鎖定遭到遺棄、嚴重的寫入存取違規或是傾印過多。Specifies that an automatic failover should be initiated on critical SQL ServerSQL Server internal errors, such as orphaned spinlocks, serious write-access violations, or too much dumping.

這是預設值。This is the default value.
44 指定應該在發生中度 SQL ServerSQL Server 內部錯誤時起始自動容錯移轉,例如 SQL ServerSQL Server 內部資源集區中持續的記憶體不足狀況。Specifies that an automatic failover should be initiated on moderate SQL ServerSQL Server internal errors, such as a persistent out-of-memory condition in the SQL ServerSQL Server internal resource pool.
55 指定應該在發生任何符合的失敗狀況時起始自動容錯移轉,這些狀況包括:Specifies that an automatic failover should be initiated on any qualified failure conditions, including:



-SQL 引擎背景工作執行緒耗盡。- Exhaustion of SQL Engine worker-threads.

-偵測無法解決鎖死。- Detection of an unsolvable deadlock.

安全性Security

權限Permissions

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

另請參閱See Also

sys.availability_replicas (Transact-SQL) sys.availability_replicas (Transact-SQL)
AlwaysOn 可用性群組 (SQL Server) Always On Availability Groups (SQL Server)
監視可用性群組 (Transact-sql) Monitor Availability Groups (Transact-SQL)
監視可用性群組 (Transact-SQL)Monitor Availability Groups (Transact-SQL)