SQLSeverCDC實施方案

1、SQL Sever CDC 實施手冊目錄1.SQL Server 變更數(shù)據(jù)捕獲簡介32.CDC與傳統(tǒng)增量處理方式的對比43.現(xiàn)行數(shù)據(jù)庫架構(gòu)64.建議方案85.啟用變更數(shù)據(jù)捕獲96.添加 CDC之后的影響147.注意事項159.監(jiān)視變更數(shù)據(jù)捕獲進程171.SQL Server 變更數(shù)據(jù)捕獲簡介變更數(shù)據(jù)捕獲可記錄應(yīng)用于SQL Server 表的插入、更新和刪除活動。這樣，就可以按易于使用的關(guān)系格式提供這些更改的詳細信息。更改表鏡像了所跟蹤原表的列結(jié)構(gòu)，將修改的行應(yīng)用到目標環(huán)境所需的列信息和元數(shù)據(jù)會被捕獲并存儲在更改表中。SQL提供了一些表值函數(shù)，以便使用這對更改數(shù)據(jù)進行系統(tǒng)級訪問。CDC技術(shù)典型應(yīng)

2、用場景是提取、 轉(zhuǎn)換和加載 (ETL) 應(yīng)用程序。 通常 ETL應(yīng)用程序需要以增量方式將 SQL Server 源表中的更改數(shù)據(jù)加載到數(shù)據(jù)倉庫或數(shù)據(jù)集市。雖然數(shù)據(jù)倉庫中的源表的表示形式必須反映源表中的更改，但數(shù)據(jù)倉庫中目標表的數(shù)據(jù)表現(xiàn)形式不一定和源表相似，因而刷新源副本的端到端技術(shù)并不適用。相反，需要一種結(jié)構(gòu)化的可靠更改數(shù)據(jù)流，以便使用者可以將其應(yīng)用于不同的目標數(shù)據(jù)表示形式。SQL Server 變更數(shù)據(jù)捕獲就提供了這一技術(shù)。變更數(shù)據(jù)捕獲的變更數(shù)據(jù)源為SQL Server事務(wù)日志。在將插入、更新和刪除操作應(yīng)用于跟蹤的源表時，描述這些更改的條目會添加到事務(wù)日志中。日志用作變更數(shù)據(jù)捕獲進程的輸入來

3、源， 它會讀取日志， 并在所跟蹤的表的關(guān)聯(lián)更改表中添加有關(guān)更改的信息。系統(tǒng)將提供一些函數(shù)， 以枚舉在更改表中指定范圍內(nèi)發(fā)生的更改，并以篩選的結(jié)果集的形式返回該值。應(yīng)用程序進程使用篩選的結(jié)果集通常用以在某種外部環(huán)境中更新源表示形式。2. CDC 與傳統(tǒng)增量處理方式的對比在沒有使用CDC 之前，為了確定數(shù)據(jù)更改，通常應(yīng)用程序開發(fā)人員必須在其應(yīng)用程序中使用觸發(fā)器、 時間戳列和其他表的組合來實現(xiàn)自定義跟蹤方法。 創(chuàng)建這些應(yīng)用程序通常涉及多項工作，導(dǎo)致架構(gòu)更新，并且通常帶來較高的性能開銷。通過觸發(fā)器可以把 DML 操作中的 INSERT/UPDATE/DELETE數(shù)據(jù)記錄下來， 但是觸發(fā)器的維護比較困難

4、。另外一種常用的方式是 時間戳 , 它是以業(yè)務(wù)表中某一個字段的值，作為判斷新舊數(shù)據(jù)的標志，每次只抽取上次抽取記錄時間以后產(chǎn)生的數(shù)據(jù)。時間戳方式也存在一些問題：變化數(shù)據(jù)的捕獲需要在源數(shù)據(jù)庫上進行，并且只能捕獲最終結(jié)果，而非整個變化歷史。時間戳的粒度通常設(shè)置到秒，在某些場合下這個粒度可能會略掉一些變化數(shù)據(jù)。無法捕獲對時間戳以前數(shù)據(jù)的 delete 和 update 操作 ,在數(shù)據(jù)準確性上受到了一定的限制。 而且 要求業(yè)務(wù)系統(tǒng)的表必須一個可以標識新舊數(shù)據(jù)的列，并且需要對該列做索引，而某些表沒有設(shè)置這種列。CDC提供了一種機制， 對源表數(shù)據(jù)的更新進行跟蹤， 在應(yīng)用程序中使用變更數(shù)據(jù)捕獲而不開發(fā)自定義解

5、決方案來跟蹤數(shù)據(jù)庫中的更改具有以下好處：減少了開發(fā)時間。 由于 SQL Server 中提供了功能， 因此無需開發(fā)自定義解決方案。不需要架構(gòu)更改。 您無需添加列、 添加觸發(fā)器或創(chuàng)建要在其中跟蹤已刪除的行或存儲更改跟蹤信息的端表（如果無法將列添加到用戶表）。具有內(nèi)置清除機制。 更改跟蹤的清除操作在后臺自動執(zhí)行。 不需要端表中存儲的數(shù)據(jù)的自定義清除。提供功能的目的是獲取更改信息。降低了 DML 操作的開銷。 開銷通常會低于使用其他解決方案，對于需要使用觸發(fā)器的解決方案，尤其如此。提供可用于配置和管理的標準工具。 SQL Server 提供標準的 DDL 語句、SQLServer Managemen

6、t Studio 、目錄視圖和安全權(quán)限。3.現(xiàn)行數(shù)據(jù)庫架構(gòu)DB_1， DB_2，DB_3， ， DB_50 是分布在各個學校的數(shù)據(jù)庫。DB1， DB2， ， DB50 是集成在一個服務(wù)器上50 個不同的DB。DC 與 IDC 的區(qū)別： DC 包含的數(shù)據(jù)表比較全，系統(tǒng)壓力比較大；IDC 基本能滿足當前所涉及的表，系統(tǒng)壓力比DC 好一些。ORACLE是最終的目標庫。當前共涉及到50 個 DB，每個 DB 約有 10 張表。每張表每天的增量數(shù)據(jù)統(tǒng)計如下：4.備選方案和建議第一種方案：在 DC服務(wù)器上為50 個不同的DB 啟用 CDC。 (目前涉及 10 張表 )最后用 Informatica 開發(fā)

7、ETL，從 DC 服務(wù)器上50 個 DB 的變化庫中循環(huán)抽取。影響：1.啟用 CDC會在每個 DB 的系統(tǒng)表中會生成6 張公用表。為每張表啟用時會多增加一個系統(tǒng)表。50 個 DB 共計增加 800 張表。2.為每個 DB 啟用 CDC，都會在 SQL Server代理下 job 中新增 2 個 job 。50 個 DB 共計增加 100 個 job。第二種方案：在各個學校的 DB 上啟用 CDC。然后為每張啟用 CDC對應(yīng)的系統(tǒng)表創(chuàng)建分布式服務(wù)，將該系統(tǒng)表同步到 DC 服務(wù)器對應(yīng)的 DB 的表中。最后用 informatica 開發(fā)，從DC 服務(wù)器上50 個 DB 循環(huán)抽取。影響：1.因為啟用

8、 CDC，會在每個庫的系統(tǒng)表里新增 16 張表。 需要在不同的 DB 上為這些系統(tǒng)表創(chuàng)建分布式服務(wù)。2.需要在 DC 服務(wù)器上不同數(shù)據(jù)庫中創(chuàng)建對應(yīng)的表(DB_1 中的系統(tǒng) )，來接受上游送來的數(shù)據(jù)。建議方案對比上述兩個方案，建議采用方案1，其最主要的優(yōu)點在于對源數(shù)據(jù)庫無需修改，所有的更改都在DC 上進行，而方案2 需要對每一個源數(shù)據(jù)庫分別啟用CDC，對于管理員來說工作會比較繁瑣， 雖然總體需要增加的表的數(shù)量沒變，但方案 1 所有的更改都集中在一個服務(wù)器上，相對容易管理一些，而且以后的升級和修改都只需要考慮一個系統(tǒng)，而不是像方案2那樣需要考慮50 個系統(tǒng)。但所有的操作集中在DC 上之后，也不可避

9、免地會帶來負載的增加，由于方案2 仍需要在 DC 上建立針對各個數(shù)據(jù)庫的變化表進行寫入，負載的增加主要集中在日志的掃描部分，這一部分基本上是讀操作，對負載的影響可能不象寫入帶來的影響那么明顯。另外一點， 由于任務(wù)都集中在一個系統(tǒng)上，任務(wù)的管理會比較麻煩一些。5.啟用變更數(shù)據(jù)捕獲1.確定要添加CDC的 DB，比如： CDC。執(zhí)行如下sql備注：執(zhí)行完成后，會在DB 的系統(tǒng)表里新增：6 張表，一個CDC的賬戶，一個CDC的架構(gòu)，如下圖：2.查看數(shù)據(jù)庫CDC是否啟用成功，執(zhí)行sql 如下圖：3.對數(shù)據(jù)庫表進行啟用CDC，執(zhí)行 sql 如下圖：備注：執(zhí)行成功后，會新增DB 系統(tǒng)表里新增一張表，一個角色

10、，2 個 job 作業(yè)。如下圖：4.查看數(shù)據(jù)庫表CDC是否啟用成功，執(zhí)行sql 如下圖：5.對數(shù)據(jù)庫，對表啟用CDC后，也會在DB 中新增 1 個函數(shù)，及若干個存儲過程：6. 之 上 步 驟 執(zhí) 行 完 成 后 ， 對 表 進 行 的 insert ， delete ， update 操 作 都 會 在cdc.dbo_o_reg_bs_student_ct 表中存儲。6.添加 CDC之后的影響1.不能對表使用truncate操作。2.刪除DB 時，要先刪除該DB 對應(yīng)的2 個 JOB，否則會報錯。3.對表啟用CDC后， SQL服務(wù)器會跟蹤總事務(wù)日志中的inser, update和delete操

11、作，在對源叔據(jù)做變更時， 服務(wù)器需要在變更表中同時也插入一條記錄，這在一定程度上會影響服務(wù)器的性能。 如果某表的數(shù)據(jù)量每天的變量兩很大，性能的影響可能會比較明顯。可以調(diào)整系統(tǒng)的 CT表保留時長減少對性能的影響。設(shè)置sql 如下：4.為適應(yīng)固定列結(jié)構(gòu)更改表，在為源表啟用CDC后，負責填充更改表的捕獲進程將忽略未指定進行捕獲的任何新列。如果刪除了某個跟蹤的列，則會為在后續(xù)更改項中為該列提供Null值。但是，如果現(xiàn)有列只是更改了其數(shù)據(jù)類型，則會將更改傳播到更改表。5. 變更數(shù)據(jù)捕獲和事務(wù)復(fù)制可以共存于同一數(shù)據(jù)庫中，但在啟用這兩項功能后，更改表的填充處理方式將發(fā)生變化。變更數(shù)據(jù)捕獲和事務(wù)復(fù)制始終使用相

12、同的過程sp_replcmds從事務(wù)日志讀取更改。 當單獨啟用變更數(shù)據(jù)捕獲時，SQLServer代理作業(yè)會調(diào)用sp_replcmds。在同一數(shù)據(jù)庫中啟用這兩項功能時，日志讀取器代理會調(diào)用sp_replcmds。此代理將填充更改表和分發(fā)數(shù)據(jù)庫表。7.注意事項1. 性能盡管變更數(shù)據(jù)捕獲通過獲取進行DML 更改的方面和更改的實際數(shù)據(jù)，提供用戶表的歷史更改信息。更改是使用異步進程捕獲的，此進程讀取事務(wù)日志，并且對系統(tǒng)造成的影響較小。但對本身負載已經(jīng)比較中的服務(wù)器，建議評估啟用CDC后對數(shù)據(jù)庫性能的影響，以及啟用變更數(shù)據(jù)捕獲所需的數(shù)據(jù)表和日志需求的增加。2. 安全配置和管理若要為數(shù)據(jù)庫啟用或禁用變更數(shù)據(jù)

13、捕獲，sys.sp_cdc_enable_db (Transact-SQL)或 sys.sp_cdc_disable_db (Transact-SQL)的調(diào)用者必須是sysadmin固定服務(wù)器角色的成員。若要在表級啟用或禁用變更數(shù)據(jù)捕獲，要求sys.sp_cdc_enable_table (Transact-SQL)和 sys.sp_cdc_disable_table(Transact-SQL)的調(diào)用者必須是 sysadmin角色成員或數(shù)據(jù)庫databasedb_owner 角色成員。僅限服務(wù)器 sysadmin角色成員和 database db_owner角色成員能夠使用存儲過程來支持變更數(shù)

14、據(jù)捕獲作業(yè)管理。更改枚舉和元數(shù)據(jù)查詢?nèi)粢@取對與捕獲實例關(guān)聯(lián)的更改數(shù)據(jù)的訪問，必須為用戶授予關(guān)聯(lián)源表中的所有捕獲列的選擇訪問權(quán)限。此外， 如果在創(chuàng)建捕獲實例時指定了訪問控制角色，調(diào)用者還必須是指定訪問控制角色的成員。所有數(shù)據(jù)庫用戶可通過public角色訪問用于訪問元數(shù)據(jù)的其他常規(guī)變更數(shù)據(jù)捕獲功能，但返回的元數(shù)據(jù)訪問通常也是使用基礎(chǔ)源表的選擇訪問權(quán)限以及任何定義的訪問控制角色成員控制的。對啟用了變更數(shù)據(jù)捕獲的源表執(zhí)行的DDL 操作為表啟用變更數(shù)據(jù)捕獲后，只能由固定服務(wù)器角色sysadmin成員、 databaseroledb_owner 成員或 database role db_ddladmin

15、成員將 DDL 操作應(yīng)用于該表。如果為用戶顯式授予了對表執(zhí)行DDL 操作的權(quán)限， 這些用戶在嘗試執(zhí)行這些操作時將收到錯誤 22914 。8. 數(shù)據(jù)類型變更數(shù)據(jù)捕獲支持所有基列類型。下表列出了幾個列類型的行為和限制。列類型在更改表中捕獲更改限制稀疏列是不支持在使用列集時捕獲更改。計算列否不跟蹤對計算列的更改。在更改表中該列將顯示為相應(yīng)類型，不過其值為NULL。XML是不跟蹤對單個 XML 元素的更改。時間戳是更改表中的數(shù)據(jù)類型將轉(zhuǎn)換為binary。BLOB 數(shù)據(jù)類型是僅當 BLOB 列本身更改時才存儲該列的上一映像。9.監(jiān)視變更數(shù)據(jù)捕獲進程通過監(jiān)視變更數(shù)據(jù)捕獲進程，可以確定更改是否正以合理的滯后

16、時間正確寫入更改表中。監(jiān)視還可以幫助您標識可能發(fā)生的任何錯誤。SQL Server包括兩個動態(tài)管理視圖，用于幫助您監(jiān)視變更數(shù)據(jù)捕獲：sys.dm_cdc_log_scan_sessions和 sys.dm_cdc_errors。標識包含空結(jié)果集的會話sys.dm_cdc_log_scan_sessions中的每一行表示一個日志掃描會話（ID為0的行除外）。 一個日志掃描會話等同于執(zhí)行一次sp_cdc_scan。 在會話期間， 掃描可以返回更改，也可以返回空結(jié)果。如果結(jié)果集為空，則sys.dm_cdc_log_scan_sessions中的empty_scan_count列將設(shè)置為1 。 如果有

17、連續(xù)的空結(jié)果集（例如， 當捕獲作業(yè)正在連續(xù)運行時），則最后一個現(xiàn)有行中的empty_scan_count將遞增。例如，如果sys.dm_cdc_log_scan_sessions已經(jīng)包含與返回了更改的掃描相對應(yīng)的10行，并且存在五個連續(xù)的空結(jié)果， 則該視圖包含11行。 最后一行在empty_scan_count列的值是5 。 若要確定有空掃描的會話，請運行以下查詢：SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count 0確定滯后時間sys.dm_cdc_log_scan_sessions管理視圖包括一個用于記錄每個捕

18、獲會話滯后時間的列。滯后時間是指在源表上提交的事務(wù)與在更改表上提交的最后一個捕獲的事務(wù)之間所經(jīng)過的時間。只為活動會話填充滯后時間列。對于其 empty_scan_count列的值大于0的會話，滯后時間列將設(shè)置為0 。 以下查詢返回最近進行的會話的平均滯后時間：SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0可以使用滯后時間數(shù)據(jù)確定捕獲進程正在以多快或多慢的速度處理事務(wù)。 當捕獲進程連續(xù)運行時，該數(shù)據(jù)最有用。 如果捕獲進程正在按計劃運行，那么，由于在源表上提交的事務(wù)與按計劃時間運行的捕獲進程之間存在滯后，因

19、此滯后時間可能會很長。捕獲進程效率的另一個重要度量值是吞吐量。若要確定會話的吞吐量，請將 command_count返回最近會話的平均吞吐量：它是在每個會話期間每秒處理的平均命令數(shù)。列中的值除以持續(xù)時間列中的值。 以下查詢SELECT command_count/duration AS Throughput FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0使用數(shù)據(jù)收集器收集抽樣數(shù)據(jù)SQL Server數(shù)據(jù)收集器用于從任何表或動態(tài)管理視圖中收集數(shù)據(jù)的快照，并生成性能數(shù)據(jù)倉庫。對 數(shù)據(jù)庫啟用變更數(shù)據(jù)捕獲時，最好按固定時間間隔取得sys.d

20、m_cdc_log_scan_sessions視圖和 sys.dm_cdc_errors視圖的快照， 以便隨后進行分析。以下過程設(shè)置一個數(shù)據(jù)收集器，用于從 sys.dm_cdc_log_scan_sessions管理視圖收集示例數(shù)據(jù)。配置數(shù)據(jù)集合1. 啟用數(shù)據(jù)收集器，并配置管理數(shù)據(jù)倉庫。2. 執(zhí)行以下代碼，為變更數(shù)據(jù)捕獲創(chuàng)建自定義收集器。Transact-SQL USE msdb;DECLARE schedule_uiduniqueidentifier;- Collect and upload data every 5 minutes SELECT schedule_uid = (SELECT

21、 schedule_uid from sysschedules_localserver_view WHERE name = NCollectorSchedule_Every_5min)DECLARE collection_set_idint;EXEC dbo.sp_syscollector_create_collection_setname = N CDC Performance Data Collector,schedule_uid = schedule_uid,collection_mode = 0,days_until_expiration = 30,description = NThis collection set collects CDC metadata,collection_set_id = collection_set_id output