Shulou(Shulou.com)06/01 Report--

7 、 Sys. Availability_replicas

Returns a row for each availability copy that belongs to any AlwaysOn availability group in the WSFC failover cluster.

If the local server instance cannot contact the WSFC failover cluster, for example, due to cluster shutdown or loss of quorum, only the rows of the local availability copy are returned. These rows will contain only data columns that are cached locally in the metadata.

Column name

Data type

Description

Replica_id

Uniqueidentifier

The unique ID of the copy.

Group_id

Uniqueidentifier

The unique ID of the availability group to which the replica belongs.

Replica_metadata_id

Int

The ID of the local metadata object for the available copy in the database engine.

Replica_server_name

Nvarchar (256)

The server name of the SQL Server instance hosting this copy or, for a non-default instance, its instance name.

Owner_sid

Varbinary (85)

The security identifier (SID) registered with this server instance by the external owner of this availability copy.

NULL for non-local availability copies.

Endpoint_url

Nvarchar (128)

A string representation of the user-specified database mirroring endpoint used by the connection between the primary and secondary replicas used for data synchronization. For information about the URL syntax of these endpoints, see specifying Endpoint URL (SQL Server) when adding or modifying an available copy.

NULL = unable to contact the WSFC failover cluster.

To change this endpoint, use the ENDPOINT_URL option ALTER AVAILABILITY GROUP T-SQL statement.

Availability_mode

Availability_mode_desc

Tinyint

Nvarchar (60)

The availability mode of the copy, which can be one of the following values:

0 | ASYNCHRONOUS_COMMIT submitted asynchronously. The primary copy can commit the transaction without waiting for the secondary copy to write the log to disk.

1 | SYNCHRONOUS_COMMIT synchronous submission. The primary copy waits for the given transaction to be committed until the secondary copy writes the transaction to disk.

4 | CONFIGURATION_ONLY is only configured. The availability group configuration metadata that the master replica sends synchronously to the replica. User data is not transferred to the copy. Available in SQL Server 2017 CU1 and later.

To change the availability mode of the availability copy of this setting, use the AVAILABILITY_MODE option ALTER AVAILABILITY GROUP T-SQL statement. You cannot change the availability mode of a replica to CONFIGURATION_ONLY. You cannot change the CONFIGURATION_ONLY copy to be added to the secondary or primary copy.

For more information, see availability modes (AlwaysOn availability groups).

Failover_mode

Failover_mode_desc

Tinyint

Nvarchar (60)

One of the failover mode availability copies:

0 | automatic failover of AUTOMATIC. Replicas are potential targets for automatic failover. The availability mode is set to synchronous commit before automatic failover (availability_mode = 1) is supported and the availability copy is currently synchronized.

1 | MANUAL manual failover. Failover to the secondary copy that is set to manual failover must be initiated manually by the database administrator. The type of failover to be performed will depend on whether the secondary replica is synchronized, as follows: if the availability copy is not synchronized or is still synchronizing, only a forced failover can occur (data loss may occur); if the availability mode is set to synchronous commit (availability_mode = 1) and the availability copy is currently synchronized, manual failover will not cause data loss.

To view a summary of database synchronization health for each availability database in the availability copy, use the columns of the Sys.dm_hadr_availability_replica_states dynamic management views synchronization_health and synchronization_health _ desc. This summary takes into account the synchronization status of each availability database and the availability mode of its availability copy.

Note: to view the synchronous 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.

To change the failover mode, use the ALTER AVAILABILITY GROUP Transact-SQL statement with the Failover_mode option.

Session_timeout

Int

Timeout period (seconds). The timeout period is the maximum time a replica waits to receive messages from other replicas, after which the connection between the primary and secondary replicas is considered to have failed. The session timeout detects whether the secondary copy is connected to the primary copy.

When a failure to connect to the secondary copy is detected, the primary copy will be "out of sync" of the secondary copy. When a failed connection to the secondary copy is detected, the secondary copy only attempts to reconnect.

Note: session timeout does not cause automatic failover.

To change this value, use the ALTER AVAILABILITY GROUP Transact-SQL statement with the SESSION_TIMEOUT option.

Primary_role_allow_connections

Primary_role_allow_connections_desc

Tinyint

Nvarchar (60)

Availability allows all connections or only read-write connections, where:

2 = ALL all (default)

3 = READ_WRITE read and write

Secondary_role_allow_connections

Secondary_role_allow_connections_desc

Tinyint

Nvarchar (60)

Whether the available copy (that is, the secondary copy) that is performing the secondary role can accept connections from the client can be one of the following values:

0 = NO No. Connections to databases in the secondary replica are not allowed, and reading of these databases is not supported. This is the default setting.

1 = READ_ONLY read-only. Only read-only connections are allowed for databases in the secondary copy. All databases in the replica are available for read access.

2 = ALL all. Allows read-only access to all connections to the database in the secondary copy.

For more information, see active secondary copy: readable secondary copy (Always On availability group).

Create_date

Datetime

The date the copy was created.

NULL = the copy is not located on this server instance.

Modify_date

Datetime

The date on which the copy was last modified.

NULL = the copy is not located on this server instance.

Backup_priority

Int

Represents the priority specified by the user who performed the backup on this copy over other copies in the same availability group. The value is an integer in the range 0. 100.

"for more information, see active secondary copy: secondary copy backup (Always On availability group)."

Read_only_routing_url

Nvarchar (256)

The connection endpoint (URL) of the read-only availability copy. For more information, see configuring read-only routing (SQL Server) for availability groups.

Seeding_mode

Seeding_mode_desc

Tinyint

Nvarchar (60)

New addition to SQL 2017

Auxiliary database seed setting mode.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.