解读SQL Server 2008变更跟踪

一些应用程序是设计成将数据从一个中央数据库拿到一个本地缓存中，举例来说，到处奔走的销售人员只是偶尔连接到中央数据库来获取***的库存量数据。或者业务逻辑的重型处理是从产品数据库服务器上脱机下载下来的，本地进行处理然后发送回产品数据库。这些应用程序需要某种方法来跟踪数据变更，以便知道什么数据被改变了。

在SQL Server 2008之前，应用程序开发人员需要使用触发器或时间戳字段来实现定制跟踪解决方案，并创建额外的表以便跟踪数据变更。而我们都知道，触发器是很昂贵的。DML操作中所涉及的每个表通过一个用于触发器展示的内部函数递归检查。此外，因为触发器是作为启动它们的事务的一部分来执行的，导致它们需要更长的时间来提交，而且引起负载的锁问题。SQL Server 2008提供了一个新特性，变更跟踪。

变更跟踪对于建立单向和双向的同步应用程序来说是很好的，它被设计为使用用于ADO.NET的Sync Services。应用程序开发人员可以使用变更跟踪来同步SQL Server数据库间的任何数据，或甚至是在SQL Server和非SQL Server数据库之间。与复制相比，变更跟踪更适合于开发人员而不是数据库管理员，因为它提供给开发人员一个灵活的基础来使用.NET建立同步应用程序，但是它缺乏存储过程的支持或内置的监控工具，比如Replication Monitor。

在一个已有的表上激活变更跟踪并不需要对表结构进行任何改变。唯一的要求是这个表必须已有一个主键。变更跟踪信息同步地在事务提交时进行了记录，所以它展示的DML操作顺序是正确的。尽管变更跟踪是和事务同步执行的，但是它的执行成本和触发器相比是非常高的。此外，它只捕捉变更记录主键字段的值，并在变更跟踪表中记录这些值。这些值会被连接到基础表以获得变更的数据。与变更数据捕获相比，它的存储成本要低，因为在变更跟踪表中不捕获非主键字段。但是，因为对数据记录来说，能够被查询的只是净变化，而不是中间的变化，变更跟踪不适合于审计。

本篇文章中，我们将介绍怎样对数据库AdventureWorks2008中的表HumanResources.Department建立变更跟踪以及怎样查询变更的数据。在你对表激活变更跟踪之前，你需要对这个数据库激活变更跟踪。执行下面的命令在数据库AdventureWorks2008上激活变更跟踪。

ALTER DATABASE AdventureWorks2008   
SET CHANGE_TRACKING = ON   
(AUTO_CLEANUP=ON, CHANGE_RETENTION=8 hours)   
GO  

当这个命令运行时，当AUTO_CLEANUP选项设置为打开时，它还激活变更跟踪信息的自动清理。保留时间设置为至少8小时。每30分钟会有一个内部任务运行一次来删除旧的事务。SQL Server 也将使用事务信息填充到一个内部系统表sys.syscommittab中去。这个表将对每个引起数据库中跟踪表上数据修改的事务具有一条记录。尽管这个表不能在非DAC连接中查看到，但是这个表中的信息是通过一个DMV——sys.dm_tran_commit_table ——暴露的。下面是这个DMV中的字段。

commit_ts：一个递增的数字，它是作为用于每个提交的事务的一个数据库特定的时间戳。

xdes_id：一个用于事务的数据库特定的内部ID。

commit_lbn：日志块的数目，它包含事务的提交日志记录。

commit_csn：事务的实例特定的提交顺序数字。

commit_time：事务提交的时间。

如果你在这个数据库上从没激活过变更跟踪，那么这个DMV是空的。

select * from sys.dm_tran_commit_table 

要在表HumanResources.Department上激活变更跟踪，那么运行下面的命令。

ALTER TABLE HumanResources.Department  
 
ENABLE CHANGE_TRACKING  
 
GO  

这个命令创建一个内部表，它被用来记录对表HumanResources.Department所做的变更。这个表的名称是change_tracking_[tableObjectID]。因为它是一个内部表，所以它只能在DAC连接中查看。在我们的例子中，表HumanResources.Department的对象ID是757577737，因此这个表的名称是“change_tracking_757577737”。这个表如下所示是空的，这是因为我们没有对表HumanResources.Department做任何修改，下面是这个表的字段。

sys_change_xdes_id：修改记录的事务的事务ID。

sys_change_xdes_id_seq：事务中操作的顺序标识。

sys_change_operation：影响记录的操作类型：插入、更新或删除。

sys_change_columns：修改了的字段的列表(用于更新，只有当字段跟踪被激活时)。

sys_change_context：在DML操作过程中使用WITH CHANGE_ TRACKING_CONTEXT选项所提供的应用程序特定的上下文信息。

k_[name]_[ord]：从目标表获得的主键字段。[name]是主键字段的名称，[ord]是主键中的顺序位置，而[type]是这个字段的数据类型。

因为表HumanResources.Department在主键上只有一个字段——DepartmentID，所以在变更跟踪表中只有一个主键字段k_DepartmentID_00000001。sys_change_columns字段可以被用来跟踪对于每个更新操作哪些字段被修改了。(插入和删除语句总是改变所有的字段，所以这个字段对于插入和删除总是NULL)。通过在这个表上激活字段跟踪，你可以获得只从这些更新了的字段而来的数据。这将限制返回和通过网络转移的数据的数量。这还将更有效地合并增加数据的变更，因为大规模字段——例如varbinary(max)和xml——只在它们被更新了才返回。要激活字段跟踪，设置TRACK_COLUMNS_UPDATED选项。

ALTER TABLE HumanResources.Department  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON) 

你还会使用sys_change_context字段来跟踪表发生变更的上下文。这个上下文是由生成DML语句的客户端提供的。它可以是一个常量，例如一个应用ID。一个示例更新语句如下所示。

DECLARE @originator_id varbinary(128)   
SET @originator_id = CAST('MyApplication' AS varbinary(128))   
WITH CHANGE_TRACKING_CONTEXT (@originator_id)   
UPDATE HumanResources.Department   
SET GroupName='Accounting'   
WHERE DepartmentID=10  

如果你想关闭数据库上的变更跟踪，那么你需要首先关闭这个数据库中所有表的变更跟踪。你可以从sys.change_tracking_tables目录视图中查找这样表的列表。下面是一个生成所有ALTER TABLE语句的简单SQL语句。

SELECT 'ALTER TABLE ' + object_name(object_id) + ' DISABLE CHANGE_TRACKING;'  
FROM sys.change_tracking_tables 

在你关闭了表的变更跟踪之后，运行下面的命令来关闭数据库的变更跟踪。

ALTER DATABASE AdventureWorks2008 SET CHANGE_TRACKING = OFF 

【编辑推荐】

1. SQL Server 2008行数据和页数据压缩解密
2. 图解SQL Server 2008 R2主数据服务安装
3. 理解SQL Server 2008索引的存储结构
4. SQL Server移动系统数据库开发详解
5. SQL Server 2008 R2十大新特性解析