SqlServer Change Data Capture CDC使用手册

1、CDC介绍

官网文档:什么是变更数据捕获 (CDC)? - SQL Server | Microsoft Learn

变更数据捕获(Change Data Capture ,简称 CDC)记录 SQL Server 表的插入、更新和删除活动。使用变更数据捕获可以更有效跟踪表对象DML历史操作,对 ETL 等数据转移也非常有用。

变更数据捕获适用版本:

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

变更数据捕获原理:

变更数据捕获的更改数据源为 SQL Server 事务日志。当对表启用变更数据捕获时,系统将生成一个与该表结构类似的副本。当对源表进行插入、更新和删除 时,在事务日志会记录相关操作信息。变更数据捕获代理使用异步进程读取事务日志,将相关操作结果应用到副本表(捕获实例表)中,这样就完成了对源表操作的记录跟踪。

其他用途:

CDC不单单用于同步数据,在数据记录上也有很大的作用,增删改查,修改时候会记录修改前修改后的值

2、开启CDC步骤

2.1、第一步: 数据库开启CDC功能

确保你的 SQL Server 版本支持 CDCCDC 在不同版本的 SQL Server 中可用性可能有所不同,因此请确保你正在使用支持 CDC 功能的版本。

官网文档:sys.sp_cdc_enable_db (Transact-SQL) - SQL Server | Microsoft Learn

在目标数据库上启用 CDC。你可以使用下面的 T-SQL 语句来启用 CDC

USE [Libby];

EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_db 是启用 SQL Server CDCChange Data Capture)功能的存储过程,执行该存储过程会在数据库中创建以下表格(表格在系统表中查看)

  1. cdc.captured_columns:保存启用 CDC 功能的表的列信息,包括列名、数据类型和是否为标识列等。
  2. cdc.change_tables:保存启用 CDC 功能的表的元数据信息,包括表名、模式名、CDC 表的名称和 CDC 表的架构等。
  3. cdc.ddl_history:保存对启用 CDC 功能的表所做的所有 DDL 更改的历史记录,包括更改类型、更改时间、更改用户和更改语句等。
  4. cdc.lsn_time_mapping:保存 LSNLog Sequence Number)和时间戳之间的映射关系。
  5. cdc.index_columns:保存索引列的列表。
  6. cdc.lsn_tracking:用于跟踪 CDC 表中的 LSN 状态。(我在操作中没有遇到这个表)
  7. cdc.****_CT:此表是生成的捕获示例表,其中包含有关插入、更新和删除操作的详细信息。
  8. cdc.fn_cdc_get_all_changes_*:这些表值函数用于检索与 CDC 表关联的表中所做的所有更改。它们需要指定起始 LSN 和结束 LSN
  9. cdc.fn_cdc_get_net_changes_*:这些表值函数用于检索与 CDC 表关联的表中所做的最新更改。它们需要指定起始时间和结束时间。
  10. cdc.sp_cdc_help_*:这些存储过程用于帮助管理 CDC 表,包括启用、禁用和删除 CDC 表等。(我在操作中存储过程名不是这个但是作用应该是一样的,我的存储过程名如下)

这些表和函数的作用是:

保存 CDC 表的元数据信息,包括表名、列名、索引信息等。

保存捕获的变更数据,包括操作类型、操作时间、操作用户等。

提供查询 CDC 表变更数据的接口,支持检索历史变更记录和最新变更记录。

总之,这些表和函数是 CDC 功能的核心组件,它们帮助我们捕获、存储和查询数据库表的变更信息,提供了高效的数据库审计和追踪功能。

2.2、第二步:选择启用CDC的表

选择要启用 CDC 的表选择要启用 CDC 的表。你可以使用下面的 T-SQL 语句来启用特定表的 CDC

官网文档:sys.sp_cdc_enable_table (Transact-SQL) - SQL Server | Microsoft Learn

sql创建了两个作业[cdc.Libby_capture][cdc.Libby_cleanup]

USE [Libby];

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo',
                             @source_name = N'CdcTest_TableName',
                             @role_name = NULL,
                             @supports_net_changes = 1;

这将在指定的表上启用 CDC,并创建用于存储更改数据的 CDC 表和函数。

执行 sys.sp_cdc_enable_table 存储过程。该存储过程将使用以下参数:

  1. @source_schema:要启用 CDC 功能的表所在的架构。在这个例子中,架构名为 "dbo"
  2. @source_name:要启用 CDC 功能的表的名称。在这个例子中,表名为 "CdcTest_TableName"
  3. @role_name:指定使用 CDC 功能的角色的名称。如果不需要指定,则可以设置为 NULL。如果设定了角色,只会记录该角色的增删改等操作,设置NULL则是记录所有角色的增删改操作
  4. @supports_net_changes:指示是否启用 Net Changes 功能。如果设置为 1,则为启用;否则为禁用。Net Changes 功能是一种优化技术,可以减少 CDC 函数返回的行数。设置1的前提,目标表必须主键,设置0不会记录修改和删除

2.3、第三步:配置CDC

配置 CDC 选项。你可以使用下面的 T-SQL 语句来配置 CDC 的选项,例如保留期限、日志清理等:

官网文档:sys.sp_cdc_change_job (Transact-SQL) - SQL Server | Microsoft Learn

USE [Libby]

EXEC sys.sp_cdc_change_job @job_type = 'cleanup', @retention = 4320;

这将设置 CDC 清理作业的保留期限为 4320 分钟(3 天)。

开始捕获更改数据。CDC 将自动开始捕获启用了 CDC 的表的更改数据,并将其存储在 CDC 相关的表中。

你可以使用 CDC 提供的系统函数和视图来查询和分析更改数据。

请注意,启用 CDC 会增加数据库的负载和存储需求,因此在启用之前,请确保你的环境能够承受这些额外开销。

这个时候[dbo].[CdcTest]的增删改操作都会被记录到这个[cdc].[dbo_CdcTest_CT]表中

通过[cdc].[dbo_CdcTest_CT]就可以查看了

__$start_lsn 列标识为更改指定的提交日志序列号 (LSN) 提交 LSN 不仅标识在同一事务中提交的更改,而且还对这些事务进行排序。 可以使用

__$seqval 列对同一事务中进行的其他更改进行排序。

__$operation 列记录与更改关联的操作:1 = 删除,2 = 插入,3 = 更新(旧值),4 = 更新(新值)。

__$update_mask 列是一个可变的位掩码,每个捕获列都有一个对应的定义位。 对于插入和删除项,更新掩码始终设置所有位。 但是,更新行仅设置与更改列对应的那些位。

3、后续调整CDC,修改或者关闭

官网文档:启用和禁用“变更数据捕获” - SQL Server | Microsoft Learn

3.1、关闭数据库的CDC功能

USE [Libby]
GO

EXEC sys.sp_cdc_disable_db
GO

3.2、修改表的文件组

USE [Libby]
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'CdcTest',
    @role_name     = N'MyRole',
    @filegroup_name = N'MyDB_CT',
    @supports_net_changes = 1
GO

3.3、修改表的角色配置

USE [Libby]
GO

    EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'CdcTest',
    @role_name     = NULL,
    @supports_net_changes = 1
GO

3.4、关闭表的CDC功能

命令执行后,CDC日志表直接删除

USE [Libby]
GO

    EXEC sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name   = N'CdcTest',
    @capture_instance = N'dbo_CdcTest'
GO

3.5、修改为非实时监控,变为手动更新记录

--关闭capture
EXEC sys.sp_cdc_stop_job N'capture';

--调整配置,数据保留时间
EXEC sys.sp_cdc_change_job @job_type = 'cleanup', @retention = 4320;
GO

--调整模式,自动监测还是手动监测  0是手动
EXEC sys.sp_cdc_change_job @job_type = 'capture', @continuous = 0;

--开启capture
EXEC sys.sp_cdc_start_job N'capture';

--刷新数据,可以定时执行这个语句,来实现监控
EXEC sys.sp_cdc_scan 500, 10;

4、运维CDC

4.1、查询对象是否开启CDC,状态查询

--查询数据库是否开启CDC
SELECT name,is_cdc_enabled FROM sys.databases

--查询表是否开启CDC
SELECT  C.name,
        B.name,
        is_tracked_by_cdc
FROM    sys.tables AS A
        LEFT JOIN sys.objects AS B ON A.object_id = B.object_id
        LEFT JOIN sys.schemas AS C ON C.schema_id = B.schema_id;

5.2、查询捕捉示例

可以查询捕捉实例的相关配置

EXEC sys.sp_cdc_help_change_data_capture @source_schema = N'dbo', @source_name = N'CdcTest';

5、常见问题以及对应解决方案

5.1、表修改调整列

如果被监控表执行了DDL操作,CDC日志表是不会随之改变的,举例增删了列或者删除了列,CDC只会忽略变更的列,如果是新增,CDC不会记录新增列,如果是删除列,CDC会把NULL插入到此列,如果想同步这个变更列需要进行以下操作

需要注意的是,如果CDC日志表有数据,会被删除掉,此方案只适用于没有变更记录的情况下,或者能接受变更记录丢失的情况

USE [Libby];
GO

EXEC sys.sp_cdc_disable_table @source_schema = N'dbo',
                              @source_name = N'CdcTest',
                              @capture_instance = N'dbo_CdcTest';
GO


USE [Libby];

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo',
                             @source_name = N'CdcTest',
                             @role_name = NULL,
                             @supports_net_changes = 1;
GO

5.2、账号无权限

错误信息如下:

消息 22830,级别 16,状态 1,过程 sp_cdc_enable_db_internal,行 193 [批起始行 0] 无法更新元数据来指示已对数据库 SDP_Kelun 启用了变更数据捕获。执行命令 'SetCDCTracked(Value = 1)' 时失败。返回的错误为 15517: '无法作为数据库主体执行,因为主体 "dbo" 不存在、无法模拟这种类型的主体,或您没有所需的权限。'。请使用此操作和错误来确定失败的原因并重新提交请求。

解决方案:

错误原因是因为dbo架构原因导致的,需要调整权限,根据以下方式看看自己属于哪个问题,这里都以sa账户举例:

15517错误码官网文档:MSSQLSERVER_15517 - SQL Server | Microsoft Learn

①检查这里是不是sa账户

②检查架构是否存在

解决方案脚本:

①分配权限

GRANT ALTER ANY SCHEMA TO sa;

GRANT CONTROL TO sa;

②修改数据库归属, dbo 更改为有效用户

ALTER AUTHORIZATION ON DATABASE::SDP_Kelun TO [sa];

5.3、实例名称不一致

在执行开启表CDC的时候会提示错误信息,内容如下:

消息 22832,级别 16,状态 1,过程 sp_cdc_enable_table_internal,第 623 行
无法更新元数据来指示已对表 [XXXXX].[XXXX] 启用了变更数据捕获。执行命令 '[sys].[sp_cdc_add_job] @job_type = N'capture'' 时失败。返回的错误为 22836: '无法更新数据库 XXX的元数据来指示已添加某变更数据捕获作业。执行命令 'sp_add_jobstep_internal' 时失败。返回的错误为 14234: '指定的 '@server' 无效(有效值由 sp_helpserver 返回)。'。请使用此操作和错误来确定失败的原因并重新提交请求。'。请使用此操作和错误来确定失败的原因并重新提交请求。

是因为实例名不一致导致的,实例名查询语句如下

SELECT * FROM master.dbo.sysservers

SELECT SERVERPROPERTY('ServerName')

如不一致,执行以下语句,进行更改

DECLARE @server sysname

SET @server = @@servername

EXEC sp_dropserver @server = @server

SET @server = CAST(SERVERPROPERTY('servername') AS sysname)

EXEC sp_addserver @server = @server, @local = 'LOCAL'

5.4、数据库所有者为空

当执行开启数据库CDC是提示以下错误:

消息 22830,级别 16,状态 1,过程 sp_cdc_enable_db_internal,第 193 行
无法更新元数据来指示已对数据库 SDP_BI_Master 启用了变更数据捕获。执行命令 'SetCDCTracked(Value = 1)' 时失败。返回的错误为 15517: '无法作为数据库主体执行,因为主体 "dbo" 不存在、无法模拟这种类型的主体,或您没有所需的权限。'。请使用此操作和错误来确定失败的原因并重新提交请求。

这个也是因为数据库的所有者的归属问题,和6.2情况类似,解决方案如下:

右键选择数据库,查看属性,查看所有者是否为空,为空则补充即可

其他扩展文章:
https://zhuanlan.zhihu.com/p/630551501

  • 18
    点赞
  • 13
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值