What is the main difference between snapshot and transactional and merge replication?

does anybody have the specific answer...........

Thanks  in advance........

May 26th, 2015 2:32am

Very brief answer just to highlight the difference and get you going:

In Transactional Replication: The changes made to the publisher are applied (Incermentally) on the subscriber(s) on periodic basis or continuous basis depending upon how you configured it. Additionally, changes made to the subscriber can also be applied on the publisher under Transactional Replication with Updatable Subscriber topology

Note: At a minimum, the table must have primary key to be able to become part of Transactional replication topology, this is required to track changes and apply it on the subscriber database

In Snapshot Replication: There is no concept of incremental data, the subscribers are initialized after generating the snapshot and remains as is (Stale) until next time you again re-generate the snapshot and applied it.

Note: It is not mandatory that the tables should have primary key to be a part of Snapshot Replication topology

Please note that this is just a very short explanation to make you understand it conceptually and you can find more information on MSDN/ Technet, few links:

Free Windows Admin Tool Kit Click here and download it now
May 26th, 2015 2:43am

What are the different types of SQL Server replication?

  • Snapshot replication - As the name implies snapshot replication takes a snapshot of the published objects and applies it to a subscriber. Snapshot replication completely overwrites the data at the subscriber each time a snapshot is applied. It is best suited for fairly static data or if it's acceptable to have data out of sync between replication intervals. A subscriber does not always need to be connected, so data marked for replication can be applied the next time the subscriber is connected.  An example use of snapshot replication is to update a list of items that only changes periodically.
  • Transactional replication - As the name implies, it replicates each transaction for the article being published. To set up transactional replication, a snapshot of the publisher or a backup is taken and applied to the subscriber to synchronize the data. After that, when a transaction is written to the transaction log, the Log Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. Only committed transactions are replicated to ensure data consistency. Transactional replication is widely applied where high latency is not allowed, such as an OLTP system for a bank or a stock trading firm, because you always need real-time updates of cash or stocks. 
  • Merge replication - This is the most complex types of replication which allows changes to happen at both the publisher and subscriber.  As the name implies, changes are merged to keep data consistency and a uniform set of data. Just like transactional replication, an initial synchronization is done by applying snapshot. When a transaction occurs at the Publisher or Subscriber, the change is written to change tracking tables. The Merge Agent checks these tracking tables and sends the transaction to the distribution database where it gets propagated.  The merge agent has the capability of resolving conflicts that occur during data synchronization.  An example of using merge replication can be a store with many branches where products may be centrally stored in inventory. As the overall inventory is reduced it is propagated to the other stores to keep the databases synchronized.

May 26th, 2015 2:55am

Transactional replication

Snapshot replication

Merge replication

  • It replicates each and every transaction   between a Publisher and Subscriber. Here only the change in the Publisher database   is updated in the Subscriber Database through distribution database.
  • Transactional replication is faster than   Snapshot replication as it updates only the changes at Publisher database.
  • Here a complete snapshot of the Publisher   database is moved to the Subscriber database where the transaction data at   the Subscriber database is completely over written.
  • Snapshot replication is slower than   Transactional as on each attempt it moves multiple records.
  • Here a join is created between the Publisher and   Subscriber database and through merger agent the changes occurred at both the   ends are tracked and updated accordingly.
  • This is a bit complex replication but keeps   the data consistent.
Free Windows Admin Tool Kit Click here and download it now
May 26th, 2015 3:26am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics