Change Data Capture & Change Tracking

Change Data Capture (CDC)

CDC là gì?

Change Data Capture (CDC) là tính năng theo dõi và ghi lại INSERT, UPDATE, DELETE trên các tables được chỉ định. CDC đọc từ transaction log và lưu trữ cả giá trị trước (before) và sau (after) khi data thay đổi vào các change tables riêng.

Đặc điểm:

• Không ảnh hưởng đến hiệu năng của source table (asynchronous, đọc từ log)
• Yêu cầu SQL Server Agent để chạy capture và cleanup jobs
• Phù hợp cho ETL/data integration workloads
• Lưu trữ full before/after values

Bật CDC

Bật CDC ở cấp Database

-- Bước 1: Bật CDC cho database
USE YourDatabase;
EXEC sys.sp_cdc_enable_db;

-- Kiểm tra CDC đã bật chưa
SELECT name, is_cdc_enabled 
FROM sys.databases 
WHERE name = 'YourDatabase';

Bật CDC ở cấp Table

-- Bước 2: Bật CDC cho từng table
USE YourDatabase;
EXEC sys.sp_cdc_enable_table
    @source_schema      = N'dbo',
    @source_name        = N'Orders',
    @role_name          = NULL,          -- NULL = không giới hạn access
    @capture_instance   = N'dbo_Orders', -- Tên instance (tối đa 100 chars)
    @supports_net_changes = 1,           -- Cho phép fn_cdc_get_net_changes
    @captured_column_list = NULL;        -- NULL = capture tất cả columns
    -- @captured_column_list = N'OrderID, CustomerID, Amount' -- Chỉ capture một số columns

-- Tắt CDC cho một table
EXEC sys.sp_cdc_disable_table
    @source_schema    = N'dbo',
    @source_name      = N'Orders',
    @capture_instance = N'dbo_Orders';

-- Kiểm tra các tables đang được CDC theo dõi
SELECT 
    ct.capture_instance,
    OBJECT_NAME(ct.source_object_id) AS source_table,
    ct.supports_net_changes,
    ct.has_drop_pending,
    ct.index_name,
    ct.captured_column_list
FROM cdc.change_tables ct;

CDC Change Table Structure

Khi CDC bật cho dbo.Orders, SQL Server tạo change table: cdc.dbo_Orders_CT

-- Xem cấu trúc của change table
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'cdc' AND TABLE_NAME = 'dbo_Orders_CT'
ORDER BY ORDINAL_POSITION;

-- Các system columns được thêm vào change table:
-- __$start_lsn       : LSN của transaction gây ra change (kiểu binary(10))
-- __$end_lsn         : Luôn NULL (reserved)
-- __$seqval          : Sequence value trong transaction
-- __$operation       : 1=DELETE, 2=INSERT, 3=UPDATE before, 4=UPDATE after
-- __$update_mask     : Bitmask chỉ ra columns nào bị thay đổi
-- [source_columns]   : Các columns từ source table

LSN (Log Sequence Number)

LSN là giá trị duy nhất xác định vị trí trong transaction log. CDC dùng LSN để theo dõi khoảng thời gian cần query changes.

-- Lấy LSN min và max hiện tại cho một capture instance
DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn(N'dbo_Orders');
DECLARE @to_lsn   binary(10) = sys.fn_cdc_get_max_lsn();

SELECT 
    sys.fn_cdc_get_min_lsn(N'dbo_Orders') AS min_lsn,
    sys.fn_cdc_get_max_lsn()               AS current_max_lsn;

-- Convert LSN sang datetime và ngược lại
SELECT sys.fn_cdc_map_lsn_to_time(@from_lsn) AS from_time;
SELECT sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', 
    DATEADD(DAY, -1, GETDATE())) AS yesterday_lsn;

Consume CDC Changes

fn_cdc_get_all_changes — Lấy mọi thay đổi (kể cả trung gian)

DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn(N'dbo_Orders');
DECLARE @to_lsn   binary(10) = sys.fn_cdc_get_max_lsn();

-- Lấy tất cả changes (bao gồm UPDATE before & after)
SELECT 
    __$start_lsn,
    __$operation,
    CASE __$operation
        WHEN 1 THEN 'DELETE'
        WHEN 2 THEN 'INSERT'
        WHEN 3 THEN 'UPDATE (before)'
        WHEN 4 THEN 'UPDATE (after)'
    END AS operation_desc,
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS change_time,
    OrderID,
    CustomerID,
    Amount,
    Status
FROM cdc.fn_cdc_get_all_changes_dbo_Orders(
    @from_lsn, @to_lsn, N'all'  -- 'all' hoặc 'all update old'
)
ORDER BY __$start_lsn, __$seqval;

fn_cdc_get_net_changes — Chỉ lấy trạng thái cuối cùng

-- Net changes: nếu 1 row INSERT rồi UPDATE 3 lần → chỉ thấy 1 INSERT với giá trị cuối
-- Cần @supports_net_changes = 1 khi enable CDC
SELECT 
    __$start_lsn,
    __$operation,
    OrderID,
    CustomerID,
    Amount
FROM cdc.fn_cdc_get_net_changes_dbo_Orders(
    @from_lsn, @to_lsn, N'all'
)
ORDER BY __$start_lsn;

Incremental Load Pattern (ETL)

-- Lưu LSN đã xử lý vào control table
CREATE TABLE dbo.CDC_Checkpoint (
    CaptureInstance NVARCHAR(100) PRIMARY KEY,
    LastProcessedLSN BINARY(10) NOT NULL,
    LastRunTime DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

-- ETL logic
DECLARE @from_lsn binary(10), @to_lsn binary(10);

-- Lấy LSN từ lần chạy trước
SELECT @from_lsn = ISNULL(
    (SELECT sys.fn_cdc_increment_lsn(LastProcessedLSN) 
     FROM dbo.CDC_Checkpoint WHERE CaptureInstance = 'dbo_Orders'),
    sys.fn_cdc_get_min_lsn('dbo_Orders')  -- Lần đầu chạy
);

SET @to_lsn = sys.fn_cdc_get_max_lsn();

-- Xử lý changes
INSERT INTO dbo.Orders_Staging (OrderID, CustomerID, Amount, ChangeType, ChangeTime)
SELECT 
    OrderID,
    CustomerID,
    Amount,
    CASE __$operation WHEN 1 THEN 'D' WHEN 2 THEN 'I' WHEN 4 THEN 'U' END,
    sys.fn_cdc_map_lsn_to_time(__$start_lsn)
FROM cdc.fn_cdc_get_net_changes_dbo_Orders(@from_lsn, @to_lsn, N'all')
WHERE __$operation IN (1, 2, 4);  -- DELETE, INSERT, UPDATE after

-- Cập nhật checkpoint
MERGE dbo.CDC_Checkpoint AS target
USING (SELECT 'dbo_Orders' AS CaptureInstance, @to_lsn AS LSN) AS src
ON target.CaptureInstance = src.CaptureInstance
WHEN MATCHED THEN UPDATE SET LastProcessedLSN = src.LSN, LastRunTime = SYSUTCDATETIME()
WHEN NOT MATCHED THEN INSERT (CaptureInstance, LastProcessedLSN) VALUES (src.CaptureInstance, src.LSN);

CDC Cleanup và Capture Jobs

CDC tự động tạo 2 SQL Agent Jobs:

1. cdc.YourDatabase_capture — đọc log và ghi vào change tables
2. cdc.YourDatabase_cleanup — xóa change records cũ hơn retention period

-- Xem CDC jobs
SELECT name, enabled, description
FROM msdb.dbo.sysjobs
WHERE name LIKE 'cdc.%'
ORDER BY name;

-- Cấu hình retention period (giây) — default 4320 phút = 3 ngày
EXEC sys.sp_cdc_change_job
    @job_type     = N'cleanup',
    @retention    = 10080,  -- 7 ngày = 7 * 24 * 60
    @threshold    = 5000;   -- Max rows deleted per cleanup cycle

-- Xem cấu hình hiện tại của CDC jobs
EXEC sys.sp_cdc_help_jobs;

-- Manual cleanup nếu cần
EXEC sys.sp_cdc_cleanup_change_table
    @capture_instance = N'dbo_Orders',
    @low_water_mark   = NULL,  -- NULL = dùng retention period
    @threshold        = 5000;

CDC Limitations

• Yêu cầu SQL Server Agent (không hoạt động trên Express Edition)
• Data types không hỗ trợ: không cho phép capture LOB columns nếu row vượt 8060 bytes? (thực ra LOB được hỗ trợ từ SQL 2012+)
• Columns có kiểu timestamp/rowversion không được capture
• Không hoạt động với In-Memory OLTP tables
• DDL changes: nếu thay đổi schema table, cần disable rồi re-enable CDC
• Không hỗ trợ database tham gia replication trong một số kịch bản
• Performance overhead: scan log liên tục, ghi vào change tables

Change Tracking

Change Tracking là gì?

Change Tracking (CT) là giải pháp nhẹ hơn CDC: chỉ tracking rows nào đã thay đổi (INSERT/UPDATE/DELETE) nhưng KHÔNG lưu before/after values. Phù hợp cho data synchronization scenarios.

Đặc điểm:

• Không cần SQL Agent
• Overhead thấp hơn CDC
• Automatic cleanup dựa trên retention period
• Cần query source table để lấy current values

Bật Change Tracking

-- Bước 1: Bật Change Tracking ở cấp Database
ALTER DATABASE YourDatabase
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS,  -- Giữ history 7 ngày
 AUTO_CLEANUP = ON);          -- Tự động dọn history cũ

-- Bước 2: Bật Change Tracking cho từng table
ALTER TABLE dbo.Products
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);  -- Track columns nào bị update

-- Tắt Change Tracking cho table
ALTER TABLE dbo.Products DISABLE CHANGE_TRACKING;

-- Tắt Change Tracking cho database (phải tắt tất cả tables trước)
ALTER DATABASE YourDatabase SET CHANGE_TRACKING = OFF;

-- Kiểm tra CT status
SELECT 
    name,
    is_change_tracking_on,
    is_track_columns_updated_on
FROM sys.change_tracking_tables ct
JOIN sys.tables t ON ct.object_id = t.object_id;

Query Changes với CHANGETABLE

-- Lấy current version
DECLARE @current_version BIGINT = CHANGE_TRACKING_CURRENT_VERSION();
DECLARE @sync_version BIGINT = 0;  -- Lấy từ lần sync trước

-- Lấy tất cả changes kể từ @sync_version
SELECT 
    ct.ProductID,
    ct.SYS_CHANGE_OPERATION,  -- 'I' = Insert, 'U' = Update, 'D' = Delete
    ct.SYS_CHANGE_VERSION,
    ct.SYS_CHANGE_CREATION_VERSION,
    -- Join với source table để lấy current values (chỉ cho I và U)
    p.ProductName,
    p.Price,
    p.CategoryID
FROM CHANGETABLE(CHANGES dbo.Products, @sync_version) AS ct
LEFT JOIN dbo.Products p ON ct.ProductID = p.ProductID
ORDER BY ct.SYS_CHANGE_VERSION;

-- Lấy version hiện tại của một row cụ thể
SELECT * FROM CHANGETABLE(VERSION dbo.Products, (ProductID), (42)) AS ct;

Sync Pattern với Change Tracking

-- Synchronization pattern
DECLARE @last_sync_version BIGINT;

-- 1. Lấy last sync version từ client/control table
SELECT @last_sync_version = LastVersion 
FROM dbo.CT_SyncCheckpoint 
WHERE ClientID = 'ClientA';

-- 2. Kiểm tra version đủ cũ chưa (không bị cleanup)
IF @last_sync_version < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.Products'))
BEGIN
    -- Phải full sync lại vì history đã bị cleanup
    RAISERROR('Sync version too old, full sync required', 16, 1);
    RETURN;
END

-- 3. Lấy changes và current values
SELECT 
    ct.ProductID,
    ct.SYS_CHANGE_OPERATION,
    p.ProductName,
    p.Price
FROM CHANGETABLE(CHANGES dbo.Products, @last_sync_version) ct
LEFT JOIN dbo.Products p ON ct.ProductID = p.ProductID;

-- 4. Cập nhật sync version
UPDATE dbo.CT_SyncCheckpoint 
SET LastVersion = CHANGE_TRACKING_CURRENT_VERSION(), 
    LastSync = SYSUTCDATETIME()
WHERE ClientID = 'ClientA';

TRACK_COLUMNS_UPDATED

-- Nếu TRACK_COLUMNS_UPDATED = ON, có thể biết column nào bị update
SELECT 
    ct.ProductID,
    ct.SYS_CHANGE_OPERATION,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK(
        COLUMNPROPERTY(OBJECT_ID('dbo.Products'), 'Price', 'ColumnId'),
        ct.SYS_CHANGE_COLUMNS
    ) AS price_changed,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK(
        COLUMNPROPERTY(OBJECT_ID('dbo.Products'), 'ProductName', 'ColumnId'),
        ct.SYS_CHANGE_COLUMNS
    ) AS name_changed
FROM CHANGETABLE(CHANGES dbo.Products, @sync_version) ct
WHERE ct.SYS_CHANGE_OPERATION = 'U';

So sánh CDC vs Change Tracking

Temporal Tables (So sánh với CDC)

System-Versioned Temporal Tables

Temporal Tables (SQL Server 2016+, SQL Standard 2011) tự động lưu toàn bộ lịch sử thay đổi trong một history table riêng.

-- Tạo Temporal Table từ đầu
CREATE TABLE dbo.Employees (
    EmployeeID   INT NOT NULL PRIMARY KEY,
    Name         NVARCHAR(100) NOT NULL,
    Department   NVARCHAR(50),
    Salary       DECIMAL(15,2),
    -- Period columns (auto-managed by SQL Server)
    ValidFrom    DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo      DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.Employees_History,
        DATA_CONSISTENCY_CHECK = ON
    )
);

Query Temporal History

-- Xem data tại một thời điểm cụ thể
SELECT * FROM dbo.Employees
FOR SYSTEM_TIME AS OF '2024-01-01 12:00:00';

-- Xem tất cả changes trong khoảng thời gian
SELECT * FROM dbo.Employees
FOR SYSTEM_TIME FROM '2024-01-01' TO '2024-12-31';

CDC vs Temporal Tables

Q&A theo Cấp Độ

Junior Level

Q: CDC và Change Tracking khác nhau thế nào?

A: CDC lưu đầy đủ before/after values của mọi change và yêu cầu SQL Agent — phù hợp ETL. Change Tracking chỉ ghi nhận rows nào thay đổi (không có before/after values), không cần SQL Agent, overhead nhỏ hơn — phù hợp data sync giữa systems.

Q: Làm sao enable CDC cho một table?

A:

-- Bước 1: Enable ở database level
EXEC sys.sp_cdc_enable_db;

-- Bước 2: Enable cho table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'YourTable',
    @role_name = NULL,
    @supports_net_changes = 1;

Q: LSN là gì trong CDC context?

A: LSN (Log Sequence Number) là số thứ tự duy nhất trong transaction log, xác định vị trí chính xác của mỗi log record. CDC dùng LSN làm “bookmark” để track đến đâu đã process, tránh process duplicate hay bỏ sót changes.

Mid Level

Q: Giải thích sự khác biệt giữa fn_cdc_get_all_changes và fn_cdc_get_net_changes?

A:

• get_all_changes: Trả về mọi operation riêng lẻ — nếu row UPDATE 5 lần sẽ thấy 10 records (5 before + 5 after). Dùng khi cần toàn bộ audit trail.
• get_net_changes: Chỉ trả về trạng thái cuối cùng — nếu INSERT rồi UPDATE 5 lần sẽ thấy 1 INSERT với giá trị cuối. Yêu cầu @supports_net_changes = 1 khi enable. Dùng cho ETL batch loads.

Q: CDC overlap với log replication/Always On thế nào?

A: CDC đọc từ transaction log. Trên Always On secondary replicas chạy ở readable mode, CDC vẫn hoạt động bình thường trên primary. Log được ship sang secondary và CDC capture job chạy trên primary. Không nên chạy CDC capture trực tiếp từ secondary. Nếu failover xảy ra, new primary cần re-enable CDC jobs.

Senior Level

Q: Làm sao thiết kế một data integration pipeline dùng CDC để đảm bảo exactly-once semantics?

A: Exactly-once với CDC cần:

1. Idempotent target operations: Dùng MERGE thay vì INSERT để handle duplicates. Nếu pipeline restart và replay từ cùng LSN, kết quả phải giống nhau.
2. Transactional checkpoint: Lưu @to_lsn vào cùng transaction với data write vào target. Không dùng separate checkpoint store.
3. Dead letter queue: Với rows không thể process, ghi vào DLQ thay vì skip hay crash.
4. Version tracking: Trong target table, lưu source_lsn và source_seqval — kiểm tra trước khi apply change.

-- Idempotent merge với LSN tracking
MERGE dbo.Orders_DW AS target
USING (
    SELECT OrderID, CustomerID, Amount, __$start_lsn, __$seqval, __$operation
    FROM cdc.fn_cdc_get_all_changes_dbo_Orders(@from_lsn, @to_lsn, N'all update old')
    WHERE __$operation IN (2, 4)  -- INSERT, UPDATE after
) AS src ON target.OrderID = src.OrderID
    AND target.SourceLSN >= src.__$start_lsn  -- Đừng overwrite newer changes
WHEN MATCHED AND target.SourceLSN < src.__$start_lsn THEN 
    UPDATE SET CustomerID = src.CustomerID, Amount = src.Amount, SourceLSN = src.__$start_lsn
WHEN NOT MATCHED THEN 
    INSERT (OrderID, CustomerID, Amount, SourceLSN)
    VALUES (src.OrderID, src.CustomerID, src.Amount, src.__$start_lsn);

Q: Khi nào nên dùng Temporal Tables thay vì CDC? Liệu có thể dùng cả hai không?

A: Dùng Temporal Tables khi: Cần point-in-time queries trong application code (AS OF), audit trail đơn giản, không cần integration với external systems, muốn zero maintenance.

Dùng CDC khi: Cần feed data sang data warehouse/data lake, cần downstream systems nhận changes realtime/near-realtime, cần before values của deletes, complex ETL transformations.

Dùng cả hai: Hoàn toàn hợp lệ — Temporal Table cho audit/compliance, CDC cho ETL pipeline. Temporal history table có thể cũng được CDC capture (không phổ biến). Một pattern hay là: Temporal Table trên OLTP source → CDC feed từ source table → DW staging. Temporal table phục vụ point-in-time reporting trực tiếp từ OLTP khi cần.