使用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:變更追蹤概述