Mastering SQL Server Transactional Replication: Creating, Testing, and Monitoring a Reliable Data Sync Environment

In modern IT environments, businesses often need data to be available in multiple locations for reporting, disaster recovery, analytics, or application performance. One of the most proven technologies for near real-time data distribution in Microsoft SQL Server is Transactional Replication.

Whether you’re syncing production data to a reporting server, distributing data to branch offices, or maintaining a hot standby environment, transactional replication can be a powerful solution when designed correctly.

This guide walks you through creating, testing, and monitoring SQL Server Transactional Replication step by step.

What is Transactional Replication?

Transactional Replication captures committed transactions from a source database and delivers them to one or more destination databases in near real time.

It uses three core roles:

Publisher

The source SQL Server that owns the original database and publishes data.

Distributor

The server that stores replication metadata and transaction history.

Subscriber

The destination SQL Server receiving replicated data.

Common Use Cases

Transactional Replication is ideal for:

  • Reporting databases
  • Read-only scale-out environments
  • Remote office data distribution
  • Application offloading
  • Selective table replication
  • Data synchronization between servers
  • DR reporting copies
Architecture Overview 
Application → Publisher → Log Reader Agent → Distributor → Distribution Agent → Subscriber

Changes made at the Publisher are read from the transaction log and pushed or pulled to Subscribers.

Prerequisites

Before configuring replication, ensure:

  • SQL Server Agent is running
  • TCP/IP enabled
  • Publisher and Subscriber can communicate
  • Sufficient disk space for snapshots
  • Database recovery model is Full or Simple (depending on design)
  • Tables have Primary Keys
  • SQL Server accounts have required permissions
Step 1: Configure the Distributor

Open SQL Server Management Studio (SSMS):

Replication → Configure Distribution

Choose:

  • Local Distributor (same server)
    or
  • Remote Distributor

Create the distribution database.

Step 2: Enable the Database for Publishing

Run:

USE master;
GOEXEC sp_replicationdboption
    @dbname = 'SalesDB',
    @optname = 'publish',
    @value = 'true';
GO
Step 3: Create a Transactional Publication

In SSMS:

Replication → Local Publications → New Publication

Choose:

  • Database: SalesDB
  • Publication Type: Transactional Publication

Select the articles:

  • Tables
  • Views
  • Stored Procedures (optional)

Example tables:

  • Customers
  • Orders
  • Products
Step 4: Configure Snapshot Agent

The Snapshot Agent creates the initial schema and data set for Subscribers.

Use a dedicated folder such as:

C:\ReplData

Share it as:

\\SQLSERVER01\ReplData

Grant access to SQL Server Agent and replication accounts.

Avoid using:

C:\Program Files\...

because Windows permissions may block folder creation.

Step 5: Create the Subscription

In SSMS:

Right-click Publication → New Subscriptions

Choose:

Push Subscription

Publisher pushes changes to Subscriber.

Pull Subscription

Subscriber pulls changes from Distributor.

For most internal environments, Push Subscription is easier to manage.

Step 6: Initialize the Subscriber

Run Snapshot Agent:

EXEC sp_startpublication_snapshot
    @publication = 'Sales-PUB';
GO

The initial schema and data will be delivered to the Subscriber.

Testing Transactional Replication

After setup, testing is essential.

Test 1: Insert Data

On Publisher:

USE SalesDB;
GOINSERT INTO Customers(CustomerID, Name)
VALUES (101, 'John Smith');
GO

Check Subscriber:

SELECT * FROM Customers
WHERE CustomerID = 101;

Expected result: row appears.

Test 2: Update Data

Publisher:

UPDATE Customers
SET Name = 'John S'
WHERE CustomerID = 101;

Subscriber should reflect the update.

Test 3: Delete Data

Publisher:

DELETE FROM Customers
WHERE CustomerID = 101;

Subscriber should remove the row.

Test 4: Replication Latency

Measure how quickly data arrives.

Insert a timestamped row:

INSERT INTO Orders(OrderID, CreatedDate)
VALUES (1, GETDATE());

Compare arrival time at Subscriber.

How to Add New Tables Later

You can add articles to an existing publication.

EXEC sp_addarticle
    @publication = 'Sales-PUB',
    @article = 'Invoices',
    @source_owner = 'dbo',
    @source_object = 'Invoices',
    @type = 'logbased';
GO

Then regenerate snapshot.

Monitoring Transactional Replication

Replication without monitoring is risky. Use multiple methods.

1. Replication Monitor

In SSMS:

Replication → Launch Replication Monitor

Track:

  • Agent status
  • Latency
  • Throughput
  • Undistributed commands
  • Failures
2. SQL Server Agent Jobs

Monitor jobs:

  • Snapshot Agent
  • Log Reader Agent
  • Distribution Agent

Check:

SQL Server Agent → Jobs

Ensure jobs succeed regularly.

3. Query Replication Status

Pending Commands

EXEC sp_replmonitorsubscriptionpendingcmds
    @publisher = 'SQLPUB01',
    @publisher_db = 'SalesDB',
    @publication = 'Sales-PUB',
    @subscriber = 'SQLSUB01',
    @subscriber_db = 'SalesDB';
GO

Agent History

EXEC msdb.dbo.sp_help_jobhistory;
Common Problems and Fixes
Issue: Snapshot Agent Access Denied

Error:

Access to path is denied

Fix:

  • Grant NTFS permissions
  • Use C:\ReplData
  • Avoid Program Files folder
Issue: Cannot Connect to Distributor

Fix:

  • Start SQL Browser
  • Enable TCP/IP
  • Open firewall ports
  • Use static SQL port
Issue: Subscriber Behind

Fix:

  • Check network latency
  • Increase Distribution Agent performance
  • Investigate blocking
  • Review pending commands
Issue: Missing Rows

Fix:

  • Verify article settings
  • Reinitialize subscription if required
  • Check PKs and identity settings
Best Practices

Use Dedicated Snapshot Folder

D:\ReplData

Monitor Daily

Set alerts for failed jobs.

Keep Transactions Small

Large batch transactions increase latency.

Use Stable Network Links

Replication depends on connectivity.

Separate Reporting Workloads

Use subscribers for read-heavy reporting.

Backup Both Systems

Replication is not a backup solution.

Performance Tips
  • Index replicated tables properly
  • Avoid huge transactions
  • Keep transaction log healthy
  • Use fast disks for distribution database
  • Monitor tempdb and msdb
  • Purge old replication history
Security Recommendations
  • Use least privilege accounts
  • Restrict snapshot share access
  • Encrypt SQL connections where possible
  • Audit agent failures
  • Rotate passwords regularly
When to Use Transactional Replication vs Always On

Use Transactional Replication When:

  • Need subset of tables
  • Need reporting copy
  • Need selective data distribution
  • Need multi-version subscribers

Use Always On When:

  • Need HA/failover
  • Need full database copy
  • Need automatic failover
Final Thoughts

SQL Server Transactional Replication remains one of the most practical and reliable ways to distribute SQL Server data in near real time.

When configured correctly, it provides:

  • Fast synchronization
  • Reliable reporting databases
  • Flexible table-level replication
  • Scalable read workloads

The key to success is not just setup—but ongoing testing, monitoring, and operational discipline.