CDC---变更数据捕获

 
----变更数据捕获
--变更数据捕获使用异步进程读取事务日志，获取DML更改实际数据做为数据捕获的结果。在捕获结果中，还包含更改相关的一些信息（例如更改的操作类型、更新操作影响的列等）。可以从捕获结果中获取DML更改的全部数据，而无需查询数据变更的原始表

---启用数据捕获功能
use database_name
go
EXEC sys.sp_cdc_enable_db;
---启用后会在系统表中增加 5个以 cdc框架开头的表，外加 dbo.systranschemas 表

       cdc.captured_columns 
       返回指定的跟踪列

       cdc.change_tables
       返回启用CDC的表.使用sys.sp_cdc_help_change_data_capture比直接查询好.

       cdc.ddl_history 
       返回每个表再启用CDC后的DDL变更.可以使用sys.sp_cdc_get_ddl_history代替查询该表.

       cdc.index_columns 
       返回启用CDC的表的相关索引列.同样用sys.sp_cdc_help_change_data_capure来获取比较好.

       cdc.lsn_time_mapping 
       为每个在更改表中存在行的事务返回一行.该表用于在日志序列号(LSN) 提交值和提交事务的时间之间建立映射.
       要避免直接查询该表,使用sys.fn_cdc_map_lsn_to_time和sys.fn_cdc_map_time_to_lsn函数.



go

DECLARE @agent_service sysname;
SET @agent_service = N'SQLServerAgent';
 
DECLARE @agent_status TABLE(state varchar(50))---定义一个表变量用来存放当前代理服务的状态值

INSERT @agent_status
EXEC master.sys.xp_servicecontrol N'QueryState',@agent_service --获取当前代理的运行状态

IF NOT EXISTS(
        SELECT * FROM @agent_status
        WHERE state = N'Running.')
 begin
    EXEC master.sys.xp_servicecontrol N'Start',@agent_service --启动代理服务
 end
else
 return;


GO
EXEC sys.sp_cdc_enable_table

    @source_schema = N'dbo',

    @source_name = N't_a',

    @capture_instance = N'dbo_t_a',

    @role_name = NULL;
----执行成功后会提示 ：作业 'cdc.DB_test_capture' 已成功启动。作业 'cdc.DB_test_cleanup' 已成功启动。

-- 创建一个变更数据捕获实例- 选择捕获的列 《《《每个表上最多可以建立 2个捕获实例

EXEC sys.sp_cdc_enable_table

    @source_schema = N'dbo',

    @source_name = N't_a',

    @capture_instance = N'dbo_t_a_col',

    @role_name = NULL,

    @captured_column_list = N'id,code,name;

GO
insert t_a select 'zhao','shuju','shujubuhuo'
union all
select 'wen','bu','shujubuhuo'
union all
select 'zhong','huo','shujubuhuo'

go
update t_a set remarks='testshujubuhuo' where code='wenzhong'
go
delete from t_a where id=3
GO
WITH
LSN AS(
    SELECT
        from_lsn = sys.fn_cdc_get_min_lsn(N'dbo_t_a'),
        to_lsn = sys.fn_cdc_get_max_lsn()
),

CHGE_ALL AS(
    SELECT
        chge.*
    FROM LSN
        CROSS APPLY cdc.fn_cdc_get_all_changes_dbo_t_a(LSN.from_lsn, LSN.to_lsn, 'ALL UPDATE OLD') chge
),

CHGE_NET AS(
    SELECT
        chge.*
    FROM LSN
        CROSS APPLY cdc.fn_cdc_get_net_changes_dbo_t_a(LSN.from_lsn, LSN.to_lsn, 'ALL') chge
)

SELECT * FROM CHGE_ALL;
注： _
$operation (1 删除(更新主键时,先删除原来的然后再插入.这里的主键应该是不具有IDENTITY 功能。个人理解。),2 insert,3 软删除或做需更新标识(个人理解),4 update(更新非主键))