通过

跟踪数据更改 （SQL Server）

SQL Server 2014 提供了两项功能，用于跟踪数据库中数据的更改： 更改数据捕获 和 更改跟踪。 这些功能使应用程序能够确定对数据库中用户表进行的 DML 更改（插入、更新和删除作）。 可以在同一数据库上启用更改数据捕获和更改跟踪;不需要特殊注意事项。 有关支持更改数据捕获和更改跟踪的 SQL Server 版本，请参阅 SQL Server 2014 各版本支持的功能。

使用变更数据捕获或更改跟踪的优势

对于某些注重效能的应用程序来说，查询数据库中已更改的数据的能力是一项很重要的要求。 通常，为了确定数据更改，应用程序开发人员必须在其应用程序中使用触发器、时间戳列和其他表的组合来实现自定义跟踪方法。 创建这些应用程序通常涉及多项工作，导致架构更新，并且通常带来较高的性能开销。

在应用程序中使用变更数据捕获或更改跟踪来跟踪数据库中的更改，而不是开发自定义解决方案具有以下优势：

  • 开发时间缩短。 由于 SQL Server 2014 中提供了功能，因此无需开发自定义解决方案。

  • 不需要架构更改。 无需添加列、添加触发器或创建侧表，以便跟踪已删除的行或存储更改跟踪信息（如果列无法添加到用户表）。

  • 有一个内置的清理机制。 清理变更记录追踪的操作会在后台自动进行。 不需要对存储在侧表中的数据进行自定义清理。

  • 提供了用于获取更改信息的函数。

  • DML操作的开销较低。 同步更改跟踪始终会产生一些开销。 但是，使用更改跟踪有助于最大程度地减少开销。 开销通常会低于使用其他解决方案的开销，尤其是那些需要使用触发器的解决方案。

  • 变更跟踪是基于已提交的事务。 更改的顺序基于事务提交时间。 这使得在长时间运行和重叠的事务中能够获得可靠的结果。 使用 timestamp 值的自定义解决方案必须专门设计为处理这些方案。

  • 标准工具可用于配置和管理。 SQL Server 2014 提供标准 DDL 语句、SQL Server Management Studio、目录视图和安全权限。

更改数据捕获和更改跟踪之间的功能差异

下表列出了变更数据捕获和更改跟踪之间的功能差异。 更改数据捕获过程中的跟踪机制包括从事务日志中异步捕获更改，以便 DML 操作后可获取更改。 在更改跟踪中，跟踪机制涉及与 DML 操作同步实时跟踪更改，以便能够立即提供更改信息。

功能 / 特点 变更数据捕获过程 更改跟踪
跟踪修订
DML 更改 是的 是的
跟踪信息
历史数据 是的 否
列是否已更改 是的 是的
DML 类型 是的 是的

变更数据捕获

更改数据捕获通过捕获 DML 更改和更改的实际数据来为用户表提供历史更改信息。 更改是使用异步过程捕获的，该进程读取事务日志，并且对系统的影响较低。

如下图所示，对用户表所做的更改将记录在相应的更改表中。 这些更改表提供了一段时间内更改的历史视图。 SQL Server 提供的 变更数据捕获函数使更改数据能够轻松、系统地使用。

变更数据捕获的概念图

安全模型

本部分介绍变更数据捕获安全模型。

配置和管理
若要启用或禁用数据库的更改数据捕获， sys.sp_cdc_enable_db（Transact-SQL） 或 sys.sp_cdc_disable_db（Transact-SQL） 的调用方必须是固定服务器 sysadmin 角色的成员。 启用和禁用表级别的更改数据捕获需要调用sys.sp_cdc_enable_table（Transact-SQL）和sys.sp_cdc_disable_table（Transact-SQL）的用户必须是sysadmin角色或数据库database db_owner角色的成员。

使用存储过程来支持变更数据捕获作业的管理仅限于服务器角色 sysadmin 的成员和 database db_owner 角色的成员。

更改枚举和元数据查询
若要获取与捕获实例关联的更改数据，必须授予用户对关联源表的所有捕获列的访问权限。 此外，如果在创建捕获实例时指定了一个门控角色，则调用者也必须是指定门控角色的成员。 其他用于访问元数据的常规变更数据捕获函数可以通过公共角色让所有数据库用户都可以访问，不过对返回的元数据的访问通常也会通过对基础源表的选择访问权限以及任何已定义的限制角色的成员身份进行访问限制。

更改启用数据捕获的源表的 DDL 操作
为更改数据捕获启用表时，只有固定服务器角色 sysadmin 成员、database role db_owner 成员或 database role db_ddladmin 成员才能对表进行 DDL 操作。 对表具有执行 DDL 操作的显式授权的用户如果尝试执行这些操作，将收到错误 22914。

变更数据捕获的数据类型注意事项

所有基础列类型都受到变更数据捕获的支持。 下表列出了多个列类型的行为和限制。

列的类型 更改表中记录的变化 局限性
稀疏列 是的 在使用列集时不支持捕捉变更。
计算列 否 不会跟踪对计算列的更改。 列将显示在具有适当类型的更改表中，但值为 NULL。
XML 是的 不会跟踪对单个 XML 元素的更改。
时间戳 是的 更改表中的数据类型将转换为二进制。
BLOB 数据类型 是的 仅当列本身发生更改时，才会存储 BLOB 列的上一个图像。

更改数据捕获和其他 SQL Server 功能

本部分介绍以下功能如何与变更数据捕获交互：

  • 数据库镜像

  • 事务复制

  • 数据库还原或附接

数据库镜像

已启用更改数据捕获的数据库可以进行镜像。 若要确保捕获和清理在镜像上自动发生，请执行以下步骤：

  1. 确保 SQL Server 代理在镜像上运行。

  2. 当主体切换到镜像后，在镜像上创建捕获作业和清理作业。 若要创建作业，请使用存储过程sys.sp_cdc_add_job（Transact-SQL）。

有关数据库镜像的详细信息，请参阅数据库镜像（SQL Server）。

事务复制

更改数据捕获和事务复制可以共存在同一数据库中，但启用这两个功能时，更改表的总体处理方式不同。 更改数据捕获和事务复制始终使用相同的过程 sp_replcmds 从事务日志读取更改。 当自行启用更改数据捕获时，SQL Server 代理作业将调用 sp_replcmds。 在同一数据库上启用这两项功能时，日志读取器代理将调用 sp_replcmds。 此代理同时填充更改表和分发数据库表。 有关详细信息，请参阅 复制日志读取器代理。

请考虑在 AdventureWorks2012 数据库上启用更改数据捕获的方案，并为捕获启用两个表。 若要填充更改表，捕获作业将调用 sp_replcmds。 为事务复制启用数据库，并创建发布。 现在，将为数据库创建日志读取器代理，并删除捕获作业。 日志读取器代理继续从提交到更改表的最后一个日志序列号扫描日志。 这可确保更改表中的数据一致性。 如果在此数据库中禁用了事务复制，则会删除日志读取器代理，并重新创建捕获作业。

注释

当日志读取器代理用于更改数据捕获和事务复制时，复制的更改首先写入分发数据库。 然后，捕获的更改将写入更改表。 这两个操作一起提交。 如果在写入分发数据库时存在任何延迟，则更改显示在更改表中之前，会有相应的延迟。

还原或附加启用了更改数据捕获的数据库

SQL Server 使用以下逻辑来确定在还原或附加数据库后是否仍启用更改数据捕获：

  • 如果数据库还原到具有相同数据库名称的同一服务器，更改数据捕获将保持启用状态。

  • 如果数据库还原到另一台服务器，则默认禁用更改数据捕获，并删除所有相关元数据。

    若要保留更改数据捕获，在还原数据库时请使用 KEEP_CDC 选项。 有关此选项的详细信息，请参阅 RESTORE。

  • 如果数据库已分离并附加到同一服务器或其他服务器，则更改数据捕获将保持启用状态。

  • 如果数据库被附加或还原，并使用 KEEP_CDC 选项到企业版以外的任何版本，操作将被阻止，因为变更数据捕获功能需要 SQL Server 企业版。 显示错误消息 932：

    SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

可以使用 sys.sp_cdc_disable_db 从被还原或附加的数据库中移除变更数据捕获。

更改跟踪

更改跟踪可以捕获表中行已被更改的事实，但无法捕获已更改的数据。 这样，应用程序就可以确定使用从用户表中直接获取的最新行数据更改的行。 因此，与变更数据捕获相比，更改跟踪可以解答的历史问题比较有限。 但是，对于不需要历史信息的应用程序，由于未捕获已更改的数据，存储开销要小得多。 同步跟踪机制用于跟踪更改。 这项设计旨在对 DML 操作的开销降到最低。

插图显示了使用更改跟踪可以受益的同步方案。 在这种情况下，应用程序需要以下信息：自上次同步表以来更改的表中的所有行，以及仅当前行数据。 由于同步机制用于跟踪更改，因此应用程序可以执行双向同步，并可靠地检测可能发生的任何冲突。

更改跟踪的示意图

用于 ADO.NET 的更改跟踪和同步服务

用于 ADO.NET 的同步服务支持数据库之间的同步，提供直观的灵活 API，使你能够生成面向脱机和协作方案的应用程序。 用于 ADO.NET 的同步服务提供用于同步更改的 API，但它实际上不会跟踪服务器或对等数据库中的更改。 可以创建自定义更改跟踪系统，但这通常会带来显著的复杂性和性能开销。 若要跟踪服务器或对等数据库中的更改，建议在 SQL Server 2014 中使用更改跟踪，因为它易于配置并提供高性能跟踪。

有关 ADO.NET 更改跟踪和同步服务的详细信息，请使用以下链接：

  • 关于更改跟踪 (SQL Server)

    介绍更改跟踪，提供更改跟踪工作原理的高级概述，并介绍了更改跟踪如何与其他 SQL Server 数据库引擎功能交互。

  • Microsoft Sync Framework 开发人员中心

    提供同步框架和同步服务的完整文档。 在同步服务的文档中，主题“How to： Use SQL Server Change Tracking”包含详细信息和代码示例。
任务 主题
提供对变更数据捕获的概述。 关于变更数据捕获 (SQL Server)
介绍如何在数据库或表上启用和禁用更改数据捕获。 启用和禁用变更数据捕获 (SQL Server)
介绍如何管理和监视变更数据捕获。 管理和监视变更数据捕获 (SQL Server)
介绍如何处理可供变更数据捕获用户使用的变更数据。 本主题介绍验证 LSN 边界、查询函数和查询函数方案。 处理变更数据 (SQL Server)
概述更改跟踪。 关于更改跟踪 (SQL Server)
介绍如何对数据库或表启用和禁用更改跟踪。 启用和禁用更改跟踪（SQL Server）
介绍如何管理更改跟踪、配置安全性，以及在使用更改跟踪时对存储和性能的影响。 管理更改跟踪 (SQL Server)
描述使用更改跟踪的应用程序如何获取修订、将这些更改应用到其他数据存储，以及更新源数据库。 本主题还描述了在发生故障转移时，更改跟踪所起的作用，以及在这种情况下必须从备份中还原数据库的情形。 使用更改跟踪 (SQL Server)

另请参阅

更改数据捕获函数 （Transact-SQL）
更改跟踪函数 （Transact-SQL）
更改数据捕获存储过程（Transact-SQL）
更改数据捕获表（Transact-SQL）
更改数据捕获相关的动态管理视图（Transact-SQL）