December 14

0 comments

SQL Server 2022 – Multi-Write Replication 

By Ryan Freeman

December 14, 2022


Welcome to 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 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 that are 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 at individual peers. Instead, they are detected when those changes are replicated and applied at other peers. In peer-to-peer replication, conflicts are detected by the stored procedures that apply changes to each node, based on one or more hidden columns in each published table. 

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, when a conflict arose between data that was written to different replicas, the conflict would need to be addressed manually. 

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 replicas. Multi-Write Replication uses the timestamp recorded in UTC time to avoid time zone issues. 

Hopefully that is a good brief introduction to Multi-Write Replication, next week we’ll be looking 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 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, then please feel free to get in touch and we will be very happy to assist. 

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

Ryan Freeman

About the author

You might also like

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