Edit

sys.availability_groups_cluster (Transact-SQL)

  • Article

Applies to: 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 SQL Server 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 SQL Server 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 SQL Server 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 SQL Server internal errors, such as a persistent out-of-memory condition in the SQL Server 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 GROUPTransact-SQL 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 GROUPTransact-SQL 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

Remarks

In a Windows Server Failover Cluster (WSFC), the cluster columns display the Windows cluster details. In cases where there's no Windows cluster, such as read-scale availability groups, or availability groups on Linux, columns related to the cluster might display data about an internal default cluster. These columns are for internal use only and can be disregarded.

Permissions

Requires VIEW ANY DEFINITION permission on the server instance.

Related content