John Liu Blog

Every drop counts

SQL Server Change Data Capture (CDC)

CDC (Change Data Capture) was introduced in SQL2008 Enterprise only, only available to Standard edtions since SQL2016 SP1. It’s not available in Express edtion as Express edtion doesn’t have SQL Agent. CDC involves three-tier configuration: Server-level (agent job), Database-level and Table-level. It works in all recovery modes. When using CDC, we need to minotr log file growth and adjust retention period, especially if not using FULL recovery mode. CDC is like a video recording of every single change, where as Change Tracking (CT) is more like a “motion sensor”.

Posted by John Liu Thursday, December 25, 2025

SQL Server Change Tracking (CT)

Change Tracking (CT) introduced in SQL2008, all edtitions, includes Express. CT is more like a “motion sensor”. CT tells you which rows changed and what the latest values are, but id doesn’t keep a history of every intermediate step. CDC is like a video recording of every single change. CT is much more lightweight than CDC. CT can only be enabled on table with PK. It’s a sync operation (overhead on every transaction).

Posted by John Liu Thursday, December 25, 2025

FEATURED TAGS
ai api automation availability availability sets availability zones aws vm azure azure automation runbook azure blob azure cosmos db azure data lake azure function app azure openai azure sign-in azure site recovery azure sql database azure sql db azure subscription azure vm base64 certificate change data capture change tracking chrome clr container cte data api builder data conversion data gateway database role database size date table dax db config derived table diagram direct query disk management disk space docker downtime dtc dynamic m parameter embedding encrypted connection excel excel online execution plan extended events external data fabric fabric capacity failover cluster fk geometry hierarchy httpwebrequest hugo hyper-v incognito mode index infrastructure inline tvf json kql lakehouse linked server live query statistics locking m machine learning machine learning model machine learning services master key mcp mdx memory memory grant mermaid mirrored sql server network network card network category onedrive onnx runtime openrowset p2v parquet performance polybase power automate power bi power bi report tricks power platform power query powershell printer public ip address pyspark python qgis qt designer query performance query plan query troubleshooting r regex replication route s3 schema design scripting self-signed certificate server role sharepoint snowflake software development sofware development spark sql sql agent sql availability group sql error sql failover cluster instance sql index sql openjson sql permission sql recovery sql script sql security sql server sql server admin sql server config sql statistics ssis ssisdb ssl ssl/tls error ssms table expression tempdb tips troubleshooting unicode view visual studio visual studio code vmware wait statistics wi-fi connection issue windows settings