解讀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
【編輯推薦】
