SQL Replication

Replication provides High safety, High availability for the data. i.e. making objects (tables, View, Stored procedure, Function) replicated from publisher(source) to subscriber(Destination). Publisher may be one instance which publish the information, Subscriber is another instance which subscribe for it.

High Availability

Suppose if we shut down the publisher for any maintenance reasons or any hardware issues happened. We can redirect the request to subscriber.

Distributing the Work

Unlike mirroring, Replication allows Secondary(Subscriber) read-only purpose.

So the application can distribute the workload, hence Articles are replicated between publisher and subscriber.

Components of Replication

Publisher

SQL instance that publish the Articles (Tables, view, stored procedure) is called the publisher. Publishing means replicating the Articles to the subscriber.

Distributor

As the name describes distributer distributes the Articles among the subscriber. Distributer can be configured with in the publisher or dedicated SQL Instance.

Subscriber

Subscriber is a SQL instance that receives the Articles for which it subscribes.

Articles

Object that are selected for replication are called articles.

Publication

Articles that are published by publisher is called the publication.

Subscription

Articles for which the subscriber applied for it is called the Subscription.

Distribution

Articles which are going to dispense to subscriber is called the distribution.

Types of Replication

Snapshot Replication

Taking complete snapshot of the Articles that are selected for the replication. It includes schema of the tables with complete data inside the table.

Snapshot Replication normally used, when changes to the data are very rarely happens. For example, Employee Salaries hike happens once in year. Historical data can be moved using snapshot replication.

If you are doing schema level changes more frequently, at that time snapshot replication is very helpful.

It is a Unidirectional replication.

It uses a BCP command to get snapshot.

Transactional Replication

It responsible for replicating only the modification happened to the current data. Instead of replicating the entire articles like snapshot, it replicates only the changes.

It is much faster and traffic less as compared to the snapshot replication.

This replication is very useful, when data changes more frequently.

It is also a unidirectional replication.

Merge Replication

It is a bi-directional replication; hence both the ends can fire their changes.

In the bi-directional replication, possibility of conflict is more.

In merge replication has lots of technic to overcome this conflict.

Peer to Peer Replication

It is also bi-directional replication, but it does not have that much conflict detection.

SQL Agents for replication

Snapshot Agent

This Agent is responsible for taking complete (Schema, data) snapshot of the object. And writes in the distribution database.

Log Reader Agent

This agent will read the log file, if any new transaction is recorded it just written it to the distribution database.

It read transaction from the .ldf file by using sp_replcmds.

Distributor Agent

It will capture the data from the distribution database and apply on the subscriber.

a. Pull Subscription

If the Distribution agent runs in the Subscriber is called the Pull Subscription.

b. Push Subscription

If the Distribution agent runs in the Distributer is called the Pull Subscription.

Snapshot Replication

In snapshot replication, snapshot agent will execute the BCP command.

This command gets complete snapshot of the articles and place it on the Share folder.

Then distributor Agent will read this share folder and place it on the subscriber.

Transactional Replication

In transactional replication, Log reader agent will use command sp_replcmds to read the log file(.ldf) of the database to finds any new transaction for the published articles.

If it found any new transaction, then it writes into table named msrepl_transcations.

Then the distribution agents read it and send to subscriber.