When you meet up with an old friend, you immediately try to recall when you last met so you know how much you have to catch up on. Merge replication works in a similar way.

How merge replication works

Merge replication is a database technology that combines changes occurring on two or more nodes in a publisher/subscriber topology. It is important to note that each node in a merge replication topology isn't completely autonomous; it's not true multi-master replication. You must have a node designated as the Publisher, which acts as a clearing house. It's where all changes on the nodes are collected, reconciled and distributed to all other nodes, the Subscribers.

Changes that have occurred between merges or synchronizations are tracked by triggers on the tables being replicated. These triggers write tracking information to metadata tables (for insert and update activity, the metadata is recorded in a merge_content table; for delete activity, the metadata is recorded in a merge_tombstone table). In the tables you are replicating, each row is identified by a Unique ID (UID). Think of the UID as a primary key that spans all the tables you are replicating; it is used to identity the rows that have changed on either side of the replication topology.

Consider a case in which you are replicating the authors' table in your publications database. The first row is for a Johnson White. This row could have a UID value of 40353CBD-C085-4953-9F46-B06BC704AFC1. On every Subscriber, this row will have the same GUID value.

When you meet up with an old friend and ask him or her what has happened since you last met, the first question you need to answer is when did you last meet. You use this point of reference as a basis to fill in what has happened since you last saw each other. Merge replication has a similar concept called a generation -- each node maintains its own generation number.

Each time the Merge Agent connects to a Subscriber, it determines the last generation number it synchronized with this Subscriber (it references the MSmerge_replinfo and MSmerge_genhistory tables for this) and asks each Subscriber what has changed since the last time it ran, which is everything above a certain generation value. It then gathers a list of UIDs (from the merge_contents and the merge_tombstone tables) that have this generation value. This list represents the rows that have been modified (inserted, updated or deleted) since the last synchronization.

The synchronization process brings the list of UIDs to the Publisher and compares the list with a list of what has changed on the Publisher since the last synchronization with that Subscriber. If nothing in the lists are the same, the synchronization process merely fills in the missing data. It will fire stored procedures on the Subscriber replacing one UID with another UID.

If one of the UIDs occurs in the list originating at the Subscriber and it also occurs in the list that originates at the Publisher, you have a conflict. Conflicts can be classified into four groups:
1. A primary key collision -- the primary key value of a newly inserted row is the same on the Publisher and the Subscriber.

2. The same row is updated on the Publisher and deleted on the Subscriber between synchronizations. 3. The same row is updated on both the Publisher and Subscriber.

4. The same column is updated on the same row on both the Publisher and Subscriber (and you have enabled column-level tracking).

Conflicts can be viewed and rolled back using your Conflict Viewer.

See Also Replication Techniques, Merge replication tips and tricks