SQL Server 2022 – Multi-Write Replication 

By Ryan Freeman

December 14, 2022

SQL, sql server

Welcome to the seventh in the series of our SQL Server 2022 feature-specific posts. Last time we were looking at the Azure Synapse Link for SQL, and this week, Multi-Write Replication

When will we have SQL Server 2022?

SQL Server 2022 was released on the 16th of November, 2022 and is available for download. These editions include the free options of Developer edition and Express edition plus the paid options of Enterprise edition and Standard edition.

Customers purchasing via CSP, OEM, and SPLA can begin purchasing SQL Server 2022 in January 2023. 

‘SQL Server 2022 is the most Azure-enabled release of SQL Server yet, with continued innovation across performance, security, and availability.’ – Microsoft. 

Understanding conflicts and conflict detection 

In the example of a single database, changes made to the same row by different applications do not cause a conflict.

Locks handle concurrent changes, and transactions are serialised. In an asynchronous distributed system (such as peer-to-peer Replication), transactions act independently on each node, and there is no mechanism to serialise transactions across multiple nodes.  

In systems such as peer-to-peer Replication, conflicts are not detected when changes are committed to individual peers.

Instead, they are detected when those changes are replicated and applied to other peers. In peer-to-peer Replication, conflicts are detected by the stored procedures that involve changes to each node based on one or more hidden columns in each published table. 

Multi-Write Replication in SQL Server 2022 

Multi-Write Replication in SQL Server 2022 automates a last-writer wins rule for replication conflict resolution. With earlier versions of SQL Server, the match would need to be addressed manually when a conflict arose between data written to different replicas. 

Multi-Write Replication solves this issue by implementing the last-write wins rule. If a conflict were to occur between replicas, the last write would take precedence and be written to all models. Multi-Write Replication uses the timestamp in UTC to avoid time zone issues. 

Hopefully, that is an excellent introduction to Multi-Write Replication; next week, we’ll look at the Azure Active Directory authentication, so stay tuned! 

Digital Samurai 

Are you excited by the new version of SQL 2022? At Digital Samurai, we certainly are! We would love to hear your thoughts. 

It may also benefit you to know that on the 27th of July 2022, extended support ended for SQL Server version 2012. Is this a version you are using, or any prior? 

If you have any questions on SQL Server 2022 or upgrading to the later versions of SQL Server, please feel free to get in touch; we will be delighted to assist. 

Digital Samurai are a team of data and SQL experts that have experienced every version through the years – yes, back to SQL Server 6.0! We also have a wealth of experience in upgrades and migrations and are always available to help.

Ryan Freeman

About the author

You might also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}