Updated: June 10, 2016
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 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 hung. The default value is 30000 milliseconds (30 seconds).|
To change this value, use the HEALTH_CHECK_TIMEOUT option of the ALTER AVAILABILITY GROUPTransact-SQL 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:|
|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.
|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 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.
Requires VIEW ANY DEFINITION permission on the server instance.