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

適用于:yesSQL Server (所有支援的版本) Yes Azure SQL Database Yes Azure SQL 受控執行個體

在本文中,瞭解 CDC) (異動資料擷取,這會在資料表和資料列遭到修改時記錄資料庫上的活動。 異動資料擷取在Azure SQL Database、SQL Server和Azure SQL 受控執行個體中正式推出。

概觀

異動資料擷取 (CDC) 會使用SQL Server代理程式來記錄適用于資料表的插入、更新和刪除活動。 這樣會以方便取用的關聯式格式提供變更的詳細資料。 系統會針對修改的資料列擷取資料行資訊以及將變更套用至目標環境所需的中繼資料,並且將它們儲存在鏡像追蹤來源資料表之資料行結構的變更資料表中。 此外,系統會提供資料表值函式,讓取用者以有系統的方式存取異動資料。

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

CDC & Azure SQL Database

在 Azure SQL Database 中,異動資料擷取排程器會取代 SQL Server 代理程式,此代理程式會叫用預存程式,來啟動異動資料擷取資料表的定期擷取和清除。 排程器會在SQL Database內自動執行擷取和清除,而不需要任何外部相依性來達到可靠性或效能。 使用者仍然可以選擇依需求手動執行擷取和清除。

若要瞭解如何擷取異動資料擷取,您也可以參考此資料公開的劇集。

效能考量

在Azure SQL Database上啟用異動資料擷取的效能影響類似于啟用 CDC 以進行SQL Server或Azure SQL 受控執行個體的效能影響。 以下是影響啟用 CDC 效能影響的一些層面:

  • 已啟用 CDC 的資料表數目
  • 追蹤資料表中的變更頻率
  • 源資料庫中可用的空間,因為 CDC 成品 (例如 CT 資料表、cdc_jobs等) 儲存在相同的資料庫中
  • 資料庫是單一或集區。 對於彈性集區中的資料庫,除了考慮已啟用 CDC 的資料表數目之外,請注意這些資料表所屬的資料庫數目。 集區中的資料庫會共用資源 (例如磁碟空間) ,因此在多個資料庫上啟用 CDC 會執行達到彈性集區磁片大小上限的風險。 監視 CPU、記憶體和記錄輸送量等資源。

若要為客戶提供更明確的效能優化指引,每個客戶的工作負載需要更多詳細資料。 不過,根據 TPCC 工作負載上執行的效能測試,以下是一些額外的一般指引:

  • 請考慮增加虛擬核心數目或轉移至較高的資料庫層 (例如超大規模資料庫) ,以確保與在Azure SQL Database上啟用 CDC 之前相同的效能等級。

  • 在生產環境中啟用資料庫上的 CDC 之前,請密切監視空間使用率,並徹底測試您的工作負載。

  • 監視記錄產生速率。 若要 在這裡深入瞭解。

  • 掃描/清除是使用者工作負載的一部分, (使用者的資源) 。 效能影響可能會很大,因為會將整個資料列新增至變更資料表,而且也會包含更新作業前置映射。

  • 彈性集區 - 啟用 CDC 的資料庫數目不應超過集區的虛擬核心數目,以避免延遲增加。 在這裡深入瞭解密集彈性集區中的資源管理。

  • 清除 – 根據客戶的工作負載,建議保留期間小於預設值 3 天,以確保清除會趕上變更資料表中的所有變更。 一般而言,最好保持低保留期並追蹤資料庫大小。

  • 當變更將填入變更資料表時,不提供服務等級協定 (SLA) 。 也不支援次秒延遲。

資料流程

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

Change data capture data flow

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

擷取執行個體

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

與擷取執行個體相關聯的所有物件都會建立在啟用資料庫的異動資料擷取結構描述中。 擷取執行個體名稱的需求包括,它必須是有效的物件名稱,而且它在資料庫擷取執行個體中必須是唯一的。 根據預設,名稱為 < 來源資料表的架構 name_table 名稱> 。 其相關聯變更資料表的命名方式是將 _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 傳回的傳遞結果集維持不變,即使基礎來源資料表的資料行結構變更也一樣。 這個固定的資料行結構也會反映在已定義之查詢函數所存取的基礎變更資料表中。

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

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

注意

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

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

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

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

注意

在SQL Server和Azure SQL 受控執行個體中,擷取邏輯的兩個實例都需要執行SQL Server Agent,才能執行進程。

擷取程序的主要工作是掃描記錄,並且將資料行資料和交易相關的資訊寫入異動資料擷取變更資料表。 為了確保它所擴展的所有異動資料擷取變更資料表在交易方面具有一致的界限,擷取處理序會針對每個掃描循環開啟並認可自己的交易。 它會偵測出資料表最近啟用異動資料擷取的時間,並且自動將它們加入目前正在記錄中監視變更項目的資料表集合。 同樣地,它也會偵測出停用異動資料擷取,進而從目前正在監視變更資料的資料表集合中移除來源資料表。 當某個記錄區段的處理完成時,擷取處理序就會發出伺服器記錄截斷邏輯的信號,而此邏輯會使用這項資訊來識別適合用於截斷的記錄項目。

注意

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

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

代理程式作業

兩個SQL Server Agent作業通常與已啟用異動資料擷取的資料庫相關聯:一個用來填入資料庫變更資料表,另一個負責變更資料表清除。 這兩個作業都包含執行 Transact-SQL 命令的單一步驟。 叫用的 Transact-SQL 命令是實作作業邏輯的變更資料擷取定義預存程式。 當資料庫的第一個資料表啟用異動資料擷取時,系統就會建立這些作業。 系統一定會建立清除作業。 但是,只有當資料庫沒有任何已定義的交易式發行集時,才會建立擷取作業。 當資料庫啟用異動資料擷取和異動複寫時,也會建立擷取作業,而且因為資料庫不再定義發行集,所以會移除交易記錄讀取器作業。

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

當您針對資料庫停用異動資料擷取時,系統就會移除異動資料擷取代理程式作業。 當第一個發行集加入至資料庫,而且同時啟用異動資料擷取和異動複寫時,也可以移除擷取作業。

就內部而言,異動資料擷取代理程式作業是分別使用 sys.sp_cdc_add_jobsys.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_jobsys.sp_cdc_stop_job

注意

啟動和停止擷取作業不會導致變更資料遺失。 它只會讓擷取處理序目前無法在記錄中掃描要儲放在變更資料表中的變更項目。 避免記錄掃描在尖峰要求期間增加負載的合理策略是停止擷取作業,然後在要求降低時重新啟動它。

SQL Server Agent作業的設計目的是要有足夠的彈性且足以設定,以符合異動資料擷取環境的基本需求。 不過,在這兩種情況下,系統已經公開提供核心功能的基礎預存程序,方便您進一步自訂。

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

注意

在Azure SQL Database中,Agent 作業會由自動執行擷取和清除的排程器取代。

Azure SQL Database 中的 CDC 清除

在 Azure SQL Database 中,異動資料擷取排程器會取代 SQL Server 代理程式,此代理程式會叫用預存程式,來啟動異動資料擷取資料表的定期擷取和清除。 排程器會在SQL Database內自動執行擷取和清除,而不需要任何外部相依性來達到可靠性或效能。 使用者仍可選擇使用 sp_cdc_scansp_cdc_cleanup_change_tables 程式視需要手動執行擷取和清除。

Azure SQL Database包含兩個動態管理檢視,可協助您監視異動資料擷取:sys.dm_cdc_log_scan_sessionssys.dm_cdc_errors

定序差異

請務必注意,在資料庫與設定進行異動資料擷取的資料表資料行之間有不同的定序。 CDC 會使用暫時儲存體來填入側邊資料表。 如果資料表的 CHAR 或 VARCHAR 資料行具有與資料庫定序不同的定序,而且如果這些資料行儲存非 ASCII 字元 (例如雙位元組 DBCS 字元),則 CDC 可能無法保存與基底資料表中資料一致的已變更資料。 原因是過度儲存體變數不能有與其建立關聯的定序。

請考慮下列其中一種方法來確保變更擷取資料已與基底資料表一致:

  • 將 NCHAR 或 NVARCHAR 資料類型用於包含非 ASCII 資料的資料行。

  • 或者,對於資料行和資料庫使用相同的定序。

例如,如果您有一個使用定序SQL_Latin1_General_CP1_CI_AS的資料庫,請考慮下表:

CREATE TABLE T1( 
     C1 INT PRIMARY KEY, 
     C2 VARCHAR(10) collate Chinese_PRC_CI_AI)

CDC 可能無法擷取資料行 C2 的二進位資料,因為其定序不同 (Chinese_PRC_CI_AI)。 使用 NVARCHAR 避免這個問題:

CREATE TABLE T1( 
     C1 INT PRIMARY KEY, 
     C2 NVARCHAR(10) collate Chinese_PRC_CI_AI --Unicode data type, CDC works well with this data type
     )

所需的權限

需要系統管理員許可權,才能啟用SQL Server或Azure SQL 受控執行個體的異動資料擷取。 需要db_owner角色,才能啟用Azure SQL Database的異動資料擷取。

限制

異動資料擷取具有下列限制:

Linux
Linux 上的 SQL Server 2017 (從 CU18 開始),以及 Linux 上的 SQL Server 2019 現已支援 CDC。

資料行存放區索引
無法在具有叢集資料行存放區索引的資料表上啟用變更資料擷取。 從 SQL Server 2016 開始,您可以在具有非叢集資料行存放區索引的資料表上啟用此功能。

使用變數進行資料分割切換
語句不支援 ALTER TABLE ... SWITCH TO ... PARTITION ... 在具有異動資料擷取的資料庫或資料表上使用具有異動資料擷取的變數 (CDC) 。 請參閱分割區切換限制以深入了解。

Azure SQL 資料庫中 CDC 的可用性
CDC 只能在資料庫層 S3 和更新版本上啟用。 CDC 不支援子核心 (基本、S0、S1、S2) Azure SQL資料庫。

S3 以上資料庫層的 Dbcopy 已啟用 CDC 至子核心 SLO 目前會保留 CDC 成品,但未來可能會移除 CDC 成品。

Azure SQL 資料庫的擷取和清除自訂
您無法在 Azure SQL 資料庫中設定 CDC 的擷取和清除程式頻率。 排程器會自動執行擷取和清除。

計算資料行
CDC 不支援計算資料行的值,即使計算資料行定義為保存也一樣。 擷取實例中包含的計算資料行一律具有 的值 NULL 。 此行為是預定的,而不是 Bug。

還原時間點 (PITR)
如果您以Microsoft Azure Active Directory (Azure AD) 使用者身分在資料庫上啟用 CDC,則無法將 TIME 還原 (PITR) 至子核心 SLO。 建議您將資料庫還原至與來源或更高的 SLO 相同,然後視需要停用 CDC。

Microsoft Azure Active Directory (Azure AD)
如果您以Microsoft Azure Active Directory (Azure AD) 使用者身分在 Azure SQL Database 中建立資料庫,並在其中啟用異動資料擷取 (CDC) ,SQL使用者 (,即使系統管理員角色) 也無法停用/變更 CDC 成品。 但是,其他 Azure AD 使用者可在該資料庫上啟用或停用 CDC。

同樣地,如果您以SQL使用者身分建立Azure SQL Database,將變更資料擷取啟用/停用為Azure AD使用者將無法運作。

積極性記錄截斷
當您在Azure SQL Database上啟用異動資料擷取 (CDC) 時,請注意, (CDC 掃描會使用資料庫交易記錄) 停用積極記錄截斷。

在資料庫上啟用異動資料擷取 (CDC) 會停用積極記錄截斷行為。 使用中交易會繼續保留交易記錄截斷,直到交易認可和 CDC 掃描趕上或交易中止為止。 這可能會導致交易記錄已滿,且資料庫進入唯讀模式。

ALTER COLUMN 至 VARCHAR 和 VARBINARY 之後 CDC 會失敗
當已啟用 CDC 資料表的資料行資料類型從 TEXT 變更為 VARCHARIMAGEVARBINARY ,而現有資料列會更新為異列值時。 更新之後,CDC 掃描將會導致錯誤。

在使用 Microsoft Azure Active Directory (Azure AD) 建立的還原Azure SQL DB 上啟用 CDC 失敗
如果您以Microsoft Azure Active Directory (Azure AD) 使用者身分在 Azure SQL Database 中建立資料庫,且未啟用 CDC,則啟用 CDC 將會失敗,然後還原資料庫並在還原的資料庫上啟用 CDC。

若要解決此問題,請依照下列步驟執行︰

  • 以伺服器的Azure AD系統管理員身分登入
  • 在資料庫上執行 ALTER AUTHORIZATION 命令:
ALTER AUTHORIZATION ON DATABASE::[<restored_db_name>] TO [<azuread_admin_login_name>];

EXEC sys.sp_cdc_enable_db

另請參閱

追蹤資料變更 (SQL Server)
啟用和停用異動資料擷取 (SQL Server)
使用變更資料 (SQL Server)
管理及監視異動資料擷取 (SQL Server)
時態表