In Replication series, this is part 2 of the series which will give you an idea on the types of replications that exist, how one is different from another, and which one fits your requirement.

Microsoft SQL Server offer the following types of replications:

• Snapshot replication.
• Merge replication.
• Transactional replication.

The type of replication we setup depends on many factors

• Physical replication environment: which includes the number and location of computers involved in replication and whether these computers are clients’ servers.
• Nature of work and quantity of data to be replicated
• Whether the data is updated at the Subscriber.

Snapshot replication

This replication creates an identical copy of the article and their data each time it runs. When synchronization occurs or initiated, the entire snapshot is generated from scratch and sent to all the Subscribers which means it copy the entire data again, overwrites the data on subscribers. This replication does not have synchronization capability.

Every time snapshot replication runs, SQL server use BCP utility to write the contents of each article into the snapshot folder. During their initial setup, all other replication type generates snapshot to sync the Publisher to its Subscribers.

Snapshot replication is appropriate when data changes are substantial but not frequent.

Transactional replication

Transactional replication copies data from the publisher to the subscriber database. In transactional replication, log reader agent scans log of the publication database and scan committed transaction related to the articles. If found, those changes are pushed to the distribution database on distributor server. Later Distribution Agent apply those changes to the Subscriber server.

Transactional replication allows near-time synchronization.

Transactional replication is typically used in the following cases:

• Incremental changes to be propagated to Subscribers as they occur.
• The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.

Merge replication

Merge replications allow for data changes on the Publisher as well as the Subscriber – and vice versa and keep both in sync. Any change applied to one database will automatically be applied to the other databases. The Merge Agent is for synchronize the changes between the Publisher and its Subscribers. It identifies the articles that have changed and record those changes. Because data can be modified on both Subscriber and Publisher, it's possible for a row to be updated in two different places at the same time, which can result in data being in conflict. Merge replication comes with several built-in options to help resolve such conflicts.

This replication is typically used in the following cases:

• Multiple Subscribers get update same data at various times and propagate those changes to the Publisher and to other Subscribers.
• Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.