- Transactional replication
Transactional replication replicates each transaction from a publisher to a subscriber for the article/table being published. Initially transactional replication takes a snapshot of the publisher database and applies to the subscriber to synchronize the data. As we know replication is helpful for synchronizing the data among the publisher and subscriber databases.
A Log Reader Agent reads transactions from the transaction log and writes it to the distribution database and then to the subscriber database. Each database published using transactional replication keeps a Log Reader agent and moves transactions from the publisher to the distributor. Transactional replication is helpful where real time data is required such as online trading and bank-specific transactions to keep a live data backup of each debit or credit transaction.
Note: I also noticed that transactional replication applies only to those articles/tables with a primary key.This is the only reason it keeps track of the data changed at the publisher level.
- Snapshot replication
Snapshot replication works slightly differently than transactional replication. As the 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.
A snapshot is best when the data frequency is a bit low or the subscriber needs data on a certain interval rather than very frequently. For example Snapshot Replication is for updating a list of items that only change periodically or at certain intervals like end of business day. A snapshot is bit slower than transactional because on each attempt it moves multiple records, perhaps millions of records, from one end (the publisher) to the other end(the subscriber).
- Merge replication
As the name implies “Merge” joins publisher and subscriber databases, it is one of the complex replications and helps to keep data consistent among multiple ends. Merge replications work in an integrated manner with a publisher and a subscriber. Every time the Merge Agent traces each change that has occurred at both ends and sends those changed transactions to the distributor database for further propagation.
The Merge Agent runs either at the distributor end for push subscriptions or the subscriber for pull subscriptions. What I understand about Merge replication, it best fits into the Retail market like Pantaloons, BigBazar Lifestyle and many more. Where it’s helpful in synchronizing the records among multiple stores as per the inventory increased or decreased.
To know more about Replication please go through these links.