| title | sys.availability_groups (Transact-SQL) | Microsoft Docs | ||||
|---|---|---|---|---|---|
| ms.custom | |||||
| ms.date | 06/10/2016 | ||||
| ms.prod | sql | ||||
| ms.prod_service | database-engine | ||||
| ms.reviewer | |||||
| ms.technology | system-objects | ||||
| ms.topic | language-reference | ||||
| f1_keywords |
|
||||
| dev_langs |
|
||||
| helpviewer_keywords |
|
||||
| ms.assetid | da7fa55f-c008-45d9-bcfc-3513b02d9e71 | ||||
| author | CarlRabeler | ||||
| ms.author | carlrab |
[!INCLUDEtsql-appliesto-ss2012-xxxx-xxxx-xxx-md]
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 integer values shown in the table immediately below this table. 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[!INCLUDEtsql] 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 milliseconds (30 seconds). To change this value, use the HEALTH_CHECK_TIMEOUT option of the ALTER AVAILABILITY GROUP[!INCLUDEtsql] statement. |
| automated_backup_preference | tinyint | Preferred location for performing backups on the availability databases in this availability group. The following are the possible values and their descriptions. 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 (Always On Availability Groups). |
| automated_backup_preference_desc | nvarchar(60) | Description of automated_backup_preference, one of: PRIMARY SECONDARY_ONLY SECONDARY NONE |
| version | smallint | The version of the availability group metadata stored in the Windows Failover Cluster. This version number is incremented when new features are added. |
| basic_features | bit | Specifies whether this is a Basic availability group. For more information, see Basic Availability Groups (Always On Availability Groups). |
| dtc_support | bit | Specifies whether DTC support has been enabled for this availability group. The DTC_SUPPORT option of CREATE AVAILABILITY GROUP controls this setting. |
| db_failover | bit | Specifies whether the availability group supports failover for database health conditions. The DB_FAILOVER option of CREATE AVAILABILITY GROUP controls this setting. |
| is_distributed | bit | Specifies whether this is a distributed availability group. For more information, see Distributed Availability Groups (Always On Availability Groups). |
The following table describes the possible failure condition levels for the failure_condition_level column.
| Value | Failure condition |
|---|---|
| 1 | Specifies that an automatic failover should be initiated when any of the following occurs: - The [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] internal errors, such as a persistent out-of-memory condition in the [!INCLUDEssNoVersion] 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. |
Requires VIEW ANY DEFINITION permission on the server instance.
sys.availability_replicas (Transact-SQL)
Always On Availability Groups (SQL Server)
Monitor Availability Groups (Transact-SQL)
Monitor Availability Groups (Transact-SQL)