关于变更数据捕获 (SQL Server)About Change Data Capture (SQL Server)

适用对象: yesSQL ServeryesAzure SQL 数据库(仅限托管实例)noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

变更数据捕获可记录应用于 SQL ServerSQL Server 表的插入、更新和删除活动。Change data capture records insert, update, and delete activity that is applied to a SQL ServerSQL 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 ServerSQL Server 源表中的更改数据加载到数据仓库或数据市场。An ETL application incrementally loads change data from SQL ServerSQL 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 ServerSQL 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 ServerSQL Server 事务日志。The source of change data for change data capture is the SQL ServerSQL 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 name_table name> of the source table. 它的关联更改表的命名方式为:在捕获实例名称后面追加 _CTIts associated change table is named by appending _CT to the capture instance name. 用于查询所有更改的函数的命名方式为:在捕获实例名称后面追加 fn_cdc_get_all_changes_The 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_mapping 中为每个具有更改表项的事务添加新的项。At 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.

捕获作业与事务复制日志读取器之间的关系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 代理捕获作业作为调用 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. 如果还启用了复制,则会单独使用事务日志读取器来满足这两个使用者的更改数据需求。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 ServerSQL Server 代理才能执行相应的进程。Both instances of the capture logic require SQL ServerSQL 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 ServerSQL Server 代理作业与启用了变更数据捕获的数据库相关联:一个作业用于填充数据库更改表,另一个作业负责清除更改表。Two SQL ServerSQL 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. 如果为数据库同时启用了变更数据捕获和事务复制,并删除了事务日志读取器作业,则也会创建捕获作业,因为数据库不再具有定义的发布。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 分钟),使用单个删除语句最多可删除 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 ServerSQL Server 代理作业从设计上都具有足够高的灵活性和可配置性,可以满足变更数据捕获环境的基本需求。Both SQL ServerSQL 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.

数据库引擎服务或 SQL Server 代理服务在 NETWORK SERVICE 帐户下运行时,变更数据捕获无法正常工作。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)