SELECT @@VERSION;

select is_cdc_enabled from sys.databases where name='dbname';
--0為關(guān)閉，1為開(kāi)啟。數(shù)據(jù)庫(kù)名為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的權(quán)限，數(shù)據(jù)庫(kù)名為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 開(kāi)啟語(yǔ)句
select is_cdc_enabled from sys.databases where name='dbname';
-- 2.2.3 檢查是否開(kāi)啟成功，為1則開(kāi)啟
/* -- 本段注釋可不看
或者
USE ERP
GO  
-- 開(kāi)啟：
EXEC sys.sp_cdc_enable_db  
-- 關(guān)閉：
EXEC sys.sp_cdc_disable_db
GO  
 
注釋： 如果在禁用變更數(shù)據(jù)捕獲時(shí)為數(shù)據(jù)庫(kù)定義了很多捕獲實(shí)例，則長(zhǎng)時(shí)間運(yùn)行事務(wù)可能導(dǎo)致 sys.sp_cdc_disable_db 的執(zhí)行失敗。
通過(guò)在運(yùn)行 sys.sp_cdc_disable_db 之前使用 sys.sp_cdc_disable_table 禁用單個(gè)捕獲實(shí)例，可以避免此問(wèn)題。
 
示例：
 
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庫(kù)的物理文件
ALTER DATABASE dbname ADD FILEGROUP CDC1;
-- 2.3.2 為該庫(kù)添加名為CDC1的文件組
ALTER DATABASE dbname
ADD FILE
(
  NAME= 'dbname_CDC1',
  FILENAME = 'D:\DATA\dbname_CDC1.ndf'
)
TO FILEGROUP CDC1;
-- 2.3.3 將新增文件，并映射到文件組。重復(fù)2.3.1查詢操作

SELECT name,is_tracked_by_cdc FROM sys.tables WHERE  is_tracked_by_cdc = 0;
-- 2.4.1 查詢未開(kāi)啟的表
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開(kāi)啟表級(jí)別CDC，文件組為CDC1
DECLARE @tableName nvarchar(36)  -- 聲明變量
DECLARE My_Cursor CURSOR --定義游標(biāo)
    FOR (SELECT 'new_srv_workorderBase' name
union select 'tablename1'
union select 'tablename2'
union select 'tablename3'
 ) --查出需要的集合放到游標(biāo)中
OPEN My_Cursor; --打開(kāi)游標(biāo)
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; --關(guān)閉游標(biāo)
DEALLOCATE My_Cursor; --釋放游標(biāo)
-- 2.4.3 游標(biāo)批量開(kāi)啟表
SELECT name,is_tracked_by_cdc FROM sys.tables WHERE  is_tracked_by_cdc = 1 ORDER BY NAME;
-- 2.4.4 查詢已開(kāi)啟的表

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()); -- 測(cè)試表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; -- 開(kāi)啟表級(jí)別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); -- 插入數(shù)據(jù)測(cè)試
select *  from dbname.dbo.test_hht; -- 數(shù)據(jù)表
SELECT * FROM [cdc].[dbo_test_hht_CT]; -- CDC日志表

/*
cdc.<capture_instance>_CT   可以看到，這樣命名的表，是用于記錄源表更改的表。

對(duì)于insert/delete操作，會(huì)有對(duì)應(yīng)的一行記錄，而對(duì)于update，會(huì)有兩行記錄。
對(duì)于__$operation列：1 = 刪除、2= 插入、3= 更新（舊值）、4= 更新（新值）
對(duì)于__$start_lsn列：由于更改是來(lái)源與數(shù)據(jù)庫(kù)的事務(wù)日志，所以這里會(huì)保存其事務(wù)日志的開(kāi)始序列號(hào)（LSN）
*/

alter  table test_hht add   product_count decimal(18,2);
-- 2.7.1 增加新的一列測(cè)試
insert into test_hht(id,city_name,userid,useramount,ismaster,product_count)values('2','wuhan',     20,2000.25,1,2.5);
-- 2.7.2 插入數(shù)據(jù)測(cè)試
SELECT * FROM [cdc].[dbo_test_hht_CT];
-- 2.7.3 CT表無(wú)新的一列，CDC正常捕獲到之前的列變化
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo'
,@source_name = 'test_hht'
,@capture_instance ='dbo_test_hht_v2' -- 給一個(gè)新的名字
,@supports_net_changes = 1
,@role_name = NULL
,@index_name = NULL
,@captured_column_list = NULL
,@filegroup_name = 'CDC1';
-- 2.7.4 為表dbo.test_hht開(kāi)啟一個(gè)新的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 插入數(shù)據(jù)測(cè)試
EXEC sys.sp_cdc_disable_table @source_schema = 'dbo',@source_name = 'test_hht', @capture_instance = 'dbo_test_hht';
-- 2.7.6 SQL SERVER最多允許兩個(gè)捕獲表，所以多次改變時(shí)需要先禁用之前的表

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 全庫(kù)禁用（禁用后cdc的模式消失）