SQLSERVER|CDC日志变更捕获机制

红太狼 2021-10-15 12:29 572阅读 0赞

## 一、什么是CDC？ ##

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

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTI5NTE0Mi8yMDE4MDYvMTI5NTE0Mi0yMDE4MDYyNjE0MjcyNjQ2MS0yMDc1ODA0NDY2LnBuZw][]

开启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、添加次要数据文件组及文件

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

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTI5NTE0Mi8yMDE4MDYvMTI5NTE0Mi0yMDE4MDYyNjE0NDMyNDI0NC0xMDg2MjEyNTcuanBn][]

“文件” >> “添加”

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTI5NTE0Mi8yMDE4MDYvMTI5NTE0Mi0yMDE4MDYyNjE0NDUxOTI3OS02NTY4ODE1OTcuanBn][]

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTI5NTE0Mi8yMDE4MDYvMTI5NTE0Mi0yMDE4MDYyNjE0NDYwMTM3MC0xNzE4Nzc3ODU2LmpwZw][]

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

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTI5NTE0Mi8yMDE4MDYvMTI5NTE0Mi0yMDE4MDYyNjE0NTAzOTQzMy04NDMzOTE2MDQucG5n][]

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

**系统表：**

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

[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTI5NTE0Mi8yMDE4MDYvMTI5NTE0Mi0yMDE4MDYyNjE0MjcyNjQ2MS0yMDc1ODA0NDY2LnBuZw]: /images/20211015/5fb1f328c17e4a7783e7dfe6c0249f3d.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTI5NTE0Mi8yMDE4MDYvMTI5NTE0Mi0yMDE4MDYyNjE0NDMyNDI0NC0xMDg2MjEyNTcuanBn]: /images/20211015/cad7f6c27ff64e7aa18962a6d4eae990.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTI5NTE0Mi8yMDE4MDYvMTI5NTE0Mi0yMDE4MDYyNjE0NDUxOTI3OS02NTY4ODE1OTcuanBn]: /images/20211015/0fd3f38e320644d1a0f422aad399f203.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTI5NTE0Mi8yMDE4MDYvMTI5NTE0Mi0yMDE4MDYyNjE0NDYwMTM3MC0xNzE4Nzc3ODU2LmpwZw]: /images/20211015/0c969340224c4f9b832c1f027e01b3ad.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTI5NTE0Mi8yMDE4MDYvMTI5NTE0Mi0yMDE4MDYyNjE0NTAzOTQzMy04NDMzOTE2MDQucG5n]: /images/20211015/79af1ab79e72466e9035db21434a7572.png