Share via


sys.availability_groups (Transact-SQL)

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_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 the ALTER AVAILABILITY GROUP Transact-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.

See Also

Reference

sys.availability_replicas (Transact-SQL)

Concepts

AlwaysOn Availability Groups (SQL Server)

Monitor Availability Groups (Transact-SQL)

Monitor Availability Groups (Transact-SQL)