Merge replication is another new topology that offers a significant gain for distributed systems. It is becoming very common to decentralize data by publishing from a central location to multiple remote locations. The drawback with this approach is that all data entered must occur at the central site.

Merge replication allows you to utilize a central subscriber topology with a slight twist. In this topology, a subscribing server can process a transaction and have it propagated to the publisher.  While this topology is replication, it functions significantly differently than what you are used to.  Merge replication is accomplished via triggers and MS DTC.  The triggers record those rows and columns which were changed in a table.  The merge agent then propogates the changes to the publisher.

Merge replication is the most robust, resilient, and manageable replication topology that I have dealt with from any manufacturer.  It is a perfect fit for any type of scenario where subscribers have to process and propogate changes.  Merge recovers flawlessly from disconnections, failed servers, restorations, and anything else that you can throw at it.

The worst problem in any replication environment is what to do when a publisher or subscriber fails and needs to be restored from a backup.  The only way to get an environment back in synch in this case is by unsubscribing, resubscribing, and performing a synchronization.  In just about any production system, this is prohibitive.   Merge is the first topology that I have encountered that allows you to resynch an environment on the fly and is impervious to restoring an older copy of a database on either the publiser or subscriber.  Because of this single feature, I am very hard pressed to recommend any other topology even when the data has to flow one way.

Merge replication

Bi-directional

Bi-directional replication has been the "Holy Grail" in replicated systems. Many DBMSes offer bi-directional replication in some form or another, but none of them do it very well. This topology is very labor intensive and can pose a risk to data loss. With this topology, the entire system is in a constant state of flux. At any point in time, all of the servers participating in this design can have different sets of data. They can also contain data that no other server has. If one of these servers crashes, there is the possibility of data loss that can not be recovered. This topology also places a heavy burden on resources. Each server participating in the bi-direction replication as its own distributor. This means that each server acts as a publisher, subscriber, and distributor.

You must be extremely careful in the database design and replication setup so as not to create resonances. A resonance can quickly overwhelm a system and network. This is caused as follows: ServerA publishes the authors table to ServerB and also subscribes to the authors table from ServerB. ServerB has the reverse setup. A modifies an author on ServerA. This causes the change to be replicated to ServerB. ServerB commits the change and notices that an update has been performed on the authors table. It then replicates this same transaction back to ServerA. The whole loop then repeats with ServerA and ServerB sending the same transaction to each other in an infinite loop.

Replication Primer 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40