SQL Server 2008數(shù)據(jù)庫變更跟蹤詳解
SQL Server 2008數(shù)據(jù)庫變更跟蹤的相關知識是本文我們主要要介紹的內(nèi)容，本文我們主要介紹了怎樣在數(shù)據(jù)庫、表上激活變更跟蹤，以及怎樣打開各種選項，例如AUTO_CLEANUP、TRACK_COLUMNS_UPDATED，一些應用程序是設計成將數(shù)據(jù)從一個中央數(shù)據(jù)庫拿到一個本地緩存中，舉例來說，到處奔走的銷售人員只是偶爾連接到中央數(shù)據(jù)庫來獲取最新的庫存量數(shù)據(jù)。或者業(yè)務邏輯的重型處理是從產(chǎn)品數(shù)據(jù)庫服務器上脫機下載下來的，本地進行處理然后發(fā)送回產(chǎn)品數(shù)據(jù)庫。這些應用程序需要某種方法來跟蹤數(shù)據(jù)變更，以便知道什么數(shù)據(jù)被改變了。
在SQL Server 2008之前，應用程序開發(fā)人員需要使用觸發(fā)器或時間戳字段來實現(xiàn)定制跟蹤解決方案，并創(chuàng)建額外的表以便跟蹤數(shù)據(jù)變更。而我們都知道，觸發(fā)器是很昂貴的。DML操作中所涉及的每個表通過一個用于觸發(fā)器展示的內(nèi)部函數(shù)遞歸檢查。此外，因為觸發(fā)器是作為啟動它們的事務的一部分來執(zhí)行的，導致它們需要更長的時間來提交，而且引起負載的鎖問題。SQL Server 2008提供了一個新特性，變更跟蹤。
變更跟蹤對于建立單向和雙向的同步應用程序來說是很好的，它被設計為使用用于ADO.NET的Sync Services。應用程序開發(fā)人員可以使用變更跟蹤來同步SQL Server數(shù)據(jù)庫間的任何數(shù)據(jù)，或甚至是在SQL Server和非SQL Server數(shù)據(jù)庫之間。與復制相比，變更跟蹤更適合于開發(fā)人員而不是數(shù)據(jù)庫管理員，因為它提供給開發(fā)人員一個靈活的基礎來使用.NET建立同步應用程序，但是它缺乏存儲過程的支持或內(nèi)置的監(jiān)控工具，比如Replication Monitor。
在一個已有的表上激活變更跟蹤并不需要對表結構進行任何改變。唯一的要求是這個表必須已有一個主鍵。變更跟蹤信息同步地在事務提交時進行了記錄，所以它展示的DML操作順序是正確的。盡管變更跟蹤是和事務同步執(zhí)行的，但是它的執(zhí)行成本和觸發(fā)器相比是非常高的。此外，它只捕捉變更記錄主鍵字段的值，并在變更跟蹤表中記錄這些值。這些值會被連接到基礎表以獲得變更的數(shù)據(jù)。與變更數(shù)據(jù)捕獲相比，它的存儲成本要低，因為在變更跟蹤表中不捕獲非主鍵字段。但是，因為對數(shù)據(jù)記錄來說，能夠被查詢的只是凈變化，而不是中間的變化，變更跟蹤不適合于審計。
對數(shù)據(jù)庫AdventureWorks2008中的表HumanResources.Department建立變更跟蹤以及怎樣查詢變更的數(shù)據(jù)。在你對表激活變更跟蹤之前，你需要對這個數(shù)據(jù)庫激活變更跟蹤。執(zhí)行下面的命令在數(shù)據(jù)庫AdventureWorks2008上激活變更跟蹤。
- ALTER DATABASE AdventureWorks2008
- SET CHANGE_TRACKING = ON
- (AUTO_CLEANUP=ON, CHANGE_RETENTION=8 hours)
- GO
當這個命令運行時，當AUTO_CLEANUP選項設置為打開時，它還激活變更跟蹤信息的自動清理。保留時間設置為至少8小時。每30分鐘會有一個內(nèi)部任務運行一次來刪除舊的事務。SQL Server 也將使用事務信息填充到一個內(nèi)部系統(tǒng)表sys.syscommittab中去。這個表將對每個引起數(shù)據(jù)庫中跟蹤表上數(shù)據(jù)修改的事務具有一條記錄。盡管這個表不能在非DAC連接中查看到，但是這個表中的信息是通過一個DMV——sys.dm_tran_commit_table ——暴露的。下面是這個DMV中的字段。
commit_ts：一個遞增的數(shù)字，它是作為用于每個提交的事務的一個數(shù)據(jù)庫特定的時間戳。
xdes_id：一個用于事務的數(shù)據(jù)庫特定的內(nèi)部ID。
commit_lbn：日志塊的數(shù)目，它包含事務的提交日志記錄。
commit_csn：事務的實例特定的提交順序數(shù)字。
commit_time：事務提交的時間。
如果你在這個數(shù)據(jù)庫上從沒激活過變更跟蹤，那么這個DMV是空的。
1.select * from sys.dm_tran_commit_table 要在表HumanResources.Department上激活變更跟蹤，那么運行下面的命令。
1.ALTER TABLE HumanResources.Department 2. 3.ENABLE CHANGE_TRACKING 4. 5.GO 這個命令創(chuàng)建一個內(nèi)部表，它被用來記錄對表HumanResources.Department所做的變更。這個表的名稱是change_tracking_[tableObjectID]。因為它是一個內(nèi)部表，所以它只能在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]是這個字段的數(shù)據(jù)類型。
因為表HumanResources.Department在主鍵上只有一個字段——DepartmentID，所以在變更跟蹤表中只有一個主鍵字段k_DepartmentID_00000001。sys_change_columns字段可以被用來跟蹤對于每個更新操作哪些字段被修改了。(插入和刪除語句總是改變所有的字段，所以這個字段對于插入和刪除總是NULL)。通過在這個表上激活字段跟蹤，你可以獲得只從這些更新了的字段而來的數(shù)據(jù)。這將限制返回和通過網(wǎng)絡轉移的數(shù)據(jù)的數(shù)量。這還將更有效地合并增加數(shù)據(jù)的變更，因為大規(guī)模字段——例如varbinary(max)和xml——只在它們被更新了才返回。要激活字段跟蹤，設置TRACK_COLUMNS_UPDATED選項。
- ALTER TABLE HumanResources.Department
- ENABLE CHANGE_TRACKING
- WITH (TRACK_COLUMNS_UPDATED = ON)
你還會使用sys_change_context字段來跟蹤表發(fā)生變更的上下文。這個上下文是由生成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
如果你想關閉數(shù)據(jù)庫上的變更跟蹤，那么你需要首先關閉這個數(shù)據(jù)庫中所有表的變更跟蹤。你可以從sys.change_tracking_tables目錄視圖中查找這樣表的列表。下面是一個生成所有ALTER TABLE語句的簡單SQL語句。
- SELECT 'ALTER TABLE ' + object_name(object_id) + ' DISABLE CHANGE_TRACKING;'
- FROM sys.change_tracking_tables
在你關閉了表的變更跟蹤之后，運行下面的命令來關閉數(shù)據(jù)庫的變更跟蹤。
- ALTER DATABASE AdventureWorks2008 SET CHANGE_TRACKING = OFF
關于SQL Server 2008數(shù)據(jù)庫變更跟蹤的相關知識就介紹到這里了，希望本次的介紹能夠對您有所收獲！
【編輯推薦】
