Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Replication is one of the High Availability solutions. But it is not usually used as a High Availability solution.
We would use SQL Server Always On Failover Cluster or Database Mirroring before Always ON Avalability Group as the HA (High Availability) solution.
We usually use Replication when a table or tables should be regularly transferred to another server for reporting purposes.
In Replication, we are able to define the index regardless of the primary server where we transfer the data.
This reinforces our use for reporting purposes.
In Always ON, we cannot define the index in the secondary regardless of the primary.
One of the most important features that distinguishes Replication from other High Availability options is that it can be done on a table basis.
If you remember other HA solutions, we could not do this. You can read the following articles for other HA solutions.
“Database Mirroring On SQL Server“,
“SQL Server Always ON Availability Group“,
“SQL Server Always On Failover Cluster“,
There may be other situations in which we should use Replication. For example, there may be cases where data from a central database should be transferred regularly to distributed subscribers. In such cases we can choose.
In Replication, data is not distributed to subscribers from the central database only. In some cases it may be necessary to transfer data from the subscribers to the central database to collect the data at the central location.
There are several types of replication. You can perform the scenarios described above with the following types of replication.
- Transactional Replication
- Peer To Peer Transactional Replication
- Snapshot Replication
- Merge Replication
You can find the details of the above types of replication in the following articles.
“Transactional Replication On SQL Server“,
“Peer to Peer Transactional Replication On SQL Server”
“Snapshot Replication On SQL Server“,
“Merge Replication On SQL Server”
To understand Replication, we need to know what the concepts that make up the Replication are.
Below you can find explanations of these concepts.
| Article | Its an object in the database.(Table,view,stored procedure etc.) |
| Publication | It is a collection of one or more articles. You can think of Publication as a newspaper and articles as news in this newspaper. |
| Subscriber | It is the SQL server instance that receives the replicated data. If we are going to proceed through the example, they are subscribers to the newspaper. |
| Publisher | It is an instance that has a publication ready to be distributed to Subscriber. We can think of it as the main building of the newspaper. |
| Distributor | it is an instance that behaves like one or more publisher’s stores. I will continue with our example. If publisher is the main building of the newspaper, Distributor is the place where the printed newspapers are stored. Typically Publisher and Distributor are on the same instance. This is called a local Distributor. If the distributor is on a different instance, it is also called a remote distributor. Each publisher is associated with a database called “distribution” in the distributor. In the “distribution” database, the replication status and publisher metadata are stored, and in some cases, the delivery of the data from the publisher to the subscriber is performed. If we continue with our example, consider that there is a newspaper store within the main building of the newspaper and that there is a stock tracking staff inside this store. This store is Distributor. The “distribution” database is the stock tracking staff working in this store. In some cases (according to the type of Replication), the tasks of the stock tracking staff are to bring the newspaper to the subscribers. |
| Subscribtion | Subscription determines how and when Publication is transferred from publisher to subscriber. There are two types. Push Subscription: In the Push Subscription method, publications are forwarded to the subscriber without a request from “Subscriber”. If we look at our example, Stock Tracking Staff will be a little intense in this type of subscription. He has to distribute all newspapers to subscribers. Pull Subscription: Subscriber has to request the newspaper from the publisher. If we look at our example we can say that Stock Tracking Staff likes this subscription type. Because he’s freed from distribution. Subscribers come to the store and take the newspapers themselves. |
Replication monitors the changes that occur in the master database so that the changed data is transferred to the database in other instances.
It performs this process with some programs that are called agents. Below you can find out what these agents are and what they do.
Before looking at the table below, I would like to mention the concept of snapshot.
You can think of Snapshot as schema information of the table to be transferred and the image of the current data.
| Snapshot Agent | It is used in all replication types mentioned above. It makes the schema information and data of the objects to be published ready for transfer to the subscriber. Stores the snapshot file and the synchronization status in the “distribution” database. It works on the Distributor. |
| Log Reader Agent | Only used in Transactional Replication type. Imports transactions from Publisher that are marked to be replicated in the transaction log file on Publisher to the “distribution” database. Each database published by Transactional Replication has its own Log Reader Agent. It works on the Distributor. |
| Distribution Agent | Used in Snapshot and Transactional Replication types. It transfers the snapshot taken in the startup and the transactions that are held in the “distribution” database to the subcribers. |
| Merge Agent | Used in Merge Replication type. It transfers the snapshot taken in the startup a, and collects the data changes that occur in all subscribers in a compatible way. When you read my article “Merge Replication On SQL Server“, you will understand what I mean. |
| Queue Reader Agent | Used in the Transactional Replication type if the Queued updating option is activated. It is responsible for transferring the changes made on Subscriber back to Publisher. |
