Types of Replication

SQL Server has snapshot, transactional, and merge replication. Each replication type is best suited for one or more sets of scenarios. This section discusses different types of replication and scenarios in which they should be used.

Transactional Replication

Transactional replication, as the name suggests, replicates the transactions as and when they are committed at the publisher to the subscribers.

It's one of the most commonly used replications to load balance read-write workloads. The writes are done at the publisher and the reads (or reporting) are done at the subscriber, thereby eliminating read-write blocking. Moreover, the subscriber database can be better indexed to speed up the reads and the publisher database can be optimized for Data Manipulation Language (DML) operations.

The log reader and distribution agent carry out the transactional replication, as stated earlier. The agents are implemented as SQL agent jobs, that is, there's a SQL agent job for a log reader agent and a SQL agent job for the distribution agent.

There are two other transactional replications that allow changes to flow from subscriber to publisher: transactional replication with updatable subscription (bidirectional transactional replication) and peer-to-peer transactional replication.

Transaction replication is discussed in detail in Lesson 2, Transactional Replication.

Merge Replication

Merge replication, as the name suggests, replicates changes from publishers to subscribers and from subscribers to publishers. This sometimes results in conflict in cases where the same row is updated with different values from the publisher and subscriber.

Merge replication has a built-in mechanism to detect and resolve conflicts; however, in some cases, it may get difficult to troubleshoot conflicts. This makes it the most complex replication type available in SQL Server.

Merge replication uses the merge agent to initialize subscribers and merge changes. Unlike transaction replication, where the snapshot agent is used to initialize subscribers, in merge replication, the snapshot agent only creates the snapshot. The merge agent applies that snapshot and starts replicating the changes thereafter.

Merge replication isn't covered in this book as it's not used as an HA and DR solution anymore.

Snapshot Replication

Snapshot replication generates a snapshot of the articles to be replicated and applies it to the subscriber. The snapshot replication can be run on demand or as per schedule. It's the simplest form of replication and is also used to initialize transactional and merge replication.

Consider the following diagram:

Figure 1.6: Snapshot replication example

The preceding diagram demonstrates how snapshot replication works. The finance database is replicated from publisher to subscriber. Here's how it works:

1. A publication for the finance database is created at the publisher.

2. The snapshot agent creates the snapshot (.sch files for object schema and .bcp files for data). The snapshot files are kept at a shared folder that's accessible by the publisher and the distributor.

3. A subscription for the finance publication is created at the subscriber.

4. The distribution agent applies the snapshot at the subscriber's finance database.