Monday, December 29, 2014

Difference between Transactional,Snapshot and Merge Replication

Posted on 4:42 AM by Sachin Kalia

Difference between Transactional,Snapshot and Merge Replication

 

    • Transactional replication – Transactional replication replicates each transaction from publisher to subscriber for the article/table which is being published. Initially transactional replication takes a snapshot of the publisher database and applies to the subscriber to synchronize the data. As we know that replication is helpful to synchronize the data among the Publisher and subscriber databases. A Log Reader Agent reads transaction from the transaction log and writes it to the distribution database and then to the subscriber database. Each database published using transactional replication keeps Log Reader agent and moves transaction from publisher to distributor. Transactional replication is helpful where real time data is required such as online trading and bank specific transactions to keep live data backup of each debit or credit transaction.

                   Note: I also noticed that transactional replication applies only on those article/tables which has primary key in it.This is the only reason it keeps track of data changed at publisher level.

    • Snapshot replication - Snapshot replication works slightly different in manner from transactional replication. As name “snapshot” says it takes a snapshot of the published database and moves it to a subscriber database. Snapshot replication completely overwrites the transactions/data at the subscriber database every time as it drops the tables and recreates it again. Snapshot is best in use when data frequency is bit low or subscriber needs data on a certain interval rather than very frequently. E.g. Snapshot replication is to update a list of items that only changes periodically or at certain interval like end of business day. Snapshot is bit slowly than transactional because on each attempt it moves multiple records suppose millions of records from one end(Publisher) to another end(Subscriber).
    • Merge replication – As name implies “Merge” it joins publisher and subscriber databases, it is one of the complex replication and helps to keep data consistent among multiple ends. Merge replications works in an integrated manner with Publisher and subscriber, every time Merge Agent traces each change occurred at both end and send those changed transactions to distributor database for further propagation. The Merge Agent runs either at distributor end for push subscriptions or the Subscriber for pull subscriptions. What I understood about Merge replication, its best fit into Retail market like Pantaloons, BigBazar Lifestyle and many more. Where it’s helpful in synchronizing the records among multiple stores as per inventory increased or decreased.

To know more about Replication please go through these links.

 

 

Thanks

Sachin Kalia

Categories:

0 comments :

Post a Comment

Subscribe to: Post Comments ( Atom )