Snapshot, Transactional, and Merge Replication Architecture and Appropriate Use Cases

Learning outcomes

Chapter 16 kept a service available by moving ownership between replicas. Chapter 17 starts with a different question: what if the business wants copies of selected data in other places? Reporting, disconnected field work, regional distribution and integration do not all need whole-database failover. SQL Server replication is a family of data distribution technologies. It can copy selected objects and rows, but it does not replace backups, point-in-time recovery, cluster quorum or an application failover plan.

1. Start with the publishing model

A Publisher exposes data. A publication groups one or more articles, such as tables or other supported objects. The Distributor stores replication metadata and, for transactional replication, queued commands in a distribution database. A Subscriber receives a publication through a subscription. These roles describe the data path; they do not tell you that a Subscriber is a recoverable replacement for the Publisher.

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;GO

EXEC sys.sp_get_distributor;GOEXEC sys.sp_helpreplicationdboption;GOSELECT name, is_published, is_merge_published, is_subscribed, is_distributorFROM sys.databasesWHERE is_published=1 OR is_merge_published=1 OR is_subscribed=1 OR is_distributor=1;GO

On an untouched ServiceHub learning instance, the usual result is no configured Distributor and no published databases. That is useful evidence: it prevents a learner from running topology-changing procedures under a false assumption. SQL Server 2025 Express cannot be a replication Publisher; use free Enterprise Developer or Standard Developer for a publishing lab. Express can participate only in supported subscriber scenarios, and it lacks SQL Server Agent, which affects normal agent scheduling.

2. Three replication families solve different problems

Snapshot replication is appropriate when data changes infrequently or full refresh cost is acceptable. Transactional replication is designed for incrementally propagating committed changes. The Log Reader Agent scans the publication database transaction log and writes replicated commands into the distribution database; Distribution Agents deliver them to Subscribers. Merge replication tracks changes at multiple nodes and uses the Merge Agent to exchange them, so it needs conflict rules and a much stronger application-level ownership model.

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);GO

INSERT lab17.DeliveryQueue(technology, source_commit_id)VALUES ('TRANSACTIONAL', 70001),('TRANSACTIONAL',70002),('SNAPSHOT',70003);WAITFOR DELAY '00:00:01';UPDATE lab17.DeliveryQueueSET delivered_at=SYSUTCDATETIME(), status='DELIVERED'WHERE queue_id=1;SELECT queue_id, technology, source_commit_id, status,       DATEDIFF_BIG(millisecond,created_at,COALESCE(delivered_at,SYSUTCDATETIME())) AS age_msFROM lab17.DeliveryQueueORDER BY queue_id;GO

The simulation is not SQL Server replication; it isolates the operational idea. A committed source transaction can exist while downstream delivery is still pending. Therefore a Subscriber read has a freshness contract, not an automatic “same data now” guarantee.

3. Initialization is a deployment event

All three major replication types commonly use a Snapshot Agent for initialization. Snapshot files contain schema/data needed by the publication and must be protected like database data. The Distribution Agent applies snapshots for snapshot and transactional subscriptions; the Merge Agent applies merge initialization and later synchronization. Push versus pull describes where the Distribution/Merge Agent runs: Distributor for push, Subscriber for pull. That affects credentials, scheduling, network direction and where failures are diagnosed.

SQL Server 2025 adds TDS 8.0/TLS 1.3 options to replication agents. Current Microsoft documentation also flags remote-Distributor upgrade/configuration issues in SQL Server 2025 scenarios, so remote distribution should be validated against the current CU/release notes before production change windows.

4. Production judgment

Use snapshot replication when periodic full distribution is acceptable; transactional replication when one authoritative write stream must be projected with low latency; merge only when offline/multi-writer synchronization and conflicts are explicit business requirements. Record who owns schema changes, agent credentials, snapshot storage, retention, reinitialization, conflict handling and monitoring. Chapter 17 Lesson 2 turns those architecture boxes into monitored publications, subscriptions and agent queues.

Authoritative references

• Replication publishing model overview — Publisher, Distributor, Subscriber, article, publication and subscription roles
• Snapshot replication — snapshot initialization and SQL Server 2025 TLS/TDS behavior
• Merge replication — multi-writer synchronization and conflict model
• Prepare SQL Server for replication — edition and security prerequisites