SQL SERVER如何开启CDC

本篇内容介绍了"SQL SERVER如何开启CDC"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

目录

• 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.1RECONFIGURE;GO -- 2.1.2sp_configure 'Agent XPs', 1;GO -- 2.1.3RECONFIGUREGO -- 2.1.4

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

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [sa];-- 2.2.1 变更为sa的权限，数据库名为dbnameif exists(select 1 from sys.databases where name='dbname' and is_cdc_enabled=0)begin    exec sys.sp_cdc_enable_dbend;-- 2.2.2 开启语句select is_cdc_enabled from sys.databases where name='dbname';-- 2.2.3 检查是否开启成功，为1则开启/* -- 本段注释可不看或者USE ERPGO  -- 开启：EXEC sys.sp_cdc_enable_db  -- 关闭：EXEC sys.sp_cdc_disable_dbGO   注释： 如果在禁用变更数据捕获时为数据库定义了很多捕获实例，则长时间运行事务可能导致 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 dbnameADD 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_nameEND;-- 2.4.2 为dbname.dbo.AccountBase开启表级别CDC，文件组为CDC1DECLARE @tableName nvarchar(36)  -- 声明变量DECLARE My_Cursor CURSOR --定义游标    FOR (SELECT 'new_srv_workorderBase' nameunion select 'tablename1'union select 'tablename2'union select 'tablename3' ) --查出需要的集合放到游标中OPEN My_Cursor; --打开游标FETCH NEXT FROM My_Cursor INTO @tableName;WHILE @@FETCH_STATUS = 0BEGIN    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;ENDCLOSE 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_hhtIF 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_nameEND; -- 开启表级别CDCinsert 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._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', @capture_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 dbnameGOEXEC sys.sp_cdc_disable_dbGO-- 3.2 全库禁用（禁用后cdc的模式消失）

"SQL SERVER如何开启CDC"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！