Chapter 17 · Replication, Log Shipping, Distributed Availability, and Data Movement
Articles, Publications, Subscriptions, Agents, Latency, Conflicts, and Monitoring
Operate publications, articles, subscriptions and replication agents using backlog, latency, security and reinitialization evidence rather than job-state assumptions.
Learning outcomes
A replication topology can look correct in Object Explorer while the Distributor is accumulating commands that no Subscriber receives. This lesson treats publications and subscriptions as deployable operational objects: you need an inventory, agent ownership, backlog evidence, latency tests and a safe reinitialization/rollback plan.
Inspect publications, articles and published objects using supported replication procedures/catalog metadata.
Explain push/pull subscriptions and where Snapshot, Log Reader, Distribution and Merge agents execute.
Measure transactional backlog and end-to-end latency instead of equating “agent running” with freshness.
Diagnose a stopped agent as a queueing problem before deleting/reinitializing anything.
Build a governed replication runbook covering security, cleanup, conflict handling and rollback.
1. Inventory before intervention
SELECT SERVERPROPERTY('ProductVersion') AS product_version, SERVERPROPERTY('ProductUpdateLevel') AS update_level, SERVERPROPERTY('Edition') AS edition, SERVERPROPERTY('EngineEdition') AS engine_edition, SERVERPROPERTY('IsHadrEnabled') AS hadr_enabled, SERVERPROPERTY('InstanceDefaultBackupPath') AS default_backup_path, SERVERPROPERTY('ServerName') AS server_name;GOSELECT name, compatibility_level, recovery_model_desc, is_published, is_subscribed, is_merge_published, is_distributorFROM sys.databasesWHERE name IN (N'ServiceHubLab', N'master', N'msdb') OR is_published=1 OR is_subscribed=1 OR is_merge_published=1 OR is_distributor=1;GOUSE ServiceHubLab;GOIF EXISTS (SELECT 1 FROM sys.databases WHERE database_id=DB_ID() AND is_published=1) EXEC sys.sp_helppublication;ELSE SELECT N'ServiceHubLab is not enabled for snapshot/transactional publishing' AS observation;GO-- For a real publication, run with the exact publication name:-- EXEC sys.sp_helparticle @publication = N'ServiceHub_Orders';GOSELECT name AS published_table, is_published, is_merge_published, is_schema_publishedFROM sys.tablesWHERE is_published=1 OR is_merge_published=1 OR is_schema_published=1;GOAn article is the unit you publish; a publication is the delivery contract. Filters, schema options and article properties determine what the Subscriber receives. Deleting a publication/article is therefore a topology change, not a harmless metadata cleanup. The Subscriber may retain objects even when publication metadata is removed.
| Agent | Primary job | Typical execution location |
|---|---|---|
| Snapshot Agent | Generate schema/data snapshot files | Distributor |
| Log Reader Agent | Read marked transactions from publication DB log into distribution DB | Distributor |
| Distribution Agent | Apply snapshots/transactional commands to Subscriber | Distributor for push; Subscriber for pull |
| Merge Agent | Exchange merge changes and resolve conflicts | Distributor for push; Subscriber for pull |
2. Running is not the same as caught up
Transactional replication has at least two queues to reason about: Publisher log → Distributor, and Distributor → Subscriber. A stopped Log Reader can leave replicated transactions in the publication log and contribute to log reuse pressure. A stopped Distribution Agent can leave a growing distribution backlog even if the Publisher log is being drained. Measure both legs.
-- Run on a transactional Publisher with appropriate db_owner/sysadmin permissions.EXEC sys.sp_replcounters;GO-- Run in the distribution database with real topology names.-- EXEC sys.sp_replmonitorsubscriptionpendingcmds-- @publisher = N'SQLPUB01',-- @publisher_db = N'ServiceHubLab',-- @publication = N'ServiceHub_Orders',-- @subscriber = N'SQLSUB01',-- @subscriber_db = N'ServiceHubReporting',-- @subscription_type = 0;GO
sp_replmonitorsubscriptionpendingcmds returns
pending commands and an estimated processing time. That estimate
is not an SLA. In Replication Monitor, tracer tokens add a
marker to the publication log and measure its travel through
Distributor and Subscriber, which is stronger end-to-end
evidence than “last agent action succeeded.”
3. Reproduce a broken-agent backlog safely
USE ServiceHubLab;GOIF SCHEMA_ID(N'lab17') IS NULL EXEC(N'CREATE SCHEMA lab17 AUTHORIZATION dbo;');GODROP TABLE IF EXISTS lab17.DeliveryQueue;DROP TABLE IF EXISTS lab17.SiteState;DROP TABLE IF EXISTS lab17.MovementRequirement;GOCREATE TABLE lab17.DeliveryQueue( queue_id bigint IDENTITY PRIMARY KEY, technology varchar(20) NOT NULL, source_commit_id bigint NOT NULL, created_at datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(), delivered_at datetime2(3) NULL, status varchar(16) NOT NULL DEFAULT 'PENDING');CREATE TABLE lab17.SiteState( site_name varchar(30) NOT NULL PRIMARY KEY, role_name varchar(30) NOT NULL, last_commit_id bigint NOT NULL, writable bit NOT NULL, last_refresh_at datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME());CREATE TABLE lab17.MovementRequirement( requirement_name varchar(80) NOT NULL PRIMARY KEY, whole_database bit NOT NULL, subset_filtering bit NOT NULL, multiple_writers bit NOT NULL, automatic_failover bit NOT NULL, target_rpo_seconds int NULL, notes nvarchar(300) NULL);GOINSERT lab17.DeliveryQueue(technology,source_commit_id,status)SELECT 'TRANSACTIONAL', 80000 + n, 'PENDING'FROM (VALUES(1),(2),(3),(4),(5)) AS x(n);WAITFOR DELAY '00:00:02';SELECT COUNT(*) AS pending_commands, MIN(created_at) AS oldest_pending_created_at, DATEDIFF(second,MIN(created_at),SYSUTCDATETIME()) AS oldest_age_secondsFROM lab17.DeliveryQueueWHERE technology='TRANSACTIONAL' AND status='PENDING';GO-- Repair: restore delivery, do not delete the queue to make the dashboard green.UPDATE lab17.DeliveryQueueSET status='DELIVERED', delivered_at=SYSUTCDATETIME()WHERE technology='TRANSACTIONAL' AND status='PENDING';GOReinitialization can generate/apply a new snapshot, create significant I/O/network load and overwrite Subscriber-side state depending on article properties. First identify the failed leg: agent history, authentication/TLS errors, disk pressure, distribution database growth, blocking, long transactions or Subscriber capacity. Reinitialize only when the subscription truly needs a new baseline and you have a maintenance/rollback plan.
4. Merge conflicts and security are not afterthoughts
Merge replication explicitly permits changes at multiple nodes. That makes conflict detection/resolution part of the business contract: which node wins, can losing changes be audited, and what happens after a long offline period? Transactional topologies still have security complexity: agent process accounts, Publisher/Distributor/Subscriber permissions, snapshot-share ACLs and TLS certificates. Avoid one shared sysadmin account for every agent.
-- Capture, do not immediately mutate:-- 1) Replication Monitor agent state + last error/action.-- 2) Publisher sp_replcounters output.-- 3) Distributor pending commands / oldest backlog age.-- 4) Distribution DB data/log free space and growth events.-- 5) Subscriber blocking, I/O, constraint/schema errors.-- 6) Agent job history and credential/TLS failures.-- 7) Last known tracer-token latency (transactional replication).5. Production judgment
Production judgment. Define thresholds for backlog age and transaction count, not merely job-state alerts. Protect the distribution database and snapshot folder as production assets. Review cleanup retention so a slow Subscriber does not outlive retained commands. Maintain a documented reinitialization window and verify downstream row counts/business invariants afterward.
Check your understanding
- What is the practical difference between push and pull subscriptions?
- Why can the Log Reader be healthy while a Subscriber is stale?
- What does a tracer token add to monitoring?
- Why can distribution cleanup settings cause an outage for a slow Subscriber?
- What should happen before reinitialization?
Review the answers
1. The Distribution/Merge Agent runs at the Distributor for push and at the Subscriber for pull, changing ownership, credentials and scheduling location.
2. The Log Reader may successfully move commands into the distribution database while the Distribution Agent is stopped or the Subscriber cannot apply them.
3. It measures a real marker through Publisher-to-Distributor-to-Subscriber rather than inferring freshness from agent state.
4. If commands expire before a Subscriber receives them, that Subscriber can no longer catch up from the retained distribution history and may need reinitialization.
5. Identify the failed leg and root cause, preserve evidence, estimate snapshot/reseed impact, schedule the change, and define verification/rollback.
Authoritative references
- Replication Monitor — agent state, pending commands and tracer tokens
- Monitor performance with Replication Monitor — tracer-token latency
- sp_replmonitorsubscriptionpendingcmds — transactional pending-command monitoring
- Distributor and Publisher information script — supported inventory procedures and catalog queries