Article
MS SQL Server CDC: transaction log versus change tracking
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
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
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
- Change Tracking agent
- Free trial if you want to validate against your SQL Server edition—or message me; I am around.