SQLServer 2008 CDC實(shí)現(xiàn)數(shù)據(jù)變更捕獲使用圖文詳解

2020-06-27 15:33:44

字體：大中小

供稿：網(wǎng)友

適用環(huán)境：

僅在SQLServer2008（含）以后的企業(yè)版、開發(fā)版和評估版中可用。

詳解：

CDC功能主要捕獲SQLServer指定表的增刪改操作，由于任何操作都會寫日志（哪怕truncate），所以CDC的捕獲來源于日志文件。日志文件會把更改應(yīng)用到數(shù)據(jù)文件中，同時(shí)也會標(biāo)記符合要求的數(shù)據(jù)標(biāo)記為需要添加跟蹤的項(xiàng)。然后通過一些配套函數(shù)，最后寫入到數(shù)據(jù)倉庫中。大概流程：

SQLServer2008,CDC,數(shù)據(jù)變更捕獲

步驟：本文中以：AdventureWorks為例

第一步、對目標(biāo)庫顯式啟用CDC：

在當(dāng)前庫使用sys.sp_cdc_enable_db。返回0（成功）或1（失敗）。注意，無法對系統(tǒng)數(shù)據(jù)庫和分發(fā)數(shù)據(jù)庫啟用該功能。且執(zhí)行者需要用sysadmin角色權(quán)限。

該存儲過程的作用域是整個(gè)目標(biāo)庫。包含元數(shù)據(jù)、DDL觸發(fā)器、cdc架構(gòu)和cdc用戶。

使用以下代碼啟用：

復(fù)制代碼

代碼如下:

USE AdventureWorks GO EXECUTE sys.sp_cdc_enable_db;
GO

在一開始直接執(zhí)行時(shí)，出現(xiàn)了報(bào)錯(cuò)信息：

消息22830，級別16，狀態(tài)1，過程sp_cdc_enable_db_internal，第193 行

無法更新元數(shù)據(jù)來指示已對數(shù)據(jù)庫AdventureWorks 啟用了變更數(shù)據(jù)捕獲。執(zhí)行命令'SetCDCTracked(Value = 1)' 時(shí)失敗。返回的錯(cuò)誤為15517: '無法作為數(shù)據(jù)庫主體執(zhí)行，因?yàn)橹黧w"dbo" 不存在、無法模擬這種類型的主體，或您沒有所需的權(quán)限。'。請使用此操作和錯(cuò)誤來確定失敗的原因并重新提交請求。

這里引出了另外一個(gè)知識點(diǎn)：錯(cuò)誤號 15517 的錯(cuò)誤

這種錯(cuò)誤會在很多地方出現(xiàn)，如還原數(shù)據(jù)庫的時(shí)候也會有可能出現(xiàn)。共同點(diǎn)是：某個(gè)/些存儲過程使用了具有WITHEXECUTE AS 的選項(xiàng)。使其在當(dāng)前庫具有了某個(gè)架構(gòu)，但是當(dāng)在別的地方執(zhí)行時(shí)，由于沒有這個(gè)架構(gòu)，所以就報(bào)錯(cuò)，解決方法：

ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa]

經(jīng)過檢查，uspUpdateEmployeeHireInfo這個(gè)存儲過程的確有：WITH EXECUTE AS CALLER

使用sa的原因是即使sa被禁用，sa還是存在的。所以不會報(bào)錯(cuò)。

現(xiàn)在重新執(zhí)行：

復(fù)制代碼

代碼如下:

USE AdventureWorks GO EXECUTE sys.sp_cdc_enable_db; GO

啟用成功，然后通過以下語句檢查是否成功：

復(fù)制代碼

代碼如下:

SELECT is_cdc_enabled,CASEWHENis_cdc_enabled=0THEN'CDC功能禁用'ELSE'CDC功能啟用'END描述 FROM sys.databases WHERE NAME = 'AdventureWorks'

創(chuàng)建成功后，將自動(dòng)添加CDC用戶和CDC架構(gòu)。

創(chuàng)建這兩個(gè)用戶、架構(gòu)的原因是因?yàn)镃DC要求獨(dú)占方式使用這兩個(gè)架構(gòu)，所以要單獨(dú)創(chuàng)建。如果存在了非CDC功能創(chuàng)建的CDC用戶、架構(gòu)的話，則需要先刪除該cdc命名的架構(gòu)，才能開啟。

第二步、對目標(biāo)表啟用CDC：

使用db_owner角色的成員執(zhí)行sys.sp_cdc_enable_table為每個(gè)需要跟蹤的表創(chuàng)建捕獲實(shí)例。然后通過sys.tables目錄視圖中的is_tracked_by_cdc列來判斷是否創(chuàng)建成功。

默認(rèn)情況下會對表的全部列做捕獲。如果只需要對某些列做捕獲，可以使用@captured_column_list參數(shù)指定這些列。

如果要把更改表放到文件組里的話，最好創(chuàng)建單獨(dú)的文件組（最起碼與源表獨(dú)立）。

如果不想控制訪問角色，則@role_name必須顯式設(shè)置為null。

復(fù)制代碼

代碼如下:

sys.sp_cdc_enable_table [ @source_schema = ] 'source_schema', [ @source_name = ] 'source_name' , [ @role_name = ] 'role_name' [,[ @capture_instance = ] 'capture_instance' ] [,[ @supports_net_changes = ] supports_net_changes ] [,[ @index_name = ] 'index_name' ] [,[ @captured_column_list = ] 'captured_column_list' ] [,[ @filegroup_name = ] 'filegroup_name' ] [,[ @partition_switch = ] 'partition_switch' ]

例子：

把HumanResources.Department 這個(gè)表開啟變更捕獲。

復(fù)制代碼

代碼如下:

USE AdventureWorks GO EXEC <a href="mailto:sys.sp_cdc_enable_table@source_schema='HumanResources'">sys.sp_cdc_enable_table@source_schema='HumanResources'</a>, @source_name = 'Department',@role_name= NULL

然后查詢是否成功：

對表開啟以后，可以在下圖中看到多了很多cdc架構(gòu)開頭的表：

啟動(dòng)之后，可以看到SQLServer代理里面的作業(yè)，也出現(xiàn)了這兩個(gè)作業(yè)：

案例：

下面開始從頭到尾做一個(gè)實(shí)際案例：

先檢查原庫的內(nèi)容：可以看到系統(tǒng)表里面只有一個(gè)dbo.sysdiagrams表

然后看看SQLServer代理：可以看到也沒有相關(guān)的作業(yè)

步驟二：對數(shù)據(jù)庫啟用CDC數(shù)據(jù)庫級別功能。必須顯式啟用：

Step1、執(zhí)行以下語句：

復(fù)制代碼

代碼如下:

USE AdventureWorks GO EXEC sys.sp_cdc_enable_db GO

某些數(shù)據(jù)庫可能存在一些存儲過程包含有：execute as 等語句，此時(shí)會報(bào)錯(cuò)：

文字描述：

消息22830，級別16，狀態(tài)1，過程sp_cdc_enable_db_internal，第186 行

消息266，級別16，狀態(tài)2，過程sp_cdc_enable_db_internal，第0 行

EXECUTE 后的事務(wù)計(jì)數(shù)指示BEGIN 和COMMIT 語句的數(shù)目不匹配。上一計(jì)數(shù)= 0，當(dāng)前計(jì)數(shù)= 1。

消息266，級別16，狀態(tài)2，過程sp_cdc_enable_db，第0 行

EXECUTE 后的事務(wù)計(jì)數(shù)指示BEGIN 和COMMIT 語句的數(shù)目不匹配。上一計(jì)數(shù)= 0，當(dāng)前計(jì)數(shù)= 1。

消息3998，級別16，狀態(tài)1，第1 行

在批處理結(jié)束時(shí)檢測到不可提交的事務(wù)。該事務(wù)將回滾。

如果出現(xiàn)這個(gè)錯(cuò)誤，目前的解決方法是執(zhí)行下面語句，原因已在開頭說明，對于沒有使用EXECUTE AS的庫，一般不會有這樣的問題：

ALTER AUTHORIZATIONON DATABASE::[AdventureWorks]TO [sa]

然后再次執(zhí)行，就成功開啟了：

復(fù)制代碼

代碼如下:

USE AdventureWorks GO EXEC sys.sp_cdc_enable_db GO

現(xiàn)在檢查是否成功：

復(fù)制代碼

代碼如下:

SELECT is_cdc_enabled , CASE WHEN is_cdc_enabled = 0 THEN 'CDC功能禁用' ELSE 'CDC功能啟用' END 描述 FROM sys.databases WHERE NAME = 'AdventureWorks'

現(xiàn)在檢查表和作業(yè)：

作業(yè)沒有改變

角色權(quán)限中多出了：

步驟三：對某些表開啟捕獲：

這里選擇HumanResources.Department、Person.ADDRESS、Person.Contact開啟：

先來看開啟之前的樣子：

然后對該表開啟：

復(fù)制代碼

代碼如下:

USE AdventureWorks; GO EXECUTE sys.sp_cdc_enable_table @source_schema= N'HumanResources' , @source_name= N'Department' , @role_name= N'cdc_Admin'--可以自動(dòng)創(chuàng)建 , @capture_instance=DEFAULT GO

結(jié)果如圖:

可以看到在創(chuàng)建的同時(shí),也創(chuàng)建了兩個(gè)作業(yè): cdc.AdventureWorks_cleanup和cdc.AdventureWorks_capture

也可以看到多了一個(gè)角色CDC_ADMIN，是在上面語句中動(dòng)態(tài)創(chuàng)建的：

按照上面步驟把另外兩個(gè)表也開啟了：

復(fù)制代碼

代碼如下:

</div> <div> USE AdventureWorks; GO EXECUTE sys.sp_cdc_enable_table @source_schema= N'Person' , @source_name= N'ADDRESS' , @role_name= N'cdc_Admin'--可以自動(dòng)創(chuàng)建 , @capture_instance=DEFAULT GO EXECUTE sys.sp_cdc_enable_table @source_schema= N'Person' , @source_name= N'Contact' , @role_name= N'cdc_Admin'--可以自動(dòng)創(chuàng)建 , @capture_instance=DEFAULT GO

可以從系統(tǒng)表中看到：

了3個(gè)表，并且是剛才開啟CDC功能的表。現(xiàn)在來檢查是否開啟成功：

復(fù)制代碼

代碼如下:

</div> SELECT name , is_tracked_by_cdc , CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用' ELSE 'CDC功能啟用' END 描述 FROM sys.tables WHERE OBJECT_ID IN(OBJECT_ID('HumanResources.Department'), OBJECT_ID('Person.ADDRESS'), OBJECT_ID('Person.Contact'))

結(jié)果如下：

步驟三：檢驗(yàn)：

下面來改動(dòng)數(shù)據(jù)：

然后把表中的數(shù)據(jù)復(fù)制一份：

復(fù)制代碼

代碼如下:

INSERT INTO HumanResources.Department (Name , GroupName , ModifiedDate ) SELECT Name + '1' , GroupName + '1' , GETDATE()ModifiedDate FROM HumanResources.Department

結(jié)果如下：

然后從cdc.HumanResources_Department_CT 表查詢：

可以看到的確多了16條記錄。這部分記錄證明了，有16條數(shù)據(jù)從監(jiān)控到目前為止做了改動(dòng)，現(xiàn)在再來改動(dòng)一下：

DELETE FROM HumanResources.DepartmentWHERE DepartmentID>17

再查詢cdc表，可以看到又多了16條記錄：

現(xiàn)在來做下update的實(shí)驗(yàn)：

UPDATE HumanResources.DepartmentSET ModifiedDate=GETDATE()

再查看：

現(xiàn)在來分析一下這個(gè)表：

可以在聯(lián)機(jī)叢書上查看：cdc.<capture_instance>_CT 可以看到，這樣命名的表，是用于記錄源表更改的表。對于insert/delete操作，會有對應(yīng)的一行記錄，而對于update，會有兩行記錄。

對于__$operation列：1 = 刪除、2= 插入、3= 更新（舊值）、4= 更新（新值）

對于__$start_lsn列：由于更改是來源與數(shù)據(jù)庫的事務(wù)日志，所以這里會保存其事務(wù)日志的開始序列號（LSN）

但是微軟不檢查直接查詢這類表，建議使用cdc.fn_cdc_get_all_changes_<捕獲實(shí)例>和cdc.fn_cdc_get_net_changes_<capture_instance> 來查詢

下文開始，來熟悉各種函數(shù)、存儲過程的使用，并嘗試一些不正常的操作。

日常使用情景：1、查詢已經(jīng)開啟的捕獲實(shí)例：

由于可能不記得或者不知道開啟了什么表的捕獲，所以可以使用以下語句來查找：

--返回所有表的變更捕獲配置信息

復(fù)制代碼

代碼如下:

EXECUTE sys.sp_cdc_help_change_data_capture; GO

可以看到以下截圖：

SQLServer2008,CDC,數(shù)據(jù)變更捕獲

查看對某個(gè)實(shí)例(即表)的哪些列做了捕獲監(jiān)控:

復(fù)制代碼

代碼如下:

</div> <div> EXEC sys.sp_cdc_get_captured_columns @capture_instance ='HumanResources_Department' -- sysname

得到下圖:

也可以從下面中查找配置信息：

SELECT * FROM msdb.dbo.cdc_jobs

如圖：

2、查看當(dāng)前配置使用sp_cdc_help_jobs：

從上文可以看到，啟用cdc之后會自動(dòng)創(chuàng)建了兩個(gè)作業(yè)，可以先使用以下語句來查看：

sp_cdc_help_jobs

得到的結(jié)果：

對于一個(gè)大型的OLTP系統(tǒng)，由于數(shù)據(jù)更改會非常頻繁，變更表中的數(shù)據(jù)會非常多，如果存放過久（最久可以存放100年），那對數(shù)據(jù)庫空間是非常大的挑戰(zhàn)。此時(shí)可以調(diào)整上圖中cdc.AdventureWorks_cleanup中retention（單位：分鐘）。

3、修改配置：sp_cdc_change_job：

--顯示原有配置：

復(fù)制代碼

代碼如下:

EXEC sp_cdc_help_jobs GO --更改數(shù)據(jù)保留時(shí)間為分鐘
EXECUTE sys.sp_cdc_change_job @job_type= N'cleanup', @retention=100 GO --停用作業(yè) EXEC sys.sp_cdc_stop_jobN'cleanup' GO --啟用作業(yè) EXEC sys.sp_cdc_start_jobN'cleanup' GO --再次查看 EXEC sp_cdc_help_jobs GO

得到以下結(jié)果：

證明修改成功，此處注意，修改后要先停用（如果已經(jīng)啟用），再啟用，才能生效。

4、停止/啟用、刪除/創(chuàng)建作業(yè)：

停止/開始作業(yè)，可以使用以下語句：

復(fù)制代碼

代碼如下:

--停用作業(yè) EXEC sys.sp_cdc_stop_jobN'cleanup' GO --啟用作業(yè) EXEC sys.sp_cdc_start_jobN'cleanup' GO

刪除作業(yè)：

復(fù)制代碼

代碼如下:

EXEC sys.sp_cdc_drop_job@job_type= N'cleanup' -- nvarchar(20) GO --查看作業(yè) EXEC sys.sp_cdc_help_jobs GO

可以看到現(xiàn)在只剩下一個(gè)作業(yè)了:

創(chuàng)建作業(yè)：

復(fù)制代碼

代碼如下:

EXEC sys.sp_cdc_add_job @job_type= N'cleanup', @start_job = 0, @retention = 5760 --查看作業(yè) EXEC sys.sp_cdc_help_jobs GO

下面看到已經(jīng)創(chuàng)建成功:

5、DDL變更捕獲：

CDC除了捕獲數(shù)據(jù)變更之外，還能捕獲DDL操作的變化。前提是先要確保SQLServer 代理的啟用，其實(shí)CDC功能都需要確保sql 代理正常運(yùn)行，因?yàn)樗胁僮鞫纪ㄟ^代理中的兩個(gè)作業(yè)來實(shí)現(xiàn)的。

現(xiàn)在先來對HumanResources.Department 表修改一下，把name的長度加長：

復(fù)制代碼

代碼如下:

ALTER TABLEHumanResources.DepartmentALTER COLUMN Name NVARCHAR(120); GO

然后查詢ddl記錄表：

復(fù)制代碼

代碼如下:

SELECT * FROM cdc.ddl_history

可以看到：

由于在截圖之前已經(jīng)試了幾次，所以里面有3條數(shù)據(jù)，之所以試了幾次，就是因?yàn)樯厦嫠f的，忘了開SQL代理，所以查不出數(shù)據(jù)，所以切記要開啟SQL代理。

6、使用CDC的函數(shù)來獲取更改：

A、使用cdc.fn_cdc_get_all_changes_HumanResources_Department 函數(shù)報(bào)告捕獲實(shí)例HumanResources_Department 的當(dāng)前所有可用更改：

復(fù)制代碼

代碼如下:

DECLARE @from_lsn binary(10),@to_lsn binary(10) SET @from_lsn= sys.fn_cdc_get_min_lsn('HumanResources_Department') SET @to_lsn = sys.fn_cdc_get_max_lsn() SELECT *FROM cdc.fn_cdc_get_all_changes_HumanResources_Department (@from_lsn,@to_lsn, N'all update old'); GO

B、獲取某個(gè)時(shí)間段的更改信息：

先根據(jù)日志序列號（logsequence number ,LSN）來獲取跟蹤變更數(shù)據(jù)：

Sys.fn_cdc_map_time_to_lsn獲取變更范圍內(nèi)的最大、最小LSN值。可以使用：

Smallest greater than;smallest greater than orequal;largest less than;largest less than or equal.

如查詢某個(gè)時(shí)間段插入的數(shù)據(jù)：

--插入數(shù)據(jù)

復(fù)制代碼

代碼如下:

INSERT INTOHumanResources.Department(name,GroupName,ModifiedDate) VALUES('test','abc',GETDATE()) INSERT INTOHumanResources.Department(name,GroupName,ModifiedDate) VALUES('test1','abc1',GETDATE()) go

--檢查數(shù)據(jù)

復(fù)制代碼

代碼如下:

DECLARE @bglsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal','2012-10-12 12:00:00.997') DECLARE @edlsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('largest less than or equal',GETDATE()) SELECT DepartmentID,GroupName,Name FROM cdc.HumanResources_Department_CT WHERE [__$operation]=2AND [__$start_lsn]BETWEEN @bglsn AND @edlsn

得到以下結(jié)果：

C、sys.fn_cdc_map_lsn_to_time 查詢變更時(shí)間：

復(fù)制代碼

代碼如下:

SELECT [__$operation] , CASE [__$operation] WHEN 1 THEN '刪除'WHEN 2 THEN '插入' WHEN 3 THEN '更新（捕獲的列值是執(zhí)行更新操作前的值）' WHEN 4 THEN '更新（捕獲的列值是執(zhí)行更新操作后的值）'END [類型], sys.fn_cdc_map_lsn_to_time([__$start_lsn])[更改時(shí)間], name , DepartmentID , GroupName , ModifiedDate FROM cdc.HumanResources_Department_CT

結(jié)果: