MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

0    50    1

Tags:

👉 本文共约13835个字,系统预计阅读时间或需53分钟。

CDC简介

在2008版本之前,通常使用DML触发器监控对表数据库的变更,但是触发器的维护比较困难,性能也不高。2008推出了新功能 变更数据捕获(Change Data Capture,CDC)可以用捕获对表的DML操作,常用于ETL,同步至其他(类型)数据库。
当在一个表上启用CDC 时,SQL Server 会创建一个系统更改表,更改表包含元数据列及与被跟踪表相同的列。CDC 的数据源为 SQL Server 事务日志,在将DML应用于跟踪的源表时,捕获进程读取日志,将记录发生的更改记录到更改表中。系统还将提供一些变更数据查询函数,通过指定范围访问更改的数据,并以过滤结果集的形式返回。
对于启用CDC的表DDL操作不会被阻止,但新增列也不会被映射;如果是删除一列,目标库该表对应列将返回null值而不是被删除。可以为表创建另一个捕获实例映射新架构,每个表最多可以有2个跟踪实例。
MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

CDC实现过程

1. 启用CDC

例如我们的测试库名为CDC_DB

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

这个过程会在当前数据库下创建6个系统表

  • cdc.captured_columns
  • cdc.change_tables
  • cdc.ddl_history
  • cdc.index_columns
  • cdc.lsn_time_mapping
  • dbo.systranschemas

启动之后会自动创建一些系统表、新用户和架构
MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

2. 创建测试表,对表变更启用CDC

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍
MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍
Department表启用CDC后,首先会创建cdc.dbo_Department_CT系统更改表(CT代表Capture Table,表名为:用户.架构_表_CT)。对于每一个表启用CDC的表,都会生成一个对应的更改表。
MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍
另外会创建两个作业 cdc.dbname_capture和cdc.dbname_cleanup(捕获和清理作业),清理作业默认凌晨2点执行,清除72小时以上的数据。如果同一数据库的表已经启用CDC,不会重建job。需要开启SQL Server Agent服务,不然会报错。
MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍
多了个数据库角色
MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

3. DML测试

测试DML操作,观察cdc.dbo_Department_CT帮我们记录些什么。

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍
对于insert/delete操作,会有对应的一行记录,而对于update,会有两行记录。
__$operation列:1 = 删除、2= 插入、3= 更新(旧值)、4= 更新(新值);后面几列与表原有列相同。

4. 获取更改数据

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

CDC的维护

禁用表(“dbo.t1”)

禁用数据库CDC

1. 获取配置信息

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍
由于前面 sys.sp_cdc_enable_table 的参数 @captured_column_list = NULL,所以dbo.Department表的所有字段都进行监控了,如果你只关心某些字段,可以在创建捕获时指定。

2. 获取job信息

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍
alwayson主从切换后,如果业务有配置CDC,目标库需要使用sp_cdc_add_job 创建cdc job(不要导出脚本然后在从库建,job执行会报错)

Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server) | Microsoft Docs
对于用户表所做的 DML 更改,2008版本除了CDC之外,其实还有一个新增功能——更改跟踪(Chang Tracking,CT),它跟CDC有什么不同?如何配置和管理?

开启CDC数据同步实验

1、 数据库需要开启代理服务。

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

开启方式:

​ a). 点击开始菜单--》SQL Server配置管理工具--》SQLserver服务--》SQLserver代理(右键)启动

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

b). 打开电脑服务,找到SQLserver 代理,点击左侧状态

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

2、 数据库配置

a).首先查看数据库是否已经开启CDC服务

返回已经开启CDC的数据库,如果为空,则所有的库都没有开启CDC服务,转到步骤b,对数据库开启CDC。如果有结果,则转到步骤c。

b).对数据库开启CDC服务

检查是否开启成功:

创建成功后,将自动添加CDC用户和CDC架构。

在用户和架构下面可以看到cdc用户和cdc架构

c.查看当前已经开启CDC的数据表。

d.开启表CDC

示例:

对'USRALMHS'表开启变更捕获

如果不想控制访问角色,则@role_name必须显式设置为null。

查询是否成功

此时,已经可以看到相关的cdc进程了:

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

对表开启成功后,可以查看数据库,在数据库系统表下增加了很多表。

在SQLserver 代理中多了两个作业:在可编程性-》函数-》表值函数里,也多了两个函数

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

3、 测试

a) 向表中插入数据

在DBO_USRALMHS_CT中查看:
会有同样的1000 条数据,唯一不同的是在DBO_USRALMHS_CT中会多几个字段,分别代表不同的含义,其中最主要的是 __$operation 代表含义 1 删除、2插入、3更新前的内容、4更新后的内容 @bglsn 开始时间的时间戳 @edlsn 结束时间的时间戳

MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍

b) 测试更新和删除操作(生成数据的__$operation 不同)

省略

4、 分析(系统自带数据库)

a) 分析存储过程

1.sys.sp_cdc_add_job

在当前数据库中创建变更数据捕获清理或捕获作业

1.创建捕获作业

USE AdventureWorks2008R2;

GO

EXEC sys.sp_cdc_add_job

​ @job_type = N'capture';

GO

2.创建清理作业

---创建清理作业,作业连续运行,更改数据行将在更改表中保留2880分钟,清除时使用一条语句最多删除4000条记录

USE AdventureWorks2008R2;

GO

EXEC sys.sp_cdc_add_job

​ @job_type = N'cleanup'

​ ,@start_job=1

​ ,@retention=2880

​ ,@threshold =4000

2.sys.sp_cdc_change_job

修改当前数据库中变更数据捕获清除或捕获作业的配置

--仅在使用 sp_cdc_stop_job 停止作业并使用 sp_cdc_start_job 重新启动该作业后,对该作业所做的更改才会生效

1.更改捕获作业

--将每个循环扫描最多处理的事务数更改为200,为了从日志中提取所有行而要执行的最大扫描循环50次

USE AdventureWorks2008R2;

GO

EXECUTE sys.sp_cdc_change_job

​ @job_type = N'capture',

​ @maxtrans = 200,

​ @maxscans = 50;

GO

2.更改清除作业,将记录保留时间更改为3440分钟

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
MSSQL变更数据捕获(CDC)和 更改跟踪(Chang Tracking)介绍后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部