SQLSERVER|CDC日志变更捕获机制

最新推荐文章于 2023-09-25 14:09:51 发布

羽之大公公

最新推荐文章于 2023-09-25 14:09:51 发布

阅读量1.1k

点赞数

分类专栏： sqlserver

原文链接：https://www.cnblogs.com/maikucha/p/9039205.html

版权

sqlserver 专栏收录该内容

39 篇文章 3 订阅

订阅专栏

一、什么是CDC？

变更数据捕获（Change Data Capture ，简称 CDC）记录 SQL Server 表的插入、更新和删除活动。SQLServer的操作会写日志，这也是CDC捕获数据的来源。

开启cdc的源表在插入、更新和删除活动时会插入数据到日志表中。cdc通过捕获进程将变更数据捕获到变更表中，通过cdc提供的查询函数，我们可以捕获这部分数据。

二、开启CDC

2.1、开启CDC的必要条件

sqlserver 2008 以上版本
需要开启代理服务（作业）
磁盘要有足够的空间，保存日志文件
表必须要有主键或者是唯一索引

2.2、开启数据库CDC

1、在需要开启cdc的数据库上执行脚本如下：

if exists(select 1 from sys.databases where name='db_name' and is_cdc_enabled=0)
begin
    exec sys.sp_cdc_enable_db
end

2、查询数据库的cdc开启状态

select is_cdc_enabled from sys.databases where name='db_name'

查询结果为“1”，表示开启成功。

2.3、开启表CDC

*注意：表中必须有主键或者唯一索引

1、添加次要数据文件组及文件

数据库右键“属性” >> “文件组”>> ”添加”

“文件” >> “添加”

2、执行以下脚本，开启表cdc

--CDC是数据库文件组的名称
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='table_name' AND is_tracked_by_cdc = 0)
BEGIN
    EXEC sys.sp_cdc_enable_table
        @source_schema = 'dbo', -- source_schema
        @source_name = 'table_name', -- table_name
        @capture_instance = NULL, -- capture_instance
        @supports_net_changes = 1, -- supports_net_changes
        @role_name = NULL, -- role_name
        @index_name = NULL, -- index_name
        @captured_column_list = NULL, -- captured_column_list
        @filegroup_name = 'CDC' -- filegroup_name
END

@source_schema :

是源表所属的架构的名称.source_schema 的数据类型为sysname，无默认值，并且

不能为NULL.

@source_name :

是对其启用变更数据捕获的源表的名称.source_name 的数据类型为sysname，无默

认值，并且不能为NULL. source_name 必须存在于当前数据库中.不能对cdc 架构

中的表启用变更数据捕获.

@role_name :

是用于控制更改数据访问的数据库角色的名称.role_name 为sysname，并且必须指定.

如果显式设置为NULL，则没有控制角色用于限制对更改数据的访问.如果当前存在该角

色，则使用它.如果不存在该角色，则会尝试创建具有指定名称的数据库角色.在尝试创

建该角色之前，将删除角色名称字符串右侧的空格.如果调用方无权在数据库中创建角色，

则存储过程操作将失败.

@capture_instance :

是用于命名特定于实例的变更数据捕获对象的捕获实例的名称.capture_instance 为

sysname，并且不能为NULL.源表最多可以有两个捕获实例.

@supports_net_changes :

指示是否对此捕获实例启用净更改查询支持.supports_net_changes 为bit，如果此表有

主键，或者有已使用@index_name 参数进行标识的唯一索引，则此参数的默认值为1.否

则，此参数默认为0.如果为0，则只生成查询所有更改的支持函数.如果为1，则还会生

成查询净更改所需的函数.

如果将supports_net_changes 设置为1，则必须指定index_name，或者源表必须具有已

定义的主键.

@index_name :

用于唯一标识源表中的行的唯一索引的名称.index_name 为sysname，并且可以为NULL.如

果指定，则index_name 必须是源表的唯一有效索引.如果指定index_name，则标识的索引列

优先于任何定义的主键列，就像表的唯一行标识符一样.

@captured_column_list :

标识将包括在更改表中的源表列.captured_column_list 的数据类型为nvarchar(max)，并且

可以为NULL.如果为NULL，则所有列都将包括在更改表中.

@filegroup_name : 这个选项允许指定CDC的数据存储到哪里.filegroup_name 为sysname，并且

可以NULL.如果指定，则必须为当前数据库定义filegroup_name.如果为NULL，则使用默认文

件组. 对于大型数据集,通过不同的文件组进行分隔会带来更好的管理型和性能.

@allow_partition_switch : 指示是否可以对启用了变更数据捕获的表执行ALTER TABLE 的SWITCH

PARTITION 命令.allow_partition_switch 为bit，默认值为1.

3、查看表cdc开启状态

SELECT is_tracked_by_cdc FROM sys.tables WHERE name='table_name'

查询结果为“1”，表示开启成功。

4.关闭表CDC

EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', -- sysname
@source_name = 'test_cdc', -- sysname
@capture_instance = 'dbo_test_cdc' -- sysname

注意事项

1.当表字段删除时，监控表里以null代替，并不会随着自动更新,新增字段时同理

2.当表字段类型更改时，会自动更新.

三、使用CDC

开启cdc后会在数据库中生成以下文件，开启数据库GY_DB，开启表VW_GHZDK

下面我们会对部分表和函数进行说明

系统表：

cdc.change_tables：表开启cdc后会插入一条数据到这张表中，记录表一些基本信息

cdc.captured_columns：开启cdc后的表，会记录它们的字段信息到这张表中

cdc.dbo_VW_GHZDK_CT：记录VW_GHZDK表中所有变更的数据，字段“__$operation”为“1”代表删除，“2”代表插入，“3”执行更新操作前的值，“4”执行更新操作后的值。字段“__$start_lsn”由于更改是来源于数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）

函数：

cdc.fn_cdc_get_all_changes_dbo_VW_GHZDK：针对在指定日志序列号 (LSN) 范围内应用到源表的每项更改均返回一行。如果源行在该间隔内有多项更改，则每项更改都会表示在返回的结果集中

cdc.fn_cdc_get_net_changes_dbo_VW_GHZDK：针对指定 LSN 范围内每个已更改的源行返回一个净更改行。也就是说，如果在 LSN 范围内源行具有多项更改，则该函数将返回反映该行最终内容的单一行

sys.fn_cdc_map_time_to_lsn：为指定的时间返回 cdc.lsn_time_mapping 系统表中 start_lsn 列中的日志序列号 (LSN) 值。可以使用此函数系统地将日期时间范围映射到基于 LSN 的范围，以供变更数据捕获枚举函数 cdc.fn_cdc_get_all_changes_<capture_instance> 和 cdc.fn_cdc_get_net_changes_<capture_instance> 返回此范围内的数据更改。

查询命令

--返回所有表的变更捕获配置信息
EXECUTE sys.sp_cdc_help_change_data_capture;

--返回某个表的变更捕获配置信息
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department'

--查看对某个表的哪些列做了捕获监控,使用上面返回的capture_instance列值
EXEC sys.sp_cdc_get_captured_columns
@capture_instance = 'dbo_Department'

--所有数据库CDC Job信息
SELECT B.name,A.* FROM msdb.dbo.cdc_jobs AS A
LEFT JOIN sys.databases AS B
ON A.database_id = B.database_id

--当前数据库CDC Job信息
EXEC sp_cdc_help_jobs