The following tip is excerpted from the McGraw-Hill/Osborne Media book SQL Server 2000 Administration, Chapter 10, 'Replication', written by Mark A. Linsenbardt and Shane Stigler. For a summary and link to the complete chapter, click here.
In situations where two or more servers must share all information equally, and where all sites must be able to update data, merge replication is the answer. In merge replication, each server accepts changes from the local users, and changes are later merged at defined intervals or when manually initiated. This method of replication has the potential for conflicts and transactional inconsistencies, which makes it the most difficult type to design and manage. Additionally, merge replication requires changes to be made to the database schema. A few system tables are added and unique columns are required to identify each server's database from the others.
Just like transactional replication, merge replication begins with a snapshot; however, merge replication is thereafter maintained by the Merge agent. The Merge agent is the active SQL Server component in this type of replication and is responsible for the replication workload. In pull subscriptions, the Merge agent runs on the subscribing computer. For push subscriptions, the Merge agent runs on the distributor.
Because this type of replication moves data both ways, the Merge agent will first copy changes from the publisher and apply them to the subscriber, and then take changes from the subscriber and apply them to the publisher. Once the changes have been applied both ways, the Merge agent will look at and resolve any conflicts. Prior to being applied, changes for each server are stored in the DISTRIBUTION Database.
From the field: Merge replication is subject to conflicts, though most organizations find these to be infrequent. You would do well to watch the replication carefully for the first few months to determine if a full-time administrator is required to watch and control the result of conflicts.
You should use merge replication in situations that require independent updates to data at more than one server location. Conflict handling is a matter of design; the typical method of conflict resolution is to use the Merge agent, which resolves conflicts based on preassigned priorities. You can, however, take greater control over the process and create custom triggers that handle conflict resolution.
Click to return to the beginning of this book excerpt: Understanding replication methods
