Table of Contents

Class AvailabilitySchema.AvailabilityReplica

Namespace
LinqToDB.Tools.DataProvider.SqlServer.Schemas
Assembly
linq2db.Tools.dll

sys.availability_replicas (Transact-SQL)

Applies to: √ SQL Server (all supported versions)

Returns a row for each of the availability replicas that belong to any Always On availability group in the WSFC failover cluster.
If the local server instance is unable to talk to the WSFC failover cluster, for example because the cluster is down or quorum has been lost, only rows for local availability replicas are returned. These rows will contain only the columns of data that are cached locally in metadata.


See sys.availability_replicas.

[Table(Schema = "sys", Name = "availability_replicas", IsView = true)]
public class AvailabilitySchema.AvailabilityReplica
Inheritance
AvailabilitySchema.AvailabilityReplica
Extension Methods

Properties

AvailabilityMode

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 is not transmitted to the replica. Available in SQL Server 2017 CU1 and later.

For more information, see Availability Modes (Always On Availability Groups).

[Column("availability_mode")]
[Nullable]
public byte? AvailabilityMode { get; set; }

Property Value

byte?

AvailabilityModeDesc

Description of availability_mode, one of:

ASYNCHRONOUS_COMMIT

SYNCHRONOUS_COMMIT

CONFIGURATION_ONLY

To change this the availability mode of an availability replica, use the AVAILABILITY_MODE option of ALTER AVAILABILITY GROUPTransact-SQL statement.

You cannot change the availability mode of a replica to CONFIGURATION_ONLY. You cannot change a CONFIGURATION_ONLY replica to a secondary or primary replica.

[Column("availability_mode_desc")]
[Nullable]
public string? AvailabilityModeDesc { get; set; }

Property Value

string

BackupPriority

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

[Column("backup_priority")]
[Nullable]
public int? BackupPriority { get; set; }

Property Value

int?

CreateDate

Date that the replica was created.

NULL = Replica not on this server instance.

[Column("create_date")]
[Nullable]
public DateTime? CreateDate { get; set; }

Property Value

DateTime?

EndpointUrl

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 the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server).

NULL = Unable to talk to the WSFC failover cluster.

To change this endpoint, use the ENDPOINT_URL option of ALTER AVAILABILITY GROUPTransact-SQL statement.

[Column("endpoint_url")]
[Nullable]
public string? EndpointUrl { get; set; }

Property Value

string

FailoverMode

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 = 1) and the availability replica is currently synchronized.

1 | Manual failover. A failover to a secondary replica set to manual failover must be manually initiated by the database administrator. The type of failover that is performed will depend on whether the secondary replica is synchronized, as follows:

If the availability replica is not 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 = 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.

[Column("failover_mode")]
[Nullable]
public byte? FailoverMode { get; set; }

Property Value

byte?

FailoverModeDesc

Description of failover_mode, one of:

MANUAL

AUTOMATIC

To change the failover mode, use the FAILOVER_MODE option of ALTER AVAILABILITY GROUPTransact-SQL statement.

[Column("failover_mode_desc")]
[Nullable]
public string? FailoverModeDesc { get; set; }

Property Value

string

GroupID

Unique ID of the availability group to which the replica belongs.

[Column("group_id")]
[Nullable]
public Guid? GroupID { get; set; }

Property Value

Guid?

ModifyDate

Date that the replica was last modified.

NULL = Replica not on this server instance.

[Column("modify_date")]
[Nullable]
public DateTime? ModifyDate { get; set; }

Property Value

DateTime?

OwnerSID

Security identifier (SID) registered to this server instance for the external owner of this availability replica.

NULL for non-local availability replicas.

[Column("owner_sid")]
[Nullable]
public byte[]? OwnerSID { get; set; }

Property Value

byte[]

PrimaryRoleAllowConnections

Whether the availability allows all connections or only read-write connections, one of:

2 = All (default)

3 = Read write

[Column("primary_role_allow_connections")]
[Nullable]
public byte? PrimaryRoleAllowConnections { get; set; }

Property Value

byte?

PrimaryRoleAllowConnectionsDesc

Description of primary_role_allow_connections, one of:

ALL

READ_WRITE

[Column("primary_role_allow_connections_desc")]
[Nullable]
public string? PrimaryRoleAllowConnectionsDesc { get; set; }

Property Value

string

ReadOnlyRoutingUrl

Connectivity endpoint (URL) of the read only availability replica. For more information, see Configure Read-Only Routing for an Availability Group (SQL Server).

[Column("read_only_routing_url")]
[Nullable]
public string? ReadOnlyRoutingUrl { get; set; }

Property Value

string

ReplicaID

Unique ID of the replica.

[Column("replica_id")]
[Nullable]
public Guid? ReplicaID { get; set; }

Property Value

Guid?

ReplicaMetadataID

ID for the local metadata object for availability replicas in the Database Engine.

[Column("replica_metadata_id")]
[Nullable]
public int? ReplicaMetadataID { get; set; }

Property Value

int?

ReplicaServerName

Server name of the instance of SQL Server that is hosting this replica and, for a non-default instance, its instance name.

[Column("replica_server_name")]
[Nullable]
public string? ReplicaServerName { get; set; }

Property Value

string

SecondaryRoleAllowConnections

Whether an availability replica that is 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 are not 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 database(s) 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 Active Secondaries: Readable Secondary Replicas (Always On Availability Groups).

[Column("secondary_role_allow_connections")]
[Nullable]
public byte? SecondaryRoleAllowConnections { get; set; }

Property Value

byte?

SecondaryRoleAllowConnectionsDesc

Description of secondary_role_allow_connections, one of:

NO

READ_ONLY

ALL

[Column("secondary_role_allow_connections_desc")]
[Nullable]
public string? SecondaryRoleAllowConnectionsDesc { get; set; }

Property Value

string

SeedingMode

One of:

0: Automatic

1: Manual

[Column("seeding_mode")]
[Nullable]
public byte? SeedingMode { get; set; }

Property Value

byte?

SeedingModeDesc

Describes seeding mode.

AUTOMATIC

MANUAL

[Column("seeding_mode_desc")]
[Nullable]
public string? SeedingModeDesc { get; set; }

Property Value

string

SessionTimeout

The time-out period, in seconds. The time-out period is the maximum time that the replica waits to receive a message from another replica before considering connection between the primary and secondary replica have failed. Session timeout detects whether secondaries are connected 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 do not cause automatic failovers.

To change this value, use the SESSION_TIMEOUT option of ALTER AVAILABILITY GROUPTransact-SQL statement.

[Column("session_timeout")]
[Nullable]
public int? SessionTimeout { get; set; }

Property Value

int?