Overview

SQL Server implements merge replication via the Snapshot Agent and the Merge Agent. As with snapshot and transactional replication, the Snapshot Agent is responsible for creating the initial snapshot of the data that will be distributed by the publisher. Once it creates the initial snapshot, the Snapshot Agent creates synchronization jobs on the publisher and builds replication-specific system tables, stored procedures, and triggers. Subscriber-side system objects are created when the snapshot is first applied to the subscriber.

Note that a merge subscription table can subscribe to only one publication at a time. If you attempt to subscribe from a single subscriber table to an article in two different publications, one of the Merge Agents will fail when the initial snapshot is applied.

The Distribution Agent is not used in merge replication. The functions normally performed by the Distribution Agent in snapshot and transactional replication are handled by the Merge Agent in merge replication. The distribution database is also not used much in merge replication. Its main function is to support agent logging and history—no data is stored in it for forwarding to and from subscribers. Each publisher/subscriber database maintains its own store-and-forward tables. Because the role of the distributor is so limited with merge replication, it's not unusual for the distributor to reside on the same server as the publisher.

The Merge Agent is responsible for taking the initial snapshot and applying it to subscribers. Once the snapshot is applied, the Merge Agent is responsible for collecting the incremental changes made after the snapshot was taken, applying them to subscribers, and uploading changes made at subscribers to the publisher. This process is known as synchronization. For push subscriptions, the Merge Agent runs on the distributor. For pull subscriptions, it runs on the subscriber. As with the Distribution Agent used for snapshot and transactional replication, you can change the machine on which the Merge Agent runs via remote agent activation.

As with the other replication agents, the Merge Agent is an ODBC console application. Normally it runs via SQL Server Agent, but you can also run it from the command line. Its executable file is replmerge.exe. If you check its thread count in Perfmon or attach a debugger and dump its thread stacks, you'll see that it's a multithreaded application.

The Merge Agent applies changes to a publisher or subscriber by calling the stored procedures generated by the Snapshot Agent. There are separate stored procedures for inserting, updating, and deleting rows. Typically, these procedures are named sp_upd_GUID, sp_ins_GUID, and sp_del_GUID, where GUID is the uniqueidentifier article ID (from the artid column in sysmergearticles) for the corresponding article. These are invoked via RPC.

The Merge Agent is able to detect changed data on the publisher and subscribers because special triggers record data changes to published tables in merge system tables on the publisher or subscriber as they occur. Each time a row is inserted or updated, the change is recorded in MSmerge_contents. Each time a row is deleted, a trigger records it in MSmerge_tombstone. Together, these tables serve the same purpose in merge replication that the MSrepl_commands table serves in snapshot and transactional replication. You can use the rowguid column in each of them to join back to the original publishing table. By examining these tables, along with the MSmerge_genhistory and MSmerge_info tables, the Merge Agent is able to determine which rows to send to the other party in the synchronization operation.

When you publish a table as an article in a merge publication it must have a uniqueidentifier column that has been flagged with the ROWGUIDCOL property and that has a unique index on it. If such a column does not exist, one named rowguidcol will be added automatically and a unique index will be built for it. This column stores a GUID value. A GUID is guaranteed to be unique among all the networked computers in the world. This allows a particular row to be identified uniquely across multiple publisher and subscriber machines.

It's worth mentioning here that the publisher and subscribers in a merge replication scenario are pretty much equal partners. Unlike snapshot and transactional replication, where the publisher is clearly preeminent, because merge replication is designed for bidirectional replication, most of the key system tables that exist on the publisher also exist on subscribers. For example, a merge subscriber logs changes to published articles in its own MSmerge_contents and MSmerge_tombstone tables just as the publisher does. It has an MSmerge_genhistory table that is structured identically to the one on the publisher. It tracks sent and received generations in its own MSmerge_replinfo table exactly as the publisher does. The one exception to this symmetry is the set of conflict tables. Every published article has its own conflict table. When a conflict is resolved, the details of the resolution are logged in the conflict table. Since conflict resolution always occurs from the vantage point of the publisher, only the publisher maintains conflict tables. Other than this, the system tables and processes for transmitting changed data are virtually the same on the subscriber as on the publisher in a merge replication implementation.