對開發人員來說，SQL Server 中的一個難題是跟蹤數據庫中哪些數據發生了更改。但更大的挑戰是設計出一套既不會嚴重影響工作負荷性能，又不難創建、實現和管理的簡單解決方案。那為什么要這么大費周章跟蹤更改呢？跟蹤更改真的值得下這么多功夫嗎？兩個經常引用的典型示例是：支持數據倉庫的更新，以及支持異構、偶爾連接的系統進行同步處理。

數據倉庫通常具有 Online Transaction Processing (OLTP) 數據庫中表的某些表示，但是表架構實際上可能截然不同。這表示需要有 ETL（提取、轉換、加載）過程將數據從 OLTP 數據庫移動到數據倉庫。

觀看 Paul Randal 向您演示如何使用 SQL Server 2008 中全新的“更改數據捕獲”功能來跟蹤數據庫中的更改。

我可以考慮用三種方法執行此操作。第一種是定期刷新整個數據倉庫。顯然，如果數據量太大，這種方法是不切實際的，而且也意味著對數據倉庫的更新并不連續。第二種方法是在 OLTP 數據庫中使用分區架構，只對自上次 ETL 過程以來添加的數據執行 ETL 過程。此方法只能用于數據插入的工作，不能用于更新或刪除工作，而且需要復雜的機制來管理分區邊界定義和切換分區。第三種方法是跟蹤對 OLTP 數據的更改，并且只使用已更改的數據來執行 ETL 過程。就數據量來說，這是最有效的方法。

移動設備在如今的企業環境中無所不在，換句話說，處理偶爾連接的系統是必要的。就數據庫系統來說，問題在于如何有效地更新不常連接的設備上的數據存儲，特別是當數據存儲本身可能很小而且架構可能與主數據庫截然不同時。

假設有一名移動銷售代表，她負責超大型產品目錄的一部分。她每晚都會將自己的手持設備連接到主數據庫來下載最新的數據 — 對該部分產品目錄的所有更改，經過簡化以便存儲在手持設備上。數據傳輸應該盡可能高效。

您可以讓數據庫系統準備要下載到設備的整個產品目錄的相關部分，并且讓設備進行下載。換句話說，每次設備連接時都會下載所有數據，即便數據沒有更改也一樣。這顯然是效率低下的方法。

另一種方法是讓數據庫系統跟蹤產品目錄的相關部分發生的更改。然后在手持設備連接時，它會要求獲得自上次連接以來發生更改的數據。在這種解決方案中，數據庫系統只需要準備數據的子集，而且下載也盡可能高效。

跟蹤更改的另一個原因是要支持審核，這在當今是必不可少的。審核除了跟蹤所做的更改之外，還會跟蹤更改時間和更改者。這對于完整審核記錄的持久性、安全性和正確性都有嚴謹的規范，無疑將事情提升到了另一個級別。

SQL Server 2008 中針對跟蹤數據更改而設計的技術并非旨在支持審核，然而，SQL Server 2008 提供的一項名為 SQL Server Audit 的新功能則是專為審核而設計的。在 2008 年 4 月出版的《Technet 雜志》中，Rick Byham 發表了“SQL Server 2008：安全性”一文，討論了 SQL Server 審核功能（文章的地址為 technet.microsoft.com/magazine/cc434691）。

您可以看到，跟蹤數據的更改有很多吸引人的理由。因此，重要的問題是進行跟蹤的最佳方法是什么？

如何在 SQL Server 2005 中跟蹤更改

SQL Server 2005 及其早期版本中并沒有簡單、內置的解決方案。所以，對于這些平臺，開發人員必須為應用程序創建自定義解決方案，通常包括時間戳列、DML（數據操作語言）觸發器和其他表。但這些解決方案導致了各種潛在問題。例如：

添加時間戳列會使表架構發生更改（從而在存儲過程和其他代碼中產生連鎖影響）。

DML 觸發器是事務的隱含部分（事務中包含的 DML 可以觸發該觸發器），因此它的執行時間會增加事務的長度。觸發器越復雜，執行所花的時間越長，對工作負荷性能就越不利。用于跟蹤更改的 DML 觸發器必須處理插入和刪除的表，以搜集所有更改，然后將其插入另一跟蹤表。

跟蹤表必須以某種方式來管理，才能避免增長失控，而這可能需要您創建類似于代理作業的內容來定期刪除舊數據。

在 SQL Server 2008 中跟蹤更改的更簡單方法

SQL Server 2008 引入了兩種新技術，使得跟蹤數據更改更加容易：更改跟蹤和更改數據捕獲。這兩種功能都可以跟蹤發生更改的數據（也可以使用插入、更新或刪除作業來準確跟蹤數據的更改過程），而且有了它們，完全不需要自定義解決方案。除了這些相似性之外，這兩種功能的機制和具體的跟蹤內容其實大相徑庭。

更改數據捕獲使用的是異步機制，可以跟蹤表（或是表中一組定義的數據列）發生的所有更改，包括列值本身。這是專為我先前介紹的數據倉庫 ETL 過程等情形設計的。

圖 1 說明了不同時間段獲取的更改數據。更改數據捕獲機制會將更改的數據提取到一組表，最新的更改在表的最上方。然后，ETL 過程對存儲更改數據的表查詢在固定時段內發生的所有更改。這套機制允許 ETL 過程限制每批必須獲取的數據量。

圖 1 不同時間段獲取的歷史更改數據（單擊可獲得大圖）

另一方面，更改跟蹤則是采用同步機制，只能跟蹤表中已更改的特定數據行（或者經過更改的數據列）。這是為了解決我先前介紹的偶爾連接的系統方案所遇到的問題而設計的。圖 2 說明了這種方法。

圖 2 使用更改跟蹤數據的偶爾連接的系統（單擊可獲得大圖）

這兩種功能都會增加 I/O 和記錄，自定義解決方案也一樣 — 更改數據必須存儲在某個位置。這兩種功能與自定義解決方案可能的區別在于，用于存儲更改數據的表必須與要跟蹤的表位于相同的數據庫中。這表示所有更改數據都將包含在備份中，而可能通過日志傳送或數據庫鏡像在網絡上傳輸。

就程序開發而言，這兩種功能應該可以明顯降低跟蹤更改的復雜性。因為無論是哪一種技術，都不需要表架構更改或觸發器。兩種技術都具有可配置的自動清除過程，可依據事務提交時間對更改排序，并且提供內置函數來檢索更改信息。

從管理的角度來看，每種方法各有其優缺點。與任何技術一樣，在開發和部署使用這些功能的解決方案之前，您必須掌握很多信息。在本文的其余部分，我將簡要介紹這些功能，稍微討論一下其工作原理，以及在用于生產之前需要考慮的重點。

更改數據捕獲的工作原理

更改數據捕獲并不會涉及更改要跟蹤的表中的事務。相反，插入、更新和刪除操作像平常一樣寫入事務日志中，并且定期從日志中搜集。搜集由 SQL 代理日志讀取器作業執行，而搜集到的結果會存儲在一個稱為更改表的單獨表中。隨后，可使用兩個函數之一來查詢更改表以獲取更改數據。更改表與兩個函數的組合稱為捕獲實例。圖 3 顯示了使用更改數據捕獲來驅動數據倉庫 ETL 過程的數據流。

啟用更改數據捕獲的過程分為兩個階段。首先，系統管理員固定服務器角色的成員必須使用 sys.sp_cdc_enable_db 為數據庫啟用更改數據捕獲。然后，db_owner 固定服務器角色的成員必須使用 sys.sp_cdc_enable_table 在特定表上啟用更改數據捕獲。因為如果更改數據捕獲配置不當，可能會占用大量磁盤空間，所以存在這些安全性要求。顯然，表所有者不能啟用該功能，以免導致占用額外的磁盤空間，給數據庫管理員帶來麻煩。

如果為數據庫啟用了更改數據捕獲，可以在數據庫中加入一些項目，包括新的架構（稱為 cdc）、一些元數據表，以及用于捕獲數據定義語言 (DDL) 事件的觸發器（我認為，支持獲得對表的 DDL 更改的列表是一項出色的功能）。

啟用更改數據捕獲也會創建表的捕獲實例（更改表和最多兩個函數）來返回更改表。更改表名稱跟捕獲實例的名稱一樣，只不過追加了 _CT。第一個函數通常都會創建，而且可以用來返回更改表中的更改數據。第二個函數則會在指定允許凈更改的選項時才會創建。這表示只會返回所有捕獲更改的最終結果，而不是第一個函數返回的所有中間更改。這兩個函數的名稱分別為 fn_cdc_get_all_changes_ 和 fn_cdc_get_net_changes_，再加上捕獲實例名稱。請注意，與更改跟蹤功能類似，這項功能要求表必須具有主鍵或其他唯一索引。

當您處理數據庫中的第一個表以啟用更改數據捕獲時，可能會創建兩個 SQL 代理作業：捕獲作業和清除作業。之所以說“可能會創建”，是因為捕獲作業與在事務復制中用來搜集事務的是同一個作業。如果已配置事務復制，則只會創建清除作業，并會將現有的日志讀取器作業用作捕獲作業。這樣的好處是如果擁有兩個記錄讀取器作業，很快就會導致日志的爭用問題，從而降低性能。無論是哪種情況，如果要使用更改數據捕獲，都必須運行 SQL 代理。

日志讀取器中的邏輯會自動處理啟用和禁用表的更改數據捕獲，并適當更改從事務日志中搜集到的內容。此處特別需要注意，一旦啟用更改數據捕獲，事務日志就會像對待事務復制一樣 — 日志只有等到日志讀取器處理之后才會截斷。這表示檢查點操作（即使在 SIMPLE 恢復模式中）也要等到日志讀取器處理日志之后才將其截斷。

另外，如果使用 BULK_LOGGED 恢復模式來減少日志記錄，則除了索引創建/舍棄/重建操作外，更改數據捕獲將強制完整記錄所有項目。如果您從未遇到過這類行為，請注意這可能會導致事務日志過大，特別是如果更改了捕獲任務默認值而不經常處理日志的話更是如此。

默認情況下，捕獲作業會連續運行，每五秒掃描一次日志，最多可處理日志中的 500 個事務。另外，默認情況下清理作業也會在每天凌晨兩點運行，并從更改表中刪除三天前的所有更改數據項。您可以使用 sys.sp_cdc_change_job 過程來更改這些配置，但更改值在您使用 sys.sp_cdc_stop_job 和 sys.sp_cdc_start_job 重新啟動作業后才會生效。

雖然日志讀取器進程對系統性能的影響通常很小，但 OLTP 系統還是有可能承載著大量更改數據而不堪重負，即使多加一個日志讀取器進程都可能引起事務日志爭用。真正的爭用原因是磁頭必須在事務寫入日志的點與日志讀取器進程讀取日志的點之間來回移動。在這種情況下，可能必須更改捕獲作業的運行頻率，以確保 OLTP 性能不受影響。然而，這會產生典型的磁盤空間與效率的折衷 — 日志會在捕獲作業處理它之前持續增長。

如果更改清除作業頻率或更改數據保留周期，也會發生同樣的問題 — 更改表會在更改數據清除之前持續增長。這需要在設計時全面考慮要跟蹤哪些內容，以及其保留時限。此處要考慮的重點包括：

捕獲實例所需的數據列列表。捕獲的數據列越多，插入更改表中的更改數據就越多。

更改表使用的磁盤空間量。

使用更改數據的進程的運行頻率。請記住，數據要使用之后才能刪除。

清除進程的運行頻率 — 生成的更改數據有可能太多，以致于刪除它的清除進程只能安排在周末運行，因為它可能生成了太多的事務日志。

您可以將更改數據捕獲設置為只跟蹤表的所有更改，或跟蹤表中的數據列子集。如果有些不重要的數據列是非常寬的 varchar 數據列或大型二進制對象 (BLOB) 數據列（如文字、圖像或 XML），使用子集可能很有用，否則，更改表所使用的空間可能很快增大到難以處理的地步。

由于磁盤空間使用量有可能增加，請在啟用更改數據捕獲時設置更改表的文件組位置。這使得管理基本磁盤空間更為輕松，也意味著所有更改數據可存儲在比主數據庫價格便宜的 RAID 級別卷中。另外，雖然清除作業設置可應用到所有捕獲實例，但如果磁盤空間出現問題，可隨時分別清除單獨的捕獲實例。您可以在捕獲表上使用 sp_spaceused 輕松監視磁盤空間的使用情況。

實際寫入更改表的數據行中包含事務的元數據（提交日志序號或 LSN）、發生更改的事務內部的順序、操作的內容、發生更改的數據列的位掩碼，以及實際的數據列值。

如果啟用更改數據捕獲，DDL 更改將沒有限制。然而，如果添加或刪除數據列，它們可能會對收集到的更改數據產生影響。如果刪除跟蹤的數據列，捕獲實例中所有后續項目在該數據列中都會有 NULL。若添加數據列，捕獲實例會將其忽略。換句話說，捕獲實例在創建時就已定型。

若有必要更改數據列，可為表再創建一個捕獲實例（每個表最多可創建兩個實例），并允許更改數據的用戶遷移到新的表架構。但執行此操作時應該特別小心，因為如果跟蹤表有兩個捕獲實例，磁盤空間、I/O 和日志記錄也會加倍。

簡而言之，更改是使用我先前介紹的函數從更改表中檢索到的。函數包含開始 LSN 和結束 LSN，而且還提供了其他函數以允許您將正常時間轉換成 LSN。在檢索更新時，您甚至可以指定是要查看更新前后的值，還是只查看更新前的值。www.technetmagazine.com/video 上提供了我使用更改數據捕獲的截屏視頻。

更改跟蹤的工作原理

前面提到，更改跟蹤是一種同步處理程序，而且比更改數據捕獲簡單得多。它是在要跟蹤的表中進行更改的事務的一部分，而數據行的更改會在另外一個表中跟蹤。該表正是所謂的內部表，您不能控制其名稱或存儲位置。我認為這沒有什么問題，因為與更改數據捕獲所用的更改表相比，這個表中的數據應該少得多。但還是有可能產生磁盤空間問題，稍后我將對此進行解釋。

更改跟蹤以同步方式完成，這意味著在更改要跟蹤的表的每項事務中會額外進行一些處理工作。這對性能的影響與表中存在非群集索引而必須對表更新每項更改的情況類似。事務在根據內部 sys.syscommittab 表中的數據列提交時，本身也會被跟蹤。

更改跟蹤可以使用標準 ALTER DATABASE 和 ALTER TABLE 語法來啟用和禁用，而且它遵守的模型與更改數據捕獲相同，也就是必須在表級別之前在數據庫級別上啟用。操作的順序如下所示：

ALTER DATABASE AdventureWorks2000 SET CHANGE_TRACKING = ON 
  (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);  
GO  
USE AdventureWorks2000;  
GO  
ALTER TABLE Person.Person ENABLE CHANGE_TRACKING  
  WITH (TRACK_COLUMNS_UPDATED = ON);  
GO 

在數據庫和表級別上啟用更改跟蹤所需的權限也與啟用更改數據捕獲不同：分別是 db_owner 和表所有者。在表級別上啟用更改跟蹤時，可設置保留期以及是否自動清除更改數據。默認的保留期為 2 天，最長為 90 天，最短為一分鐘。

默認情況下也會打開自動清除。如果更改這些設置，您必須評估我在討論更改數據捕獲時所提到的折衷 — 主要是在磁盤空間和性能與應用程序需要之間進行權衡。

默認情況下，每個數據列中捕獲的內容正是發生更改的內容。這是通過以下操作完成的：記下更改的數據列的主鍵（即表上的更改跟蹤要求它必須具有主鍵）、版本號（數據庫一旦啟用更改跟蹤，就會產生版本號，從而允許排列操作的順序）以及進行更改的操作類型等。您也可以選擇是否跟蹤哪些數據列發生更改，每個更改的數據列需要 4 個字節。

磁盤空間監視與更改跟蹤稍有不同，因為更改數據是存儲在內部表中。若要找到使用的內部表的名稱，使用 sys.internal_tables 系統目錄視圖即可：

SELECT [name] FROM sys.internal_tables  
  WHERE [internal_type_desc] = 'CHANGE_TRACKING';  
GO 

然后，將名稱傳遞到 sp_spaceused 來查看所用的磁盤空間。

與更改數據捕獲不同的是，啟用更改跟蹤對 DDL 有些限制，而這些限制可能會應用于要跟蹤的表中。最明顯的限制是無論采取什么方式都無法更改主鍵。值得一提的另一個限制是，如果涉及的表啟用了更改跟蹤，ALTER TABLE SWITCH 將失敗。這很可能是因為：對于分別要從已跟蹤更改的已分區表轉換出的分區，或者要轉換成已分區表的已跟蹤更改表來說，自動開始或刪除更改跟蹤沒有意義。

更改是使用新的 CHANGE­TABLES (CHANGES …) 函數從內部更改表中檢索到的。這會采用它上次所用的更改跟蹤表名稱再加上版本號，返回自上次以來發生更改的所有數據行的相關信息?？墒褂酶鞣N不同的函數查找目前和最舊的有效版本。應用程序隨后可使用返回的信息來查詢要跟蹤更改的表，以獲得實際的列值。這個程序當然需要多個步驟 — 您獲得當前版本，使用該版本來查詢更改跟蹤，然后查詢實際的表來獲取與該版本相對應的列數據。

在經常更改的系統上，除非版本、更改數據和實際的列數據保持某種視圖不變，否則可能會得到不一致或不正確的結果。為此，您可以使用快照隔離，并將包含多個步驟的過程封裝在一個顯式事務中。這種作法雖然效果不錯，但也有潛在的缺點?？煺崭綦x可能會影響任務負載的性能，而且它也會影響 tempdb 的性能和空間使用情況。有關此問題的詳細信息，請訪問 technet.microsoft.com/library/cc280358。

結束語

圖 4 并排比較了更改跟蹤與更改數據，以便您進一步了解 DBA 關注的主要差異。從表中您可以看到，與更改跟蹤相比，更改數據捕獲屬于比較復雜的功能。由于在要跟蹤的表中包含 BLOB 數據列或非常寬的數據行時，跟蹤表的大小可能會快速增長，所以必須更加謹慎地決定要跟蹤的內容。此外，也可能出現事務日志管理問題，因為日志必須等到日志讀取器從日志搜集到記錄后才會截斷。圖 4 更改跟蹤與更改數據捕獲之間的比較