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.
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
SQL instance that publish the Articles (Tables, view, stored procedure) is called the publisher. Publishing means replicating the Articles to the subscriber.
As the name describes distributer distributes the Articles among the subscriber. Distributer can be configured with in the publisher or dedicated SQL Instance.
Subscriber is a SQL instance that receives the Articles for which it subscribes.
Object that are selected for replication are called articles.
Articles that are published by publisher is called the publication.
Articles for which the subscriber applied for it is called the Subscription.
Articles which are going to dispense to subscriber is called the distribution.
Types of 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.
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.
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
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.
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.
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.
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.