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

2.2.3 two synchronous copies and configuration-only copies

An availability group with two (or more) synchronous replicas and one configuration-only replica provides data protection and may also provide high availability. The following figure shows this architecture:

The 1 and 2 in the illustration indicate:

1. Synchronize user data to a secondary copy. It also includes availability group configuration metadata.

two。 Synchronized availability groups configure replication of metadata. It does not include user data.

In this availability group diagram, the master copy pushes the configuration data to the secondary copy and the configuration copy only. The secondary copy also receives user data. Only the configuration copy does not receive user data. The secondary copy is in synchronous availability mode. Only the configuration copy does not contain the databases in the availability group-- only metadata about the availability group. Only the configuration data on the configuration copy is submitted synchronously.

Note: the availabilility group for configuring only replicas is a new feature for SQL Server 2017 CU1. All instances in the SQL Server availability group must be SQL Server 2017 CU1 or later.

The default value for REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT is 0. The following table describes the available sexual behaviors.

& High availability

Data protection

Data protection

REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT=

0 *

@ shouldalert

Primary copy interrupt

Automatic failover. The new master copy is R / w.

Automatic failover. User transactions not available for the new master database.

Secondary copy interrupt

The primary copy is RWeiW, which may result in data loss (if the primary database will fail and cannot be recovered). If the master does not have automatic failover, it will also fail.

User transactions that are not available in the primary database. If the master fails over to no copy, it will also fail.

Configuration only copy interrupt

Mainly R / w. If the master does not have automatic failover, it will also fail.

Mainly R / w. If the master does not have automatic failover, it will also fail.

Synchronization Auxiliary + configuration only copy interrupt

User transactions that are not available in the primary database. No automatic failover.

User transactions that are not available in the primary database. Fail over to if there is no copy and the primary server fails.

* default value

Note: an instance of SQL Server that hosts only a copy of the configuration can also host other databases. It can also join a configuration-only database as multiple availability groups.

Request:

All replicas in an availability group that contains only configured replicas must be SQL Server 2017 CU1 or later.

Any version of SQL Server can host only copies of the configuration, including SQL Server Express.

In addition to the primary copy, the availability group requires at least one secondary copy.

Only configuration replicas do not count towards the maximum number of replicas per SQL Server instance. SQL Server Standard Edition allows up to three copies, and SQL Server Enterprise Edition supports up to nine.

Note:

Each availability group cannot have more than one configuration-only copy.

Only the configuration copy cannot be the primary copy.

You cannot modify the availability mode for only configured replicas. To change from a configuration-only copy to a synchronous or asynchronous secondary copy, delete the configuration-only copy and add a secondary copy of the desired availability mode.

Only the configuration copy is synchronized with the availability group metadata. There is no user data.

Invalid when there is an availability group with a primary copy and a configuration-only copy, but without any secondary copies.

You cannot create an availability group on an instance of the SQL Server Express version.

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.