How to Set Up SQL Server Replication on Windows Server 2022
SQL Server Replication is a technology that copies and distributes data and database objects from one database to another, then synchronizes between databases to maintain consistency. On Windows Server 2022, SQL Server replication is frequently used for distributing read workloads, offloading reporting queries, and maintaining high-availability copies of critical data. This guide walks through every stage of setting up replication, from choosing the right type to monitoring and troubleshooting agents.
Understanding Replication Types
SQL Server supports three primary replication types, each designed for different workload requirements.
Snapshot Replication takes a complete picture of the published data at a point in time and delivers it to Subscribers. It is the simplest form and is appropriate when data does not change frequently, or when the Subscriber needs a full refresh periodically. The Snapshot Agent generates a snapshot and the Distribution Agent delivers it.
Transactional Replication captures every INSERT, UPDATE, and DELETE from the Publisher’s transaction log and forwards those changes to Subscribers, usually within seconds. It requires the database to be in full or bulk-logged recovery model. The Log Reader Agent reads the transaction log and moves committed transactions to the distribution database; the Distribution Agent then applies them. This is the most popular type for OLTP environments that need near-real-time read replicas.
Merge Replication allows changes to occur both at the Publisher and at each Subscriber, with conflicts detected and resolved based on a conflict resolver configured per article. It is suited for disconnected or occasionally-connected environments such as field laptops that synchronize when they reconnect to the corporate network. Merge replication uses rowguid columns (uniqueidentifiers) to track rows across nodes.
Peer-to-Peer Transactional Replication (covered briefly at the end) extends transactional replication to allow all nodes to act as Publishers and Subscribers simultaneously, providing a scale-out read/write topology.
Architecture: Publisher, Distributor, and Subscriber
Every replication topology involves three logical roles:
The Publisher is the SQL Server instance that owns the source data and makes it available via a publication. The Distributor hosts the distribution database, which acts as a store-and-forward queue between Publisher and Subscriber. The Distributor can be co-located on the Publisher (local distributor) or on a dedicated server (remote distributor). The Subscriber receives and applies the replicated data. A single publication can have multiple Subscribers.
Configuring the Distribution Database
The first step is configuring a Distributor. In SSMS, right-click the Replication node under the instance and choose Configure Distribution, or run the following T-SQL. The example uses a local distributor:
USE master;
GO
-- Declare the server as its own distributor
EXEC sp_adddistributor
@distributor = @@SERVERNAME,
@password = 'StrongDistributorPwd#2022';
GO
-- Create the distribution database
EXEC sp_adddistributiondb
@database = 'distribution',
@data_folder = 'C:Program FilesMicrosoft SQL ServerMSSQL16.MSSQLSERVERMSSQLDATA',
@log_folder = 'C:Program FilesMicrosoft SQL ServerMSSQL16.MSSQLSERVERMSSQLDATA',
@log_file_size = 2,
@min_distretention = 0,
@max_distretention = 72,
@history_retention = 48,
@security_mode = 1; -- Windows Authentication
GOThe max_distretention value (hours) controls how long undelivered transactions are kept before they are purged. Increase this in environments where Subscribers may be offline for extended periods.
Configuring the Publisher
After the distribution database exists, register the Publisher with the Distributor:
EXEC sp_adddistpublisher
@publisher = @@SERVERNAME,
@distribution_db = 'distribution',
@security_mode = 1,
@working_directory = '\WIN2022PUBReplData',
@trusted = 'false',
@thirdparty_flag = 0;
GOThe @working_directory is the UNC path to the snapshot folder. Both the Snapshot Agent service account and all Subscriber Distribution Agent accounts must have read access to this share.
Enabling the Publication Database
USE master;
GO
EXEC sp_replicationdboption
@dbname = 'SalesDB',
@optname = 'publish',
@value = 'true';
GOCreating a Transactional Publication
USE SalesDB;
GO
EXEC sp_addpublication
@publication = 'SalesDB_Trans_Pub',
@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,
@ftp_login = 'anonymous',
@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,
@allow_initialize_from_backup = 'false',
@enabled_for_p2p = 'false',
@enabled_for_het_sub = 'false';
GOAdding Articles with sp_addarticle
An article defines a table, view, stored procedure, or other object to be replicated within a publication. Add each table you want to replicate:
USE SalesDB;
GO
EXEC sp_addarticle
@publication = 'SalesDB_Trans_Pub',
@article = 'Orders',
@source_owner = 'dbo',
@source_object = 'Orders',
@type = 'logbased',
@description = NULL,
@creation_script = NULL,
@pre_creation_cmd = 'drop',
@schema_option = 0x000000000803509F,
@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
EXEC sp_addarticle
@publication = 'SalesDB_Trans_Pub',
@article = 'Customers',
@source_owner = 'dbo',
@source_object = 'Customers',
@type = 'logbased',
@pre_creation_cmd = 'drop',
@schema_option = 0x000000000803509F,
@destination_table = 'Customers',
@destination_owner = 'dbo',
@status = 24,
@vertical_partition = 'false';
GOStarting the Snapshot Agent
Before adding subscriptions, generate the initial snapshot. In SSMS, right-click the publication, choose View Snapshot Agent Status, and click Start. Via T-SQL, call the Snapshot Agent job directly:
-- Find the snapshot agent job name
SELECT ja.name, j.name AS job_name
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
WHERE j.name LIKE '%SalesDB_Trans_Pub%'
AND js.step_name = 'Run agent.';
-- Start the snapshot agent job (replace job name with actual value)
EXEC msdb.dbo.sp_start_job
@job_name = N'WIN2022PUB-SalesDB-SalesDB_Trans_Pub-1';
GOAdding a Push Subscription with sp_addsubscription
USE SalesDB;
GO
EXEC sp_addsubscription
@publication = 'SalesDB_Trans_Pub',
@subscriber = 'WIN2022SUB', -- Subscriber server name
@destination_db = 'SalesDB_Replica',
@subscription_type = 'Push',
@sync_type = 'automatic',
@article = 'all',
@update_mode = 'read only',
@subscriber_type = 0;
GO
-- Add the Distribution Agent job for the push subscription
EXEC sp_addpushsubscription_agent
@publication = 'SalesDB_Trans_Pub',
@subscriber = 'WIN2022SUB',
@subscriber_db = 'SalesDB_Replica',
@subscriber_security_mode = 1, -- Windows Auth
@frequency_type = 64, -- When SQL Agent starts
@frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 4,
@frequency_subday_interval = 5,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0;
GOUsing Replication Monitor
Replication Monitor is the primary GUI tool for monitoring replication health. Launch it from SSMS by right-clicking the Replication node and selecting Launch Replication Monitor. The left pane shows Publishers grouped by Distributor. Selecting a publication reveals tabs for All Subscriptions, Agents, and Warnings & Agents.
Key columns to watch in the All Subscriptions tab: Status (Running, Retrying, Error), Latency (seconds between commit at Publisher and delivery to Subscriber), and Last Synchronized. Click any subscription to drill into agent history and see individual command delivery counts.
Understanding and Troubleshooting Replication Agents
Three agents drive transactional replication:
The Snapshot Agent (snapshot.exe) runs on the Distributor and generates the initial snapshot files in the snapshot folder. If it fails, check permissions on the UNC share and verify the agent account has db_owner on the publication database.
The Log Reader Agent (logread.exe) runs continuously on the Distributor, reading the Publisher’s transaction log. Common failures: the publication database log is full, or the agent account lacks db_owner on the publication database. Query its status:
-- Check Log Reader Agent history
SELECT TOP 20
h.runstatus,
h.start_time,
h.time,
h.duration,
h.comments,
h.delivered_commands,
h.delivered_transactions
FROM distribution.dbo.MSlogreader_history h
JOIN distribution.dbo.MSlogreader_agents a ON h.agent_id = a.id
WHERE a.publication = 'SalesDB_Trans_Pub'
ORDER BY h.time DESC;
GOThe Distribution Agent (distrib.exe) moves commands from the distribution database to the Subscriber. Check its history similarly:
SELECT TOP 20
h.runstatus,
h.start_time,
h.time,
h.duration,
h.comments,
h.delivered_commands,
h.delivered_transactions,
h.error_id
FROM distribution.dbo.MSdistribution_history h
JOIN distribution.dbo.MSdistribution_agents a ON h.agent_id = a.id
WHERE a.subscriber_db = 'SalesDB_Replica'
ORDER BY h.time DESC;
GOA runstatus of 6 means error. The error_id joins to distribution.dbo.MSerror_class for a description. For persistent “Retrying” states, check network connectivity from Distributor to Subscriber and verify the Subscriber database is online.
Checking Replication Latency with Tracer Tokens
-- Insert a tracer token
EXEC sys.sp_posttracertoken
@publication = 'SalesDB_Trans_Pub';
GO
-- After a few seconds, retrieve results
EXEC sys.sp_helptracertokenhistory
@publication = 'SalesDB_Trans_Pub',
@tracer_id = 1; -- Use the ID returned by sp_posttracertoken
GOThe result shows distributor_latency (Publisher to Distributor) and subscriber_latency (Distributor to Subscriber) in seconds.
Peer-to-Peer Transactional Replication Overview
Peer-to-peer (P2P) replication extends transactional replication to a multi-master topology. Each node publishes and subscribes to all other nodes. All nodes must run Enterprise or Developer edition. Enable it at the publication level with @enabled_for_p2p = 'true', then use the Configure Peer-to-Peer Topology wizard in SSMS (right-click the publication > Configure Peer-to-Peer Topology). P2P replication includes a conflict detection mechanism: when the same row is updated on two nodes simultaneously, SQL Server raises error 22815. Define a conflict detection level (@p2p_conflictdetection) as row, column, or logical record. For most deployments, designate one node as the originator to avoid conflicts rather than allowing true multi-master writes.
Removing Replication
-- Remove a subscription first
USE SalesDB;
EXEC sp_dropsubscription
@publication = 'SalesDB_Trans_Pub',
@subscriber = 'WIN2022SUB',
@article = 'all';
GO
-- Then remove the publication
EXEC sp_droppublication
@publication = 'SalesDB_Trans_Pub';
GO
-- Disable replication on the database
EXEC sp_replicationdboption
@dbname = 'SalesDB',
@optname = 'publish',
@value = 'false';
GO
-- Finally, remove the distributor
EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1;
GOSQL Server Replication on Windows Server 2022 provides a mature, flexible data distribution platform. Whether you need a lightweight snapshot for periodic refreshes, continuous transactional delivery for near-real-time read replicas, or a fully distributed peer-to-peer topology, the combination of T-SQL stored procedures and the Replication Monitor gives you complete visibility and control. Always baseline latency with tracer tokens after setup and schedule regular agent history reviews as part of ongoing database operations.
