使用Change Tracking实现数据同步

通常的做法是自定义应用程序使用比如触发器、timestamp 列和新表组合来存储跟踪信息，同事还需要自定义清除程序清除过时的数据。

在日常应用中经常会有这样的需求，需要Audit那些数据更改，，或者需要跟踪更改的数据实现对数据的同步。（最常见的应用如数据仓库数据同步，因为数据量巨大，需要将数据的更改同步到数据仓库，这种同步不要求实时）。

通常的做法是自定义应用程序使用比如触发器、timestamp 列和新表组合来存储跟踪信息，同事还需要自定义清除程序清除过时的数据。在SQL Server 2008以后提供了一个功能更改跟踪(Change Tracking).这一种轻量型解决方案（相对于自己自定义的程序，性能要高）为应用程序提供了一种有效的更改跟踪机制。

注意：用程序需要有关所有所做更改的信息以及所更改数据的中间值，则可能适合使用变更数据捕获，而不适合使用更改跟踪。

工作原理：为表配置了更改跟踪后，任何影响该表中的行的 DML 语句都将导致针对每个有所修改的行的更改跟踪信息被记录下来。更改信息会记录到SQL Server内部表中，可以使用查询到内部表，使用CHANGETABLE函数获得数据更改信息。

 下面是启动Change Tracking并且获得更改数据的脚本：

 --创建测试数据库和表

go

   

    20)NULL,

 

(

    [MAXID] ASC

--在数据库和表启动更改跟踪：

2

-- AUTO_CLEANUP = ON在经过指定的保持期后会自动删除更改跟踪信息。

ENABLE CHANGE_TRACKING

如果表没有主键启动更改跟踪会出现下面的错误：

Msg 4997, Level16, State 1, Line 1

Cannot enablechange tracking on table 'A'. Change tracking requires a primary key on thetable. Create a primary key on the table before enabling change tracking.

原因：主键列值是来自所跟踪的并记录更改信息的表中的唯一信息。这些值用于标识发生更改的行。要获取这些行的最新数据，应用程序可以使用主键列值联接源表和所跟踪的表。

（也可以在SSMS中启用更改跟踪）

 

 

 

-- 查询数据库和表更改跟踪信息

 

--返回与上次提交的事务相关联的版本

--指定的表中获取更改跟踪信息的最低版本

0

 

--插入测试数据并且查询跟踪信息最新版本号

 1

2

go

go

1

go

 

0

2

 

--将更改跟踪信息与原表信息关联获得最新数据（这些数据就是我们需要同步的数据）

2

GO

 

 

 

0

GO

 

CHANGETABLE返回值：

注意：

如果执行了TRUNCATE TABLE或者Cleanup进程清理了Change Tracking记录，那么可以通过CHANGE_TRACKING_MIN_VALID_VERSION函数了解可获取的最小更新版本号。如果这个最小版本号比复制目的端记录的最近一次复制成功的最大版本号都高，则意味着源数据库已经丢失了一部份尚未复制的记录，也就代表目的系统需要重新初始化。

对于UPDATE语句，如果在启用表的Change Tracking功能是设置了Track_Column_Updated选项为ON，Change Tracking会记录下UPDATE语句影响到字段信息，这个信息可以通过使用CHANGE_TRACKING_IS_COLUMN_IN_MASK函数解析，比如说要知道TestCT表的Name字段是否在版本2的UPDATE操作中被影响到，可以使用CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('TestCT'), 'Name', 'ColumnId'), SYS_CHANGE_COLUMNS)函数。

 

更多信息参考MSDN:更改跟踪概述