變更資料捕獲（Change Data Capture ，簡稱 CDC）記錄 SQL Server 表的插入、更新和删除活動。SQLServer的操作會寫日志，這也是CDC捕獲資料的來源
開啟cdc的源表在插入、更新和删除活動時會插入資料到日志表中。cdc通過捕獲程序将變更資料捕獲到變更表中，通過cdc提供的查詢函數，我們可以捕獲這部分資料。
同時也可以捕獲ddl的修改
附一個測試用的sql檔案（來源于debezium 工具的sqlserver腳本，工具類似mysql的canal和maxwell。） https://files.cnblogs.com/files/wang2650/testdbsql.zip
####### 開啟CDC的必要條件
1 sqlserver 2008 以上版本
2 需要開啟代理服務（作業）
3 磁盤要有足夠的空間，儲存日志檔案
4 表必須要有主鍵或者是唯一索引
####### 開啟資料庫CDC
1、 在需要開啟cdc的資料庫上執行腳本如下：
if exists(select 1 from sys.databases where name='db_name' and is_cdc_enabled=0)
begin
exec sys.sp_cdc_enable_db
end
2、查詢資料庫的cdc開啟狀态
select is_cdc_enabled from sys.databases where name='db_name'
查詢結果為“1”，表示開啟成功。
開啟表CDC
*注意：表中必須有主鍵或者唯一索引
1、添加次要資料檔案組及檔案
資料庫右鍵“屬性” >> “檔案組”>> ”添加”
“檔案” >> “添加”
2、執行以下腳本，開啟表cdc
--CDC是資料庫檔案組的名稱
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='table_name' AND is_tracked_by_cdc = 0)
BEGIN
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo', -- source_schema
@source_name = 'table_name', -- table_name
@capture_instance = NULL, -- capture_instance
@supports_net_changes = 1, -- supports_net_changes
@role_name = NULL, -- role_name
@index_name = NULL, -- index_name
@captured_column_list = NULL, -- captured_column_list
@filegroup_name = 'CDC' -- filegroup_name
END
3、檢視表cdc開啟狀态
SELECT is_tracked_by_cdc FROM sys.tables WHERE name='table_name'
三、使用CDC
開啟cdc後會在資料庫中生成以下檔案，開啟資料庫GY_DB，開啟表VW_GHZDK
下面我們會對部分表和函數進行說明
系統表：
cdc.change_tables：表開啟cdc後會插入一條資料到這張表中，記錄表一些基本資訊
cdc.captured_columns：開啟cdc後的表，會記錄它們的字段資訊到這張表中
cdc.dbo_VW_GHZDK_CT：記錄VW_GHZDK表中所有變更的資料，
字段“$operation”為“1”代表删除，“2”代表插入，“3”執行更新操作前的值，“4”執行更新操作後的值。
字段“$start_lsn”由于更改是來源于資料庫的事務日志，是以這裡會儲存其事務日志的開始序列号（LSN）
字段 __$update_mask : 表示那個列做了操作，02 就是0010 表示第二列 ，那07就是 0111 表示123列都做了修改羅
注意，當修改了表結構，例如字段類型等會有問題，最好從心做一個捕獲執行個體
參考文章 https://www.cnblogs.com/tiancai/p/11996801.html
可以在聯機叢書上檢視：cdc.<capture_instance>_CT 可以看到，這樣命名的表，是用于記錄源表更改的表。對于insert/delete操作，會有對應的一行記錄，而對于update，會有兩行記
函數：
cdc.fn_cdc_get_all_changes_dbo_VW_GHZDK：針對在指定日志序列号 (LSN) 範圍内應用到源表的每項更改均傳回一行。如果源行在該間隔内有多項更改，則每項更改都會表示在傳回的結果集中
cdc.fn_cdc_get_net_changes_dbo_VW_GHZDK：針對指定 LSN 範圍内每個已更改的源行傳回一個淨更改行。也就是說，如果在 LSN 範圍内源行具有多項更改，則該函數将傳回反映該行最終内容的單一行
sys.fn_cdc_map_time_to_lsn：為指定的時間傳回 cdc.lsn_time_mapping 系統表中 start_lsn 列中的日志序列号 (LSN) 值。可以使用此函數系統地将日期時間範圍映射到基于 LSN 的範圍，以供變更資料捕獲枚舉函數 cdc.fn_cdc_get_all_changes_<capture_instance> 和 cdc.fn_cdc_get_net_changes_<capture_instance> 傳回此範圍内的資料更改。
以上文章參考 https://www.cnblogs.com/maikucha/p/9039205.html
https://blog.csdn.net/dba_huangzj/article/details/8130448 這個文章更詳細
其他
停止/開始作業，可以使用以下語句：
--停用作業
EXEC sys.sp_cdc_stop_jobN'cleanup'
GO
--啟用作業
EXEC sys.sp_cdc_start_jobN'cleanup'
GO
--對作業的更改 非常重要 尤其是retention參數。
EXEC sys.sp_cdc_change_job
@job_type = 'capture'
,@maxtrans = 1000 --每個掃描循環可以處理的最多事務數
,@maxscans = 10 --為了從日志中提取所有行而要執行的最大掃描循環次數
,@continuous = 1 --連續運作最多處理(max_trans * max_scans) 個事務
,@pollinginterval = 5
EXEC sys.sp_cdc_change_job
@job_type = 'cleanup'
,@retention = 4320 --更改行将在更改表中保留的分鐘數
,@threshold = 5000 --清除時可以使用一條語句删除的删除項的最大數量
删除作業：
EXEC sys.sp_cdc_drop_job@job_type = N'cleanup' -- nvarchar(20)
GO
--檢視作業
EXEC sys.sp_cdc_help_jobs
GO
建立作業：
EXEC sys.sp_cdc_add_job
@job_type = N'cleanup',
@start_job = 0,
@retention = 5760
--檢視作業
EXEC sys.sp_cdc_help_jobs
GO
檢視表是否啟用了CDC
select name, is_tracked_by_cdc from sys.tables where object_id = OBJECT_ID('dbo.t1')
禁用表(“dbo.t1”)
EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 't1', @capture_instance = 'all';
禁用資料庫CDC
EXEC sys.sp_cdc_disable_db;
根據釋出批量生成表
SELECT 'EXEC sys.sp_cdc_enable_table @source_schema = N'''+b.source_owner+''','
+'@source_name='''+b.source_object+''','+'@role_name=''cdc'',@supports_net_changes = 1'
FROM dbo.MSpublications a,dbo.MSarticles b
WHERE a.publication_id=b.publication_id AND a.publisher_db=b.publisher_db
and a.publication ='his_repl'
ddl的捕獲
ELECT * FROM cdc.ddl_history
根據系統表批量生成表
select 'EXEC sys.sp_cdc_enable_table @source_schema = ''dbo'', @source_name = '''+name+''', @role_name = null;'
from sysobjects where xtype='U' and category ='0'
擷取某個時間段的更改資訊： 先根據日志序列号（logsequence number ,LSN）來擷取跟蹤變更資料：
Sys.fn_cdc_map_time_to_lsn擷取變更範圍内的最大、最小LSN值。可以使用： Smallest greater than;smallest greater than orequal;largest less than;largest less than or equal.
如查詢某個時間段插入的資料：
--插入資料
INSERT INTO HumanResources.Department(name,GroupName,ModifiedDate)
VALUES('test','abc',GETDATE())
INSERT INTO HumanResources.Department(name,GroupName,ModifiedDate)
VALUES('test1','abc1',GETDATE())
go
--檢查資料
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]=2 AND [__$start_lsn] BETWEEN @bglsn AND @edlsn
sys.fn_cdc_map_lsn_to_time 查詢變更時間：
SELECT [__$operation] ,
CASE [__$operation] WHEN 1 THEN '删除' WHEN 2 THEN '插入' WHEN 3 THEN '更新（捕獲的列值是執行更新操作前的值）'
WHEN 4 THEN '更新（捕獲的列值是執行更新操作後的值）' END [類型],
sys.fn_cdc_map_lsn_to_time([__$start_lsn]) [更改時間] ,
name , DepartmentID , GroupName , ModifiedDate
FROM cdc.HumanResources_Department_CT
擷取LSN邊界：
SELECT sys.fn_cdc_get_max_lsn()[資料庫級别的最大LSN],
sys.fn_cdc_get_min_lsn('cdc.HumanResources_Department_CT')[捕獲執行個體的lsn]
結果如下：
這兩個值可以用于上面提到的函數裡面用于篩選資料之用。
原文 https://www.cnblogs.com/zzchao/p/10918494.html
查詢 ： 最好給表加一個自增長的主鍵或者通過關聯lsn_time_mapping表，擷取指定時間内的記錄檔。
select top 100 * from [testdb].[cdc].[dbo_userinfo_ct] where [__$start_lsn]>0x0000002e000004
作者：
過錯出處：http://www.cnblogs.com/wang2650/
關于作者：net開發做的久而已。十餘年時光虛度！
本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接。如有問題，可以郵件：[email protected]
聯系我，非常感謝。