SQL Server变更数据捕获（CDC）（转载）
变更数据捕获（Change Data Capture ，简称 CDC）：记录 SQL Server 表的插入、更新和删除操作。开启cdc的源表在插入、更新和删除操作时会插入数据到日志表中。cdc通过捕获进程将变更数据捕获到变更表中，通过cdc提供的查询函数，可以捕获这部分数据。
1、sqlserver 2008 以上版本
2、需要开启sql代理服务
3、磁盘要有足够的空间，保存日志文件
4、表必须要有主键或者是唯一索引
if exists(select 1 from sys.databases where name='db_name' and is_cdc_enabled=0) begin exec sys.sp_cdc_enable_db end
此时刷新，会发现数据库的schemas下多出了cdc
select is_cdc_enabled from sys.databases where name='db_name'
查询结果为“1”，表示开启成功。
注意：表中必须有主键或者唯一索引
选中数据库，右击属性。
注意：CDC是数据库文件组的名称，table_name改为对应表名。
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
SELECT is_tracked_by_cdc FROM sys.tables WHERE name='table_name'
查询结果为“1”，表示开启成功。
- cdc.change_tables：表开启cdc后会插入一条数据到这张表中，记录表一些基本信息。
- cdc.captured_columns：开启cdc后的表，会记录它们的字段信息到这张表中。
- cdc.dbo_C_CT：记录C表所有数据的变更情况。
select * from [cdc].[dbo_C_CT]
（1）字段$operation
- 红色部分是新增数据（操作代码2表示新增）
- 蓝色部分是删除数据（操作代码1表示删除）
- 绿色部分是修改数据（操作代码3是执行更新前的数据，4是更新后的数据）
（2）“$start_lsn”由于更改是来源于数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）
（3）$update_mask : 表示哪个列做了操作，上图07就是 0111 表示123列都做了修改，06就是0110表示23列做了修改。
注意：当修改了表结构，例如字段类型、字段增减等会有问题，需要重新做一个捕获实例。
增加字段，只会捕获原有字段。
删减字段，不会捕获，禁用该表CDC也会报错，需要禁用数据库CDC，然后再开启数据库CDC和该表CDC功能。
停用作业
EXEC sys.sp_cdc_stop_job N'cleanup' GO
启用作业
EXEC sys.sp_cdc_start_job N'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
查看表C是否启用了CDC
select name, is_tracked_by_cdc from sys.tables where object_id = OBJECT_ID('C')
禁用表C
EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'C', @capture_instance = 'all';
禁用数据库CDC
EXEC sys.sp_cdc_disable_db;
启用数据库CDC
EXECUTE sys.sp_cdc_enable_db;
查看已启用CDC的数据库
SELECT name,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1;
begin declare @temp1 varchar(100) -- 申明游标为表名 declare tb_cursor cursor for (select name from sys.tables WHERE is_tracked_by_cdc=0 and schema_id=1) -- 打开游标 open tb_cursor -- 开始循环游标变量 fetch next from tb_cursor into @temp1 -- 返回fetch语句执行的最后游标的状态 while @@fetch_status=0 begin EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = @temp1, @role_name = NULL -- 转到下一个游标 fetch next from tb_cursor into @temp1 end -- 关闭游标 close tb_cursor -- 释放游标 deallocate tb_cursor end ;
begin declare @temp2 varchar(100) -- 申明游标为表名 declare tb_cursor cursor for (select name from sys.tables WHERE is_tracked_by_cdc=1 and schema_id=1) -- 打开游标 open tb_cursor -- 开始循环游标变量 fetch next from tb_cursor into @temp2 -- 返回fetch语句执行的最后游标的状态 while @@fetch_status=0 begin EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = @temp2, @capture_instance = 'all' -- 转到下一个游标 fetch next from tb_cursor into @temp2 end -- 关闭游标 close tb_cursor -- 释放游标 deallocate tb_cursor end ;
What is change data capture (CDC)?
Enable and disable change data capture
其中这里有讲到，如何通过下面两个函数来获取到CDC中变更的数据。
cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)
cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)
