• SQLSERVER|CDC日志变更捕获机制


    一、什么是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

    3、查看表cdc开启状态

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

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

    三、使用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> 返回此范围内的数据更改。

    以上我只列出了部分经常使用的表和函数,如果想对其他内容有更深的了解,可以参考以下网址:

    微软官网

    网友总结

  • 相关阅读:
    python3----数据结构
    Java的同步容器和并发容器
    Java基础——IO
    JVM(2)——GC算法和收集器
    Java集合(2)——深入理解ArrayList、Vector和LinkedList
    java线程(7)——阻塞队列BlockingQueue
    JVM(1)——简介
    java泛型——基本使用
    java线程(6)——线程池(下)
    java线程(5)——线程池(上)
  • 原文地址:https://www.cnblogs.com/maikucha/p/9039205.html
Copyright © 2020-2023  润新知