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

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

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

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

　　移動(dòng)設(shè)備在如今的企業(yè)環(huán)境中無所不在，換句話說，處理偶爾連接的系統(tǒng)是必要的。就數(shù)據(jù)庫系統(tǒng)來說，問題在于如何有效地更新不常連接的設(shè)備上的數(shù)據(jù)存儲，特別是當(dāng)數(shù)據(jù)存儲本身可能很小而且架構(gòu)可能與主數(shù)據(jù)庫截然不同時(shí)。

　　假設(shè)有一名移動(dòng)銷售代表，她負(fù)責(zé)超大型產(chǎn)品目錄的一部分。她每晚都會(huì)將自己的手持設(shè)備連接到主數(shù)據(jù)庫來下載最新的數(shù)據(jù) — 對該部分產(chǎn)品目錄的所有更改，經(jīng)過簡化以便存儲在手持設(shè)備上。數(shù)據(jù)傳輸應(yīng)該盡可能高效。

　　您可以讓數(shù)據(jù)庫系統(tǒng)準(zhǔn)備要下載到設(shè)備的整個(gè)產(chǎn)品目錄的相關(guān)部分，并且讓設(shè)備進(jìn)行下載。換句話說，每次設(shè)備連接時(shí)都會(huì)下載所有數(shù)據(jù)，即便數(shù)據(jù)沒有更改也一樣。這顯然是效率低下的方法。

　　另一種方法是讓數(shù)據(jù)庫系統(tǒng)跟蹤產(chǎn)品目錄的相關(guān)部分發(fā)生的更改。然后在手持設(shè)備連接時(shí)，它會(huì)要求獲得自上次連接以來發(fā)生更改的數(shù)據(jù)。在這種解決方案中，數(shù)據(jù)庫系統(tǒng)只需要準(zhǔn)備數(shù)據(jù)的子集，而且下載也盡可能高效。

　　跟蹤更改的另一個(gè)原因是要支持審核，這在當(dāng)今是必不可少的。審核除了跟蹤所做的更改之外，還會(huì)跟蹤更改時(shí)間和更改者。這對于完整審核記錄的持久性、安全性和正確性都有嚴(yán)謹(jǐn)?shù)囊?guī)范，無疑將事情提升到了另一個(gè)級別。

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

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

　　如何在 SQL Server 2005 中跟蹤更改

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

　　添加時(shí)間戳列會(huì)使表架構(gòu)發(fā)生更改（從而在存儲過程和其他代碼中產(chǎn)生連鎖影響）。

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

　　跟蹤表必須以某種方式來管理，才能避免增長失控，而這可能需要您創(chuàng)建類似于代理作業(yè)的內(nèi)容來定期刪除舊數(shù)據(jù)。

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

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

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

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

圖 1 不同時(shí)間段獲取的歷史更改數(shù)據(jù)

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

圖 2 使用更改跟蹤數(shù)據(jù)的偶爾連接的系統(tǒng)

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

　　就程序開發(fā)而言，這兩種功能應(yīng)該可以明顯降低跟蹤更改的復(fù)雜性。因?yàn)闊o論是哪一種技術(shù)，都不需要表架構(gòu)更改或觸發(fā)器。兩種技術(shù)都具有可配置的自動(dòng)清除過程，可依據(jù)事務(wù)提交時(shí)間對更改排序，并且提供內(nèi)置函數(shù)來檢索更改信息。

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

　　更改數(shù)據(jù)捕獲的工作原理

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

　　啟用更改數(shù)據(jù)捕獲的過程分為兩個(gè)階段。首先，系統(tǒng)管理員固定服務(wù)器角色的成員必須使用 sys.sp_cdc_enable_db 為數(shù)據(jù)庫啟用更改數(shù)據(jù)捕獲。然后，db_owner 固定服務(wù)器角色的成員必須使用 sys.sp_cdc_enable_table 在特定表上啟用更改數(shù)據(jù)捕獲。因?yàn)槿绻臄?shù)據(jù)捕獲配置不當(dāng)，可能會(huì)占用大量磁盤空間，所以存在這些安全性要求。顯然，表所有者不能啟用該功能，以免導(dǎo)致占用額外的磁盤空間，給數(shù)據(jù)庫管理員帶來麻煩。

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

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

　　當(dāng)您處理數(shù)據(jù)庫中的第一個(gè)表以啟用更改數(shù)據(jù)捕獲時(shí)，可能會(huì)創(chuàng)建兩個(gè) SQL 代理作業(yè)：捕獲作業(yè)和清除作業(yè)。之所以說“可能會(huì)創(chuàng)建”，是因?yàn)椴东@作業(yè)與在事務(wù)復(fù)制中用來搜集事務(wù)的是同一個(gè)作業(yè)。如果已配置事務(wù)復(fù)制，則只會(huì)創(chuàng)建清除作業(yè)，并會(huì)將現(xiàn)有的日志讀取器作業(yè)用作捕獲作業(yè)。這樣的好處是如果擁有兩個(gè)記錄讀取器作業(yè)，很快就會(huì)導(dǎo)致日志的爭用問題，從而降低性能。無論是哪種情況，如果要使用更改數(shù)據(jù)捕獲，都必須運(yùn)行 SQL 代理。

　　日志讀取器中的邏輯會(huì)自動(dòng)處理啟用和禁用表的更改數(shù)據(jù)捕獲，并適當(dāng)更改從事務(wù)日志中搜集到的內(nèi)容。此處特別需要注意，一旦啟用更改數(shù)據(jù)捕獲，事務(wù)日志就會(huì)像對待事務(wù)復(fù)制一樣 — 日志只有等到日志讀取器處理之后才會(huì)截?cái)唷＿@表示檢查點(diǎn)操作（即使在 SIMPLE 恢復(fù)模式中）也要等到日志讀取器處理日志之后才將其截?cái)唷?/p>

另外，如果使用 BULK_LOGGED 恢復(fù)模式來減少日志記錄，則除了索引創(chuàng)建/舍棄/重建操作外，更改數(shù)據(jù)捕獲將強(qiáng)制完整記錄所有項(xiàng)目。如果您從未遇到過這類行為，請注意這可能會(huì)導(dǎo)致事務(wù)日志過大，特別是如果更改了捕獲任務(wù)默認(rèn)值而不經(jīng)常處理日志的話更是如此。

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

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

　　如果更改清除作業(yè)頻率或更改數(shù)據(jù)保留周期，也會(huì)發(fā)生同樣的問題 — 更改表會(huì)在更改數(shù)據(jù)清除之前持續(xù)增長。這需要在設(shè)計(jì)時(shí)全面考慮要跟蹤哪些內(nèi)容，以及其保留時(shí)限。此處要考慮的重點(diǎn)包括：

　　捕獲實(shí)例所需的數(shù)據(jù)列列表。捕獲的數(shù)據(jù)列越多，插入更改表中的更改數(shù)據(jù)就越多。

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

　　使用更改數(shù)據(jù)的進(jìn)程的運(yùn)行頻率。請記住，數(shù)據(jù)要使用之后才能刪除。

　　清除進(jìn)程的運(yùn)行頻率 — 生成的更改數(shù)據(jù)有可能太多，以致于刪除它的清除進(jìn)程只能安排在周末運(yùn)行，因?yàn)樗赡苌闪颂嗟氖聞?wù)日志。

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

　　由于磁盤空間使用量有可能增加，請?jiān)趩⒂酶臄?shù)據(jù)捕獲時(shí)設(shè)置更改表的文件組位置。這使得管理基本磁盤空間更為輕松，也意味著所有更改數(shù)據(jù)可存儲在比主數(shù)據(jù)庫價(jià)格便宜的 RAID 級別卷中。另外，雖然清除作業(yè)設(shè)置可應(yīng)用到所有捕獲實(shí)例，但如果磁盤空間出現(xiàn)問題，可隨時(shí)分別清除單獨(dú)的捕獲實(shí)例。您可以在捕獲表上使用 sp_spaceused 輕松監(jiān)視磁盤空間的使用情況。

　　實(shí)際寫入更改表的數(shù)據(jù)行中包含事務(wù)的元數(shù)據(jù)（提交日志序號或 LSN）、發(fā)生更改的事務(wù)內(nèi)部的順序、操作的內(nèi)容、發(fā)生更改的數(shù)據(jù)列的位掩碼，以及實(shí)際的數(shù)據(jù)列值。

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

　　若有必要更改數(shù)據(jù)列，可為表再創(chuàng)建一個(gè)捕獲實(shí)例（每個(gè)表最多可創(chuàng)建兩個(gè)實(shí)例），并允許更改數(shù)據(jù)的用戶遷移到新的表架構(gòu)。但執(zhí)行此操作時(shí)應(yīng)該特別小心，因?yàn)槿绻櫛碛袃蓚€(gè)捕獲實(shí)例，磁盤空間、I/O 和日志記錄也會(huì)加倍。

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

　　更改跟蹤的工作原理

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

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

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

   1. ALTER DATABASE AdventureWorks2000 SET CHANGE_TRACKING = ON 
   2.   (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);  
   3. GO  
   4. USE AdventureWorks2000;  
   5. GO  
   6. ALTER TABLE Person.Person ENABLE CHANGE_TRACKING  
   7.   WITH (TRACK_COLUMNS_UPDATED = ON);  
   8. GO         

        

        
        
      
        
it知識庫：SQL Server 2008跟蹤企業(yè)數(shù)據(jù)庫中的更改，轉(zhuǎn)載需保留來源！
        
鄭重聲明：本文版權(quán)歸原作者所有，轉(zhuǎn)載文章僅為傳播更多信息之目的，如作者信息標(biāo)記有誤，請第一時(shí)間聯(lián)系我們修改或刪除，多謝。