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 版本支持 CDC。CDC 在不同版本的 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 的 CDC(Change Data Capture)功能的存储过程,执行该存储过程会在数据库中创建以下表格(表格在系统表中查看):
- cdc.captured_columns:保存启用 CDC 功能的表的列信息,包括列名、数据类型和是否为标识列等。
- cdc.change_tables:保存启用 CDC 功能的表的元数据信息,包括表名、模式名、CDC 表的名称和 CDC 表的架构等。
- cdc.ddl_history:保存对启用 CDC 功能的表所做的所有 DDL 更改的历史记录,包括更改类型、更改时间、更改用户和更改语句等。
- cdc.lsn_time_mapping:保存 LSN(Log Sequence Number)和时间戳之间的映射关系。
- cdc.index_columns:保存索引列的列表。
- cdc.lsn_tracking:用于跟踪 CDC 表中的 LSN 状态。(我在操作中没有遇到这个表)
- cdc.****_CT:此表是生成的捕获示例表,其中包含有关插入、更新和删除操作的详细信息。
- cdc.fn_cdc_get_all_changes_*:这些表值函数用于检索与 CDC 表关联的表中所做的所有更改。它们需要指定起始 LSN 和结束 LSN。
- cdc.fn_cdc_get_net_changes_*:这些表值函数用于检索与 CDC 表关联的表中所做的最新更改。它们需要指定起始时间和结束时间。
- 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 存储过程。该存储过程将使用以下参数:
- @source_schema:要启用 CDC 功能的表所在的架构。在这个例子中,架构名为 "dbo"。
- @source_name:要启用 CDC 功能的表的名称。在这个例子中,表名为 "CdcTest_TableName"。
- @role_name:指定使用 CDC 功能的角色的名称。如果不需要指定,则可以设置为 NULL。如果设定了角色,只会记录该角色的增删改等操作,设置NULL则是记录所有角色的增删改操作
- @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情况类似,解决方案如下:
右键选择数据库,查看属性,查看所有者是否为空,为空则补充即可