sys.availability_replicas (Transact-SQL)

Applies to: SQL Server

Returns a row for each of the availability replicas that belong to any Always On availability groups in the Windows Server Failover Cluster (WSFC).

If the local server instance can't connect to the WSFC failover cluster, for example because the cluster is down or quorum is lost, sys.availability_replicas returns only rows for local availability replicas. These rows contain only the columns of data that are cached locally in metadata.

Column name	Data type	Description
replica_id	uniqueidentifier	Unique ID of the replica.
group_id	uniqueidentifier	Unique ID of the availability group to which the replica belongs.
replica_metadata_id	int	ID for the local metadata object for availability replicas in the Database Engine.
replica_server_name	nvarchar(256)	Server name of the instance of SQL Server that's hosting this replica and, for a non-default instance, its instance name.
owner_sid	varbinary(85)	Security identifier (SID) registered to this server instance for the external owner of this availability replica. NULL for non-local availability replicas.
endpoint_url	nvarchar(256)	String representation of the user-specified database mirroring endpoint that is used by connections between primary and secondary replicas for data synchronization. For information about the syntax of endpoint URLs, see Specify Endpoint URL - Adding or Modifying Availability Replica. NULL = Unable to talk to the WSFC failover cluster. To change this endpoint, use the ENDPOINT_URL option of ALTER AVAILABILITY GROUP Transact-SQL statement.
availability_mode	tinyint	The availability mode of the replica, one of: 0 = Asynchronous commit. The primary replica can commit transactions without waiting for the secondary to write the log to disk. 1 = Synchronous commit. The primary replica waits to commit a given transaction until the secondary replica has written the transaction to disk. 4 = Configuration only. The primary replica sends availability group configuration metadata to the replica synchronously. User data isn't transmitted to the replica. For more information, see Differences between availability modes for an Always On availability group. Applies to SQL Server 2017 (14.x) CU 1 and later versions.
availability_mode_desc	nvarchar(60)	Description of availability_mode, one of: ASYNCHRONOUS_COMMIT SYNCHRONOUS_COMMIT CONFIGURATION_ONLY To change the availability mode of an availability replica, use the AVAILABILITY_MODE option of the ALTER AVAILABILITY GROUP Transact-SQL statement. You can't change the availability mode of a replica to CONFIGURATION_ONLY. You can't change a CONFIGURATION_ONLY replica to a secondary or primary replica.
failover_mode	tinyint	The failover mode of the availability replica, one of: 0 = Automatic failover. The replica is a potential target for automatic failovers. Automatic failover is supported only if the availability mode is set to synchronous commit (availability_mode is 1) and the availability replica is currently synchronized. 1 = Manual failover. When a secondary replica is configured for manual failover, the database administrator must manually initiate the failover. The type of failover that is performed depends on whether the secondary replica is synchronized, as follows: If the availability replica isn't synchronizing or is still synchronizing, only forced failover (with possible data loss) can occur. If the availability mode is set to synchronous commit (availability_mode is 1) and the availability replica is currently synchronized, manual failover without data loss can occur. To view a rollup of the database synchronization health of every availability database in an availability replica, use the synchronization_health and synchronization_health_desc columns of the sys.dm_hadr_availability_replica_states dynamic management view. The rollup considers the synchronization state of every availability database and the availability mode of its availability replica. Note: To view the synchronization health of a given availability database, query the synchronization_state and synchronization_health columns of the sys.dm_hadr_database_replica_states dynamic management view.
failover_mode_desc	nvarchar(60)	Description of failover_mode, one of: MANUAL AUTOMATIC To change the failover mode, use the FAILOVER_MODE option of ALTER AVAILABILITY GROUP Transact-SQL statement.
session_timeout	int	The timeout period, in seconds. The timeout period is the maximum time that the replica waits to receive a message from another replica, before considering that the connection between the primary and secondary replica has failed. Session timeout detects whether secondaries are connected to the primary replica. On detecting a failed connection with a secondary replica, the primary replica considers the secondary replica to be NOT_SYNCHRONIZED. On detecting a failed connection with the primary replica, a secondary replica simply attempts to reconnect. Note: Session timeouts don't cause automatic failovers. To change this value, use the SESSION_TIMEOUT option of ALTER AVAILABILITY GROUP Transact-SQL statement.
primary_role_allow_connections	tinyint	Whether the availability allows all connections or only read-write connections, one of: 2 = All (default) 3 = Read write
primary_role_allow_connections_desc	nvarchar(60)	Description of primary_role_allow_connections, one of: ALL READ_WRITE
secondary_role_allow_connections	tinyint	Whether an availability replica that's performing the secondary role (that is, a secondary replica) can accept connections from clients, one of: 0 = No. No connections are allowed to the databases in the secondary replica, and the databases aren't available for read access. This is the default setting. 1 = Read only. Only read-only connections are allowed to the databases in the secondary replica. All databases in the replica are available for read access. 2 = All. All connections are allowed to the databases in the secondary replica for read-only access. For more information, see Offload read-only workload to secondary replica of an Always On availability group.
secondary_role_allow_connections_desc	nvarchar(60)	Description of secondary_role_allow_connections, one of: NO READ_ONLY ALL
create_date	datetime	Date that the replica was created. NULL = Replica not on this server instance.
modify_date	datetime	Date that the replica was last modified. NULL = Replica not on this server instance.
backup_priority	int	Represents the user-specified priority for performing backups on this replica relative to the other replicas in the same availability group. The value is an integer in the range of 0..100. For more information, see Offload supported backups to secondary replicas of an availability group.
read_only_routing_url	nvarchar(256)	Connectivity endpoint (URL) of the read only availability replica. For more information, see Configure read-only routing for an Always On availability group.
seeding_mode	tinyint	One of: 0 = Automatic 1 = Manual
seeding_mode_desc	nvarchar(60)	Describes seeding mode. AUTOMATIC MANUAL
read_write_routing_url	nvarchar(256)	Connectivity endpoint (URL) for when the replica is the primary. For more information, see Secondary to primary replica read/write connection redirection (Always On Availability Groups). Applies to: SQL Server 2019 (15.x) and later versions.

Permissions

Requires VIEW ANY DEFINITION permission on the server instance.

Related content

• sys.availability_groups (Transact-SQL)
• What is an Always On availability group?
• Monitor Availability Groups (Transact-SQL)