Combining Always On Availability Groups with Peer-to-Peer Replication
Starting with SQL Server 2019 CU13, databases participating in a peer-to-peer replication configuration can now be part of an Always On availability group. This integration is flexible—you are not required to have all members of the replication configuration within an AG. You can mix and match AG and non-AG databases within your replication setup, significantly improving the availability and scalability of your configurations.
Why This Matters
- Enhanced Availability: By combining AGs with peer-to-peer replication, you ensure that your databases are highly available, minimizing downtime and data loss.
- Flexible Deployment: The ability to mix AG and non-AG databases allows for tailored configurations that meet specific business needs.
- Improved Scalability: Multi-write replication becomes more robust, supporting larger and more distributed environments.
Possible Topologies
The integration allows for various topological configurations to suit different requirements:
- Two Peers in Always On, Two Remote Distributors:
- Both peer nodes are part of an AG.
- Each node has its remote distributor.
- One Peer in Always On, One Remote Distributor with Two Distributions, Standalone Peer:
- One peer node is in an AG with a remote distributor that handles two distributions.
- The other peer is a standalone server.
These configurations demonstrate the versatility of combining Always On and peer-to-peer replication to create multi-write replication environments within AGs.
Introducing the Last Writer Wins Conflict Resolution
Conflict detection in peer-to-peer replication has also been enhanced with the Last Writer Wins feature.
Traditional Conflict Detection
Traditionally, to avoid conflicts in peer-to-peer replication, databases are configured in a sharded manner:
- Data Sharding: Each row is updated on only one replica server (e.g., key values A-M on Server 1, N-Z on Server 2).
- Conflict Handling: If the same row is updated simultaneously on different servers, a conflict is detected. This could either halt replication with an error or resolve the conflict based on the originator ID of the update.
Limitations of Traditional Method
- Complex Configuration: Requires careful planning to ensure data is properly sharded.
- Potential for Downtime: Conflicts can halt replication, leading to potential downtime.
- Rigid Conflict Resolution: Using originator IDs can be inflexible, as it always favors one replica over another.
How Last Writer Wins Improves Conflict Resolution
The Last Writer Wins feature offers a more dynamic approach:
- Timestamp-Based Resolution: Conflicts are resolved by comparing the UTC timestamps of the conflicting rows. The row with the most recent modification time is persisted across all replicas.
- Global Consistency: Using UTC timestamps avoids issues with time zones in globally distributed applications.
- Simplified Configuration: Reduces the need for strict data sharding and allows for more concurrent updates across replicas.
Benefits
- Reduced Conflicts: Minimizes replication stops due to conflicts.
- Greater Flexibility: Allows for more write operations across different replicas without complex sharding.
- Improved Performance: Enhances the efficiency of multi-write replication setups.
Summary of conflict types
| Conflict type | Conflict details | Peer-to-peer | Last writer |
| Insert-Insert | All rows in each table participating in peer-to-peer replication are uniquely identified by using primary key values. An insert-insert conflict occurs when a row with the same key value was inserted at more than one node. | If the incoming row is the winner, then we update the destination row. In either case, we record the information. | If the incoming row is the winner, then we update the destination row. In either case, we record the information. |
| Update-Update | Occurs when the same row was updated at more than one node. | If the incoming row is the winner, then we modify ONLY the changed columns. | If the incoming row is the winner, then we modify all the columns at the destination (if @upd_cmd is set to default – CALL). |
| Update-Insert | Occurs if a row was updated at one node, but the same row was deleted and then reinserted on another node. | If the incoming row is the winner, then we modify ONLY the changed columns. | This occurs when a row is updated on peer1 and the same row is delete and re-inserted on peer2. When the sync occurs, the row on peer1 is deleted as delete always wins and then same row is inserted, whereas the row is updated on peer2 as updated happened at a later time. This will lead to nonconvergence. |
| Insert- Update | Occurs if a row was deleted and then reinserted at one node and the same row was updated on another node. | If the incoming row is the winner then we update all the columns. | This occurs when a row is delete and re-inserted on peer1 and the same row is updated on peer2. When the sync occurs, the row is deleted on peer2 as delete always wins and then it is inserted again. On peer1, the update is skipped. |
| Delete-Insert Insert-Delete | Occurs if a row was deleted at one node, but the same row was deleted and then reinserted at another node. | We currently think this as D-U conflict and if the incoming row is then winner then we delete the row from destination. | This occurs when a row is deleted on peer1 and the same row is delete + re-inserted on peer2. When the sync occurs, the row on peer2 is deleted, whereas the row is inserted on peer1. This occurs because we don’t store information about the deleted row, so we don’t know whether the row was deleted or was not present on the peer. This will lead to nonconvergence. |
| Delete-Update | Occurs if a row was deleted at one node, but the same row was updated at another node. | We currently think this as D-U conflict and if the incoming row is the winner then we delete the row from the destination. | This is a D-U conflict. As delete always wins, incoming delete will be the winner and we delete the row from destination. |
| Update-Delete | Occurs if a row was updated at one node, but the same row was deleted at another node. | In the peer-to-peer Update stored procedure, if there is an U-D conflict then we print the following message and don’t resolve it. An update-delete conflict was detected and unresolved. The row could not be updated since the row does not exist. | This is a U-D conflict. As delete always wins, incoming update is skipped. |
| Delete-Delete | Occurs when a row was deleted at more than one node. | In the peer-to-peer Delete stored procedure, if there is D-D conflict then we don’t process any change, just record it. | If there is D-D conflict then we don’t process any change, just record it. |
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 |
To implement last writer wins in your publication
- Configures conflict detection and resolution policy as last write wins:
, @p2p_continue_onconflict= ‘true’
, @p2p_conflictdetection_policy = ‘lastwriter’
Configure last writer conflict detection & resolution – SQL Server | Microsoft Docs
Conclusion
Implementing Last Writer Wins in peer-to-peer replication simplifies conflict resolution. And allows multi write with Always on , you can set up a robust replication environment that efficiently handles conflicts based on the most recent updates