SQL Server变更数据捕获（CDC）（转载）

一、CDC简介

二、开启CDC的必要条件

三、开启数据库CDC

1、在需要开启CDC的数据库上执行脚本如下

2、查询数据库的CDC开启状态

四、开启表CDC

1、添加数据文件组和文件

2、执行以下脚本，开启某个表的CDC

3、查看某个表CDC的开启状态

五、CDC使用

1、对表C开启CDC后，会生成系统表以及代理作业等

2、系统表

3、作业

4、其它常用命令

六、对所有表启用和关闭CDC

1、批量开启所有表CDC功能

2、批量关闭所有表CDC功能

七、微软官方文档

 

一、CDC简介

变更数据捕获（Change Data Capture ，简称 CDC）：记录 SQL Server 表的插入、更新和删除操作。开启cdc的源表在插入、更新和删除操作时会插入数据到日志表中。cdc通过捕获进程将变更数据捕获到变更表中，通过cdc提供的查询函数，可以捕获这部分数据。

 

二、开启CDC的必要条件

1、sqlserver 2008 以上版本
2、需要开启sql代理服务
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

此时刷新，会发现数据库的schemas下多出了cdc

 

2、查询数据库的CDC开启状态

select is_cdc_enabled from sys.databases where name='db_name'

查询结果为“1”，表示开启成功。

 

四、开启表CDC

注意：表中必须有主键或者唯一索引

1、添加数据文件组和文件

选中数据库，右击属性。

 

2、执行以下脚本，开启某个表的CDC

注意：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

 

3、查看某个表CDC的开启状态

SELECT is_tracked_by_cdc FROM sys.tables WHERE name='table_name'

查询结果为“1”，表示开启成功。

 

五、CDC使用

1、对表C开启CDC后，会生成系统表以及代理作业等

 

2、系统表

• 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功能。

 

3、作业

停用作业

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

 

4、其它常用命令

查看表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;

 

六、对所有表启用和关闭CDC

1、批量开启所有表CDC功能

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 ;

 

2、批量关闭所有表CDC功能

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)

 

原文链接