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

0    117    1

Tags:

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

CDC简介

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

CDC实现过程

1. 启用CDC

例如我们的测试库名为CDC_DB

wps_clip_image-13454

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

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

启动之后会自动创建一些系统表、新用户和架构
wps_clip_image-29510wps_clip_image-14649

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

wps_clip_image-14851

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

3. DML测试

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

wps_clip_image-29776
对于insert/delete操作,会有对应的一行记录,而对于update,会有两行记录。
__$operation列:1 = 删除、2= 插入、3= 更新(旧值)、4= 更新(新值);后面几列与表原有列相同。

4. 获取更改数据

wps_clip_image-16708

CDC的维护

禁用表(“dbo.t1”)

禁用数据库CDC

1. 获取配置信息

wps_clip_image-26559
由于前面 sys.sp_cdc_enable_table 的参数 @captured_column_list = NULL,所以dbo.Department表的所有字段都进行监控了,如果你只关心某些字段,可以在创建捕获时指定。

2. 获取job信息

wps_clip_image-6647
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、 数据库需要开启代理服务。

img

开启方式:

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

imgimg

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

img

2、 数据库配置

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

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

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

检查是否开启成功:

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

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

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

d.开启表CDC

示例:

对'USRALMHS'表开启变更捕获

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

查询是否成功

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

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

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

imgimgimg

3、 测试

a) 向表中插入数据

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

img

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分钟

USE AdventureWorks2008R2;

GO

EXECUTE sys.sp_cdc_change_job

​ @job_type = N'cleanup',

​ @retention = 3440;

GO

3.sys.sp_cdc_cleanup_change_table

根据指定的 low_water_mark 值从当前数据库的更改表中删除行,重置更改表中的最小 __$start_lsn,并删除小于该值的数据.

将同时清除cdc.HR_Department_CT,cdc.lsn_time_mapping表的记录

USE AdventureWorks2008R2;

GO

EXEC sys.sp_cdc_cleanup_change_table

@capture_instance =N'HR_Department',

@low_water_mark=0x0000037D000000D30008,

@threshold=2000;

SELECT sys.fn_cdc_increment_lsn(sys.fn_cdc_get_max_lsn())

4.sys.sp_cdc_drop_job

从 msdb 中删除当前数据库的变更数据捕获清除或捕获作业。

--下例删除 AdventureWorks2008R2 数据库的清除作业和捕获作业

USE AdventureWorks2008R2;

GO

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

USE AdventureWorks2008R2;

GO

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

5.sys.sp_cdc_disable_db

对当前数据库禁用变更数据捕获

禁用当前对数据库中的所有表启用的变更数据捕获。与变更数据捕获相关的所有系统对象(如更改表、作业、存储过程和函数)都将被删除。sys.databases 目录视图中的数据库条目的 is_cdc_enabled 列设置为 0。

如果在禁用变更数据捕获时为数据库定义了很多捕获实例,则长时间运行事务可能导致 sys.sp_cdc_disable_db 的执行失败。通过在运行 sys.sp_cdc_disable_db 之前使用 sys.sp_cdc_disable_table 禁用单个捕获实例,可以避免此问题。

USE AdventureWorks2008R2;

GO

EXECUTE sys.sp_cdc_disable_db;

GO

6.sys.sp_cdc_disable_table

对当前数据库中指定的源表和捕获实例禁用变更数据捕获

删除与指定的源表和捕获实例相关联的变更数据捕获更改表和系统函数。它会删除任何与来自变更数据捕获系统表的指定捕获实例相关联的行,并将 sys.tables 目录视图中的表项的 is_tracked_by_cdc 列设置为 0。

---下例对 HumanResources.Department 表禁用了变更数据捕获

USE AdventureWorks2008R2;

GO

EXEC sys.sp_cdc_disable_table

​ @source_schema = N'HumanResources'

, @source_name = N'Department'

, @capture_instance = N'HR_Department' ---这里是定义的实例名称,在一开始创建捕获的时候创建的,这里也可以制定ALL(禁用表HumanResources.Department的所有捕获),

7.sys.sp_cdc_enable_db

对当前数据库启用变更数据捕获。必须先对数据库执行此过程,然后才能对该数据库中的任何表启用变更数据捕获。变更数据捕获可记录应用到所启用的表中的插入、更新和删除活动,同时采用易于使用的关系格式提供变更详细信息。此操作将为已修改的行捕获反映了所跟踪源表列结构的列信息,同时还捕获将更改应用到目标环境所需的元数据。

将创建以全数据库为作用域的变更数据捕获对象,包括元数据表和 DDL 触发器。它还会创建 cdc 架构和 cdc 数据库用户,并将 sys.databases 目录视图中的数据库条目的 is_cdc_enabled 列设置为 1。

USE AdventureWorks2008R2;

GO

EXECUTE sys.sp_cdc_enable_db;

GO

8.sys.sp_cdc_enable_table

--对需要进行跟踪的表启动CDC,sys.sp_cdc_enable_table

/*

为当前数据库中指定的源表启用变更数据捕获。对表启用变更数据捕获时,应用于此表的每个数据操纵语言 (DML) 操作的记录都将写入事务日志中。

变更数据捕获进程将从日志中检索此信息,并将其写入可通过使用一组函数访问的更改表中。

*/

sys.sp_cdc_enable_table

[ @source_schema = ] 'source_schema', ---表所属的架构名

[ @source_name = ] 'source_name' ,----表名

[ @role_name = ] 'role_name'---是用于控制更改数据访问的数据库角色的名称。

[,[ @capture_instance = ] 'capture_instance' ]--是用于命名特定于实例的变更数据捕获对象的捕获实例的名称

[,[ @supports_net_changes = ] supports_net_changes ]---指示是否对此捕获实例启用净更改查询支持

[,[ @index_name = ] 'index_name' ]--用于唯一标识源表中的行的唯一索引的名称。index_name 为 sysname,并且可以为 NULL。如果指定,则 index_name 必须是源表的唯一有效索引。如果指定 index_name,则标识的索引列优先于任何定义的主键列,就像表的唯一行标识符一样。

[,[ @captured_column_list = ] 'captured_column_list' ]--需要对哪些列进行捕获。captured_column_list 的数据类型为 nvarchar(max),并且可以为 NULL。如果为 NULL,则所有列都将包括在更改表中。

[,[ @filegroup_name = ] 'filegroup_name' ]--是要用于为捕获实例创建的更改表的文件组。

[,[ @partition_switch = ] 'partition_switch' ]--指示是否可以对启用了变更数据捕获的表执行 ALTER TABLE 的 SWITCH PARTITION 命令。allow_partition_switch 为 bit,默认值为 1。

9.sp_cdc_generate_wrapper_function

生成用于为 SQL Server 中可用的变更数据捕获查询函数创建包装函数的脚本

EXEC sys.sp_cdc_generate_wrapper_function

  1. sys.sp_cdc_help_change_data_capture

返回当前数据库中为变更数据捕获启用的每个表的变更数据捕获配置。最多可为每个源表返回两行,为每个捕获实例返回一行。

---返回制定表的捕获信息

USE AdventureWorks2008R2;

GO

EXECUTE sys.sp_cdc_help_change_data_capture

​ @source_schema = N'HumanResources', --架构名

​ @source_name = N'Department';--表名

GO

--返回所有表的捕获信息

USE AdventureWorks2008R2;

GO

EXECUTE sys.sp_cdc_help_change_data_capture

11.sys.sp_cdc_get_captured_columns

返回指定捕获实例所跟踪的捕获源列的变更数据捕获元数据信息。

USE AdventureWorks2008R2;

GO

EXECUTE sys.sp_cdc_get_captured_columns

​ @capture_instance = N'HR_Department';

GO

12.sys.sp_cdc_get_ddl_history

返回自对指定的捕获实例启用变更数据捕获后与该捕获实例关联的数据定义语言 (DDL) 更改历史记录。

与查询表是一样的结果

SELECT * FROM cdc.ddl_history

USE AdventureWorks2008R2;

GO

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信dbaup66,谢谢!
AiDBA后续精彩内容已被站长无情隐藏,请输入验证码解锁本文!
验证码:
获取验证码: 请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

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

您可能还喜欢...

发表回复