Difference Between Snapshot, Transactional and Merge Replication in SQL Server

In SQL Server, replication is a set of applications that helps users in copying and distributing data and database objects among different databases. It also helps in maintaining synchronization between databases in order maintain consistency. However there are types of replication possible in SQL Server such as snapshot, transactional, merge, etc. Thus, in order to have a better understanding and selecting right replication technique, we have differentiated between a snapshot, transactional and merge replication in detail here.

●     Snapshot Replication

It is pretty much clear from the name itself that it take snapshots of the published database. After that, it exports it to a subscriber database. Whenever it drop the table and recreate it again, the primary aim of the snapshot replication is to properly overwrites the data or transactions in the subscriber database

When is it Beneficial?

A user can use snapshot replication in its best way when the subscriber requires data on certain interval rather than on frequent basis or when the frequency of data is low. It means snapshot replication is advantageous in a case where the list of the item is to be updated that change only on certain interval or periodically for example, at the end of business day. Apart from all this, snapshot replication is a little bit slow when compared with transactional replication. It moves multiple records suppose millions of records, from the publisher (one end) to the subscriber (another end).

●     Transactional Replication

Transactional replication works slightly different from snapshot replication. It replicates each and every transaction for the table/article being published from a publisher to the subscriber. In the beginning, it also takes a snapshot of a publisher database and overwrites it to subscriber database to synchronize the data. As everybody knows the main aim of replication is to maintain synchronization of data between publisher and subscriber database.

When is it Beneficial?

A user can use Transactional Replication where real time data is required for example bank-specific transaction and online trading in order to keep a backup of live data of each credit or debit transaction. A log reader agent first reads transactions from the transaction log and after that write it to the distribution database and at last update it to the subscriber database. Log reader agent is must in each database if transactional replication is used. Moreover, they transfer transactions from the publisher to distributor. The main reason it keeps the track of data changed at publisher level is because transactional replication is applied only to article/ table that has a primary key.

●     Merge Replication

As snapshot replication, it is also clear from its name that merge replication joins the publisher and subscriber database. It is the complex replication if compared to other and helps users to maintain data consistent among multiple ends. The best part about merge replication is that it work in an integrated way by taking publisher and subscriber together. The main aim of merge agent is to trace the each and every change that has occurred at both publisher and subscriber end. After that, they have to send that modified transactions to distributor database for further propagation. The Merge Agent execute either at subscription end for pull subscription or distributor end for a push subscription.

When is it Beneficial?

After having a complete understanding of the Merge replication, it is very much clear that it works really well in the retail markets such as Lifestyle, Pantaloons, BigBazar and much more. At these places, it is really helpful in synchronizing records among multiple stores as stock increased or decreased

Difference B/W Snapshot, Transactional And Merge Replication in a Tabular Form

S.no	Snapshot Replication	Transactional Replication	Merge Replication
1	It is unidirectional	It is unidirectional	It is bidirectional
2	It does not require table structure	It requires table that must have primary keys	It requires table that has rowguid to column
3	High latency is acceptable	Application does not accept high latency at all	Application accept both high or low latency
4	It is used in server-to-server environment	It is used in server-to-server environment	It is used in server-to-client environment

Conclusion

Replication is very important process from SQL Server database point of view. However, it assists SQL users in maintaining consistency between database and allows them to distribute database object from one database to another. As there are various replication techniques available so, it becomes difficult for a user to select one of them. Thus, after understanding the requirement of the user, we have discussed the difference between snapshot, transactional and merge replication. After clearly understanding the difference between the three, a user can choose among three accordingly.