SQL SERVER 开启CDC 实操详细

编辑: admin 分类: mssql 发布时间: 2021-11-16 来源:互联网
目录
  • 1. 环境检查
    • 1.1 版本检查
    • 1.2 检查CDC服务开启状态
  • 2. 开启CDC
    • 2.1 开启SQL server agent服务
    • 2.2 开启数据库级别的CDC功能
    • 2.3 添加CDC专用的文件组和文件
    • 2.4 开启表级别CDC
    • 2.5 单表开启测试范例(仅供参考,可略过)
    • 2.6 开启成功说明
    • 2.7 DDL操作:DDL操作需要重新收集表的信息(以测试表test_hht为例)
  • 3. 关闭CDC

    1. 环境检查

    1.1 版本检查

    SELECT @@VERSION;
    
    
    

    Microsoft SQL Server 2016 (SP2-GDR)

    1.2 检查CDC服务开启状态

    select is_cdc_enabled from sys.databases where name='dbname';
    --0为关闭,1为开启。数据库名为dbname
    
    

    2. 开启CDC

    2.1 开启SQL server agent服务

    sp_configure 'show advanced options', 1;
    GO -- 2.1.1
    RECONFIGURE;
    GO -- 2.1.2
    sp_configure 'Agent XPs', 1;
    GO -- 2.1.3
    RECONFIGURE
    GO -- 2.1.4
    
    

    2.2 开启数据库级别的CDC功能

    ALTER AUTHORIZATION ON DATABASE::[dbname] TO [sa];
    -- 2.2.1 变更为sa的权限,数据库名为dbname
    if exists(select 1 from sys.databases where name='dbname' and is_cdc_enabled=0)
    begin
        exec sys.sp_cdc_enable_db
    end
    ;
    -- 2.2.2 开启语句
    select is_cdc_enabled from sys.databases where name='dbname';
    -- 2.2.3 检查是否开启成功,为1则开启
    /* -- 本段注释可不看
    或者
    USE ERP
    GO  
    -- 开启:
    EXEC sys.sp_cdc_enable_db  
    -- 关闭:
    EXEC sys.sp_cdc_disable_db
    GO  
     
    注释: 如果在禁用变更数据捕获时为数据库定义了很多捕获实例,则长时间运行事务可能导致 sys.sp_cdc_disable_db 的执行失败。
    通过在运行 sys.sp_cdc_disable_db 之前使用 sys.sp_cdc_disable_table 禁用单个捕获实例,可以避免此问题。
     
    示例:
     
    USE AdventureWorks2012; 
    GO 
    EXECUTE sys.sp_cdc_disable_table 
    @source_schema = N'HumanResources', 
    @source_name = N'Employee', 
    @capture_instance = N'HumanResources_Employee';
    */
    
    
    

    2.3 添加CDC专用的文件组和文件

    SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('dbname');
    -- 2.3.1 查询dbname库的物理文件
    ALTER DATABASE dbname ADD FILEGROUP CDC1;
    -- 2.3.2 为该库添加名为CDC1的文件组
    ALTER DATABASE dbname
    ADD FILE
    (
      NAME= 'dbname_CDC1',
      FILENAME = 'D:\DATA\dbname_CDC1.ndf'
    )
    TO FILEGROUP CDC1;
    -- 2.3.3 将新增文件,并映射到文件组。重复2.3.1查询操作
    
    
    

    2.4 开启表级别CDC

    SELECT name,is_tracked_by_cdc FROM sys.tables WHERE  is_tracked_by_cdc = 0;
    -- 2.4.1 查询未开启的表
    IF EXISTS(SELECT 1 FROM sys.tables WHERE name='AccountBase' AND is_tracked_by_cdc = 0)
    BEGIN
        EXEC sys.sp_cdc_enable_table
            @source_schema = 'dbo', -- source_schema
            @source_name = 'AccountBase', -- 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 = 'CDC1' -- filegroup_name
    END;
    -- 2.4.2 为dbname.dbo.AccountBase开启表级别CDC,文件组为CDC1
    DECLARE @tableName nvarchar(36)  -- 声明变量
    DECLARE My_Cursor CURSOR --定义游标
        FOR (SELECT 'new_srv_workorderBase' name
    union select 'tablename1'
    union select 'tablename2'
    union select 'tablename3'
     ) --查出需要的集合放到游标中
    OPEN My_Cursor; --打开游标
    FETCH NEXT FROM My_Cursor INTO @tableName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC sys.sp_cdc_enable_table
             @source_schema = 'dbo', -- source_schema
             @source_name = @tableName, -- 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 = 'CDC1' -- filegroup_name;
        FETCH NEXT FROM My_Cursor INTO @tableName;
    END
    CLOSE My_Cursor; --关闭游标
    DEALLOCATE My_Cursor; --释放游标
    -- 2.4.3 游标批量开启表
    SELECT name,is_tracked_by_cdc FROM sys.tables WHERE  is_tracked_by_cdc = 1 ORDER BY NAME;
    -- 2.4.4 查询已开启的表
    
    
    

    2.5 单表开启测试范例(仅供参考,可略过)

    create table test_hht
    (id varchar(36) not null primary key,
    city_name varchar(20),
    userid bigint,
    useramount decimal(18,6),
    ismaster bit,
    createtime datetime default getdate()); -- 测试表test_hht
    IF EXISTS(SELECT 1 FROM sys.tables WHERE name='test_hht' AND is_tracked_by_cdc = 0)
    BEGIN
        EXEC sys.sp_cdc_enable_table
            @source_schema = 'dbo', -- source_schema
            @source_name = 'test_hht', -- 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 = 'CDC1' -- filegroup_name
    END; -- 开启表级别CDC
    insert into test_hht(id,city_name,userid,useramount,ismaster)values('1','wuhan',     10,1000.25,1);
    insert into test_hht(id,city_name,userid,useramount,ismaster)values('1A','xiangyang',11,11000.35,0);
    insert into test_hht(id,city_name,userid,useramount,ismaster)values('1B','yichang',  12,12000.45,0); -- 插入数据测试
    select *  from dbname.dbo.test_hht; -- 数据表
    SELECT * FROM [cdc].[dbo_test_hht_CT]; -- CDC日志表
    
    

    2.6 开启成功说明

    dbname库出现cdc模式,并有CT系列表。

    /*
    cdc.<capture_instance>_CT   可以看到,这样命名的表,是用于记录源表更改的表。
    
    对于insert/delete操作,会有对应的一行记录,而对于update,会有两行记录。
    对于__$operation列:1 = 删除、2= 插入、3= 更新(旧值)、4= 更新(新值)
    对于__$start_lsn列:由于更改是来源与数据库的事务日志,所以这里会保存其事务日志的开始序列号(LSN)
    */
    

    2.7 DDL操作:DDL操作需要重新收集表的信息(以测试表test_hht为例)

    alter  table test_hht add   product_count decimal(18,2);
    -- 2.7.1 增加新的一列测试
    insert into test_hht(id,city_name,userid,useramount,ismaster,product_count)values('2','wuhan',     20,2000.25,1,2.5);
    -- 2.7.2 插入数据测试
    SELECT * FROM [cdc].[dbo_test_hht_CT];
    -- 2.7.3 CT表无新的一列,CDC正常捕获到之前的列变化
    EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo'
    ,@source_name = 'test_hht'
    ,@capture_instance ='dbo_test_hht_v2' -- 给一个新的名字
    ,@supports_net_changes = 1
    ,@role_name = NULL
    ,@index_name = NULL
    ,@captured_column_list = NULL
    ,@filegroup_name = 'CDC1';
    -- 2.7.4 为表dbo.test_hht开启一个新的CDC捕获
    insert into test_hht(id,city_name,userid,useramount,ismaster,product_count)values('2A','xiangyang',21,121000.35,0,12.5);
    -- 2.7.5 插入数据测试
    EXEC sys.sp_cdc_disable_table @source_schema = 'dbo',@source_name = 'test_hht', @游戏服务器http://www.558idc.com/yz.htmlcapture_instance = 'dbo_test_hht';
    -- 2.7.6 SQL SERVER最多允许两个捕获表,所以多次改变时需要先禁用之前的表
    
    

    3. 关闭CDC

    EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo'
    ,@source_name = 'test_hht'
    ,@capture_instance ='dbo_test_hht_v2'
    -- 3.1 单表禁用
    USE dbname
    GO
    EXEC sys.sp_cdc_disable_db
    GO
    -- 3.2 全库禁用(禁用后cdc的模式消失)
    

    到此这篇关于SQL SERVER CDC开启实操详细的文章就介绍到这了,更多相关SQL SERVER CDC开启实操内容请搜索hwidc以前的文章或继续浏览下面的相关文章希望大家以后多多支持hwidc!