SQL Server Transactional Replication: Understanding the Architecture and Data Flow
How Data Moves from Publisher to Distributor to Subscriber
Hi everyone! Today we are going to understand one of the important concepts in SQL Server DBA: Transactional Replication.
Imagine a scenario where we have a production database handling thousands of transactions every minute. At the same time, our reporting team needs access to the same data for generating reports and performing analysis.
One option is to restore a database backup regularly. But the problem is that the restored database won’t always contain the latest transactions.
So, how can we continuously move the changes from our production database to another SQL Server without manually copying the data every time?
That’s where Transactional Replication comes into the picture.
What Is Transactional Replication?
Transactional Replication is a SQL Server feature that copies data changes from one database to another and keeps the destination database synchronized with the source.
Whenever an INSERT, UPDATE, or DELETE operation happens in the published database and the transaction is committed, SQL Server captures that change and delivers it to the Subscriber.
The changes are generally delivered in near real time, depending on the replication configuration and workload.
Let’s understand this with a simple example.
Imagine we have two SQL Server instances:
Production Server: Handles application transactions.
Reporting Server: Used by the reporting team.
Whenever a customer record is inserted or updated in the Production Server, the same change is automatically delivered to the Reporting Server.
We don’t need to manually execute INSERT or UPDATE statements on the reporting database.
That’s the basic idea of Transactional Replication.
Understanding the Three Main Components
Before understanding the complete data flow, we need to understand three important terms: Publisher, Distributor, and Subscriber.
1. Publisher — Where the Data Starts
The Publisher is the source SQL Server that contains the original database and makes selected data available for replication.
For example, let’s assume we have a production database named PROD_DB. We want to replicate the CUSTOMER and ORDERS tables to another server.
We configure PROD_DB as the publication database and select the required tables for replication.
The Publisher decides which database objects and data should be shared with the Subscribers.
2. Distributor — The Middleman
The Distributor acts as a middle layer between the Publisher and Subscriber.
Its main responsibility is to collect replication transactions from the Publisher, temporarily store them, and deliver them to the respective Subscribers.
The Distributor maintains a special database called the Distribution Database.
For example, when a customer updates their address in the production database, the Log Reader Agent captures the committed transaction and moves it into the Distribution Database.
From there, the Distribution Agent delivers that change to the Subscriber.
Think of the Distributor as a delivery center that manages the flow of transactions between the source and destination.
One important point: The Distributor doesn’t always need a separate SQL Server instance. It can run on the Publisher itself (Local Distributor), or it can be configured on a separate server (Remote Distributor).
3. Subscriber — Where the Data Reaches
The Subscriber is the destination SQL Server that receives the replicated data.
For example, our reporting server can act as the Subscriber.
Whenever changes are committed at the Publisher and successfully delivered through the Distributor, the corresponding changes are applied to the Subscriber database.
In standard transactional replication, the Subscriber is generally treated as read-only because changes made directly at the Subscriber are not automatically sent back to the Publisher.
Understanding Publication, Article, and Subscription
Apart from the three server roles, there are three more important terms we need to understand.
Get Nathan2kgem’s stories in your inbox
Join Medium for free to get updates from this writer.
Publication: A collection of database objects that we want to replicate. For example, we can create a publication named Customer_Publication.
Article: An individual database object included in a publication. For example, the CUSTOMER and ORDERS tables can be two articles.
Subscription: A request made by a Subscriber to receive a particular publication from the Publisher.
In simple terms:
Publication = Collection of objects.
Article = Individual object.
Subscription = Request to receive the publication.
Complete Transactional Replication Architecture
Now let’s connect all these components.
PUBLISHER SERVER
|
PROD_DB
|
Publication
|
Articles
|
Transaction Log
|
Log Reader Agent
|
↓
DISTRIBUTOR SERVER
|
Distribution Database
|
Distribution Agent
|
↓
SUBSCRIBER SERVER
|
REPORT_DB
|
Replicated TablesThis is the basic architecture of Transactional Replication.
Now let’s understand how the actual data moves through this architecture.
How Does Transactional Replication Work?
The complete process happens in two major stages: Initial Snapshot and Continuous Transaction Replication.
Step 1: Initial Snapshot
Before sending incremental changes, the Subscriber needs an initial copy of the published tables and their data.
For this purpose, SQL Server uses the Snapshot Agent.
The Snapshot Agent generates the initial schema and data files for the published articles and stores them in the configured snapshot folder.
The Distribution Agent then delivers this snapshot to the Subscriber and initializes the destination tables.
This is generally a one-time initialization process for a new subscription, although snapshots can be generated again when required.
Step 2: Capturing Changes from the Transaction Log
Now imagine the application inserts a new customer into the production database.
INSERT INTO CUSTOMER
(
CUSTOMER_ID,
CUSTOMER_NAME,
CITY
)
VALUES
(
101,
'ARUN',
'CHENNAI'
);Once the transaction is committed, SQL Server records the transaction in the transaction log.
The Log Reader Agent continuously monitors the transaction log of the publication database and identifies committed transactions marked for replication.
It extracts those changes and moves them into the Distribution Database.
An important point here is that the Log Reader Agent doesn’t simply scan every transaction and replicate everything. It identifies the transactions relevant to replication.
Step 3: Storing Transactions in the Distribution Database
Now the captured transaction reaches the Distributor.
The Distribution Database temporarily stores the replication commands, metadata, and delivery information.
Think of this database as a queue.
If the Subscriber is temporarily unavailable, the transactions can remain in the Distribution Database until they can be delivered, subject to the configured retention and cleanup settings.
This is one of the important responsibilities of the Distributor.
Step 4: Delivering Changes to the Subscriber
Now comes the Distribution Agent.
It reads the pending transactions from the Distribution Database and applies them to the Subscriber database.
For example, the INSERT operation performed in the Publisher is applied to the corresponding table in the Subscriber.
The same process happens for UPDATE and DELETE operations.
SQL Server preserves transaction order and transaction boundaries within a publication, helping maintain transactional consistency.
The complete data flow is:
INSERT / UPDATE / DELETE
|
↓
Publisher
|
Transaction Log
|
↓
Log Reader Agent
|
↓
Distribution Database
|
↓
Distribution Agent
|
↓
Subscriber
|
↓
Changes AppliedThat’s how Transactional Replication keeps the destination database updated without manually transferring data.
