Choosing Between AGs, Replication, Log Shipping, ETL/CDC, and Application-Level Replication

Learning outcomes

The dangerous question is not “which SQL Server feature is best?” It is “what movement contract does this workload require?” A whole-database DR copy, a filtered reporting projection, an integration event stream and an offline multi-writer field database have incompatible consistency and failover semantics. This final lesson turns Chapters 16–17 into a repeatable technology-selection process.

1. Start with requirements, not product names

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.MovementRequirement(requirement_name,whole_database,subset_filtering,multiple_writers,automatic_failover,target_rpo_seconds,notes)VALUES('Local HA',1,0,0,1,5,N'Whole ServiceHub database; same-site automatic failover'),('Regional DR',1,0,0,0,60,N'Separate site; operator-controlled promotion'),('Reporting subset',0,1,0,0,30,N'Only closed work orders and customer aggregates'),('Field offline sync',0,1,1,0,3600,N'Technicians can update while disconnected'),('Warehouse feed',0,1,0,0,300,N'Transform and load into analytical model');SELECT * FROM lab17.MovementRequirement ORDER BY requirement_name;GO

Requirements should also record schema propagation, allowed staleness, whether the destination may be independently writable, conflict semantics, security boundary, data residency, expected change volume, maintenance windows and how clients find a promoted system.

2. Decision matrix

Change Data Capture (CDC) and Change Tracking are covered in Chapter 18, but they belong in the decision now because they solve different information contracts. CDC exposes change rows over an LSN window; ETL can transform them. Application-level outbox/event patterns can carry business meaning rather than physical row changes. Those are integration designs, not database failover designs.

3. Work through realistic choices

Case A — local ServiceHub HA. Requirement: one writable database, automatic failover inside a cluster domain, RPO near zero under qualified failures. Replication is rejected because it does not provide cluster authority/application failover. Log shipping is rejected because promotion is manual. An AG/FCI choice belongs to Chapter 16 and depends on database-vs-instance failover, storage and edition.

Case B — reporting only closed orders. Requirement: filtered subset, low but nonzero latency, reporting server should not become production primary. Transactional replication can fit because an article/filter can project selected data. An AG is rejected if the reporting target should contain only a subset or independently indexed/transformed schema.

Case C — regional DR. Requirement: full database in another site, restore-delay option desired, manual RTO acceptable. Log shipping may be deliberately better than a distributed AG because it is simpler and available in Standard. If local HA at both sites and lower cross-site recovery time justify Enterprise/topology complexity, a distributed AG may fit.

Case D — technicians update offline. Transactional replication is rejected because the Publisher-authoritative writer model does not solve disconnected bi-directional updates. Merge replication could fit if its conflict model is acceptable; an application synchronization service may be better when business conflicts need domain-specific reconciliation.

-- Example architecture-review rubric, not an optimizer:-- HARD EXCLUSIONS first:--   needs subset filtering? reject plain AG/log shipping.--   needs automatic DB failover? reject replication/log shipping/CDC alone.--   needs multiple independent writers? reject plain transactional replication/AG/log shipping.-- Then compare surviving options on:--   RPO/RTO, latency, operational skills, edition cost,--   schema-change process, observability, security, rollback and testability.

4. The wrong decision usually hides an unstated contract

Technology decision must record:- source of truth and allowed writers;- whole database vs object/row subset;- maximum measured staleness / RPO and recovery-time objective;- automatic vs operator-controlled promotion;- schema-change and filtering/transformation ownership;- security identities, TLS/credentials/key handling;- queue/log/restore/redo retention and capacity limits;- monitoring evidence and alert thresholds;- bootstrap/reinitialization/reseed procedure;- rollback/failback and independent backup/restore plan;- scheduled failure/data-reconciliation drills.

For SQL Server 2025, edition is part of the design: Express cannot publish replication or use built-in log shipping/AGs; Standard supports log shipping and Basic AG but not distributed AG; Enterprise enables advanced/distributed AG capabilities. Developer editions provide matching non-production feature sets for learning and testing. Verify the current licensing guide before production purchase decisions.

5. Cleanup and bridge to change feeds

USE ServiceHubLab;GODROP TABLE IF EXISTS lab17.DeliveryQueue;DROP TABLE IF EXISTS lab17.SiteState;DROP TABLE IF EXISTS lab17.MovementRequirement;GOIF SCHEMA_ID(N'lab17') IS NOT NULL   AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE schema_id=SCHEMA_ID(N'lab17'))   EXEC(N'DROP SCHEMA lab17;');GO

6. Production judgment

Production judgment. A good data-movement decision remains understandable during an incident. Operators should know which system is authoritative, what “caught up” means, which queue or LSN proves it, who can promote/reinitialize, and how to recover independently if the movement technology faithfully propagates a bad change. Chapter 18 now narrows from database copies to change contracts: Change Tracking, CDC, Service Broker and integration patterns.

Authoritative references

• SQL Server 2025 editions and supported features — edition boundaries for log shipping and AG features
• Replication publishing model — replication distribution contract
• About log shipping — warm-standby DR contract
• Distributed availability groups — multi-site AG-of-AG contract