Design an Integration Boundary for CDC, Messaging, ETL, and Cross-System Consistency

Learning outcomes

The hardest integration failure is often not that a connector is down. It is that every component reports “healthy” while two systems disagree about business state. ServiceHub needs an explicit boundary for propagating work-order changes to analytics and downstream automation. This capstone lesson combines Change Tracking, CDC, Service Broker, ETL and the application outbox pattern into one design process centered on information contracts, failure recovery and reconciliation.

1. Start with the information contract

For this design, ServiceHubLab.ops.WorkOrder remains the authoritative operational state. The analytics sink is a derived representation. That single sentence determines how conflicts are resolved: if the warehouse differs from ServiceHub, reconciliation repairs the warehouse from the source contract rather than inventing two-way merge semantics.

Now define what the sink needs. If it only needs the latest current row for every changed key, CT can be sufficient. If it needs every captured insert/update/delete image for incremental ETL, CDC is a better source feed. If the source transaction must enqueue SQL Server-native asynchronous work atomically, Service Broker can fit. If an application needs to publish a domain event to an external broker, an application outbox can atomically store the business change and publishable event in the same database transaction, while a separate relay performs at-least-once delivery. Linked-server writes are a different, synchronous coupling model and should not be smuggled in as a shortcut.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab18') IS NULL  EXEC(N'CREATE SCHEMA lab18 AUTHORIZATION dbo;');GODROP TABLE IF EXISTS lab18.ConsumerCheckpoint;DROP TABLE IF EXISTS lab18.IntegrationOutbox;GOCREATE TABLE lab18.IntegrationOutbox(  event_id uniqueidentifier NOT NULL PRIMARY KEY,  aggregate_type varchar(40) NOT NULL,  aggregate_id bigint NOT NULL,  event_type varchar(80) NOT NULL,  schema_version smallint NOT NULL,  payload nvarchar(max) NOT NULL,  occurred_at datetime2(3) NOT NULL,  published_at datetime2(3) NULL,  attempt_count int NOT NULL DEFAULT 0,  last_error nvarchar(1000) NULL,  CONSTRAINT CK_IntegrationOutbox_payload_json CHECK (ISJSON(payload)=1));CREATE INDEX IX_IntegrationOutbox_pendingON lab18.IntegrationOutbox(published_at, occurred_at)INCLUDE(event_type, aggregate_id, attempt_count);CREATE TABLE lab18.ConsumerCheckpoint(  consumer_name sysname NOT NULL PRIMARY KEY,  source_kind varchar(20) NOT NULL,  checkpoint_value varbinary(64) NULL,  updated_at datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(),  status varchar(20) NOT NULL,  last_error nvarchar(1000) NULL);GO

The outbox uses a stable event_id as a deduplication key. schema_version makes payload evolution explicit. A relay is allowed to publish the same event more than once if it crashes after external publish but before setting published_at; therefore consumers must be idempotent or deduplicate by event ID. This is at-least-once delivery made safe, not a false exactly-once claim.

2. Make the business change and outbox record one local transaction

DECLARE @event_id uniqueidentifier = NEWID();DECLARE @now datetime2(3) = SYSUTCDATETIME();BEGIN TRY  BEGIN TRAN;  UPDATE ops.WorkOrder  SET status = 'onsite'  WHERE work_order_id = 1001;  INSERT lab18.IntegrationOutbox  (event_id,aggregate_type,aggregate_id,event_type,schema_version,payload,occurred_at)  VALUES  (@event_id,'WorkOrder',1001,'WorkOrderStatusChanged',1,   JSON_OBJECT('workOrderId':1001,'status':'onsite'),@now);  COMMIT;END TRYBEGIN CATCH  IF XACT_STATE() <> 0 ROLLBACK;  THROW;END CATCH;GOSELECT event_id, aggregate_id, event_type, schema_version,       occurred_at, published_at, attempt_countFROM lab18.IntegrationOutboxORDER BY occurred_at;GO

The local database transaction now guarantees that the operational update and the durable intent-to-publish appear together. It does not guarantee external delivery. The relay owns that second boundary, including backoff, deduplication, dead-letter/poison policy and observability.

3. Design ordering, replay, deduplication and schema evolution

Global ordering is expensive and often unnecessary. Define the scope: ServiceHub may require status events for the same work order to be applied in order, while events for unrelated work orders can be processed concurrently. A consumer can track the highest logical version per aggregate or use event sequence metadata. If ordering is derived from database LSNs or CT versions, document the precise scope and never reinterpret those engine positions as business clocks across databases.

Replay needs a starting point and a validity test. CT uses a client version and minimum-valid-version check. CDC uses low/high LSN endpoints and consumer checkpoints. Service Broker uses durable queues and conversation endpoints. An outbox uses event IDs plus relay state. Each mechanism has a retention boundary. If the consumer falls behind beyond that boundary, the correct behavior is a full snapshot/rebuild or another explicitly tested recovery path.

SELECT  COUNT(*) AS pending_events,  MIN(occurred_at) AS oldest_pending_at,  MAX(attempt_count) AS max_attempt_count,  DATEDIFF(second, MIN(occurred_at), SYSUTCDATETIME()) AS oldest_pending_age_secondsFROM lab18.IntegrationOutboxWHERE published_at IS NULL;SELECT TOP (20)  event_id, aggregate_id, event_type, schema_version,  occurred_at, attempt_count, last_errorFROM lab18.IntegrationOutboxWHERE published_at IS NULLORDER BY occurred_at;GO

A process heartbeat can stay green while oldest_pending_age_seconds grows for hours. Backlog age, checkpoint lag, retry counts and reconciliation discrepancies are stronger integration health signals than “the connector process is running.”

4. Reconciliation is the safety net for every asynchronous design

Even with careful checkpoints, integration systems need reconciliation. Compare source and target counts/hashes or business invariants over stable partitions, sample key states, verify high-water marks, and preserve mismatch evidence. Reconciliation should be able to classify a mismatch as “target behind,” “duplicate,” “schema rejected,” “source corrected,” or “unknown” rather than immediately overwriting data.

DROP TABLE IF EXISTS lab18.ReconciliationRun;GOCREATE TABLE lab18.ReconciliationRun(  run_id bigint IDENTITY PRIMARY KEY,  scope_name varchar(80) NOT NULL,  source_rows bigint NOT NULL,  target_rows bigint NOT NULL,  mismatch_rows bigint NOT NULL,  source_checkpoint nvarchar(100) NULL,  target_checkpoint nvarchar(100) NULL,  recorded_at datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME());INSERT lab18.ReconciliationRun(scope_name,source_rows,target_rows,mismatch_rows,source_checkpoint,target_checkpoint)VALUES ('work-orders-demo',4,4,0,N'ct-version:demo',N'sink-version:demo');SELECT * FROM lab18.ReconciliationRun ORDER BY run_id DESC;GO

The numbers are explicitly demonstration records, not measurements of a real external system. A production reconciler should obtain evidence from both sides, define a consistency cut, and store enough context to repeat or audit the decision.

5. Choose the mechanism from the contract

These are starting points, not a product scoreboard. A design can combine mechanisms—for example CDC as a source feed into an ETL platform, or an outbox relay that publishes to an external broker. Each additional component adds failure states. State ownership and recovery must remain explicit.

USE ServiceHubLab;GO-- Lab-only Service Broker cleanup. WITH CLEANUP is appropriate here only because-- these conversations belong to this disposable course namespace.DECLARE @lab_dialog uniqueidentifier;WHILE 1=1BEGIN  SET @lab_dialog = NULL;  SELECT TOP (1) @lab_dialog = conversation_handle  FROM sys.conversation_endpoints  WHERE far_service LIKE N'//ServiceHub/Lab18/%';  IF @lab_dialog IS NULL BREAK;  END CONVERSATION @lab_dialog WITH CLEANUP;END;IF EXISTS (SELECT 1 FROM sys.services WHERE name=N'//ServiceHub/Lab18/Initiator')  DROP SERVICE [//ServiceHub/Lab18/Initiator];IF EXISTS (SELECT 1 FROM sys.services WHERE name=N'//ServiceHub/Lab18/Target')  DROP SERVICE [//ServiceHub/Lab18/Target];IF OBJECT_ID(N'lab18.InitiatorQueue',N'SQ') IS NOT NULL DROP QUEUE lab18.InitiatorQueue;IF OBJECT_ID(N'lab18.TargetQueue',N'SQ') IS NOT NULL DROP QUEUE lab18.TargetQueue;IF EXISTS (SELECT 1 FROM sys.service_contracts WHERE name=N'//ServiceHub/Lab18/WorkContract')  DROP CONTRACT [//ServiceHub/Lab18/WorkContract];IF EXISTS (SELECT 1 FROM sys.service_message_types WHERE name=N'//ServiceHub/Lab18/WorkRequested')  DROP MESSAGE TYPE [//ServiceHub/Lab18/WorkRequested];GO-- Keep ops.WorkOrder and all course baseline objects.DROP TABLE IF EXISTS lab18.ReconciliationRun;DROP TABLE IF EXISTS lab18.ConsumerCheckpoint;DROP TABLE IF EXISTS lab18.IntegrationOutbox;DROP TABLE IF EXISTS lab18.RemoteLatencyEvidence;GO-- Optional cleanup only if Lesson 2 CDC objects were created.IF EXISTS (SELECT 1 FROM sys.tables WHERE object_id=OBJECT_ID(N'lab18.CdcWorkOrder') AND is_tracked_by_cdc=1)BEGIN  EXEC sys.sp_cdc_disable_table       @source_schema=N'lab18',       @source_name=N'CdcWorkOrder',       @capture_instance=N'lab18_CdcWorkOrder';END;DROP TABLE IF EXISTS lab18.CdcWorkOrder;GO-- Optional cleanup only if Lesson 1 Change Tracking table exists.IF OBJECT_ID(N'lab18.SyncItem',N'U') IS NOT NULLBEGIN  IF EXISTS (SELECT 1 FROM sys.change_tracking_tables WHERE object_id=OBJECT_ID(N'lab18.SyncItem'))    ALTER TABLE lab18.SyncItem DISABLE CHANGE_TRACKING;  DROP TABLE lab18.SyncItem;END;GO-- Do not disable database-wide CDC or Change Tracking here automatically:-- another lab/application could be using those database options.-- Review dependencies before changing database-wide integration settings.

Cleanup deliberately avoids disabling database-wide CDC or Change Tracking automatically. Database-wide feature state can be shared by multiple tables; a safe operator inventories dependencies before changing it. The same principle applies to Service Broker objects and linked servers: remove only what the disposable lab owns.

6. Production judgment and bridge

A robust integration boundary names the authoritative system, payload/change semantics, ordering scope, delivery guarantee, checkpoint, retention, replay, deduplication, poison handling, schema compatibility, backpressure policy, observability and reconciliation owner. “Connector running” is implementation status, not consistency evidence. Chapter 19 moves from data movement to analytical storage and execution, where columnstore rowgroups, segment elimination and batch mode change how SQL Server serves large analytical workloads.

Authoritative references

• Track data changes: Change Tracking and CDC
• What is Change Data Capture (CDC)?
• SQL Server Service Broker
• Linked Servers (Database Engine)
• SQL Server 2025 editions and supported features