Canonical: https://molo17.com/blog/ms-sql-server-cdc-transaction-log-vs-change-tracking/ Markdown mirror: https://molo17.com/blog/ms-sql-server-cdc-transaction-log-vs-change-tracking/index.md Title: MS SQL Server CDC: Transaction Log vs Change Tracking · MOLO17 Description: Compare MS SQL Server CDC Transaction Log vs Change Tracking, LSN positioning, SQL Agent architecture and build vs buy. [← All articles](/blog/) Article # MS SQL Server CDC: transaction log versus change tracking  28 Sep 2026 [Data architecture and performance](/blog/?category=data-architecture-and-performance) 3 min read How SQL Server Transaction Log CDC differs from Change Tracking, LSN-based replication vs CT keys, and enterprise vs AI-built connectors. SQL Server gives you two practical change-capture models that teams often conflate: **Transaction Log CDC** (change tables + capture jobs + LSN) and **Change Tracking** (keys + CHANGETABLE for latest state). Both are valid. They solve different problems. Gluesync supports both. This article separates the mechanisms, then compares building an AI-scaffolded connector against running an enterprise agent. ## Transaction Log CDC SQL Server CDC (the feature name) mines the transaction log via **SQL Agent capture/cleanup jobs**, writes row images into system **change tables**, and positions consumers with **LSN** (Log Sequence Number). What you own operationally: - SQL Agent jobs must stay healthy (capture lag, cleanup, disk) - Change tables grow until cleanup runs—TempDB and storage planning matter - LSN is your restart coordinate; gaps or truncated logs break catch-up - You get historical change rows (net change / all changes patterns), not only “current key changed” Docs: [MS SQL Server Change Data Capture](https://docs.molo17.com/gluesync/latest/agents/ms-sql-server-agent-change-data-capture-intro.html) ## Change Tracking Change Tracking is lighter. SQL Server records that a primary key changed; you query **CHANGETABLE** for keys (and optional context) since a version watermark. It does not store full before/after images like CDC change tables. What you own operationally: - Enable CT per database/table; retain change retention window carefully - Consumers fetch **latest state** from base tables using changed keys - No LSN stream of every intermediate update—you get “what changed since version N” - Lower overhead than full CDC for many sync patterns; weaker for full audit-style change history Docs: [MS SQL Server Change Tracking](https://docs.molo17.com/gluesync/latest/agents/ms-sql-server-agent-change-tracking-intro.html) ## Side-by-side Topic Transaction Log CDC Change Tracking Source of truth Transaction log → change tables Internal CT version store Positioning LSN Change version / sync version Payload Change table row images Keys (+ optional context); fetch current row SQL Agent Capture + cleanup jobs required No CDC capture jobs History of intermediate updates Yes (within retention) No—latest change indication Typical fit Audit-like streams, richer change payloads Incremental sync of current state Ops risk Job health, change table growth, TempDB Retention too short → missed keys ## Build vs buy Dimension AI-generated / custom Gluesync SQL Server agents CT vs CDC choice You implement both paths or guess wrong Both agents documented and supported LSN / version recovery Custom watermark store + edge cases Agent positioning for the chosen mode SQL Agent / CT retention Your runbooks Product docs + support Schema changes Ad-hoc Maintained agent behavior Ownership horizon Your team per SQL Server upgrade Shared product ownership ## Choosing CT or CDC - Need **ordered change history** or consumers that expect change-table style rows → **Transaction Log CDC** - Need **efficient “keys changed since last sync”** and will read current rows from source → **Change Tracking** - Mixed estates → use the agent that matches each database’s feature enablement; do not force one model everywhere Platform materials position Gluesync for low end-to-end latency (including sub-45ms class figures in product positioning). Actual lag still depends on SQL Agent capture health, network, and topology—positioning from product materials, not a measured guarantee for every deployment. ## FAQs ### Does Gluesync support both SQL Server models? Yes. Use the CDC agent when Transaction Log CDC is enabled; use the Change Tracking agent when CT is the enabled feature. See the two docs linked above. ### Is Change Tracking “CDC”? In marketing language people say “CDC” loosely. In SQL Server product terms, CDC is the log-based change-tables feature; Change Tracking is a separate lightweight mechanism. ### What breaks first in home-grown connectors? Usually watermark handling (LSN or CT version), SQL Agent failures ignored until lag explodes, or retention shorter than the consumer’s downtime. ### Can AI build this safely? It can scaffold T-SQL enablement scripts and a reader loop. Production still needs job monitoring, retention design, and recovery tests after Agent outages. ## Next steps - [Transaction Log CDC agent](https://docs.molo17.com/gluesync/latest/agents/ms-sql-server-agent-change-data-capture-intro.html) - [Change Tracking agent](https://docs.molo17.com/gluesync/latest/agents/ms-sql-server-agent-change-tracking-intro.html) - Free trial if you want to validate against your SQL Server edition—or message me; I am around. [← Older article Query Forge: federated SQL across agents, without a warehouse](/blog/query-forge-federated-sql-why-when-what-how/) ## Keep reading 1. [Data architecture and performance IBM i CDC: system journaling versus RPG triggers IBM i journaling vs RPG triggers: native journal retrieval, receiver retention, EBCDIC decoding, recovery and connector ownership. 18 Sep 2026](/blog/ibm-db2-as400-cdc-system-journaling-qsqjrn-rpg-triggers/) 2. [Data architecture and performance Oracle CDC: technical strategies and build vs buy Compare AI-generated Oracle CDC connectors with Gluesync across SCN recovery, LogMiner, XStream, monitoring and long-term production ownership. 8 Sep 2026](/blog/oracle-cdc-technical-strategies-build-vs-buy/) 3. [Data architecture and performance Batch ETL vs real-time data replication: how to choose Architectural guide comparing batch ETL vs log-based CDC. Learn how modern platforms combine real-time replication with federated querying via Query Forge. 29 Jul 2026](/blog/batch-etl-vs-real-time-data-replication/) [Back to all articles](/blog/) [More in Data architecture and performance →](/blog/?category=data-architecture-and-performance)