SqlServer CDC 变更数据捕获

最新推荐文章于 2024-06-06 14:38:15 发布

yanzean

最新推荐文章于 2024-06-06 14:38:15 发布

阅读量758

点赞数

文章标签： sqlserver 数据库 microsoft

本文链接：https://blog.csdn.net/yanzean/article/details/128183524

版权

SqlServer变更数据捕获（CDC）

简介

CDC(Change Data Capture，变更数据捕获)。主要原理为，通过对事务日志的异步读取，记录 DML 操作的发生时间、类型和实际影响的数据变化，然后将这些数据记录到启用 CDC 时自动创建的表中。通过 cdc 相关的存储过程，可以获取详细的数据变化情况。由于数据变化是异步读取的，因此对整体性能的影响不大，远小于通过Trigger实现的数据变化记录。

变更数据捕获适用版本：

SQL Server 2008 以上的 Enterprise Edition、Developer Edition 和 Evaluation Edition

使用实例

--  查看数据库是否启用cdc

SELECT name ,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1

--  查看当前数据库表是否启用cdc

SELECT name ,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 1

--  对当前数据库启用cdc

USE MyDatabase
GO
EXECUTE sys.sp_cdc_enable_db;
GO

可能出现以下错误及解决办法：

/*

消息 22830，级别 16，状态 1，过程 sp_cdc_enable_db_internal，第 186 行

无法更新元数据来指示已对数据库 MyDatabase 启用了变更数据捕获。执行命令 'SetCDCTracked(Value = 1)' 时失败。

返回的错误为 15404: '无法获取有关 Windows NT 组/用户 'KK\administrator' 的信息，错误代码 0x54b。'。

请使用此操作和错误来确定失败的原因并重新提交请求。

消息 266，级别 16，状态 2，过程 sp_cdc_enable_db_internal，第 0 行

EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 0，当前计数 = 1。

消息 266，级别 16，状态 2，过程 sp_cdc_enable_db，第 0 行

EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 0，当前计数 = 1。

消息 3998，级别 16，状态 1，第 1 行

在批处理结束时检测到不可提交的事务。该事务将回滚。

*/

--  原因是数据库所有者为Windows用户，改为“sa”

EXEC dbo.sp_changedbowner @loginame = N 'sa' , @map = false

GO

--依赖别名已删除

启动数据库cdc后，接着对指定源表启用 cdc ：

--  接着对指定源表启用cdc

EXEC sys.sp_cdc_enable_table

@source_schema= 'dbo' ,      --源表架构

@source_name = 'CDC_Test' ,  --源表

@role_name = 'CDC_Role'     --角色（将自动创建）

GO

--作业 'cdc.MyDatabase_capture' 已成功启动。

--作业 'cdc.MyDatabase_cleanup' 已成功启动。

对作业的更改 (参考 sys.sp_cdc_change_job)

--对作业的更改

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_stop_job @job_type = N 'capture' ;

EXEC sys.sp_cdc_stop_job @job_type = N 'capture' ;

EXEC sys.sp_cdc_start_job @job_type = N 'cleanup' ;

EXEC sys.sp_cdc_start_job @job_type = N 'cleanup' ;

禁用（删除）变更数据捕获

--  对表禁用变更数据捕获

USE MyDatabase;

GO

EXEC sys.sp_cdc_disable_table

@source_schema = N 'dbo' ,

@source_name   = N 'CDC_Test' ,

@capture_instance = N 'dbo_CDC_Test'

GO

--  对数据库禁用变更数据捕获

USE MyDatabase;

GO

EXECUTE sys.sp_cdc_disable_db;

GO

--执行完成后,相关的表、函数、用户、角色、架构、作业都会完全删除！

优缺点总结

优点：
1、可以对单个表进行监控，也可以对单个表的某些字段进行监控，使用较为灵活；
2、对用户修改以前的历史记录可以有效捕捉，因此可以解决没有时间戳的变更问题；
3、使用这种技术，就可以不用再使用 triger 这种低效高耗的技术；
4、是一种很好的向数据仓库或数据中心增量加载数据的好方法。

缺点：
1、CDC 激活会显著增加日志文件的读操作。
2、CDC 激活后更新跟踪表会产生额外的写入，并消耗存储空间。
3、CDC 激活后，原数据表的聚簇索引尺寸会影响到 CDC 产生的 IO 数据量，而原始数据表上的非聚簇索引则不会。
4、CDC 激活后，被选定进行更新跟踪的列键值属性同样会影响到 CDC 产生的 IO 数据量和存储空间。
5、如果某部分日志，CDC 的进程还没有读取，那么在截断日志时就会忽略这个部分（截断日志或收缩日志都会对 CDC 有一定影响，需要考虑日志维护策略）