Write-write replication with last writer wins conflict resolution

Starting with CU13 of SQL Server 2019 , we are including new functionality which facilitates peer-to-peer replication with conflict detection & resolution using a last-writer-wins strategy. This enables many scenarios for multi-write configurations which may not have been possible or practical before.

Traditionally, customers configuring peer-to-peer replication are advised to implement a sharded solution which ensures that each row is updated on only one replica server (key values A-M are updated on server 1 while key values N-Z are updated on server 2, etc.). If it should happen that the same row is updated within a small time window by two servers in a replication environment, a conflict will be detected, which will either stop replication after raising an error, or it may optionally be resolved using the originator ID of the replica sending the update. (i.e. if you have replicas with Originator IDs 1 and 2, then updates coming from the replica with originator ID 2 would always win, and be persisted. See Conflict Detection in peer-to-peer replication - SQL Server | Microsoft Docs for a detailed explanation.

With this new feature, the user has another option when configuring conflict resolution in peer-to-peer replication: Last writer wins. With last writer wins, containing the timestamp recorded in UTC timezone to avoid timezone issues with globally distributed apps for the most recent update for this row. When a conflict is detected, with this configuration enabled the most recent modification time will be chosen to be persisted on all replicas.

The table below should illustrate the difference in strategies and outcomes.

Operation	Key Value	Data Value	Originator ID	Timestamp
Original state	25	ABC
Replica A updates row	25	DEF	2	1:00:00.0001
Replica B updates row	25	QRS	1	1:00:00.0005
Result with Originator ID resolution	25	DEF	2	1:00:00.0001
Result with Last Writer Wins	25	QRS	1	1:00:00.0005

For more information on this technology, Conflict detection in peer-to-peer Replication | Micosoft Docs or Configure last writer conflict detection and resolution | Microsoft Docs

Peer-to-peer database in Always On Availability Group

Starting with SQL Server 2019 CU13, in a peer-to-peer replication configuration can participate in an Always On availability group. It is not required that all members of a replication configuration are in an AG. You can mix AG and non-AG databases in a replication configuration. This significantly improves the availability of these configurations.

See Replication, change tracking, & change data capture - Always On availability groups | Microsoft Docs

Posted at https://sl.advdat.com/3aeHT3K