开启SQLServer数据库的CDC功能

变更数据捕获（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'

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

三、使用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

原文链接可参考：https://www.cnblogs.com/wang2650/p/14367008.html

作者：过错
出处：http://www.cnblogs.com/wang2650/
关于作者：net开发做的久而已。十余年时光虚度！
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接。如有问题，可以邮件：wang2650@163.com  联系我，非常感谢。