解讀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 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-630630/，如需轉載，請註明出處，否則將追究法律責任。