SQL基础：SQL SERVER 开启CDC 实操详细

SELECT @@VERSION;

select is_cdc_enabled from sys.databases where name=’dbname’;
–0为关闭，1为开启。数据库名为dbname

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

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’;
*/

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查询操作

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 查询已开启的表

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日志表

/*
cdc.<capture_instance>_CT 可以看到，这样命名的表，是用于记录源表更改的表。

对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录。
对于__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）
对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）
*/

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最多允许两个捕获表，所以多次改变时需要先禁用之前的表

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