sys.availability_groups_cluster (Transact-SQL)

Article
05/16/2013

Returns a row for each AlwaysOn availability group in the Windows Server Failover Clustering (WSFC) . Each row contains the availability group metadata from the WSFC cluster.

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:

Value	Failure Condition
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 AlwaysOn Lease Timeout.
2	Specifies that an automatic failover should be initiated when any of the following occurs: The instance of SQL 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.
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 GROUP Transact-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 hung. The default value is 30000 milliseconds (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:

Value	Description
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 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 Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups).

automated_backup_preference_desc

nvarchar(60)

Description of automated_backup_preference, one of:

PRIMARY

SECONDARY_ONLY

SECONDARY

NONE

Security

Permissions

Requires VIEW ANY DEFINITION permission on the server instance.

sys.availability_groups_cluster (Transact-SQL)

Security

Permissions

See Also

Reference

Concepts

Additional resources