關於異動資料擷取 (SQL Server)About Change Data Capture (SQL Server)

適用於: 是SQL Server (從 2008 開始) 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

異動資料擷取會記錄套用至 [SQL Server]SQL Server 資料表的插入、更新和刪除活動。Change data capture records insert, update, and delete activity that is applied to a [SQL Server]SQL Server table. 這樣會以方便取用的關聯式格式提供變更的詳細資料。This makes the details of the changes available in an easily consumed relational format. 系統會針對修改的資料列擷取資料行資訊以及將變更套用至目標環境所需的中繼資料,並且將它們儲存在鏡像追蹤來源資料表之資料行結構的變更資料表中。Column information and the metadata that is required to apply the changes to a target environment is captured for the modified rows and stored in change tables that mirror the column structure of the tracked source tables. 此外,系統會提供資料表值函式,讓取用者以有系統的方式存取異動資料。Table-valued functions are provided to allow systematic access to the change data by consumers.

此技術之目標資料取用者的理想範例為擷取、轉換和下載 (ETL) 應用程式。A good example of a data consumer that is targeted by this technology is an extraction, transformation, and loading (ETL) application. ETL 應用程式會將變更資料從 [SQL Server]SQL Server 來源資料表累加地載入資料倉儲或資料超市。An ETL application incrementally loads change data from [SQL Server]SQL Server source tables to a data warehouse or data mart. 雖然在資料倉儲內的來源資料表表示法必須反映來源資料表中的變更,但是重新整理來源複本的端對端技術並不適用。Although the representation of the source tables within the data warehouse must reflect changes in the source tables, an end-to-end technology that refreshes a replica of the source is not appropriate. 您需要的是結構化變更資料的可靠資料流,讓取用者可以將其套用到不同的資料目標表示法。Instead, you need a reliable stream of change data that is structured so that consumers can apply it to dissimilar target representations of the data. [SQL Server]SQL Server 異動資料擷取提供這種技術。change data capture provides this technology.

異動資料擷取資料流程Change Data Capture Data Flow

下圖顯示異動資料擷取的主要資料流程。The following illustration shows the principal data flow for change data capture.

Change data capture data flowChange data capture data flow

異動資料擷取的變更資料來源是 [SQL Server]SQL Server 交易記錄。The source of change data for change data capture is the [SQL Server]SQL Server transaction log. 當插入、更新和刪除作業套用至追蹤來源資料表時,描述這些變更的項目就會加入記錄。As inserts, updates, and deletes are applied to tracked source tables, entries that describe those changes are added to the log. 此記錄會當做擷取程序的輸入。The log serves as input to the capture process. 這樣就會讀取記錄並將變更之相關資訊新增至追蹤資料表的相關聯變更資料表。This reads the log and adds information about changes to the tracked table's associated change table. 系統會提供一些函數,以便列舉指定之範圍內出現在變更資料表中的變更,並以篩選結果集的形式傳回此資訊。Functions are provided to enumerate the changes that appear in the change tables over a specified range, returning the information in the form of a filtered result set. 應用程式處理序通常會使用篩選結果集,在某些外部環境中更新來源的表示法。The filtered result set is typically used by an application process to update a representation of the source in some external environment.

了解異動資料擷取和擷取執行個體Understanding Change Data Capture and the Capture Instance

您必須先針對資料庫明確啟用異動資料擷取,然後才能追蹤該資料庫內部任何個別資料表的變更。Before changes to any individual tables within a database can be tracked, change data capture must be explicitly enabled for the database. 這項作業是使用 sys.sp_cdc_enable_db預存程序完成的。This is done by using the stored procedure sys.sp_cdc_enable_db. 啟用資料庫之後,您就可以使用 sys.sp_cdc_enable_table預存程序,將來源資料表識別為追蹤資料表。When the database is enabled, source tables can be identified as tracked tables by using the stored procedure sys.sp_cdc_enable_table. 當某個資料表啟用異動資料擷取時,系統就會建立相關聯的擷取執行個體,以便支援來源資料表中變更資料的散播。When a table is enabled for change data capture, an associated capture instance is created to support the dissemination of the change data in the source table. 此擷取執行個體包含一個變更資料表以及最多兩個查詢函數。The capture instance consists of a change table and up to two query functions. 描述擷取執行個體之組態詳細資料的中繼資料會包含在變更資料擷取中繼資料資料表 cdc.change_tablescdc.index_columnscdc.captured_columns中。Metadata that describes the configuration details of the capture instance is retained in the change data capture metadata tables cdc.change_tables, cdc.index_columns, and cdc.captured_columns. 您可以使用 sys.sp_cdc_help_change_data_capture預存程序來擷取這項資訊。This information can be retrieved by using the stored procedure sys.sp_cdc_help_change_data_capture.

與擷取執行個體相關聯的所有物件都會建立在啟用資料庫的異動資料擷取結構描述中。All objects that are associated with a capture instance are created in the change data capture schema of the enabled database. 擷取執行個體名稱的需求包括,它必須是有效的物件名稱,而且它在資料庫擷取執行個體中必須是唯一的。The requirements for the capture instance name is that it be a valid object name, and that it be unique across the database capture instances. 預設名稱為來源資料表的 <結構描述名稱_資料表名稱>。By default, the name is <schema nametable name> of the source table. 其相關聯變更資料表的命名方式是將 _CT 附加至擷取執行個體名稱。Its associated change table is named by appending _CT to the capture instance name. 用來查詢所有變更之函數的命名方式是在擷取執行個體名稱前面加上 **fn_cdc_get_all_changesThe function that is used to query for all changes is named by prepending **fn_cdc_get_all_changes_ to the capture instance name. 如果擷取執行個體設定為支援 net changes,系統也會建立 net_changes 查詢函數,而且其命名方式是在擷取執行個體名稱前面加上 fn_cdc_get_net_changes_If the capture instance is configured to support net changes, the net_changes query function is also created and named by prepending fn_cdc_get_net_changes_ to the capture instance name.

變更資料表Change Table

異動資料擷取變更資料表的前五個資料行是中繼資料行。The first five columns of a change data capture change table are metadata columns. 這些資料行會提供與已記錄之變更相關的額外資訊。These provide additional information that is relevant to the recorded change. 其餘資料行則會鏡像來源資料表中識別之擷取資料行的名稱,通常也會鏡像其類型。The remaining columns mirror the identified captured columns from the source table in name and, typically, in type. 這些資料行會保存從來源資料表中蒐集的擷取資料行資料。These columns hold the captured column data that is gathered from the source table.

套用到來源資料表的每個插入或刪除作業會顯示成變更資料表中的單一資料列。Each insert or delete operation that is applied to a source table appears as a single row within the change table. 插入作業所產生之資料列的資料行包含插入之後的資料行值。The data columns of the row that results from an insert operation contain the column values after the insert. 刪除作業所產生之資料列的資料行包含刪除之前的資料行值。The data columns of the row that results from a delete operation contain the column values before the delete. 更新作業需要一個資料列項目來識別更新之前的資料行值,和第二個資料列項目來識別更新之後的資料行值。An update operation requires one row entry to identify the column values before the update, and a second row entry to identify the column values after the update.

變更資料表中的每個資料列也會包含其他中繼資料,以便允許解譯變更活動。Each row in a change table also contains additional metadata to allow interpretation of the change activity. 資料行 $start_lsn 會識別指派給變更的認可記錄序號 (LSN)。The column $start_lsn identifies the commit log sequence number (LSN) that was assigned to the change. 認可 LSN 會識別在相同交易中認可的變更,而且會為這些交易排序。The commit LSN both identifies changes that were committed within the same transaction, and orders those transactions. 資料行 __$seqval 可用於排序在相同交易中發生的其他變更。The column __$seqval can be used to order more changes that occur in the same transaction. 資料行 __$operation 會記錄與變更相關聯的作業:1 = 刪除、2 = 插入、3 = 更新 (建立資料影像前),以及 4 = 更新 (建立資料影像後)。The column __$operation records the operation that is associated with the change: 1 = delete, 2 = insert, 3 = update (before image), and 4 = update (after image). 資料行 __$update_mask 是變數位元遮罩,其中每個擷取資料行都有一個定義的位元。The column __$update_mask is a variable bit mask with one defined bit for each captured column. 若是插入和刪除項目,更新遮罩永遠會設定所有位元。For insert and delete entries, the update mask will always have all bits set. 不過,更新資料列將僅會設定對應到已變更之資料行的位元。Update rows, however, will only have those bits set that correspond to changed columns.

資料庫的異動資料擷取有效性間隔Change Data Capture Validity Interval for a Database

資料庫的異動資料擷取有效性間隔就是擷取執行個體可以使用變更資料的時間範圍。The change data capture validity interval for a database is the time during which change data is available for capture instances. 有效性間隔會在您建立資料庫資料表的第一個擷取執行個體時開始,並且繼續到目前的時間。The validity interval begins when the first capture instance is created for a database table, and continues to the present time.

如果您沒有定期且有系統地清除儲放在變更資料表中的資料,這項資料將無限制地成長。Data that is deposited in change tables will grow unmanageably if you do not periodically and systematically prune the data. 異動資料擷取清除處理序會負責強制執行保留性清除原則。The change data capture cleanup process is responsible for enforcing the retention-based cleanup policy. 首先,它會移動有效性間隔的低端點,以便滿足時間限制。First, it moves the low endpoint of the validity interval to satisfy the time restriction. 然後,它會移除過期的變更資料表項目。Then, it removes expired change table entries. 根據預設,系統會保留三天內的資料。By default, three days of data is retained.

對於高端點而言,因為擷取處理序會認可每一批新的變更資料,所以新項目會加入具有變更資料表項目之每個交易的 cdc.lsn_time_mappingAt the high end, as the capture process commits each new batch of change data, new entries are added to cdc.lsn_time_mapping for each transaction that has change table entries. 在對應資料表中,會保留認可記錄序號 (LSN) 和交易認可時間 (分別為 start_lsn 和 tran_end_time 資料行)。Within the mapping table, both a commit Log Sequence Number (LSN) and a transaction commit time (columns start_lsn and tran_end_time, respectively) are retained. cdc.lsn_time_mapping 中找到的最大 LSN 值代表資料庫有效性期間的上限標準。The maximum LSN value that is found in cdc.lsn_time_mapping represents the high water mark of the database validity window. 其對應認可時間會當做保留性清除用以計算新下限標準的基礎。Its corresponding commit time is used as the base from which retention based cleanup computes a new low water mark.

由於擷取處理序會從交易記錄中擷取變更資料,因此來源資料表認可變更的時間與變更顯示在其相關聯變更資料表中的時間之間具有內建的延遲。Because the capture process extracts change data from the transaction log, there is a built in latency between the time that a change is committed to a source table and the time that the change appears within its associated change table. 雖然這個延遲通常很短,但是請務必記住,在擷取處理序處理相關記錄項目之前,無法使用變更資料。While this latency is typically small, it is nevertheless important to remember that change data is not available until the capture process has processed the related log entries.

擷取執行個體的異動資料擷取有效性間隔Change Data Capture Validity Interval for a Capture Instance

雖然資料庫有效性間隔與個別擷取執行個體的有效性間隔通常會一致,但是並非永遠如此。Although it is common for the database validity interval and the validity interval of individual capture instance to coincide, this is not always true. 當擷取處理序辨識擷取執行個體並且開始將相關聯的變更記錄至其變更資料表時,擷取執行個體的有效性間隔就會開始。The validity interval of the capture instance starts when the capture process recognizes the capture instance and starts to log associated changes to its change table. 因此,如果您在不同的時間建立擷取執行個體,每個擷取執行個體一開始都會有不同的低端點。As a result, if capture instances are created at different times, each will initially have a different low endpoint. sys.sp_cdc_help_change_data_capture 所傳回之結果集的 start_lsn 資料行會顯示每個已定義之擷取執行個體的目前低端點。The start_lsn column of the result set that is returned by sys.sp_cdc_help_change_data_capture shows the current low endpoint for each defined capture instance. 當清除處理序清除變更資料表項目時,它就會調整所有擷取執行個體的 start_lsn 值,以便反映可用變更資料的新下限標準。When the cleanup process cleans up change table entries, it adjusts the start_lsn values for all capture instances to reflect the new low water mark for available change data. 只有 start_lsn 值目前小於新下限標準的這些擷取執行個體才會進行調整。Only those capture instances that have start_lsn values that are currently less than the new low water mark are adjusted. 經過一段時間後,如果沒有建立任何新的擷取執行個體,所有個別執行個體的有效性間隔通常會與資料庫有效性間隔一致。Over time, if no new capture instances are created, the validity intervals for all individual instances will tend to coincide with the database validity interval.

有效性間隔對於變更資料的取用者很重要,因為擷取執行個體的目前異動資料擷取有效性間隔必須完全涵蓋要求的擷取間隔。The validity interval is important to consumers of change data because the extraction interval for a request must be fully covered by the current change data capture validity interval for the capture instance. 如果擷取間隔的低端點位於有效性間隔低端點的左邊,可能會由於積極的清除而遺失變更資料。If the low endpoint of the extraction interval is to the left of the low endpoint of the validity interval, there could be missing change data due to aggressive cleanup. 如果擷取間隔的高端點位於有效性間隔高端點的右邊,表示擷取處理序尚未完全處理擷取間隔所代表的時間週期,而且也可能會遺失變更資料。If the high endpoint of the extraction interval is to the right of the high endpoint of the validity interval, the capture process has not yet processed through the time period that is represented by the extraction interval, and change data could also be missing.

sys.fn_cdc_get_min_lsn 函數是用來擷取擷取執行個體的目前最小 LSN,而 sys.fn_cdc_get_max_lsn 則是用來擷取目前最大 LSN 值。The function sys.fn_cdc_get_min_lsn is used to retrieve the current minimum LSN for a capture instance, while sys.fn_cdc_get_max_lsn is used to retrieve the current maximum LSN value. 查詢變更資料時,如果指定的 LSN 範圍並未落在這兩個 LSN 值之內,異動資料擷取查詢函數將會失敗。When querying for change data, if the specified LSN range does not lie within these two LSN values, the change data capture query functions will fail.

處理來源資料表的變更Handling Changes to Source Tables

對於下游取用者而言,要容納追蹤之來源資料表的資料行變更是個困難的問題。To accommodate column changes in the source tables that are being tracked is a difficult issue for downstream consumers. 雖然針對來源資料表啟用異動資料擷取無法避免這類 DDL 變更發生,但是異動資料擷取有助於減少對於取用者的影響,因為它會讓透過 API 傳回的傳遞結果集維持不變,即使基礎來源資料表的資料行結構變更也一樣。Although enabling change data capture on a source table does not prevent such DDL changes from occurring, change data capture helps to mitigate the effect on consumers by allowing the delivered result sets that are returned through the API to remain unchanged even as the column structure of the underlying source table changes. 這個固定的資料行結構也會反映在已定義之查詢函數所存取的基礎變更資料表中。This fixed column structure is also reflected in the underlying change table that the defined query functions access.

為了容納固定資料行結構的變更資料表,當來源資料表啟用異動資料擷取時,負責擴展變更資料表的擷取處理序將會忽略並未識別為待擷取的任何新資料行。To accommodate a fixed column structure change table, the capture process responsible for populating the change table will ignore any new columns that are not identified for capture when the source table was enabled for change data capture. 如果卸除了某個追蹤資料行,就會在後續變更項目中,針對此資料行提供 Null 值。If a tracked column is dropped, null values will be supplied for the column in the subsequent change entries. 不過,如果現有的資料行進行資料類型的變更,此變更就會傳播至變更資料表,以便確保擷取機制不會將資料遺失導入追蹤資料行。However, if an existing column undergoes a change in its data type, the change is propagated to the change table to ensure that the capture mechanism does not introduce data loss to tracked columns. 此外,擷取處理序也會將針對追蹤資料表之資料行結構偵測到的任何變更公佈至 cdc.ddl_history 資料表。The capture process also posts any detected changes to the column structure of tracked tables to the cdc.ddl_history table. 想要收到可能必須在下游應用程式中完成之調整警示的取用者會使用 sys.sp_cdc_get_ddl_history預存程序。Consumers wishing to be alerted of adjustments that might have to be made in downstream applications, use the stored procedure sys.sp_cdc_get_ddl_history.

一般而言,當 DDL 變更套用至相關聯的來源資料表時,目前擷取執行個體將繼續保留其外觀。Typically, the current capture instance will continue to retain its shape when DDL changes are applied to its associated source table. 不過,反映新資料行結構的資料表將無法建立第二個擷取執行個體。However, it is possible to create a second capture instance for the table that reflects the new column structure. 這樣會讓擷取處理序在具有兩種不同資料行結構的兩個不同變更資料表中,針對相同的來源資料表進行變更。This allows the capture process to make changes to the same source table into two distinct change tables having two different column structures. 因此,當某個變更資料表可以繼續滿足目前運作中的程式時,第二個變更資料表可以驅動嘗試併入新資料行資料的開發環境。Thus, while one change table can continue to feed current operational programs, the second one can drive a development environment that is trying to incorporate the new column data. 允許擷取機制一前一後擴展這兩個變更資料表是表示,系統可以完成這兩個資料表之間的轉換,而不會遺失變更資料。Allowing the capture mechanism to populate both change tables in tandem means that a transition from one to the other can be accomplished without loss of change data. 當兩個異動資料擷取時間表重疊時,就可能會發生這種情況。This can happen any time the two change data capture timelines overlap. 完成轉換之後,就可以移除已經過時的擷取執行個體。When the transition is effected, the obsolete capture instance can be removed.

注意

可同時與單一來源資料表相關聯的擷取執行個體數目上限是二。The maximum number of capture instances that can be concurrently associated with a single source table is two.

擷取作業與異動複寫 Logreader 之間的關聯性Relationship Between the Capture Job and the Transactional Replication Logreader

異動資料擷取處理序的邏輯內嵌在預存程序 sp_replcmds中,此預存程序是建立成 sqlservr.exe 一部分的內部伺服器函數,而且也會由異動複寫用來從交易記錄中收集變更。The logic for change data capture process is embedded in the stored procedure sp_replcmds, an internal server function built as part of sqlservr.exe and also used by transactional replication to harvest changes from the transaction log. 當您針對某個資料庫單獨啟用異動資料擷取時,就會將異動資料擷取 SQL Server Agent 擷取作業建立成叫用 sp_replcmds 的工具。When change data capture alone is enabled for a database, you create the change data capture SQL Server Agent capture job as the vehicle for invoking sp_replcmds. 如果同時存在複寫,交易式 Logreader 就會單獨用來滿足這兩個取用者的變更資料需求。When replication is also present, the transactional logreader alone is used to satisfy the change data needs for both of these consumers. 當您針對相同的資料庫同時啟用複寫和異動資料擷取時,這項策略可大幅減少記錄競爭的情況。This strategy significantly reduces log contention when both replication and change data capture are enabled for the same database.

每當啟用異動資料擷取之資料庫的複寫狀態變更時,就會自動在這兩種擷取變更資料的作業模式之間切換。The switch between these two operational modes for capturing change data occurs automatically whenever there is a change in the replication status of a change data capture enabled database.

重要

擷取邏輯的兩個執行個體都需要 [SQL Server]SQL Server Agent 處於執行中狀態,才能讓處理序執行。Both instances of the capture logic require [SQL Server]SQL Server Agent to be running for the process to execute.

擷取處理序的主要工作是掃描記錄,並且將資料行資料和交易相關的資訊寫入異動資料擷取變更資料表。The principal task of the capture process is to scan the log and write column data and transaction related information to the change data capture change tables. 為了確保它所擴展的所有異動資料擷取變更資料表在交易方面具有一致的界限,擷取處理序會針對每個掃描循環開啟並認可自己的交易。To ensure a transactionally consistent boundary across all the change data capture change tables that it populates, the capture process opens and commits its own transaction on each scan cycle. 它會偵測出資料表最近啟用異動資料擷取的時間,並且自動將它們加入目前正在記錄中監視變更項目的資料表集合。It detects when tables are newly enabled for change data capture, and automatically includes them in the set of tables that are actively monitored for change entries in the log. 同樣地,它也會偵測出停用異動資料擷取,進而從目前正在監視變更資料的資料表集合中移除來源資料表。Similarly, disabling change data capture will also be detected, causing the source table to be removed from the set of tables actively monitored for change data. 當某個記錄區段的處理完成時,擷取處理序就會發出伺服器記錄截斷邏輯的信號,而此邏輯會使用這項資訊來識別適合用於截斷的記錄項目。When processing for a section of the log is finished, the capture process signals the server log truncation logic, which uses this information to identify log entries eligible for truncation.

注意

啟用異動資料擷取的資料庫時,即使復原模式設定為簡單復原,在擷取處理序蒐集到所有標示為待擷取的變更之前,記錄截斷點將不會前進。When a database is enabled for change data capture, even if the recovery mode is set to simple recovery the log truncation point will not advance until all the changes that are marked for capture have been gathered by the capture process. 如果擷取處理序並未執行,而且存在要蒐集的變更,則執行 CHECKPOINT 將不會截斷記錄。If the capture process is not running and there are changes to be gathered, executing CHECKPOINT will not truncate the log.

擷取處理序也會用來維護有關追蹤資料表之 DDL 變更的記錄。The capture process is also used to maintain history on the DDL changes to tracked tables. 每當卸除啟用異動資料擷取的資料庫或資料表,或是加入、修改或卸除啟用異動資料擷取之資料表的資料行時,與異動資料擷取相關聯的 DDL 陳述式就會在資料庫交易記錄中建立項目。The DDL statements that are associated with change data capture make entries to the database transaction log whenever a change data capture-enabled database or table is dropped or columns of a change data capture-enabled table are added, modified, or dropped. 擷取處理序會先處理這些記錄項目,然後再將相關聯的 DDL 事件公佈至 cdc.ddl_history 資料表。These log entries are processed by the capture process, which then posts the associated DDL events to the cdc.ddl_history table. 您可以使用 sys.sp_cdc_get_ddl_history預存程序來取得有關影響追蹤資料表之 DDL 事件的相關資訊。You can obtain information about DDL events that affect tracked tables by using the stored procedure sys.sp_cdc_get_ddl_history.

異動資料擷取代理程式作業Change Data Capture Agent Jobs

通常有兩個 [SQL Server]SQL Server Agent 作業會與啟用異動資料擷取的資料庫相關聯:用來擴展資料庫變更資料表的作業,以及負責變更資料表清除的作業。Two [SQL Server]SQL Server Agent jobs are typically associated with a change data capture enabled database: one that is used to populate the database change tables, and one that is responsible for change table cleanup. 這兩個作業都包含執行 Transact-SQLTransact-SQL 命令的單一步驟。Both jobs consist of a single step that runs a Transact-SQLTransact-SQL command. 所叫用的 Transact-SQLTransact-SQL 命令是異動資料擷取定義的預存程序,可實作此作業的邏輯。The Transact-SQLTransact-SQL command that is invoked is a change data capture defined stored procedure that implements the logic of the job. 當資料庫的第一個資料表啟用異動資料擷取時,系統就會建立這些作業。The jobs are created when the first table of the database is enabled for change data capture. 系統一定會建立清除作業。The Cleanup Job is always created. 但是,只有當資料庫沒有任何已定義的交易式發行集時,才會建立擷取作業。The capture job will only be created if there are no defined transactional publications for the database. 當您針對資料庫同時啟用異動資料擷取和異動複寫時,系統也會建立擷取作業,而且會移除交易式 Logreader 作業,因為資料庫不再含有已定義的發行集。The capture job is also created when both change data capture and transactional replication are enabled for a database, and the transactional logreader job is removed because the database no longer has defined publications.

擷取和清除作業都是使用預設參數建立的。Both the capture and cleanup jobs are created by using default parameters. 擷取作業會立即啟動。The capture job is started immediately. 它會連續執行,而且在每個掃描循環中最多可以處理 1000 筆交易 (循環之間等候 5 秒)。It runs continuously, processing a maximum of 1000 transactions per scan cycle with a wait of 5 seconds between cycles. 清除作業則在每天早上 2 點執行。The cleanup job runs daily at 2 A.M. 它會保留變更資料表項目長達 4320 分鐘或 3 天,而且單一刪除陳述式最多可以移除 5000 個項目。It retains change table entries for 4320 minutes or 3 days, removing a maximum of 5000 entries with a single delete statement.

當您針對資料庫停用異動資料擷取時,系統就會移除異動資料擷取代理程式作業。The change data capture agent jobs are removed when change data capture is disabled for a database. 當第一個發行集加入至資料庫,而且同時啟用異動資料擷取和異動複寫時,也可以移除擷取作業。The capture job can also be removed when the first publication is added to a database, and both change data capture and transactional replication are enabled.

就內部而言,異動資料擷取代理程式作業是分別使用 sys.sp_cdc_add_jobsys.sp_cdc_drop_job預存程序建立和卸除的。Internally, change data capture agent jobs are created and dropped by using the stored procedures sys.sp_cdc_add_job and sys.sp_cdc_drop_job, respectively. 系統也會公開這些預存程序,讓管理員能夠控制這些作業的建立和移除。These stored procedures are also exposed so that administrators can control the creation and removal of these jobs.

管理員對於異動資料擷取代理程式作業的預設組態沒有明確的控制權。An administrator has no explicit control over the default configuration of the change data capture agent jobs. sys.sp_cdc_change_job 預存程序的提供目的是允許修改預設組態參數。The stored procedure sys.sp_cdc_change_job is provided to allow the default configuration parameters to be modified. 此外, sys.sp_cdc_help_jobs 預存程序則是允許檢視目前的組態參數。In addition, the stored procedure sys.sp_cdc_help_jobs allows current configuration parameters to be viewed. 擷取作業和清除作業都會在啟動時從 msdb.dbo.cdc_jobs 資料表中擷取組態參數。Both the capture job and the cleanup job extract configuration parameters from the table msdb.dbo.cdc_jobs on startup. 使用 sys.sp_cdc_change_job 針對這些值所做的任何變更要等到此作業停止並重新啟動時才會生效。Any changes made to these values by using sys.sp_cdc_change_job will not take effect until the job is stopped and restarted.

系統提供了兩個額外的預存程序,讓您能夠啟動和停止異動資料擷取代理程式作業: sys.sp_cdc_start_jobsys.sp_cdc_stop_jobTwo additional stored procedures are provided to allow the change data capture agent jobs to be started and stopped: sys.sp_cdc_start_job and sys.sp_cdc_stop_job.

注意

啟動和停止擷取作業不會導致變更資料遺失。Starting and stopping the capture job does not result in a loss of change data. 它只會讓擷取處理序目前無法在記錄中掃描要儲放在變更資料表中的變更項目。It only prevents the capture process from actively scanning the log for change entries to deposit in the change tables. 避免記錄掃描在尖峰要求期間增加負載的合理策略是停止擷取作業,然後在要求降低時重新啟動它。A reasonable strategy to prevent log scanning from adding load during periods of peak demand is to stop the capture job and restart it when demand is reduced.

這兩個 [SQL Server]SQL Server Agent 作業都設計成具備足夠的彈性而且可充分地進行設定,以便符合異動資料擷取環境的基本需求。Both [SQL Server]SQL Server Agent jobs were designed to be flexible enough and sufficiently configurable to meet the basic needs of change data capture environments. 不過,在這兩種情況下,系統已經公開提供核心功能的基礎預存程序,方便您進一步自訂。In both cases, however, the underlying stored procedures that provide the core functionality have been exposed so that further customization is possible.

以 NETWORK SERVICE 帳戶身分執行 Database Engine 服務或 SQL Server Agent 服務時,異動資料擷取無法正確運作。Change data capture cannot function properly when the Database Engine service or the SQL Server Agent service is running under the NETWORK SERVICE account. 這樣可能會造成錯誤 22832。This can result in error 22832.

使用資料庫和資料表定序差異Working with database and table collation differences

請務必注意,在資料庫與設定進行異動資料擷取的資料表資料行之間有不同的定序。It is important to be aware of a situation where you have different collations between the database and the columns of a table configured for change data capture. CDC 會使用暫時儲存體來填入側邊資料表。CDC uses interim storage to populate side tables. 如果資料表的 CHAR 或 VARCHAR 資料行具有與資料庫定序不同的定序,而且如果這些資料行儲存非 ASCII 字元 (例如雙位元組 DBCS 字元),則 CDC 可能無法保存與基底資料表中資料一致的已變更資料。If a table has CHAR or VARCHAR columns with collations that are different from the database collation and if those columns store non-ASCII characters (such as double byte DBCS characters), CDC might not be able to persist the changed data consistent with the data in the base tables. 原因是過度儲存體變數不能有與其建立關聯的定序。This is due to the fact that the interim storage variables cannot have collations associated with them.

請考慮下列其中一種方法來確保變更擷取資料已與基底資料表一致:Please consider one of the following approaches to ensure change captured data is consistent with base tables:

  • 將 NCHAR 或 NVARCHAR 資料類型用於包含非 ASCII 資料的資料行。Use NCHAR or NVARCHAR data type for columns containing non-ASCII data.

  • 或者,對於資料行和資料庫使用相同的定序。Or, Use the same collation for columns and for the database.

例如,如果您有一個使用 SQL_Latin1_General_CP1_CI_AS 定序的資料庫,請考慮使用下表:For example, if you have one database that uses a collation of SQL_Latin1_General_CP1_CI_AS, consider the following table:

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

CDC 可能無法擷取資料行 C2 的二進位資料,因為其定序不同 (Chinese_PRC_CI_AI)。CDC might fail to capture the binary data for column C2, because its collation is different (Chinese_PRC_CI_AI). 使用 NVARCHAR 避免這個問題:Use NVARCHAR to avoid this problem:

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)

另請參閱See Also

追蹤資料變更 (SQL Server) Track Data Changes (SQL Server)
啟用和停用異動資料擷取 (SQL Server) Enable and Disable Change Data Capture (SQL Server)
使用變更資料 (SQL Server) Work with Change Data (SQL Server)
管理和監視異動資料擷取 (SQL Server)Administer and Monitor Change Data Capture (SQL Server)