什麼是異動資料擷取 (CDC)？

文章
05/03/2025

本文說明異動資料擷取 (CDC)，此功能會在修改資料表和資料列時記錄資料庫上的活動。

本文說明 CDC 如何與 SQL Server 和 Azure SQL 受控執行個體搭配運作。至於 Azure SQL 資料庫，請參閱Azure SQL 資料庫的 CDC。

概觀

異動資料擷取會利用 SQL Server Agent 來記錄資料表中發生的插入、更新和刪除。因此，它可讓您使用關係型格式輕鬆地取用這些資料變更。因此，它可讓您使用關聯式格式輕鬆地存取這些資料變更。為了將變更資料套用至目標環境，系統會擷取修改後資料列中所需的欄位資料和基本中繼資料，然後將這些變更資料儲存在變更資料表中。這些資料表會依據被追蹤的來源資料表的欄位結構進行鏡像。此外，使用者可以透過表格值函數系統地存取這些變更資料。

此技術之目標資料取用者的理想範例為擷取、轉換和載入 (ETL) 應用程式。 ETL 應用程式以累加方式，將變更資料從 SQL Server 來源資料表載入資料倉儲或資料超市。雖然在資料倉儲內的來源資料表表示法必須反映來源資料表中的變更，但是重新整理來源複本的端對端技術並不適用。相反地，您需要一個可靠且有結構的變更數據流，以便消費者能夠將其應用到不相同的數據目標形式。 SQL Server 異動資料擷取提供這項技術。

資料流程

下圖顯示異動資料擷取的主要資料流程。

異動資料擷取的變更資料來源是 SQL Server 交易記錄。當插入、更新和刪除作業套用至追蹤來源資料表時，描述這些變更的項目就會加入記錄。此記錄會當做擷取程序的輸入。然後，它會讀取記錄並將變更之相關資訊新增至追蹤資料表的相關聯變更資料表。系統會提供一些函數，以便列舉指定之範圍內出現在變更資料表中的變更，並以篩選結果集的形式傳回此資訊。應用程式處理序通常會使用篩選結果集合，在某些外部環境中更新來源的表現形式。

擷取執行個體

您必須先針對資料庫明確啟用異動資料擷取，然後才能追蹤該資料庫內部任何個別資料表的變更。這項作業是使用 sys.sp_cdc_enable_db預存程序完成的。啟用資料庫之後，您就可以使用 sys.sp_cdc_enable_table預存程序，將來源資料表識別為追蹤資料表。當某個資料表啟用異動資料擷取時，系統就會建立相關聯的擷取執行個體，以便支援來源資料表中變更資料的散播。此擷取執行個體包含一個變更資料表以及最多兩個查詢函數。描述擷取執行個體之組態詳細資料的中繼資料會包含在變更資料擷取中繼資料資料表 cdc.change_tables、 cdc.index_columns和 cdc.captured_columns中。您可以使用 sys.sp_cdc_help_change_data_capture預存程序來擷取這項資訊。

與捕獲實例相關的所有物件都會在已啟用資料庫的變更數據捕獲結構中創建。擷取執行個體名稱的需求包括其必須是有效的物件名稱，而且其在資料庫擷取執行個體中必須是唯一的。預設名稱為來源資料表的<結構描述名稱_資料表名稱>。其相關聯變更資料表的命名方式是將 _CT 附加至擷取執行個體名稱。用來查詢所有變更之函數的命名方式是在擷取執行個體名稱前面加上 fn_cdc_get_all_changes_ 。如果擷取執行個體設定為支援網路變更，系統也會建立 net_changes 查詢函數，而且其命名方式是在擷取執行個體名稱前面加上 fn_cdc_get_net_changes。

重要

可同時與單一來源資料表相關聯的擷取執行個體數目上限是二。

變更資料表

異動資料擷取變更表格的前五個欄位是中繼資料欄。這些資料會提供與已記錄之變動相關的額外資訊。其餘資料行則會鏡像來源資料表中識別之擷取資料行的名稱，通常也會鏡像其類型。這些資料行保存的是從來源資料表中蒐集並擷取的資料。

套用到來源資料表的每個插入或刪除操作會顯示成變更表格中的單一資料列。插入作業所產生之資料列的欄位包含了插入後的欄位值。刪除作業所產生之資料列的資料行包含刪除之前的資料行值。更新作業需要一個資料列項目來識別更新之前的資料行值，和第二個資料列項目來識別更新之後的資料行值。

變更資料表中的每個資料列也會包含其他中繼資料，以便允許解譯變更活動。欄位 __$start_lsn 識別被指派給該變更的提交日誌序號 (LSN)。提交 LSN 用於標識相同交易中提交的變更，並為這些交易排序。資料行 __$seqval 可用於排序在相同交易中發生的其他變更。資料行 __$operation 會記錄與變更相關聯的作業：1 = 刪除、2 = 插入、3 = 更新 (建立資料影像前)，以及 4 = 更新 (建立資料影像後)。資料行 __$update_mask 是變數位元遮罩，其中每個擷取資料行都有一個定義的位元。若是插入和刪除項目，更新遮罩會設定所有位元。不過，更新資料列將僅會設定對應到已變更之資料行的位元。

有效間隔

資料庫的異動資料擷取有效性間隔就是擷取執行個體可以使用變更資料的時間範圍。有效性間隔始於您為資料庫資料表建立第一個擷取執行個體時，並持續至今。

資料庫

如果您沒有定期且有系統地清除儲放在變更資料表中的資料，這些資料將會難以管理地成長。異動資料擷取清除處理序會負責強制執行保留性清除原則。首先，它會移動有效性間隔的低端點，以便滿足時間限制。然後，它會移除過期的變更資料表項目。根據預設，系統會保留三天內的資料。

對於高層次而言，當擷取程序認可每一批新的變更資料時，新記錄會加入到具有變更資料表記錄的每個交易的 cdc.lsn_time_mapping 中。在對應資料表中，會保留提交記錄序號 (LSN) 和交易提交時間（分別為 start_lsn 和 tran_end_time 資料行）。在 cdc.lsn_time_mapping 中找到的最大 LSN 值代表資料庫有效性期間的上限標準。其對應的提交時間用作基於保留的清理計算新下限標記的基礎。

由於擷取程序會從交易記錄中擷取變更資料，因此來源資料表認可變更的時間與變更顯示在其相關聯變更資料表中的時間之間具有內建的延遲。儘管這個延遲通常很小，但仍然要記得，在擷取過程處理相關日誌條目之前，變更資料是無法取得的。

擷取實例

雖然資料庫有效性間隔與個別擷取執行個體的有效性間隔通常會一致，但是並非永遠如此。當擷取處理序辨識擷取執行個體並且開始將相關聯的變更記錄至其變更資料表時，擷取執行個體的有效性間隔就會開始。因此，如果您在不同的時間建立擷取執行個體，每個擷取執行個體都會有不同的低端點。 sys.sp_cdc_help_change_data_capture 所傳回之結果集的 start_lsn 資料行會顯示每個已定義捕获实例的當前低端點。當清理程序清除變更資料表項目時，它會調整所有擷取執行個體的 start_lsn 值，以反映新的可用變更資料的低水位標記。只有 start_lsn 值目前小於新下限標準的這些擷取執行個體才會進行調整。經過一段時間後，如果沒有建立任何新的擷取執行個體，所有個別執行個體的有效性間隔通常會與資料庫有效性間隔一致。

變更資料的有效性區間對於使用者來說非常重要，因為對於擷取要求，其擷取區間必須完全被擷取實例的當前異動資料擷取有效性區間所涵蓋。如果擷取間隔的低端點位於有效性間隔低端點的左邊，可能會由於積極的清除而遺失變更資料。如果擷取間隔的高端點位於有效性間隔高端點的右邊，表示擷取處理序尚未完全處理擷取間隔所代表的時間，而且也可能會遺失變更資料。

sys.fn_cdc_get_min_lsn 函數是用來擷取擷取執行個體的目前最小 LSN，而 sys.fn_cdc_get_max_lsn 則是用來擷取目前最大 LSN 值。查詢變更資料時，如果指定的 LSN 範圍並未落在這兩個 LSN 值之內，異動資料擷取查詢函數將會失敗。

處理來源資料表的變更

對下游使用者來說，因應正在被追蹤的來源資料表的資料行變更是一個棘手的問題。雖然針對來源資料表啟用異動資料擷取無法避免這類 DDL 變更發生，但是異動資料擷取可藉由保留透過 API 傳回的已傳遞結果集來減輕對取用者的影響，即使基礎來源資料表的資料行結構變更也一樣。這個固定的資料行結構也會反映在已定義之查詢函數所存取的基礎變更資料表中。

負責填入變更資料表的擷取處理序會忽略來源資料表啟用異動資料擷取時未識別為擷取的任何新資料行，以容納固定資料行結構的變更資料表。如果刪除某個追蹤資料行，在後續的變更項目中將會為該資料行提供空值。不過，如果現有的資料行進行資料類型的變更，此變更會被傳播至變更資料表，以確保擷取機制不會導致追蹤資料行的資料遺失。此外，擷取處理序也會將偵測到的所有變更記錄到追蹤資料表的資料行結構中，並寫入到 cdc.ddl_history 資料表。想要收到可能必須在下游應用程式中完成之調整警示的消費者，會使用 sys.sp_cdc_get_ddl_history 預存程序。

一般而言，當 DDL 變更套用至相關聯的來源資料表時，目前擷取執行個體將繼續保留其外觀。然而，可以為資料表建立一個反映新資料行結構的第二個擷取執行個體。此選項允許擷取過程對相同的來源資料表進行變更，並將其轉錄到具有兩種不同列結構的兩個不同變更資料表中。因此，當某個變更資料表可以繼續滿足目前運作中的程式時，第二個變更資料表可以驅動嘗試併入新資料行資料的開發環境。允許擷取機制一前一後擴展這兩個變更資料表是表示，系統可以完成這兩個資料表之間的轉換，而不會遺失變更資料。當兩個異動資料擷取時間表重疊時，就可能會發生這種情況。當過渡受到影響時，可以移除已廢棄的擷取執行個體。

重要

可同時與單一來源資料表相關聯的擷取執行個體數目上限是二。

與記錄讀取器代理程式的關係

異動資料擷取處理序的邏輯內嵌在預存程序 sp_replcmds中，此預存程序是建立成 sqlservr.exe 一部分的內部伺服器函數，而且也會由異動複寫用來從交易記錄中收集變更。在 SQL Server 和 Azure SQL 受控執行個體中，當您針對某個資料庫單獨啟用異動資料擷取時，就會將異動資料擷取 SQL Server Agent 擷取作業建立成叫用 sp_replcmds 的工具。當複寫也存在時，僅使用交易日誌讀取器來滿足這兩個使用者的變更資料需求。當您針對相同的資料庫同時啟用複寫和異動資料擷取時，這項策略可大幅減少記錄競爭的情況。

每當啟用異動資料擷取之資料庫的複寫狀態變更時，就會自動在這兩種擷取變更資料的作業模式之間切換。

備註

在 SQL Server 和 Azure SQL 受控執行個體中，擷取邏輯的兩個執行個體都需要 SQL Server Agent 處於執行狀態，才能執行程序。

擷取過程的主要任務是掃描日誌，並將欄資料和交易相關資訊寫入異動資料擷取的變更資料表。為了確保其填入的所有變更資料擷取變更資料表保持交易一致性邊界，擷取程序會在每個掃描循環中開啟並提交自己的交易。它會偵測出資料表最近啟用異動資料擷取的時間，並且自動將它們加入目前正在記錄中監視變更項目的資料表集合。同樣地，關閉變更數據擷取也會被偵測到，導致源數據表從正在監控變化數據的表集合中移除。當某個記錄區段的處理完成時，擷取處理序就會發出伺服器記錄截斷邏輯的信號，而此邏輯會使用這項資訊來識別適合用於截斷的記錄項目。

重要

啟用異動資料擷取的資料庫時，即使復原模式設定為簡單復原，在擷取處理序蒐集到所有標示為待擷取的變更之前，記錄截斷點將不會前進。如果擷取處理序並未執行，而且存在要蒐集的變更，則執行 CHECKPOINT 將不會截斷記錄。

擷取流程也用於維護追蹤資料表的 DDL 變更的歷史記錄。每當卸除啟用異動資料擷取的資料庫或資料表，或是加入、修改或卸除啟用異動資料擷取之資料表的資料行時，與異動資料擷取相關聯的 DDL 陳述式就會在資料庫交易記錄中建立項目。擷取處理序會先處理這些記錄項目，然後再將相關聯的 DDL 事件公佈至 cdc.ddl_history 資料表。您可以使用 sys.sp_cdc_get_ddl_history預存程序來取得有關影響追蹤資料表之 DDL 事件的相關資訊。

警告

MaxCmdsInTran 的設計不是為了要永遠開啟。其存在的目的是為了解決有人不小心在單一交易中執行大量 DML 作業的情況，這會導致在整個交易進入散發資料庫之前，命令的散發延遲、鎖持續存在等問題。如果您經常地遇到這個狀況，請檢閱您的應用程式邏輯，並找出減少交易大小的方法。
如果指定的發行資料庫同時啟用 CDC 和複寫，則不支援 MaxCmdsInTran。在此設定中使用 MaxCmdsInTran 可能會導致 CDC 變更資料表中的資料遺失。如果在復寫大型交易時新增和移除 MaxCmdsInTran 參數，也可能會造成 PK 錯誤。

代理作業

通常有兩個 SQL Server Agent 作業與啟用變更資料擷取的資料庫相關聯：一個用於填充資料庫變更資料表的作業，以及一個負責變更資料表清理的作業。這兩個作業都包含執行 Transact-SQL 命令的單一步驟。所呼叫的 Transact-SQL 命令是定義為變更資料擷取的預存程序，它實作了作業的邏輯。當資料庫的第一個資料表啟用異動資料擷取時，系統就會建立這些作業。系統一定會建立清除作業。只有在資料庫沒有任何已定義的交易式發行集的情況下，才會建立擷取作業。當您針對資料庫同時啟用變更資料擷取和交易式複寫時，系統也會建立擷取作業，並移除交易記錄讀取器作業，因為資料庫不再有已定義的發行項目。

擷取和清除作業都是使用預設參數建立的。擷取作業會立即啟動。它會連續執行，而且在每個掃描循環中最多可以處理 1000 筆交易 (循環之間等候 5 秒)。清除作業會在每天上午 2 點執行，它會保留變更資料表項目長達 4320 分鐘或 3 天，而且單一刪除陳述式最多可以移除 5000 個項目。

當您針對資料庫停用異動資料擷取時，系統就會移除異動資料擷取代理程式作業。在啟用變更資料擷取和異動複寫的條件下，當第一個發行集加入至資料庫時，也可以選擇移除擷取作業。

就內部而言，異動資料擷取代理程式作業是分別使用 sys.sp_cdc_add_job 和 sys.sp_cdc_drop_job預存程序建立和卸除的。系統也會公開這些預存程序，讓管理員能夠控制這些作業的建立和移除。

管理員對於異動資料擷取代理程式作業的預設組態沒有明確的控制權。 sys.sp_cdc_change_job 預存程序的提供目的是允許修改預設組態參數。此外， sys.sp_cdc_help_jobs 預存程序則是允許檢視目前的組態參數。擷取作業和清除作業都會在啟動時從 msdb.dbo.cdc_jobs 資料表中擷取組態參數。使用 sys.sp_cdc_change_job 針對這些值所做的任何變更，要等到此作業停止並重新啟動時才會生效。

系統提供了另外兩個預存程序，讓您能夠啟動和停止異動資料擷取代理程式作業：sys.sp_cdc_start_job 和 sys.sp_cdc_stop_job。

備註

啟動和停止擷取作業不會導致變更資料遺失。它只會阻止擷取處理程序主動掃描記錄，以搜尋並存放於變更資料表中的變更項目。避免記錄掃描在尖峰要求期間增加負載的合理策略是停止擷取作業，然後在要求降低時重新啟動它。

這兩個 SQL Server Agent 作業都設計成具備足夠的彈性而且可充分地進行設定，以便符合異動資料擷取環境的基本需求。不過，在這兩種情況下，系統已經公開提供核心功能的基礎預存程序，方便您進一步自訂。

以 NETWORK SERVICE 帳戶身分執行 Database Engine 服務或 SQL Server Agent 服務時，異動資料擷取無法正確運作。這樣可能會造成錯誤 22832。

與其他功能的互通性

使用其他 SQL Server 功能時，異動資料擷取有一些限制。參閲互通性以深入了解。

已知問題

如需與異動資料擷取相關的已知問題和錯誤，請參閲 CDC 的已知問題。

分享方式：