Replication can be a challenging feature to understand at first. In this article, I will explain how transaction replication works.
Prerequisites
In transaction replication, the following three jobs are working. To understand transaction replication, it is essential to grasp the roles of these jobs.
Snapshot Agent
The Snapshot Agent creates an initial snapshot of the schema and data at the Publisher. This snapshot is applied to the Subscribers as the starting point for replication. It is typically used for the initial synchronization.
Distribution Agent
The Distribution Agent is transferring the replicated data from the Distributor to the Subscriber. It ensures that the transactions are applied to the Subscriber database in the correct order.
Log Reader Agent
The Log Reader Agent monitors the transaction log of the Publisher database and identifies the changes that need to be replicated. It reads these changes and moves them to the distribution database for further processing.
Overview
Transaction replication works as illustrated in the following diagram.
1. Snapshot Agent creates snapshot file. The snapshot file is including the schema and data at the publisher database.
2. Distribution Agent applies the snapshot files to the Subscriber database.
3. The Log Reader Agent reads the logs that need to be applied to the Subscriber database from the Publication database. The read logs are stored as commands in the Distribution database. This process is typically performed periodically.
4. Distribution Agent reads the commands that need to be applied to the Subscriber database from the Distribution database and applies them to the Subscriber database. This process is also typically performed periodically.
5. Transaction replication is performed through the continuous operation of the Log Reader Agent and the Distribution Agent.
Detailed work
We create the following environment to verify the operation of replication:
- Instance 1: Acts as both the Publisher and the Distributor
- Instance 2: Acts as the Subscriber
1. Execute the following query to verify the existence of jobs for each agent.
USE msdb;
GO
SELECT
sj.name AS JobName,
sjt.subsystem AS AgentName
FROM
sysjobs AS sj
INNER JOIN
sysjobsteps AS sjt
ON
sj.job_id = sjt.job_id
WHERE
sjt.subsystem IN (N'Snapshot', N'LogReader', N'Distribution')
ORDER BY
sjt.subsystem;OR
use distribution
go
SELECT name, 'Snapshot' as Agent FROM MSsnapshot_agents
SELECT name, 'Log Reader' as Agent FROM MSlogreader_agents
SELECT name, 'Distribution' as Agent FROM MSdistribution_agents2. Execute the Snapshot Agent job and verify that the snapshot files are created successfully.
3. If the Distribution Agent is running continuously, verify that the snapshot files have been applied to the Subscriber database. You can verify it by executing the following query.
use distribution
go
SELECT * FROM MSdistribution_history■ Query Result Example
4. Update the data on the Publication database.
5. Verify that the Log Reader Agent is delivering commands to the Distribution database. You can confirm whether the Log Reader Agent has delivered commands by executing the following query.
use distribution
go
SELECT * FROM MSlogreader_history■ Query Result Example
6. Verify that the Distribution Agent has delivered commands to the Subscriber database. You can confirm whether the Distribution Agent has delivered commands by executing the following query.
use distribution
go
SELECT * FROM MSdistribution_history■ Query Result Example
Notes
Information about the commands written to the Distribution database can be found in the MSrepl_commands and MSrepl_transactions tables.
use distribution
go
select * from MSrepl_commands
go
select * from MSrepl_transactions
goTo check the timestamps of the records in the MSrepl_commands table, you can do so by linking it with the MSrepl_transactions table.
SELECT entry_time, *
FROM MSrepl_commands
JOIN MSrepl_transactions
ON MSrepl_commands.xact_seqno = MSrepl_transactions.xact_seqno;