How to Set Up SQL Server Transactional Replication: A Practical Guide

SQL Server transactional replication moves data and schema changes from one database to another in near real-time. It tracks individual INSERT, UPDATE, and DELETE operations as they happen on the Publisher, captures them via the transaction log, and applies them to one or more Subscribers.

This is the tool you reach for when:

• You need near real-time data movement (not just nightly batch sync)
• You want to replicate specific tables, not an entire database
• You're distributing read traffic across replicas
• You're building toward a disaster recovery copy

If you need everything synchronized including schema changes and you can tolerate synchronous replication, Always On Availability Groups are a better fit. If you need one-time data movement with no ongoing sync, snapshot replication is simpler. Transactional replication sits between those two.

The Core Components

Think of transactional replication as a pipeline with four main stops:

An article is a single replicated object. A publication is a logical grouping of articles. You subscribe to publications, not individual articles.

[Publisher DB] --> [Log Reader Agent]
                          |
                          v
                    [Distribution DB]
                          |
                          v
              [Log Reader / Distribution Agent]
                          |
                          v
                   [Subscriber DB]

How the Log Reader Agent Works

The log reader agent is the engine of transactional replication. It runs as a SQL Server Agent job and continuously reads from the transaction log of the Publisher database.

Here's the key constraint that trips up a lot of people: your Publisher database must be in Full recovery model.

In Simple recovery, SQL Server reuses log space aggressively — transaction log records are truncated after each checkpoint. The log reader agent can't read records that no longer exist. In Full recovery, log records are retained until a log backup is taken, giving the log reader time to process them.

If you're starting with a database in Simple recovery and need replication, plan for the switch. You'll need to take at least one full log backup after switching to Full recovery before the log reader can begin.

Full Recovery + Log Backups   -->  Log Reader can scan the log
Simple Recovery               -->  Log space is reused, log reader misses changes

Types of Articles

Not everything in replication is a table. SQL Server supports three article types:

• Table articles — Replicate row data (INSERT/UPDATE/DELETE). Default choice for most use cases.
• Stored procedure articles — Replicate the execution of a stored procedure call rather than the underlying row changes. Useful when you want to replicate a business process, not just data.
• View articles — Replicate the result set of a view as a table at the Subscriber.

Table articles are what you'll use 95% of the time.

Push vs. Pull Subscriptions

When a subscription is push, the Distribution Agent runs on the Distributor and pushes changes to the Subscriber. This is simpler to manage — all agent jobs live in one place — and is the default.

When a subscription is pull, the Distribution Agent runs on the Subscriber and pulls changes from the Distributor. This reduces load on the Distributor and is useful when Subscribers are on slower or intermittent network links.

For a first-time setup on a local network, push subscriptions are the easier path.

Filtering: Row Filters and Column Filters

Replication gives you two filtering mechanisms:

• Row filters — Replicate a subset of rows based on a WHERE clause. Example: only replicate rows where Region = 'EMEA'.
• Column filters — Replicate only specific columns, excluding others (useful for excluding sensitive data like SSN or PII before it leaves the Publisher).

Filters add flexibility but also add complexity. Each filtered article creates its own set of replicated commands and may require careful handling of the initial snapshot. Start without filters, add them once the baseline topology is working.

The Initial Snapshot

Before the ongoing sync begins, the Subscriber needs a starting point. That starting point is the initial snapshot — a copy of the published data at the moment the subscription is initialized.

The snapshot agent generates snapshot files (containing data in BCP format and schema scripts) and stores them in a snapshot folder (a network share). The Distribution Agent then applies these files to the Subscriber when the subscription is created.

For large databases, the initial snapshot can be slow and disk-intensive. An alternative: initialize the Subscriber from a database backup using sp_inititatesubscription with the @backupdevicetype parameter. This avoids the snapshot entirely and is significantly faster for multi-hundred-gigabyte databases.

Common Use Cases

How It Differs from Always On AGs and CDC

People often conflate these. Brief clarification:

• Always On Availability Groups require Windows Server Failover Clustering, shared storage, and operate synchronously. You can't selectively replicate tables. It's a high-availability solution, not a data distribution tool.
• Change Data Capture (CDC) captures changes via the transaction log but writes them to CDC tables in the same database. It's designed for ETL and auditing, not for moving data to a separate Subscriber database.

Transactional replication is the right tool when you need to project data to one or more separate databases on a near real-time basis, with control over exactly what moves.

Working Example

Prerequisites

• Two SQL Server instances (can be the same server with different named instances)
• SQL Server Agent running on the Publisher
• Network access from Publisher/Distributor to the snapshot folder
• sysadmin or db_owner rights on the Publisher

For this walkthrough, we'll use a single server with two databases:

• Publisher: RepPublisher (source)
• Subscriber: RepSubscriber (destination)

Step 1 — Enable the Publisher

-- Run on the Publisher instance
USE master;
GO

EXEC sp_replicationdboption
    @dbname = N'RepPublisher',
    @optname = N'publish',
    @value = N'true';
GO

This creates the replication system tables and enables the publication database.

Step 2 — Configure the Distributor

-- Run on the Publisher instance
EXEC sp_adddistributor
    @distributor = @@SERVERNAME,
    @password = N'DistributorPassword123!';
GO

EXEC sp_adddistributiondb
    @database = N'distribution',
    @security_mode = 0,
    @login = N'sa',
    @password = N'DistributorPassword123!';
GO

Replace N'sa' and the password with appropriate credentials for your environment.

EXEC sp_addpublisherdistributor
    @publisher = @@SERVERNAME,
    @distribution_db = N'distribution',
    @working_directory = N'\\\\YourServer\\replshare\\repldata',
    @password = N'DistributorPassword123!';
GO

The working_directory is a network share the snapshot and distribution agents use. Make sure the SQL Server service account has read/write access to it.

Step 3 — Create the Publication

-- Run on the Publisher instance
USE [RepPublisher];
GO

-- Create the publication
EXEC sp_addpublication
    @publication = N'Pub_OrdersAndCustomers',
    @status = N'active',
    @repl_freq = N'continuous',
    @allow_push = N'true',
    @allow_pull = N'true',
    @allow_anonymous = N'false',
    @enabled_for_internet = N'false',
    @snapshot_in_defaultfolder = N'true',
    @compress_snapshot = N'false',
    @allow_subscription_copy = N'false',
    @conflict_policy = N'pub wins',
    @centralized_conflict = N'true',
    @conflict_retention = 14,
    @allow_initialize_from_backup = N'true',
    @native_stdproc_mode = N'continuous';
GO

-- Add Customers table as an article
EXEC sp_addarticle
    @publication = N'Pub_OrdersAndCustomers',
    @article = N'Customers',
    @source_owner = N'dbo',
    @source_object = N'Customers',
    @type = N'logbased',
    @description = N'Customers table article',
    @ins_cmd = N'CALL sp_MS_ins_Customers',
    @del_cmd = N'CALL sp_MS_del_Customers',
    @upd_cmd = N'SCALL sp_MS_upd_Customers';
GO

-- Add Orders table as an article
EXEC sp_addarticle
    @publication = N'Pub_OrdersAndCustomers',
    @article = N'Orders',
    @source_owner = N'dbo',
    @source_object = N'Orders',
    @type = N'logbased',
    @description = N'Orders table article',
    @ins_cmd = N'CALL sp_MS_ins_Orders',
    @del_cmd = N'CALL sp_MS_del_Orders',
    @upd_cmd = N'SCALL sp_MS_upd_Orders';
GO

-- Create the snapshot agent job
EXEC sp_addpublication_snapshot
    @publication = N'Pub_OrdersAndCustomers',
    @frequency_type = 1,
    @frequency_interval = 0,
    @frequency_relative_interval = 0,
    @frequency_recurrence_factor = 0,
    @frequency_subday = 0,
    @frequency_subday_interval = 0,
    @active_start_date = 0,
    @active_end_date = 0,
    @active_start_time_of_day = 0,
    @active_end_time_of_day = 235959,
    @snapshot_agent_security_mode = 1,
    @login = N'sa';
GO

The article type logbased tells SQL Server to use the log reader agent for this article rather than a custom proc. The sp_MS_ins_*, sp_MS_upd_*, sp_MS_del_* procedure names are automatically generated custom procedures SQL Server creates for applying changes at the Subscriber.

Step 4 — Create a Push Subscription

-- Run on the Publisher instance
USE [RepPublisher];
GO

EXEC sp_addsubscription
    @publication = N'Pub_OrdersAndCustomers',
    @subscriber = N'YourServerName',
    @destination_db = N'RepSubscriber',
    @sync_type = N'auto',
    @subscription_type = N'push',
    @update_mode = N'read only';
GO

EXEC sp_addpushsubscriptionagent
    @publication = N'Pub_OrdersAndCustomers',
    @subscriber = N'YourServerName',
    @subscriber_db = N'RepSubscriber',
    @job_login = NULL,
    @job_password = NULL,
    @subscriber_security_mode = 1;
GO

If the Subscriber is on a different server, replace YourServerName with the actual server/instance name and use appropriate SQL authentication credentials.

Step 5 — Verify Replication Is Working

5a. Confirm the Log Reader Agent is running

-- On the Publisher
SELECT
    s.name                      AS 'Agent Name',
    s.status                    AS 'Status',
    s.last_run_time             AS 'Last Run',
    CASE s.status
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Started'
        WHEN 2 THEN 'Succeeded'
        WHEN 3 THEN 'In Progress'
        ELSE 'Unknown'
    END AS 'Status Description'
FROM msdb.dbo.MSdistributor AS d
JOIN msdb.dbo.MSdistribution_agents AS a
    ON d.id = a.distributor_id
CROSS JOIN msdb.dbo.MSreplservers AS s
WHERE a.publisher_db = 'RepPublisher';
GO

Also check SQL Server Agent Jobs for jobs named like REPL-LogReader and REPL-Snapshot.

5b. Check commands in the distribution database

-- On the Distributor (or Publisher if local Distributor)
SELECT TOP 20
    xact_seqno,
    article_id,
    command,
    entry_time
FROM distribution.dbo.MSrepl_commands
ORDER BY entry_time DESC;
GO

Rows appearing here mean the log reader has captured and written them to the distribution database. If this is empty but the log reader is running, there may be no pending changes at the Publisher yet — that's fine.

5c. Validate row counts

-- On Publisher
SELECT COUNT(*) FROM RepPublisher.dbo.Customers;  -- Source

-- On Subscriber
SELECT COUNT(*) FROM RepSubscriber.dbo.Customers;  -- Destination

Match = replication is working. If Subscriber count is 0 after a fresh subscription, the snapshot hasn't been applied yet. You can force the snapshot agent to run from SQL Server Agent Jobs (job name: REPL-Snapshot for your publication).

Step 6 — Test with an Insert and Update

-- On the Publisher — insert a test row
INSERT INTO RepPublisher.dbo.Customers (CustomerName, Email, Region)
VALUES ('Acme Corp', 'billing@acme.com', 'EMEA');

-- Wait about 30 seconds for the log reader to pick it up,
-- then verify on the Subscriber

-- On the Subscriber
SELECT * FROM RepSubscriber.dbo.Customers
WHERE CustomerName = 'Acme Corp';

You should see the row appear on the Subscriber within seconds (usually 30–60 seconds under normal latency). Now test an update:

-- On Publisher
UPDATE RepPublisher.dbo.Customers
SET Region = 'APAC'
WHERE CustomerName = 'Acme Corp';

-- Confirm on Subscriber
SELECT CustomerName, Email, Region
FROM RepSubscriber.dbo.Customers
WHERE CustomerName = 'Acme Corp';

The Region column should show APAC on the Subscriber. If both tests pass, your replication topology is live.

Common Problems and Fixes

Make Replication Production-Ready

A basic setup gets you moving. Production replication needs more:

• Monitor latency — Use Replication Monitor (right-click the publication in SSMS → Launch Replication Monitor). Latency above 30 seconds under load warrants investigation.
• Validate regularly — Run sp_articlevalidation and sp_subscription_validation on a schedule to confirm Publisher and Subscriber data match.
• Script everything — Right-click each publication and subscription in SSMS → Generate Script. Store these scripts in source control. You will need them during a DR scenario.
• Plan for large tables — Consider initializing from backup instead of snapshot for tables over 100GB. Use @sync_type = N'initialize with backup' on sp_addsubscription.

Replication setup is just one piece of a healthy SQL Server environment. If your servers haven't had a full health review, we offer a free database health assessment covering index fragmentation, backup integrity, wait stats, and replication baseline.

Request Your Free Database Health Assessment