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