SQL SERVER 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则开启

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:DATAdbname_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系列表。

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", @capture_instance = "dbo_test_hht";
-- 2.7.6 SQL SERVER最多允许两个捕获表，所以多次改变时需要先禁用之前的表

2.8 参考资料

https://blog.csdn.net/vkingnew/article/details/89508885
https://blog.csdn.net/chiwei9644/article/details/100649089

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的模式消失）