Replication Enhancements in the SQL Server 2019 CU13 Release
Published Oct 05 2021 01:21 PM 9,598 Views
Microsoft

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

Co-Authors
Version history
Last update:
‎Oct 05 2021 01:21 PM
Updated by: