sqlserver的数据更改捕获CDC功能

一. CDC（Change Data Capture）原理

异步读取事务日志，记录DML操作发生的时间，类型和实际影响的数据变化，然后将这些数据记录到启用CDC时自动创建的表中。

二. 对比Trigger方式的实现

由于是异步读取的，因此对整体性能的影响不大，远小于通过Trigger实现的数据变化记录；

三. 应用方法

1. 数据库启用CDC

Use test

EXEC sys.sp_cdc_enable_db;

2. 启用Agent

3. 表启用CDC

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name = N'tb', --跟踪的表名

@capture_instance = N'dbo_tb_col', --本跟踪的名字

@role_name = NULL,

@captured_column_list = N'id,col1,col2'; --需要跟踪的列，如果不设置，则表示跟踪所有列

4. 启用CDC后，会创建的一些系统表

（1）cdc.captured_columns 记录所有要跟踪的列

（2）cdc.change_tables 创建了跟踪的表

（3）cdc._CT 实际记录变更的表，每建立一个跟踪，就会自动生成一张对应的表

列名

描述

__$start_lsn

数据变动所属的LSN

__$end_lsn

2012后才支持，2008中为NULL

__$seqval

表示该变动，是事务中第几行改动

__$operation

1 = delete

2 = insert

3 = update (old values) Column data has row values before executing the update statement.

4 = update (new values) Column data has row values after executing the update statement.

__$update_mask

表示某列是否改动过，对应bit为1则有变动

所有要跟踪的列

例如，该表有5个要跟踪的列，那么就有5列

（4）cdc.ddl_history 记录DDL操作

（5）cdc.lsn_time_mapping LSN及时间对应表

5. CDC是通过Agent实现的，因此在代理中会看到创建的作业

6. 主要的几个相关系统函数

（1）sys.fn_cdc_get_min_lsn( 'capture_instance_name' )

sys.fn_cdc_get_max_lsn( 'capture_instance_name' ) 返回要跟踪的表的最大，最小 start_lsn 值

（2）cdc.fn_cdc_get_all_changes_ ( from_lsn , to_lsn , '' ) 每一个变动返回一行

（3）cdc.fn_cdc_get_net_changes_ ( from_lsn , to_lsn , '' ) 每一行变动返回一行