--步驟：本文中以GPOSDB為例
--第一步、對(duì)目標(biāo)庫(kù)顯式啟用CDC：
--在當(dāng)前庫(kù)使用sys.sp_cdc_enable_db。返回0（成功）或1（失?。?。
--注意，無(wú)法對(duì)系統(tǒng)數(shù)據(jù)庫(kù)和分發(fā)數(shù)據(jù)庫(kù)啟用該功能。且執(zhí)行者需要用sysadmin角色權(quán)限。
--該存儲(chǔ)過(guò)程的作用域是整個(gè)目標(biāo)庫(kù)。包含元數(shù)據(jù)、DDL觸發(fā)器、cdc架構(gòu)和cdc用戶。
--使用以下代碼啟用：
USE GPOSDB --要啟用CDC的數(shù)據(jù)庫(kù)
GO
EXECUTE sys.sp_cdc_enable_db;
GO
--在一開(kāi)始直接執(zhí)行時(shí)，出現(xiàn)了報(bào)錯(cuò)信息：
--消息22830，級(jí)別16，狀態(tài)1，過(guò)程sp_cdc_enable_db_internal，第193 行
--無(wú)法更新元數(shù)據(jù)來(lái)指示已對(duì)數(shù)據(jù)庫(kù)AdventureWorks 啟用了變更數(shù)據(jù)捕獲。
--執(zhí)行命令'SetCDCTracked(Value = 1)' 時(shí)失敗。
--返回的錯(cuò)誤為15517: '無(wú)法作為數(shù)據(jù)庫(kù)主體執(zhí)行，因?yàn)橹黧w"dbo"
--不存在、無(wú)法模擬這種類(lèi)型的主體，或您沒(méi)有所需的權(quán)限。'。請(qǐng)使用此操作和錯(cuò)誤來(lái)確定失敗的原因并重新提交請(qǐng)求。
--這里引出了另外一個(gè)知識(shí)點(diǎn)：錯(cuò)誤號(hào) 15517 的錯(cuò)誤
--這種錯(cuò)誤會(huì)在很多地方出現(xiàn)，如還原數(shù)據(jù)庫(kù)的時(shí)候也會(huì)有可能出現(xiàn)。
--共同點(diǎn)是：某個(gè)/些存儲(chǔ)過(guò)程使用了具有WITHEXECUTE AS 的選項(xiàng)。
--使其在當(dāng)前庫(kù)具有了某個(gè)架構(gòu)，但是當(dāng)在別的地方執(zhí)行時(shí)，由于沒(méi)有這個(gè)架構(gòu)，所以就報(bào)錯(cuò)，解決方法：
ALTER AUTHORIZATION ON DATABASE::[GPOSDB] TO [sa]
--經(jīng)過(guò)檢查，uspUpdateEmployeeHireInfo這個(gè)存儲(chǔ)過(guò)程的確有：WITH EXECUTE AS CALLER
--使用sa的原因是即使sa被禁用，sa還是存在的。所以不會(huì)報(bào)錯(cuò)。
--現(xiàn)在重新執(zhí)行：
USE GPOSDB
GO
EXECUTE sys.sp_cdc_enable_db;
GO
--啟用成功，然后通過(guò)以下語(yǔ)句檢查是否成功：
SELECT
is_cdc_enabled,
CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用' ELSE 'CDC功能啟用' END 描述
FROM sys.databases
WHERE NAME = 'GPOSDB'
--創(chuàng)建成功后，將自動(dòng)添加CDC用戶和CDC架構(gòu)。
--在用戶和架構(gòu)下面可以看到cdc用戶和cdc架構(gòu)
--創(chuàng)建這兩個(gè)用戶、架構(gòu)的原因是因?yàn)镃DC要求獨(dú)占方式使用這兩個(gè)架構(gòu)，所以要單獨(dú)創(chuàng)建。
--如果存在了非CDC功能創(chuàng)建的CDC用戶、架構(gòu)的話，則需要先刪除該cdc命名的架構(gòu)，才能開(kāi)啟。
--第二步、對(duì)目標(biāo)表啟用CDC：
--使用db_owner角色的成員執(zhí)行sys.sp_cdc_enable_table為每個(gè)需要跟蹤的表創(chuàng)建捕獲實(shí)例。
--然后通過(guò)sys.tables目錄視圖中的is_tracked_by_cdc列來(lái)判斷是否創(chuàng)建成功。
--默認(rèn)情況下會(huì)對(duì)表的全部列做捕獲。如果只需要對(duì)某些列做捕獲，
--可以使用@captured_column_list參數(shù)指定這些列。
--如果要把更改表放到文件組里的話，最好創(chuàng)建單獨(dú)的文件組（最起碼與源表獨(dú)立）。
--如果不想控制訪問(wèn)角色，則@role_name必須顯式設(shè)置為null。
sys.sp_cdc_enable_table
[ @source_schema = ] 'source_schema',
[ @source_name = ] 'source_name' ,
[ @role_name = ] 'role_name'
[,[ @capture_instance = ] 'capture_instance' ]
[,[ @supports_net_changes = ] supports_net_changes ]
[,[ @index_name = ] 'index_name' ]
[,[ @captured_column_list = ] 'captured_column_list' ]
[,[ @filegroup_name = ] 'filegroup_name' ]
[,[ @partition_switch = ] 'partition_switch' ]
--例子：
--把SYSTEMPARA 這個(gè)表開(kāi)啟變更捕獲。
USE GPOSDB
GO
EXEC sys.sp_cdc_enable_table @source_schema = 'DBO',
@source_name = 'SYSTEMPARA',@role_name = NULL
--然后查詢(xún)是否成功：
SELECT name ,
is_tracked_by_cdc ,
CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'
ELSE 'CDC功能啟用'
END 描述
FROM sys.tables
WHERE OBJECT_ID = OBJECT_ID('dbo.systempara')
--對(duì)表開(kāi)啟以后，可以在下圖中看到多了很多cdc架構(gòu)開(kāi)頭的表：
--刷新一下GPOSDB數(shù)據(jù)庫(kù)，在系統(tǒng)表下面可以看到多了下面幾張表
[cdc].[DBO_SYSTEMPARA_CT]
[cdc].[change_tables]
[cdc].[captured_columns]
[cdc].[ddl_history]
[cdc].[index_columns]
[cdc].[lsn_time_mapping]
[dbo].[systranschemas]
[dbo].[dtproperties]
--啟動(dòng)之后，可以看到SQLServer代理里面的作業(yè)，也出現(xiàn)了這兩個(gè)作業(yè)：
[cdc.GPOSDB_capture]
[cdc.GPOSDB_cleanup]
--在可編程性-》函數(shù)-》表值函數(shù)里，也多了兩個(gè)函數(shù)
[cdc].[fn_cdc_get_all_changes_DBO_SYSTEMPARA]
[cdc].[fn_cdc_get_net_changes_DBO_SYSTEMPARA]
--下面列出相關(guān)的存儲(chǔ)過(guò)程：
--Sys.sp_cdc_add_job
--說(shuō)明及例子
--Sys.sp_cdc_generate_wrapper_function
--說(shuō)明及例子
--Sys.sp_cdc_change_job
--說(shuō)明及例子
--Sys.sp_cdc_get_captured_columns
--說(shuō)明及例子
--Sys.sp_cdc_cleanup_change_table
--說(shuō)明及例子
--Sys.sp_cdc_get_ddl_history
--說(shuō)明及例子
--Sys.sp_cdc_disable_db
--說(shuō)明及例子 建議先禁用表，再禁用庫(kù)
--Sys.sp_cdc_help_change_data_capture
--說(shuō)明及例子
--Sys.sp_cdc_disable_table
--說(shuō)明及例子
--Sys.sp_cdc_help_jobs
--說(shuō)明及例子
--Sys.sp_cdc_drop_job
--說(shuō)明及例子
--Sys.sp_cdc_scan
--說(shuō)明及例子
--Sys.sp_cdc_enable_db
--說(shuō)明及例子
--Sys.sp_cdc_start_job
--說(shuō)明及例子
--Sys.sp_cdc_enable_table
--說(shuō)明及例子
--Sys.sp_cdc_stop_job
--說(shuō)明及例子
--函數(shù)：
--Cdc.fn_cdc_get_all_changes_capture_instance>
--說(shuō)明及例子
--Sys.fn_cdc_has_column_changed
--說(shuō)明及例子
--Cdc.fn_cdc_get_net_changes_capture_instance>
--說(shuō)明及例子
--Sys.fn_cdc_increment_lsn
--說(shuō)明及例子
--Sys.fn_cdc_decrement_lsn
--說(shuō)明及例子
--Sys.fn_cdc_is_bit_set
--說(shuō)明及例子
--Sys.fn_cdc_get_column_ordinal
--說(shuō)明及例子
--Sys.fn_cdc_map_lsn_to_time
--說(shuō)明及例子
--Sys.fn_cdc_get_max_lsn
--說(shuō)明及例子
--Sys.fn_cdc_map_time_to_lsn
--說(shuō)明及例子
--Sys.fn_cdc_get_min_lsn
--說(shuō)明及例子
--------------------下面開(kāi)始從頭到尾做一個(gè)實(shí)際案例-------------------------
--下面開(kāi)始從頭到尾做一個(gè)實(shí)際案例
--步驟一:對(duì)目標(biāo)庫(kù)顯式啟用CDC
USE GPOSDB --要啟用CDC的數(shù)據(jù)庫(kù)
GO
EXECUTE sys.sp_cdc_enable_db;
GO
--某些數(shù)據(jù)庫(kù)可能存在一些存儲(chǔ)過(guò)程包含有：execute as 等語(yǔ)句，此時(shí)會(huì)報(bào)錯(cuò)：
--文字描述：
--消息22830，級(jí)別16，狀態(tài)1，過(guò)程sp_cdc_enable_db_internal，第186 行
--無(wú)法更新元數(shù)據(jù)來(lái)指示已對(duì)數(shù)據(jù)庫(kù)AdventureWorks 啟用了變更數(shù)據(jù)捕獲。執(zhí)行命令'SetCDCTracked(Value = 1)' 時(shí)失敗。返回的錯(cuò)誤為15517: '無(wú)法作為數(shù)據(jù)庫(kù)主體執(zhí)行，因?yàn)橹黧w"dbo" 不存在、無(wú)法模擬這種類(lèi)型的主體，或您沒(méi)有所需的權(quán)限。'。請(qǐng)使用此操作和錯(cuò)誤來(lái)確定失敗的原因并重新提交請(qǐng)求。
--消息266，級(jí)別16，狀態(tài)2，過(guò)程sp_cdc_enable_db_internal，第0 行
--EXECUTE 后的事務(wù)計(jì)數(shù)指示BEGIN 和COMMIT 語(yǔ)句的數(shù)目不匹配。上一計(jì)數(shù)= 0，當(dāng)前計(jì)數(shù)= 1。
--消息266，級(jí)別16，狀態(tài)2，過(guò)程sp_cdc_enable_db，第0 行
--EXECUTE 后的事務(wù)計(jì)數(shù)指示BEGIN 和COMMIT 語(yǔ)句的數(shù)目不匹配。上一計(jì)數(shù)= 0，當(dāng)前計(jì)數(shù)= 1。
--消息3998，級(jí)別16，狀態(tài)1，第1 行
--在批處理結(jié)束時(shí)檢測(cè)到不可提交的事務(wù)。該事務(wù)將回滾。
--如果出現(xiàn)這個(gè)錯(cuò)誤，目前的解決方法是執(zhí)行下面語(yǔ)句，原因已在開(kāi)頭說(shuō)明，對(duì)于沒(méi)有使用EXECUTE AS的庫(kù)，一般不會(huì)有這樣的問(wèn)題：
ALTER AUTHORIZATION ON DATABASE::[GPOSDB] TO [sa]
--現(xiàn)在重新執(zhí)行：
USE GPOSDB
GO
EXECUTE sys.sp_cdc_enable_db;
GO
--通過(guò)以下語(yǔ)句檢查是否成功：
SELECT
is_cdc_enabled,
CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用' ELSE 'CDC功能啟用' END 描述
FROM sys.databases
WHERE NAME = 'GPOSDB'
--步驟二：對(duì)表啟用CDC
USE GPOSDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = 'DBO',
@source_name = 'SYSTEMPARA',
@role_name = NULL,
@capture_instance=DEFAULT
GO
--然后查詢(xún)是否成功：
SELECT name ,
is_tracked_by_cdc ,
CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'
ELSE 'CDC功能啟用'
END 描述
FROM sys.tables
WHERE OBJECT_ID = OBJECT_ID('dbo.systempara')
--可以看到GPOSDB數(shù)據(jù)庫(kù)里的系統(tǒng)表里新增了[cdc].[DBO_SYSTEMPARA_CT]表
--步驟三：檢驗(yàn)，下面來(lái)改動(dòng)數(shù)據(jù)
--先查詢(xún)一下DBO_SYSTEMPARA_CT表
SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
--可以看到一條記錄都沒(méi)有，因?yàn)閯倓倓?chuàng)建，并沒(méi)有對(duì)原表systempara做任何增刪改操作
--向[SystemPara]表插入一條記錄
INSERT INTO [dbo].[SystemPara]
( [ParaValue] ,
[Name] ,
[Description]
)
VALUES ( '中國(guó)' , -- ParaValue - varchar(50)
'中國(guó)' , -- Name - varchar(50)
'中國(guó)' -- Description - varchar(50)
)
--查詢(xún)一下DBO_SYSTEMPARA_CT表，可以看到多了一條記錄
SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
--更新[SystemPara]表的一條記錄
UPDATE [dbo].[SystemPara] SET [ParaValue]='德國(guó)' WHERE [Description]='中國(guó)'
--查詢(xún)一下DBO_SYSTEMPARA_CT表，可以看到多了兩條記錄
SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
--刪除[SystemPara]表的一條記錄
DELETE FROM [dbo].[SystemPara] WHERE [Description]='中國(guó)'
--查詢(xún)一下DBO_SYSTEMPARA_CT表，可以看到多了一條記錄
SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
--現(xiàn)在來(lái)分析一下DBO_SYSTEMPARA_CT表
--可以在聯(lián)機(jī)叢書(shū)上查看：
--cdc.capture_instance>_CT
--可以看到，這樣命名的表，是用于記錄源表做過(guò)更改操作的表。
--對(duì)于insert/delete操作，會(huì)有對(duì)應(yīng)的一行記錄，而對(duì)于update，會(huì)有兩行記錄。
--對(duì)于__$operation列：1 = 刪除、2= 插入、3= 更新（舊值）、4= 更新（新值）
--update語(yǔ)句的__$operation列的值是3和4，所以一條update語(yǔ)句對(duì)應(yīng)兩條記錄
--對(duì)于__$start_lsn列：由于更改是來(lái)源與數(shù)據(jù)庫(kù)的事務(wù)日志，所以這里會(huì)保存其事務(wù)日志的開(kāi)始序列號(hào)（LSN）
--但是微軟不檢查直接查詢(xún)這類(lèi)表，建議使用
--cdc.fn_cdc_get_all_changes_捕獲實(shí)例>
--cdc.fn_cdc_get_net_changes_capture_instance>
-- 來(lái)查詢(xún)
----------------------------------------------------------
--下文開(kāi)始，來(lái)熟悉各種函數(shù)、存儲(chǔ)過(guò)程的使用，并嘗試一些不正常的操作。
--日常使用情景：
--1、查詢(xún)已經(jīng)開(kāi)啟的捕獲實(shí)例：
--返回所有表的變更捕獲配置信息
USE [GPOSDB]
GO
EXECUTE sys.sp_cdc_help_change_data_capture;
GO
--查看對(duì)某個(gè)實(shí)例(即表)的哪些列做了捕獲監(jiān)控
USE [GPOSDB]
GO
EXEC sys.sp_cdc_get_captured_columns
@capture_instance = 'systempara' -- sysname
--也可以從下面中查找配置信息
SELECT * FROM msdb.dbo.cdc_jobs
--2、查看當(dāng)前配置使用sp_cdc_help_jobs：
--從上文可以看到，啟用cdc之后會(huì)自動(dòng)創(chuàng)建了兩個(gè)作業(yè)，可以先使用以下語(yǔ)句來(lái)查看：
sp_cdc_help_jobs
--對(duì)于一個(gè)大型的OLTP系統(tǒng)，由于數(shù)據(jù)更改會(huì)非常頻繁，變更表中的數(shù)據(jù)會(huì)非常多，
--如果存放過(guò)久（最久可以存放100年），那對(duì)數(shù)據(jù)庫(kù)空間是非常大的挑戰(zhàn)。
--此時(shí)可以調(diào)整上圖中cdc.AdventureWorks_cleanup 中retention（單位：分鐘）。
--3、修改配置：sp_cdc_change_job
--顯示原有配置
EXEC sp_cdc_help_jobs
GO
--更改數(shù)據(jù)保留時(shí)間為100分鐘
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention=100
GO
--重啟一下作業(yè)，以使設(shè)置生效
--停用作業(yè)
EXEC sys.sp_cdc_stop_job N'cleanup'
GO
--啟用作業(yè)
EXEC sys.sp_cdc_start_job N'cleanup'
GO
--再次查看
EXEC sp_cdc_help_jobs
GO
--可以看到retention（單位：分鐘）的值變?yōu)?00了
--4、停止/啟用、刪除/創(chuàng)建作業(yè)
--停用作業(yè)
EXEC sys.sp_cdc_stop_job N'cleanup'
GO
--啟用作業(yè)
EXEC sys.sp_cdc_start_job N'cleanup'
GO
--刪除作業(yè)
EXEC sys.sp_cdc_drop_job @job_type = N'cleanup' -- nvarchar(20)
GO
--查看作業(yè)
EXEC sys.sp_cdc_help_jobs
GO
--可以看到現(xiàn)在只剩下一個(gè)作業(yè)了:cdc.GPOSDB_capture
--創(chuàng)建作業(yè)
EXEC sys.sp_cdc_add_job
@job_type = N'cleanup',
@start_job = 0,
@retention = 5760
--查看作業(yè)
EXEC sys.sp_cdc_help_jobs
GO
--5、DDL變更捕獲：
--CDC除了捕獲數(shù)據(jù)變更之外，還能捕獲DDL操作的變化。
--前提是先要確保SQLServer 代理的啟用，其實(shí)CDC功能都需要確保sql 代理正常運(yùn)行
--因?yàn)樗胁僮鞫纪ㄟ^(guò)代理中的兩個(gè)作業(yè)來(lái)實(shí)現(xiàn)的。
--現(xiàn)在先來(lái)對(duì)SYSTEMPARA 表修改一下，把PARAVALUE的長(zhǎng)度加長(zhǎng)
USE [GPOSDB]
GO
ALTER TABLE [dbo].[SystemPara] ALTER COLUMN PARAVALUE VARCHAR(120) ;
GO
--然后查詢(xún)ddl記錄表
SELECT * FROM cdc.ddl_history
--6、使用CDC的函數(shù)來(lái)獲取更改
--A、使用 [cdc].[fn_cdc_get_all_changes_DBO_SYSTEMPARA]
--函數(shù)報(bào)告捕獲實(shí)例的當(dāng)前所有可用更改
DECLARE @from_lsn BINARY(10) ,
@to_lsn BINARY(10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('SYSTEMPARA')
SET @to_lsn = sys.fn_cdc_get_max_lsn()
SELECT *
FROM cdc.fn_cdc_get_all_changes_DBO_SYSTEMPARA(@from_lsn, @to_lsn,N'all update old');
GO
--B、獲取某個(gè)時(shí)間段的更改信息：
--先根據(jù)日志序列號(hào)（logsequence number ,LSN）來(lái)獲取跟蹤變更數(shù)據(jù)
--Sys.fn_cdc_map_time_to_lsn獲取變更范圍內(nèi)的最大、最小LSN值?？梢允褂?BR>Smallest greater than;
smallest greater than orequal;
largest less than;
largest less than or equal;
--如查詢(xún)某個(gè)時(shí)間段插入的數(shù)據(jù)
INSERT INTO [dbo].[SystemPara]
( [ParaValue] ,
[Name] ,
[Description]
)
VALUES ( '中國(guó)' , -- ParaValue - varchar(50)
'中國(guó)' , -- Name - varchar(50)
'中國(guó)' -- Description - varchar(50)
)
GO
--檢查數(shù)據(jù)
--1刪除
--2插入
--3、4更改
--曾經(jīng)插入過(guò)的記錄就算delete了也可以查詢(xún)出來(lái)
DECLARE @bglsn VARBINARY(10)= sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',
'2013-10-21 12:00:00.997')
DECLARE @edlsn VARBINARY(10)= sys.fn_cdc_map_time_to_lsn('largest less than or equal',
GETDATE())
SELECT *
FROM [cdc].[DBO_SYSTEMPARA_CT]
WHERE [__$operation] = 2
AND [__$start_lsn] BETWEEN @bglsn AND @edlsn
--C、sys.fn_cdc_map_lsn_to_time 查詢(xún)變更時(shí)間：
SELECT [__$operation] ,
CASE [__$operation]
WHEN 1 THEN '刪除'
WHEN 2 THEN '插入'
WHEN 3 THEN '更新（捕獲的列值是執(zhí)行更新操作前的值）'
WHEN 4 THEN '更新（捕獲的列值是執(zhí)行更新操作后的值）'
END [類(lèi)型] ,
sys.fn_cdc_map_lsn_to_time([__$start_lsn]) [更改時(shí)間] ,
*
FROM [cdc].[DBO_SYSTEMPARA_CT]
--D、獲取LSN邊界
SELECT sys.fn_cdc_get_max_lsn() [數(shù)據(jù)庫(kù)級(jí)別的最大LSN] ,
sys.fn_cdc_get_min_lsn('cdc.DBO_SYSTEMPARA_CT') [捕獲實(shí)例的lsn]
這兩個(gè)值可以用于上面提到的函數(shù)里面用于篩選數(shù)據(jù)之用。
----------------------------------------------------------
--1. CDC的目的是什么？
--CDC就是極大地方便了我們獲取某個(gè)表數(shù)據(jù)更新情況的一個(gè)機(jī)制。它通過(guò)一個(gè)獨(dú)立的進(jìn)程，
--異步讀取日志文件，而不是觸發(fā)器的方式工作。而且它的數(shù)據(jù)是會(huì)持久化保存到一個(gè)系統(tǒng)表的。
--2. CDC是不是SQL Server 2008特有的功能，對(duì)別的數(shù)據(jù)庫(kù)或者早期版本是否起作用？
--CDC是SQL Server 2008特有的功能，而且是企業(yè)版特有的功能。開(kāi)發(fā)版也有該功能，但僅用于測(cè)試場(chǎng)合。
--3. CDC讀取日志，那么如果日志被截?cái)嗔藭?huì)怎么樣？
--如果某部分日志，CDC的進(jìn)程還沒(méi)有讀取，那么在截?cái)嗳罩緯r(shí)就會(huì)忽略這個(gè)部分,不能截?cái)?!
--捕獲進(jìn)程是一個(gè)獨(dú)立的，它隨著代理服務(wù)啟動(dòng)而啟動(dòng)。兩次掃描之間間隔5分鐘。
--4. 系統(tǒng)表中的數(shù)據(jù)是否會(huì)永久存在？--不會(huì)，它會(huì)被保留3天。會(huì)有一個(gè)清理的作業(yè)，每天晚上2點(diǎn)進(jìn)行掃描。
--最后，補(bǔ)充一點(diǎn)的是，CDC功能依賴(lài)Agent服務(wù)，因?yàn)樗袃蓚€(gè)操作都是通過(guò)作業(yè)來(lái)啟動(dòng)的。
