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则开启
/* -- 本段注释可不看
或者
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', @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 dbname
go
exec sys.sp_cdc_disable_db
go
-- 3.2 全库禁用（禁用后cdc的模式消失）

到此这篇关于sql server cdc开启实操详细的文章就介绍到这了,更多相关sql server cdc开启实操内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！