How to Set Up SQL Server Replication on Windows Server 2025

How to Set Up SQL Server Replication on Windows Server 2025

SQL Server replication is a powerful set of technologies for copying and distributing data and database objects from one database to another, then synchronizing between databases to maintain consistency. On Windows Server 2025, SQL Server replication is commonly used to distribute data across multiple servers for load balancing, reporting offloading, or disaster recovery scenarios. This tutorial walks through the three primary replication types, how to configure a Distributor and Publisher, how to create Subscriptions, and how to monitor and troubleshoot your replication topology.

Prerequisites

• Windows Server 2025 with SQL Server 2019 or SQL Server 2022 installed
• SQL Server Agent service running and configured for automatic startup
• A Publisher instance (source of data) and at least one Subscriber instance
• Sysadmin rights on all participating SQL Server instances
• Network connectivity between Publisher, Distributor, and Subscriber
• A shared network folder (UNC path) accessible by SQL Server Agent service accounts for the snapshot share

Step 1: Understand Replication Types

SQL Server supports three main replication models. Choose the type that fits your data movement requirements:

• Snapshot Replication — Takes a full copy of the published objects at a scheduled interval and applies it to Subscribers. Suitable for small datasets that change infrequently or when data freshness requirements are relaxed.
• Transactional Replication — Reads the Publisher’s transaction log and propagates individual changes (inserts, updates, deletes) to Subscribers near real-time. Best for high-volume OLTP databases requiring low latency.
• Merge Replication — Allows changes at both Publisher and Subscriber, then reconciles conflicts. Commonly used for occasionally-connected mobile or remote scenarios.

This tutorial focuses primarily on Transactional Replication, the most widely deployed model in enterprise environments on Windows Server 2025.

Step 2: Verify SQL Server Agent Is Running

Replication agents (Log Reader Agent, Snapshot Agent, Distribution Agent) run as SQL Server Agent jobs. Verify Agent is running before proceeding:

-- Check SQL Server Agent status
SELECT servicename, status_desc
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server Agent%';

If the Agent is stopped, start it via Services Manager or from the command line:

net start "SQL Server Agent (MSSQLSERVER)"

Also set the service to start automatically in Services.msc or via PowerShell:

Set-Service -Name SQLSERVERAGENT -StartupType Automatic
Start-Service -Name SQLSERVERAGENT

Step 3: Configure the Distributor

The Distributor stores replication metadata and history in the distribution database. In most setups, the Publisher also acts as its own Distributor (local distribution). Run the following on the Distributor instance:

USE master;
GO

-- Add Distributor (self-distribution in this example)
EXEC sp_adddistributor
    @distributor = @@SERVERNAME,
    @password = 'StrongDistribPassword2025!';
GO

-- Create the distribution database
EXEC sp_adddistributiondb
    @database = 'distribution',
    @data_folder = 'C:SQLData',
    @log_folder = 'C:SQLLogs',
    @log_file_size = 2,
    @min_distretention = 0,
    @max_distretention = 72,
    @history_retention = 48,
    @security_mode = 1;
GO

Then configure the Publisher to use this Distributor:

EXEC sp_adddistpublisher
    @publisher = @@SERVERNAME,
    @distribution_db = 'distribution',
    @security_mode = 1,
    @working_directory = '\WS2025-PUBReplSnapshot',
    @trusted = 'false',
    @thirdparty_flag = 0;
GO

The @working_directory is the UNC path to the snapshot share. Ensure the SQL Server Agent service account has read/write access to this share.

Step 4: Enable the Publisher Database and Create a Publication

Enable the source database for replication and create a Transactional Publication:

USE master;
GO

-- Enable the database as a publisher source
EXEC sp_replicationdboption
    @dbname = 'SalesDB',
    @optname = 'publish',
    @value = 'true';
GO

USE SalesDB;
GO

-- Create a transactional publication
EXEC sp_addpublication
    @publication = 'SalesDB_TransPub',
    @description = 'Transactional publication of SalesDB',
    @sync_method = 'concurrent',
    @retention = 0,
    @allow_push = 'true',
    @allow_pull = 'true',
    @allow_anonymous = 'false',
    @enabled_for_internet = 'false',
    @snapshot_in_defaultfolder = 'true',
    @compress_snapshot = 'false',
    @ftp_port = 21,
    @allow_subscription_copy = 'false',
    @add_to_active_directory = 'false',
    @repl_freq = 'continuous',
    @status = 'active',
    @independent_agent = 'true',
    @immediate_sync = 'true',
    @allow_sync_tran = 'false',
    @autogen_sync_procs = 'false',
    @allow_queued_tran = 'false',
    @allow_dts = 'false',
    @replicate_ddl = 1;
GO

Step 5: Add Articles to the Publication

Articles define which tables (or views, stored procedures) are included in the publication:

-- Add the Orders table as an article
EXEC sp_addarticle
    @publication = 'SalesDB_TransPub',
    @article = 'Orders',
    @source_owner = 'dbo',
    @source_object = 'Orders',
    @type = 'logbased',
    @description = NULL,
    @creation_script = NULL,
    @pre_creation_cmd = 'drop',
    @schema_option = 0x000000000803509D,
    @identityrangemanagementoption = 'manual',
    @destination_table = 'Orders',
    @destination_owner = 'dbo',
    @status = 24,
    @vertical_partition = 'false',
    @ins_cmd = 'CALL [sp_MSins_dboOrders]',
    @del_cmd = 'CALL [sp_MSdel_dboOrders]',
    @upd_cmd = 'SCALL [sp_MSupd_dboOrders]';
GO

-- Add the Customers table
EXEC sp_addarticle
    @publication = 'SalesDB_TransPub',
    @article = 'Customers',
    @source_owner = 'dbo',
    @source_object = 'Customers',
    @type = 'logbased',
    @pre_creation_cmd = 'drop',
    @schema_option = 0x000000000803509D,
    @destination_table = 'Customers',
    @destination_owner = 'dbo',
    @status = 24;
GO

Step 6: Create Subscriptions (Push vs Pull)

A Push Subscription is managed from the Publisher; the Distributor pushes changes to the Subscriber. A Pull Subscription is managed at the Subscriber; the Subscriber polls for changes. Push is simpler to manage centrally; Pull is preferred when Subscribers are across a WAN or firewall.

Push Subscription

USE SalesDB;
GO

-- Add the subscription
EXEC sp_addsubscription
    @publication = 'SalesDB_TransPub',
    @subscriber = 'WS2025-SUBSQLEXPRESS',
    @destination_db = 'SalesDB_Replica',
    @subscription_type = 'push',
    @sync_type = 'automatic',
    @article = 'all',
    @update_mode = 'read only',
    @subscriber_type = 0;
GO

-- Add the Distribution Agent for the push subscription
EXEC sp_addpushsubscription_agent
    @publication = 'SalesDB_TransPub',
    @subscriber = 'WS2025-SUBSQLEXPRESS',
    @subscriber_db = 'SalesDB_Replica',
    @job_login = 'DOMAINSQLAgentSvc',
    @job_password = 'AgentPassword2025!',
    @subscriber_security_mode = 1,
    @frequency_type = 64,
    @frequency_interval = 0,
    @frequency_relative_interval = 0,
    @frequency_recurrence_factor = 0,
    @frequency_subday = 0,
    @frequency_subday_interval = 0,
    @active_start_time_of_day = 0,
    @active_end_time_of_day = 235959,
    @active_start_date = 0,
    @active_end_date = 0;
GO

Pull Subscription (run on Subscriber)

USE SalesDB_Replica;
GO

EXEC sp_addpullsubscription
    @publisher = 'WS2025-PUB',
    @publication = 'SalesDB_TransPub',
    @publisher_db = 'SalesDB',
    @independent_agent = 'true',
    @subscription_type = 'pull',
    @description = 'Pull subscription from WS2025-PUB',
    @update_mode = 'read only',
    @immediate_sync = 1;
GO

EXEC sp_addpullsubscription_agent
    @publisher = 'WS2025-PUB',
    @publisher_db = 'SalesDB',
    @publication = 'SalesDB_TransPub',
    @distributor = 'WS2025-PUB',
    @distributor_security_mode = 1,
    @enabled_for_syncmgr = 'false',
    @frequency_type = 64,
    @frequency_interval = 0,
    @frequency_relative_interval = 0,
    @frequency_recurrence_factor = 0,
    @frequency_subday = 0,
    @frequency_subday_interval = 0,
    @active_start_time_of_day = 0,
    @active_end_time_of_day = 235959,
    @active_start_date = 0,
    @active_end_date = 0;
GO

Step 7: Monitor Replication

Use Replication Monitor (accessible from SSMS by right-clicking the Replication node) or query system tables to check health:

-- Check distributor agent status
USE distribution;
GO

SELECT TOP 20
    a.publisher_db,
    a.publication,
    a.subscriber_db,
    h.runstatus,
    h.comments,
    h.start_time,
    h.time
FROM MSdistribution_agents a
JOIN MSdistribution_history h ON a.id = h.agent_id
ORDER BY h.time DESC;
GO

-- Check for replication errors
SELECT *
FROM MSrepl_errors
ORDER BY time DESC;
GO

-- Check Log Reader Agent status
SELECT TOP 10 *
FROM MSlogreader_history
ORDER BY time DESC;
GO

Step 8: Troubleshoot Common Issues

Snapshot Agent Failures

If the Snapshot Agent fails, verify the snapshot share is accessible from the SQL Server Agent service account. Check the agent job history in SSMS under SQL Server Agent > Jobs. Common errors include permission denied on the UNC path or insufficient disk space:

-- View snapshot agent history
SELECT TOP 20 *
FROM distribution..MSsnapshot_history
ORDER BY time DESC;

Log Reader Agent Failures

The Log Reader Agent reads the Publisher’s transaction log. If it fails, the log may grow unbounded. Common causes include the Publisher database not being in Full recovery model, or the Log Reader not having access to the distribution database:

-- Ensure Publisher database is in FULL recovery
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'SalesDB';

-- If SIMPLE, change to FULL
ALTER DATABASE SalesDB SET RECOVERY FULL;
GO

Also check that the db_owner or replication-specific roles are granted on the distribution database to the Log Reader Agent login.

Conclusion

SQL Server replication on Windows Server 2025 provides a robust mechanism for distributing and synchronizing data across your infrastructure. Whether you need near-real-time Transactional Replication for an OLTP reporting offload, or Snapshot Replication for a periodic data refresh, the T-SQL stored procedures covered in this tutorial give you full programmatic control over the entire replication topology. Regularly monitor agent statuses via Replication Monitor and query the distribution database history tables to catch latency or failure issues early. With SQL Server Agent properly configured and the correct service account permissions in place, a well-planned replication setup on Windows Server 2025 can serve reliably for years.