| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.availability_groups_cluster (Transact-SQL) |
Returns a row for each Always On availability group in the Windows Server Failover Clustering (WSFC). |
rwestMSFT |
randolphwest |
10/17/2023 |
sql |
system-objects |
reference |
|
|
|
[!INCLUDE SQL Server]
Returns a row for each Always On availability group in Windows Server Failover Clustering (WSFC). Each row contains the availability group metadata from the WSFC cluster.
| Column name | Data type | Description |
|---|---|---|
group_id |
uniqueidentifier | Unique identifier (GUID) of the availability group. |
name |
sysname | Name of the availability group. This is a user-specified name that must be unique within the Windows Server Failover Cluster (WSFC). |
resource_id |
nvarchar(40) | Resource ID for the WSFC cluster resource. |
resource_group_id |
nvarchar(40) | Resource Group ID for the WSFC cluster resource group of the availability group. |
failure_condition_level |
int | User-defined failure condition level under which an automatic failover must be triggered, one of the following integer values: 1: Specifies that an automatic failover should be initiated when any of the following occurs: - The [!INCLUDE ssNoVersion] service is down. - The lease of the availability group for connecting to the WSFC failover cluster expires because no ACK is received from the server instance. For more information, see How It Works: SQL Server Always On Lease Timeout. 2: Specifies that an automatic failover should be initiated when any of the following occurs: - The instance of [!INCLUDE ssNoVersion] doesn't connect to cluster, and the user-specified health_check_timeout threshold of the availability group is exceeded.- The availability replica is in failed state. 3: Specifies that an automatic failover should be initiated on critical [!INCLUDE ssNoVersion] internal errors, such as orphaned spinlocks, serious write-access violations, or too much dumping. This is the default value. 4: Specifies that an automatic failover should be initiated on moderate [!INCLUDE ssNoVersion] internal errors, such as a persistent out-of-memory condition in the [!INCLUDE ssNoVersion] internal resource pool. 5: Specifies that an automatic failover should be initiated on any qualified failure conditions, including: - Exhaustion of SQL Engine worker-threads. - Detection of an unsolvable deadlock. 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. Thus, the strictest condition level, 5, includes the four less restrictive condition levels (1-4), level 4 includes levels 1-3, and so forth. To change this value, use the FAILURE_CONDITION_LEVEL option of the ALTER AVAILABILITY GROUP[!INCLUDE tsql] statement. |
health_check_timeout |
int | 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. The default value is 30000 (30,000 milliseconds, or 30 seconds).To change this value, use the HEALTH_CHECK_TIMEOUT option of ALTER AVAILABILITY GROUP[!INCLUDE tsql] statement. |
automated_backup_preference |
tinyint | Preferred location for performing backups on the availability databases in this availability group. One of the following values: 0: Primary. Backups should always occur on the primary replica. 1: Secondary only. Performing backups on a secondary replica is preferable. 2: Prefer Secondary. Performing backups on a secondary replica is 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: Any Replica. No preference about whether backups are performed on the primary replica or on a secondary replica. For more information, see Offload supported backups to secondary replicas of an availability group. |
automated_backup_preference_desc |
nvarchar(60) | Description of automated_backup_preference, one of:PRIMARY SECONDARY_ONLY SECONDARY NONE |
[!INCLUDE dmv-cluster-column-display]
Requires VIEW ANY DEFINITION permission on the server instance.