关于变更数据捕获 （SQL Server）

更改应用于 SQL Server 表的数据捕获记录插入、更新和删除活动。 这使得更改的详细信息以易于使用的关系格式提供。 修改行中的列信息和元数据被捕获，以便将更改应用于目标环境，并存储在变化表中，这些表镜像所跟踪的源表的列结构。 表值函数被提供以使使用者系统地访问更改数据。

此技术面向的数据使用者的一个很好的示例是提取、转换和加载（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检索此信息。

与捕获实例关联的所有对象都是在已启用数据库的变更数据捕获架构中创建的。 捕获实例名称的要求是它是有效的对象名称，并且它在数据库捕获实例中是唯一的。 默认情况下，名称为<schema 名称_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 代理捕获作业，用来调用 sp_replcmds。 当复制也存在时，仅使用事务日志读取器来满足这两个使用者的更改数据需求。 当为同一数据库启用复制和更改数据捕获时，此策略可显著减少日志争用。

每当启用了更改数据捕获的数据库的复制状态发生更改时，捕获更改数据的这两种作模式之间的切换会自动发生。

捕获过程的主要任务是扫描日志，并将列数据和事务相关信息写入变更数据捕获更改表。 为了确保其填充的所有变更数据捕获更改表之间具备事务一致性边界，捕获过程会在每个扫描周期开启并提交自身的事务。 它会检测到何时有表被新启用用于更改数据捕获，并自动将其包含在主动监控其日志中的更改条目的表集合中。 同样，禁用更改数据捕获也将被检测到，这会导致源表从主动监控更改数据的表集合中被删除。 完成日志部分的处理后，捕获进程会向服务器日志截断逻辑发出信号，该逻辑使用此信息标识符合截断条件的日志条目。

捕获过程还用于维护对跟踪表的 DDL 更改的历史记录。 每当删除启用了更改数据捕获的数据库或表，或者启用了更改数据捕获的表的列被添加、修改或删除时，与更改数据捕获关联的 DDL 语句会向数据库事务日志中记录条目。 这些日志条目由捕获进程处理，然后将关联的 DDL 事件发布到cdc.ddl_history表。 可以使用存储过程 sys.sp_cdc_get_ddl_history获取有关影响跟踪表的 DDL 事件的信息。

变更数据捕获代理作业

两个 SQL Server 代理作业通常与启用了更改数据捕获的数据库相关联：一个用于填充数据库更改表，一个负责更改表清理。 这两个作业都包含运行 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 代理作业设计为足够灵活且足够可配置，以满足变更数据捕获环境的基本需求。 但是，在这两种情况下，提供核心功能的基础存储过程都已公开，以便可以进一步进行自定义。

当数据库引擎服务或 SQL Server 代理服务在网络服务帐户下运行时，更改数据捕获无法正常工作。 这可能会导致错误 22832。

另请参阅

跟踪数据更改 (SQL Server)
启用和禁用变更数据捕获 (SQL Server)
处理变更数据 (SQL Server)
管理和监视变更数据捕获 (SQL Server)