Replication in SQL Server 2016

SQL Server Replication

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

There are three types of replications available in SQL Server as below.

1      1.   Snapshot Replication

2      2.       Transactional Replication

3      3.       Merge Replication

Snapshot Replication:

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

It is best suited for fairly static data or if it's acceptable to have data out of sync between replication intervals. It is also commonly used to provide the initial set of data and database objects for transactional and merge publications.

Transactional Replication:

Transactional replication is a good solution to the problem of moving data between continuously connected servers.

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.

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

Note:

1   1.   Replication does not depend upon the database recovery model. We can configure      replication with any recovery model.

2   2.   Transactional Replication is a real time replication.

3   3.   In case of Transactional replication, for each article three stored procedures are created automatically for insert/update/delete operation in subscription database like sp_MSdel_dbo<article name>, sp_MSins_dbo<article name>, sp_MSdel_dboupd<article name>.