一、配置NDL文件
1、增加文件组
2、增加CDC文件
3、修改文件名
二、开启数据库CDC功能
-- 开启
USE [库名]
GO
exec sys.sp_cdc_enable_db
GO
-- 关闭
USE [库名]
GO
exec sys.sp_cdc_disable_db
GO
-- 查看某个表是否开启,1为开启,0为关闭
select is_cdc_enabled from sys.databases where name='库名'
-- 查看所有开启数据库
SELECT name,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1;
--作业 'cdc.EDD_capture' 已成功启动。
--作业 'cdc.EDD_cleanup' 已成功启动。
三、开启Table表数据库功能
- 单表格开启
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='table_name' AND is_tracked_by_cdc = 0) BEGIN
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', -- source_schema @source_name = 'table_name', -- 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 = 'CDC' -- filegroup_name END |
2、单表关闭
EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', -- source_schema @source_name = ' table_name', -- table_name @capture_instance = 'capture_instance' -- capture_instance |
3、多表格开启
--游标,批量开启所有
DECLARE @name NVARCHAR(50) --声明变量,需要读取的数据
DECLARE cur CURSOR --去掉STATIC关键字即可
FOR
-- 根据实际情况排除
SELECT name FROM SysObjects Where XType='U' and uid='1'and category =0
OPEN cur --打开游标
FETCH NEXT FROM cur INTO @name --取数据
WHILE ( @@fetch_status = 0 ) --判断是否还有数据
BEGIN
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo', -- source_schema
@source_name = @name, -- 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 = 'CDC' -- filegroup_name
--SELECT @name tblName
FETCH NEXT FROM cur INTO @name --这里一定要写取下一条数据
END
CLOSE cur --关闭游标
DEALLOCATE cur