使用Change Tracking实现数据同步


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

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

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

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

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

 --创建测试数据库和表

create databasetest

go

CREATE TABLE[dbo].[A](

    [MAXID] [int] NOT NULL,

    [name] [varchar](20)NULL,

 CONSTRAINT [PK_A] PRIMARY KEYCLUSTERED

(

    [MAXID] ASC

)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY= OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]

) ON[PRIMARY]

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

ALTER DATABASEtest

SET CHANGE_TRACKING=ON

(CHANGE_RETENTION= 2DAYS,AUTO_CLEANUP=ON)

-- CHANGE_RETENTION 指定在数据库中保留更改跟踪信息的最短期限。只有在 AUTO_CLEANUP值为 ON 时，才会删除数据。retention_period是一个整数，用于指定保持期的数值部分。默认保持期为 2天。最短保持期为1分钟。

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

 ALTERTABLEA

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED=ON)

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

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中启用更改跟踪）

 

 

 

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

SELECT *FROMsys.change_tracking_databases

SELECT *FROMsys.change_tracking_tables

SELECT *FROMsys.internal_tablesWHEREparent_object_id=OBJECT_ID('A')

 

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

SELECT CHANGE_TRACKING_CURRENT_VERSION()

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

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('A'))

--CHANGETABLE 返回Table A版本0之后更改跟踪信息（由于没有数据更改所以返回数据为0）

SELECT *FROMCHANGETABLE(CHANGESA,0)asCT

 

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

 insert  into A values (1,'kevin')

insert  into A values (2,'kevin')

go

SELECT CHANGE_TRACKING_CURRENT_VERSION()

go

update A set name='kevinmodify'wheremaxid = 1

go

SELECT CHANGE_TRACKING_CURRENT_VERSION()

 

--CHANGETABLE 返回Table A版本0之后更改跟踪信息

SELECT *FROMCHANGETABLE(CHANGESA,0)asCT

--CHANGETABLE 返回Table A版本2之后更改跟踪信息（比上面返回数据少了）

SELECT *FROMCHANGETABLE(CHANGESA,2)asCT

 

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

DECLARE @PreviousVersion bigint

SET @PreviousVersion= 2

SELECT  *  FROM CHANGETABLE(CHANGESA,@PreviousVersion)ASCTTable

LEFT OUTERJOINAON A.MAXID=CTTable.maxid

GO

 

 

 

---查看字段NAME是否被更新过

DECLARE @PreviousVersion bigint

SET @PreviousVersion= 0

SELECT *,[NameChanged?]=

CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('A'),'name', 'ColumnId'), SYS_CHANGE_COLUMNS)

FROM CHANGETABLE(CHANGESA, @PreviousVersion) AS CTTable

LEFT OUTERJOIN A ON A.MAXID= CTTable.MAXID

WHERE CTTable.SYS_CHANGE_OPERATION='U'

GO

 

CHANGETABLE返回值：

  • SYS_CHANGE_VERSION是一个bigint的字段，表示的当前这行数据最新的更新版本号。
  • SYS_CHANGE_CREATION_VERSION代表的是当前数据行被插入数据表的更新版本号。
  • SYS_CHANGE_OPERATION是一个nchar(1)的字段，I代表Insert，U代表Update，D代表Delete(根据这个值在同步数据的时候最对应的处理)。
  • SYS_CHANGE_COLUMNS代表更新操作影响到了哪些数据列，这个字段的结果是个varbinary(4100)。这个字段只有在表上激活Change Tracking时将TRACK_COLUMN_UPDATED选项设置为ON时才会返回有效值，并且对于INSERT和DELETE操作返回的都是NULL，因为DELETE和INSERT其实影响到了所有数据列，只有UPDATE操作才会返回值，这个字段的值可以通过CHANGE_TRACKING_IS_COLUMN_IN_MASK()函数来解析。
  • SYS_CHANGE_CONTEXT是一个varbinary(128)的字段，这个字段可以记录数据更新的上下文环境信息，不过上下文环境信息需要在提交DML语句时显式地通过WITH CHANGE_TRACK_CONTEXT语句提供。
  • ID是TestCT表的主键字段，因此如果TestCT的主键是内容为(ID, Name)的组合主键，则除了ID外，CHANGETABLE返回结果集中还会多一个Name字段。

注意：

  • 如果执行了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:更改跟踪概述

相关内容