SQL Server 2008跟踪企业数据库中的更改

对开发人员来说，SQL Server 中的一个难题是跟踪数据库中哪些数据发生了更改。但更大的挑战是设计出一套既不会严重影响工作负荷性能，又不难创建、实现和管理的简单解决方案。那为什么要这么大费周章跟踪更改呢？跟踪更改真的值得下这么多功夫吗？两个经常引用的典型示例是：支持数据仓库的更新，以及支持异构、偶尔连接的系统进行同步处理。

数据仓库通常具有 Online Transaction Processing (OLTP) 数据库中表的某些表示，但是表架构实际上可能截然不同。这表示需要有 ETL（提取、转换、加载）过程将数据从 OLTP 数据库移动到数据仓库。

观看 Paul Randal 向您演示如何使用 SQL Server 2008 中全新的“更改数据捕获”功能来跟踪数据库中的更改。

我可以考虑用三种方法执行此操作。第一种是定期刷新整个数据仓库。显然，如果数据量太大，这种方法是不切实际的，而且也意味着对数据仓库的更新并不连续。第二种方法是在 OLTP 数据库中使用分区架构，只对自上次 ETL 过程以来添加的数据执行 ETL 过程。此方法只能用于数据插入的工作，不能用于更新或删除工作，而且需要复杂的机制来管理分区边界定义和切换分区。第三种方法是跟踪对 OLTP 数据的更改，并且只使用已更改的数据来执行 ETL 过程。就数据量来说，这是最有效的方法。

移动设备在如今的企业环境中无所不在，换句话说，处理偶尔连接的系统是必要的。就数据库系统来说，问题在于如何有效地更新不常连接的设备上的数据存储，特别是当数据存储本身可能很小而且架构可能与主数据库截然不同时。

假设有一名移动销售代表，她负责超大型产品目录的一部分。她每晚都会将自己的手持设备连接到主数据库来下载最新的数据 — 对该部分产品目录的所有更改，经过简化以便存储在手持设备上。数据传输应该尽可能高效。

您可以让数据库系统准备要下载到设备的整个产品目录的相关部分，并且让设备进行下载。换句话说，每次设备连接时都会下载所有数据，即便数据没有更改也一样。这显然是效率低下的方法。

另一种方法是让数据库系统跟踪产品目录的相关部分发生的更改。然后在手持设备连接时，它会要求获得自上次连接以来发生更改的数据。在这种解决方案中，数据库系统只需要准备数据的子集，而且下载也尽可能高效。

跟踪更改的另一个原因是要支持审核，这在当今是必不可少的。审核除了跟踪所做的更改之外，还会跟踪更改时间和更改者。这对于完整审核记录的持久性、安全性和正确性都有严谨的规范，无疑将事情提升到了另一个级别。

SQL Server 2008 中针对跟踪数据更改而设计的技术并非旨在支持审核，然而，SQL Server 2008 提供的一项名为 SQL Server Audit 的新功能则是专为审核而设计的。在 2008 年 4 月出版的《Technet 杂志》中，Rick Byham 发表了“SQL Server 2008：安全性”一文，讨论了 SQL Server 审核功能（文章的地址为 technet.microsoft.com/magazine/cc434691）。

您可以看到，跟踪数据的更改有很多吸引人的理由。因此，重要的问题是进行跟踪的最佳方法是什么？

如何在 SQL Server 2005 中跟踪更改

SQL Server 2005 及其早期版本中并没有简单、内置的解决方案。所以，对于这些平台，开发人员必须为应用程序创建自定义解决方案，通常包括时间戳列、DML（数据操作语言）触发器和其他表。但这些解决方案导致了各种潜在问题。例如：

添加时间戳列会使表架构发生更改（从而在存储过程和其他代码中产生连锁影响）。

DML 触发器是事务的隐含部分（事务中包含的 DML 可以触发该触发器），因此它的执行时间会增加事务的长度。触发器越复杂，执行所花的时间越长，对工作负荷性能就越不利。用于跟踪更改的 DML 触发器必须处理插入和删除的表，以搜集所有更改，然后将其插入另一跟踪表。

跟踪表必须以某种方式来管理，才能避免增长失控，而这可能需要您创建类似于代理作业的内容来定期删除旧数据。

在 SQL Server 2008 中跟踪更改的更简单方法

SQL Server 2008 引入了两种新技术，使得跟踪数据更改更加容易：更改跟踪和更改数据捕获。这两种功能都可以跟踪发生更改的数据（也可以使用插入、更新或删除作业来准确跟踪数据的更改过程），而且有了它们，完全不需要自定义解决方案。除了这些相似性之外，这两种功能的机制和具体的跟踪内容其实大相径庭。

更改数据捕获使用的是异步机制，可以跟踪表（或是表中一组定义的数据列）发生的所有更改，包括列值本身。这是专为我先前介绍的数据仓库 ETL 过程等情形设计的。

图 1 说明了不同时间段获取的更改数据。更改数据捕获机制会将更改的数据提取到一组表，最新的更改在表的最上方。然后，ETL 过程对存储更改数据的表查询在固定时段内发生的所有更改。这套机制允许 ETL 过程限制每批必须获取的数据量。

图 1 不同时间段获取的历史更改数据（单击可获得大图）

另一方面，更改跟踪则是采用同步机制，只能跟踪表中已更改的特定数据行（或者经过更改的数据列）。这是为了解决我先前介绍的偶尔连接的系统方案所遇到的问题而设计的。图 2 说明了这种方法。

图 2 使用更改跟踪数据的偶尔连接的系统（单击可获得大图）

这两种功能都会增加 I/O 和记录，自定义解决方案也一样 — 更改数据必须存储在某个位置。这两种功能与自定义解决方案可能的区别在于，用于存储更改数据的表必须与要跟踪的表位于相同的数据库中。这表示所有更改数据都将包含在备份中，而可能通过日志传送或数据库镜像在网络上传输。

就程序开发而言，这两种功能应该可以明显降低跟踪更改的复杂性。因为无论是哪一种技术，都不需要表架构更改或触发器。两种技术都具有可配置的自动清除过程，可依据事务提交时间对更改排序，并且提供内置函数来检索更改信息。

从管理的角度来看，每种方法各有其优缺点。与任何技术一样，在开发和部署使用这些功能的解决方案之前，您必须掌握很多信息。在本文的其余部分，我将简要介绍这些功能，稍微讨论一下其工作原理，以及在用于生产之前需要考虑的重点。

更改数据捕获的工作原理

更改数据捕获并不会涉及更改要跟踪的表中的事务。相反，插入、更新和删除操作像平常一样写入事务日志中，并且定期从日志中搜集。搜集由 SQL 代理日志读取器作业执行，而搜集到的结果会存储在一个称为更改表的单独表中。随后，可使用两个函数之一来查询更改表以获取更改数据。更改表与两个函数的组合称为捕获实例。图 3 显示了使用更改数据捕获来驱动数据仓库 ETL 过程的数据流。

启用更改数据捕获的过程分为两个阶段。首先，系统管理员固定服务器角色的成员必须使用 sys.sp_cdc_enable_db 为数据库启用更改数据捕获。然后，db_owner 固定服务器角色的成员必须使用 sys.sp_cdc_enable_table 在特定表上启用更改数据捕获。因为如果更改数据捕获配置不当，可能会占用大量磁盘空间，所以存在这些安全性要求。显然，表所有者不能启用该功能，以免导致占用额外的磁盘空间，给数据库管理员带来麻烦。

如果为数据库启用了更改数据捕获，可以在数据库中加入一些项目，包括新的架构（称为 cdc）、一些元数据表，以及用于捕获数据定义语言 (DDL) 事件的触发器（我认为，支持获得对表的 DDL 更改的列表是一项出色的功能）。

启用更改数据捕获也会创建表的捕获实例（更改表和最多两个函数）来返回更改表。更改表名称跟捕获实例的名称一样，只不过追加了 _CT。第一个函数通常都会创建，而且可以用来返回更改表中的更改数据。第二个函数则会在指定允许净更改的选项时才会创建。这表示只会返回所有捕获更改的最终结果，而不是第一个函数返回的所有中间更改。这两个函数的名称分别为 fn_cdc_get_all_changes_ 和 fn_cdc_get_net_changes_，再加上捕获实例名称。请注意，与更改跟踪功能类似，这项功能要求表必须具有主键或其他唯一索引。

当您处理数据库中的第一个表以启用更改数据捕获时，可能会创建两个 SQL 代理作业：捕获作业和清除作业。之所以说“可能会创建”，是因为捕获作业与在事务复制中用来搜集事务的是同一个作业。如果已配置事务复制，则只会创建清除作业，并会将现有的日志读取器作业用作捕获作业。这样的好处是如果拥有两个记录读取器作业，很快就会导致日志的争用问题，从而降低性能。无论是哪种情况，如果要使用更改数据捕获，都必须运行 SQL 代理。

日志读取器中的逻辑会自动处理启用和禁用表的更改数据捕获，并适当更改从事务日志中搜集到的内容。此处特别需要注意，一旦启用更改数据捕获，事务日志就会像对待事务复制一样 — 日志只有等到日志读取器处理之后才会截断。这表示检查点操作（即使在 SIMPLE 恢复模式中）也要等到日志读取器处理日志之后才将其截断。

另外，如果使用 BULK_LOGGED 恢复模式来减少日志记录，则除了索引创建/舍弃/重建操作外，更改数据捕获将强制完整记录所有项目。如果您从未遇到过这类行为，请注意这可能会导致事务日志过大，特别是如果更改了捕获任务默认值而不经常处理日志的话更是如此。

默认情况下，捕获作业会连续运行，每五秒扫描一次日志，最多可处理日志中的 500 个事务。另外，默认情况下清理作业也会在每天凌晨两点运行，并从更改表中删除三天前的所有更改数据项。您可以使用 sys.sp_cdc_change_job 过程来更改这些配置，但更改值在您使用 sys.sp_cdc_stop_job 和 sys.sp_cdc_start_job 重新启动作业后才会生效。

虽然日志读取器进程对系统性能的影响通常很小，但 OLTP 系统还是有可能承载着大量更改数据而不堪重负，即使多加一个日志读取器进程都可能引起事务日志争用。真正的争用原因是磁头必须在事务写入日志的点与日志读取器进程读取日志的点之间来回移动。在这种情况下，可能必须更改捕获作业的运行频率，以确保 OLTP 性能不受影响。然而，这会产生典型的磁盘空间与效率的折衷 — 日志会在捕获作业处理它之前持续增长。

如果更改清除作业频率或更改数据保留周期，也会发生同样的问题 — 更改表会在更改数据清除之前持续增长。这需要在设计时全面考虑要跟踪哪些内容，以及其保留时限。此处要考虑的重点包括：

捕获实例所需的数据列列表。捕获的数据列越多，插入更改表中的更改数据就越多。

更改表使用的磁盘空间量。

使用更改数据的进程的运行频率。请记住，数据要使用之后才能删除。

清除进程的运行频率 — 生成的更改数据有可能太多，以致于删除它的清除进程只能安排在周末运行，因为它可能生成了太多的事务日志。

您可以将更改数据捕获设置为只跟踪表的所有更改，或跟踪表中的数据列子集。如果有些不重要的数据列是非常宽的 varchar 数据列或大型二进制对象 (BLOB) 数据列（如文字、图像或 XML），使用子集可能很有用，否则，更改表所使用的空间可能很快增大到难以处理的地步。

由于磁盘空间使用量有可能增加，请在启用更改数据捕获时设置更改表的文件组位置。这使得管理基本磁盘空间更为轻松，也意味着所有更改数据可存储在比主数据库价格便宜的 RAID 级别卷中。另外，虽然清除作业设置可应用到所有捕获实例，但如果磁盘空间出现问题，可随时分别清除单独的捕获实例。您可以在捕获表上使用 sp_spaceused 轻松监视磁盘空间的使用情况。

实际写入更改表的数据行中包含事务的元数据（提交日志序号或 LSN）、发生更改的事务内部的顺序、操作的内容、发生更改的数据列的位掩码，以及实际的数据列值。

如果启用更改数据捕获，DDL 更改将没有限制。然而，如果添加或删除数据列，它们可能会对收集到的更改数据产生影响。如果删除跟踪的数据列，捕获实例中所有后续项目在该数据列中都会有 NULL。若添加数据列，捕获实例会将其忽略。换句话说，捕获实例在创建时就已定型。

若有必要更改数据列，可为表再创建一个捕获实例（每个表最多可创建两个实例），并允许更改数据的用户迁移到新的表架构。但执行此操作时应该特别小心，因为如果跟踪表有两个捕获实例，磁盘空间、I/O 和日志记录也会加倍。

简而言之，更改是使用我先前介绍的函数从更改表中检索到的。函数包含开始 LSN 和结束 LSN，而且还提供了其他函数以允许您将正常时间转换成 LSN。在检索更新时，您甚至可以指定是要查看更新前后的值，还是只查看更新前的值。www.technetmagazine.com/video 上提供了我使用更改数据捕获的截屏视频。

更改跟踪的工作原理

前面提到，更改跟踪是一种同步处理程序，而且比更改数据捕获简单得多。它是在要跟踪的表中进行更改的事务的一部分，而数据行的更改会在另外一个表中跟踪。该表正是所谓的内部表，您不能控制其名称或存储位置。我认为这没有什么问题，因为与更改数据捕获所用的更改表相比，这个表中的数据应该少得多。但还是有可能产生磁盘空间问题，稍后我将对此进行解释。

更改跟踪以同步方式完成，这意味着在更改要跟踪的表的每项事务中会额外进行一些处理工作。这对性能的影响与表中存在非群集索引而必须对表更新每项更改的情况类似。事务在根据内部 sys.syscommittab 表中的数据列提交时，本身也会被跟踪。

更改跟踪可以使用标准 ALTER DATABASE 和 ALTER TABLE 语法来启用和禁用，而且它遵守的模型与更改数据捕获相同，也就是必须在表级别之前在数据库级别上启用。操作的顺序如下所示：

ALTER DATABASE AdventureWorks2000 SET CHANGE_TRACKING = ON 
  (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);  
GO  
USE AdventureWorks2000;  
GO  
ALTER TABLE Person.Person ENABLE CHANGE_TRACKING  
  WITH (TRACK_COLUMNS_UPDATED = ON);  
GO 

在数据库和表级别上启用更改跟踪所需的权限也与启用更改数据捕获不同：分别是 db_owner 和表所有者。在表级别上启用更改跟踪时，可设置保留期以及是否自动清除更改数据。默认的保留期为 2 天，最长为 90 天，最短为一分钟。

默认情况下也会打开自动清除。如果更改这些设置，您必须评估我在讨论更改数据捕获时所提到的折衷 — 主要是在磁盘空间和性能与应用程序需要之间进行权衡。

默认情况下，每个数据列中捕获的内容正是发生更改的内容。这是通过以下操作完成的：记下更改的数据列的主键（即表上的更改跟踪要求它必须具有主键）、版本号（数据库一旦启用更改跟踪，就会产生版本号，从而允许排列操作的顺序）以及进行更改的操作类型等。您也可以选择是否跟踪哪些数据列发生更改，每个更改的数据列需要 4 个字节。

磁盘空间监视与更改跟踪稍有不同，因为更改数据是存储在内部表中。若要找到使用的内部表的名称，使用 sys.internal_tables 系统目录视图即可：

SELECT [name] FROM sys.internal_tables  
  WHERE [internal_type_desc] = 'CHANGE_TRACKING';  
GO 

然后，将名称传递到 sp_spaceused 来查看所用的磁盘空间。

与更改数据捕获不同的是，启用更改跟踪对 DDL 有些限制，而这些限制可能会应用于要跟踪的表中。最明显的限制是无论采取什么方式都无法更改主键。值得一提的另一个限制是，如果涉及的表启用了更改跟踪，ALTER TABLE SWITCH 将失败。这很可能是因为：对于分别要从已跟踪更改的已分区表转换出的分区，或者要转换成已分区表的已跟踪更改表来说，自动开始或删除更改跟踪没有意义。

更改是使用新的 CHANGE­TABLES (CHANGES …) 函数从内部更改表中检索到的。这会采用它上次所用的更改跟踪表名称再加上版本号，返回自上次以来发生更改的所有数据行的相关信息。可使用各种不同的函数查找目前和最旧的有效版本。应用程序随后可使用返回的信息来查询要跟踪更改的表，以获得实际的列值。这个程序当然需要多个步骤 — 您获得当前版本，使用该版本来查询更改跟踪，然后查询实际的表来获取与该版本相对应的列数据。

在经常更改的系统上，除非版本、更改数据和实际的列数据保持某种视图不变，否则可能会得到不一致或不正确的结果。为此，您可以使用快照隔离，并将包含多个步骤的过程封装在一个显式事务中。这种作法虽然效果不错，但也有潜在的缺点。快照隔离可能会影响任务负载的性能，而且它也会影响 tempdb 的性能和空间使用情况。有关此问题的详细信息，请访问 technet.microsoft.com/library/cc280358。

结束语

图 4 并排比较了更改跟踪与更改数据，以便您进一步了解 DBA 关注的主要差异。从表中您可以看到，与更改跟踪相比，更改数据捕获属于比较复杂的功能。由于在要跟踪的表中包含 BLOB 数据列或非常宽的数据行时，跟踪表的大小可能会快速增长，所以必须更加谨慎地决定要跟踪的内容。此外，也可能出现事务日志管理问题，因为日志必须等到日志读取器从日志搜集到记录后才会截断。图 4 更改跟踪与更改数据捕获之间的比较