SQL Server審計功能入門：CDC（Change Data Capture）

標籤：sql server   audit   cdc   

介紹

SQL Server 2008引入了CDC(Change Data Capture),它能記錄：

1. 哪些資料行發生了改變

2. 資料行變更的記錄，而不僅僅是最終值。

跟CT(Change Tracking)相比，它通過作業實現非同步變更跟蹤(像事務複製)，而CT是同步實現的。因此它對效能的影響較輕並且不會影響事務。

典型應用是在提取、傳輸和載入資料到其它資料來源，就像圖中的資料倉儲。

650) this.width=650;" width="147" height="244" title="clip_image001" style="border-top-width:0px;border-right-width:0px;border-bottom-width:0px;" alt="clip_image001" src="http://s3.51cto.com/wyfs02/M00/5A/16/wKioL1T2fBnS_cp_AABWu-FTkdc786.jpg" border="0" />

實現

微軟建議CDC結合快照快照隔離等級使用，可以避免讀取變更資料與變更資料寫入時的讀寫阻塞。

需要注意：快照隔離等級會有額外的開銷，特別是Tempdb(所有的資料更改都會被版本化存到tempdb)。

use mastergocreate database CDCTestgoalter database CDCTest set allow_snapshot_isolation ongo--enable CDC on database CDCTestuse CDCTestgoexec sys.sp_cdc_enable_dbgo

啟用CDC之後會新增一個叫CDC的Schema和一系列的系統資料表、SP和View。官方建議不要直接查詢系統資料表而是使用對應的系統SP/FN來擷取CDC資料。

650) this.width=650;" width="210" height="197" title="clip_image002" style="border-top-width:0px;border-right-width:0px;border-bottom-width:0px;" alt="clip_image002" src="http://s3.51cto.com/wyfs02/M01/5A/16/wKioL1T2fBniNEWQAACoci0QnZg012.jpg" border="0" />  

系統對象	說明	建議使用的對象
cdc.captured_columns	為在捕獲執行個體中跟蹤的每一列返回一行	sys.sp_cdc_get_source_columns
cdc.change_tables	為資料庫中的每個更改表返回一行	sys.sp_cdc_help_change_data_capture
cdc.ddl_history	針對啟用了變更資料擷取的表所做的每一資料定義語言 (Data Definition Language) (DDL) 更改返回一行	sys.sp_cdc_get_ddl_history
cdc.lsn_time_mapping	為每個在更改表中存在行的事務返回一行	sys.fn_cdc_map_lsn_to_time (Transact-SQL) , sys.fn_cdc_map_time_to_lsn (Transact-SQL)
cdc.index_column	為與更改表關聯的每一索引列返回一行	sys.sp_cdc_help_change_data_capture
msdb.dbo.cdc_jobs	儲存用於捕獲和清除作業的變更資料擷取配置參數	NA
cdc.<capture_instance>_CT	對源表啟用變更資料擷取時建立的更改表。 該表為對源表執行的每個插入和刪除操作返回一行，為對源表執行的每個更新操作返回兩行.capture_instance格式=SchameName_TableName	cdc.fn_cdc_get_all_changes_<capture_instance> , cdc.fn_cdc_get_net_changes_<capture_instance>

建立測試表並對期啟用CDC。使用sys.sp_cdc_enable_table 對錶啟用CDC。

--Create a test table for CDCuse CDCTestGOcreate table tb(ID int primary key ,name varchar(20),weight decimal(10,2));goEXECUTE [email protected]_schema = N‘dbo‘  , @source_name = N‘tb‘  , @role_name = null;GO

如果源表是資料庫中第一個要啟用變更資料擷取的表，並且資料庫不存在事務發布，則 sys.sp_cdc_enable_table 還將為資料庫建立捕獲和清理作業。 它將 sys.tables 目錄檢視中的 is_tracked_by_cdc 列設定為 1。

對應的跟蹤表cdc.dbo_tb_CT包含了源表所有的變更資料。它包含原來所有的列和5個新的列，結構

650) this.width=650;" width="226" height="187" title="clip_image003" style="border-top-width:0px;border-right-width:0px;border-bottom-width:0px;" alt="clip_image003" src="http://s3.51cto.com/wyfs02/M02/5A/16/wKioL1T2fBqioIReAACsTDMEsIE160.jpg" border="0" />

驗證

當在源表中操行資料更改操作，表cdc.dbo_tb_CT會記錄下來。試一下：

650) this.width=650;" width="470" height="168" title="clipboard[22]" style="border-top-width:0px;border-right-width:0px;border-bottom-width:0px;" alt="clipboard[22]" src="http://s3.51cto.com/wyfs02/M01/5A/16/wKioL1T2fBrQ__AbAACLS_ooZNc833.jpg" border="0" />

為什麼沒有資料呢？因為之前介紹過了，CDC是靠作業來捕獲變更資料的，我的Agent還沒有運行。

手動啟用後，就有資料了。

650) this.width=650;" width="632" height="61" title="clip_image005" style="border-top-width:0px;border-right-width:0px;border-bottom-width:0px;" alt="clip_image005" src="http://s3.51cto.com/wyfs02/M00/5A/1A/wKiom1T2ewfQptR5AACLOVAeR1o944.jpg" border="0" />

結果列的含義：

列名	資料類型	說明
__$start_lsn	binary(10)	更改提交的LSN。在同一事務中提交的更改將共用同一個提交 LSN 值。
__$seqval	binary(10)	一個事務內可能有多個更改發生，這個值用於對它們進行排序。
__$operation	int	更改操作的類型： 1 = 刪除 2 = 插入 3 = 更新（捕獲的列值是執行更新操作前的值）。 4 = 更新（捕獲的列值是執行更新操作後的值）。
__$update_mask	varbinary(128)	位元遮罩，源表中被CDC跟蹤的每一列對應一個位。如果 __$operation = 1 或 2，該值將所有已定義的位設定為 1。如果 __$operation = 3 或 4，則只有那些對應已更改列的位設定為 1。

現在再插入一行，並更新它，然後再刪除ID=1的行。再查看結果：

650) this.width=650;" width="636" height="298" title="clip_image006" style="border-top-width:0px;border-right-width:0px;border-bottom-width:0px;" alt="clip_image006" src="http://s3.51cto.com/wyfs02/M01/5A/1A/wKiom1T2ewjDP-fIAAGwcNsX5ME864.jpg" border="0" />

簡單說明一下跟蹤的查詢結果：總共5行，第一行和第二行是插入資料，第三行和第四行是更新前後的資料，第五行是刪除資料。操作類型由_$operation值可得知。

簡單應用

前文中建立的tb表，記錄了每個人的姓名和體重變化資訊。另外某一個資料庫(表tb_rs)，它是體重變化趨勢報表的資料來源。它每天同步一次資料，更新自己的資料。怎麼用CDC來實現這個需求呢？

      CDC中記錄了start_lsn,如果能知道tb_rs上次同步完成時，tb中被同步的最大LSN。那下次同步時，只需要同步tb表中大於此LSN的變更記錄即可。

問題就簡單：擷取上次同步完成tb的最大LSN,擷取大於此LSN的所有變更記錄，更新tb_rs。

• 由sys.fn_cdc_map_time_to_lsn可以將時間映射到對應的LSN，時間就是前一天。

• 由cdc.fn_cdc_get_net_changes_<capture_instance>能得到一天內的所有的淨變更記錄。

• 由變更記錄自訂同步邏輯和語句。

insert into tbvalues(1,‘Ken‘,70.2),(3,‘Joe‘,66),(4,‘Rose‘,50)update tbset weight=70where ID=3;delete from tb where name=‘Rose‘;goDECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10); --get the intervalselect @begin_time=GETDATE()-1,@end_time=GETDATE();--map the time to LSN of the CDC table tbselect  @begin_lsn = sys.fn_cdc_map_time_to_lsn(‘smallest greater than or equal‘, @begin_time),  @end_lsn = sys.fn_cdc_map_time_to_lsn(‘largest less than or equal‘, @end_time);--get the net changes within the specified LSNsSELECT * FROM cdc.fn_cdc_get_net_changes_dbo_tb(@begin_lsn, @end_lsn, ‘all‘);

650) this.width=650;" width="520" height="99" title="clip_image007" style="border-top-width:0px;border-right-width:0px;border-bottom-width:0px;" alt="clip_image007" src="http://s3.51cto.com/wyfs02/M02/5A/1A/wKiom1T2ewjCy_oWAADFSMwAP9o920.jpg" border="0" />

居然沒有Rose的記錄？Joe的資訊被更新過，怎麼才一條記錄？

這是因為這裡得到是淨變更行，也就是最終結果的意思。新增然後又刪除，不影響最終結果，所以沒有。多次更新同一行的某一列資料，只返回最後更新的結果。

得到這個結果，我們就可以根據__$operation和實際資料定義同步資料的邏輯了。比如：

--generate sync statementsSELECT (case __$operation when 2 then ‘insert into tb_rs values (‘+cast(ID as varchar(2))+‘, ‘+Name+‘, ‘+cast(weight as varchar(10))+‘)‘when 4 then ‘update tb_rs set name=‘+name+‘,weight=‘+cast(weight as varchar(10))+‘ where ID=‘++cast(ID as varchar(2)) END)FROM cdc.fn_cdc_get_net_changes_dbo_tb(@begin_lsn, @end_lsn, ‘all‘);

對於更新過的行，同步資料時，我想要先判斷出列是否被更改過和被更改的時間。更改過的列才需要被同步，而不是所有列同步一次。以name為例：

DECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10); --get the intervalselect @begin_time=GETDATE()-1,@end_time=GETDATE();--map the time to LSN of the CDC table tbselect  @begin_lsn = sys.fn_cdc_map_time_to_lsn(‘smallest greater than or equal‘, @begin_time),  @end_lsn = sys.fn_cdc_map_time_to_lsn(‘largest less than or equal‘, @end_time);--get the all changes within the specified LSNsSELECT *,(Case sys.fn_cdc_has_column_changed(‘dbo_tb‘,‘name‘,__$update_mask) when 1 then ‘Yes‘ when 0 then ‘No‘ End) as isNameUpdated,sys.fn_cdc_map_lsn_to_time(__$start_lsn) as updateTimeFROM cdc.fn_cdc_get_all_changes_dbo_tb(@begin_lsn, @end_lsn, ‘all‘)where __$operation in(3,4);go

CDC不僅能記錄DML操作，還能記錄DDL操作。查詢cdc.ddl_history。

650) this.width=650;" width="727" height="200" title="clip_image008" style="border-top-width:0px;border-right-width:0px;border-bottom-width:0px;" alt="clip_image008" src="http://s3.51cto.com/wyfs02/M00/5A/1A/wKiom1T2ewnjxznaAAF9utRYvtw539.jpg" border="0" />

但有一點要格外注意：新增的列，能被CDC DDL跟蹤到，但是新列的資料變更卻不能被CDC跟蹤到。如果需要跟蹤它，先禁用表上的CDC，再啟用即可。

 

CDC Agent Job

在指定的資料庫中首次啟用CDC，並且不存在事務複製，則會建立capture和cleanup兩個作業：

650) this.width=650;" width="210" height="107" title="clip_image009" style="border-top-width:0px;border-right-width:0px;border-bottom-width:0px;" alt="clip_image009" src="http://s3.51cto.com/wyfs02/M02/5A/16/wKioL1T2fB2gJL-VAABe3IOA29c323.jpg" border="0" />

     capture作業是用於掃描記錄檔，把變更記錄寫到變更表中。調用sp_MScdc_capture_job來實現，可以根據當前庫的實際事務輸送量來設定掃描參數和掃描間隔，使得在效能開銷和跟蹤需求間達到合理平衡。

     cleanup作業是清理變更變表中的資料，預設三天的資料。

所以合理設定cleanup的間隔是非常重要的。

這兩個作業的相關的配置儲存在msdb.dbo.cdc_jobs中。當前的預設配置

650) this.width=650;" width="804" height="91" title="clip_image010" style="border-top-width:0px;border-right-width:0px;border-bottom-width:0px;" alt="clip_image010" src="http://s3.51cto.com/wyfs02/M00/5A/16/wKioL1T2fB6CG98tAADmZfev5N0590.jpg" border="0" />

 

總結

    1. CDC使用方便，易於配置，能與同步抽取等應用結合使用。

    2. CDC能滿足大多數對資料審計的要求，但不能告訴你“誰”更改了資料。

    3. 雖說CDC是非同步，對應效能影響小，但還是會增加開銷，特別是IO讀寫和容量方面的。開啟CDC，每次更改，都至少會額外增加一次資料檔案寫和記錄檔寫操作。

本文出自 “Joe TJ” 部落格，請務必保留此出處http://joetang.blog.51cto.com/2296191/1617163

SQL Server審計功能入門：CDC（Change Data Capture）