SQLServer 2008 CDC功效完成數據變革捕捉劇本。本站提示廣大學習愛好者：（SQLServer 2008 CDC功效完成數據變革捕捉劇本）文章只能為提供參考，不一定能成為您想要的結果。以下是SQLServer 2008 CDC功效完成數據變革捕捉劇本正文
CDC:Change Data Capture
--步調：本文中以GPOSDB為例
--第一步、對目的庫顯式啟用CDC：
--在以後庫應用sys.sp_cdc_enable_db。前往0（勝利）或1（掉敗）。
--留意，沒法對體系數據庫和分發數據庫啟用該功效。且履行者須要用sysadmin腳色權限。
--該存儲進程的感化域是全部目的庫。包括元數據、DDL觸發器、cdc架構和cdc用戶。
--應用以下代碼啟用：
USE GPOSDB --要啟用CDC的數據庫
GO
EXECUTE sys.sp_cdc_enable_db;
GO
--在一開端直接履行時，湧現了報錯信息：
--新聞22830，級別16，狀況1，進程sp_cdc_enable_db_internal，第193 行
--沒法更新元數據來指導已對數據庫AdventureWorks 啟用了變革數據捕捉。
--履行敕令'SetCDCTracked(Value = 1)' 時掉敗。
--前往的毛病為15517: '沒法作為數據庫主體履行，由於主體"dbo"
--不存在、沒法模仿這類類型的主體，或您沒有所需的權限。'。請應用此操作和毛病來肯定掉敗的緣由偏重新提交要求。
--這裡引出了別的一個常識點：毛病號 15517 的毛病
--這類毛病會在許多處所湧現，如復原數據庫的時刻也會有能夠湧現。
--配合點是：某個/些存儲進程應用了具有WITHEXECUTE AS 的選項。
--使其在以後庫具有了某個架構，然則當在其余處所履行時，因為沒有這個架構，所以就報錯，處理辦法：
ALTER AUTHORIZATION ON DATABASE::[GPOSDB] TO [sa]
--經由檢討，uspUpdateEmployeeHireInfo這個存儲進程切實其實有：WITH EXECUTE AS CALLER
--應用sa的緣由是即便sa被禁用，sa照樣存在的。所以不會報錯。
--如今從新履行：
USE GPOSDB
GO
EXECUTE sys.sp_cdc_enable_db;
GO
--啟用勝利，然後經由過程以下語句檢討能否勝利：
SELECT
is_cdc_enabled,
CASE WHEN is_cdc_enabled=0 THEN 'CDC功效禁用' ELSE 'CDC功效啟用' END 描寫
FROM sys.databases
WHERE NAME = 'GPOSDB'
--創立勝利後，將主動添加CDC用戶和CDC架構。
--在用戶和架構上面可以看到cdc用戶和cdc架構
--創立這兩個用戶、架構的緣由是由於CDC請求獨有方法應用這兩個架構，所以要零丁創立。
--假如存在了非CDC功效創立的CDC用戶、架構的話，則須要先刪除該cdc定名的架構，能力開啟。
--第二步、對目的表啟用CDC：
--應用db_owner腳色的成員履行sys.sp_cdc_enable_table為每一個須要跟蹤的表創立捕捉實例。
--然後經由過程sys.tables目次視圖中的is_tracked_by_cdc列來斷定能否創立勝利。
--默許情形下會對表的全體列做捕捉。假如只須要對某些列做捕捉，
--可使用@captured_column_list參數指定這些列。
--假如要把更改表放到文件組裡的話，最好創立零丁的文件組（最最少與源表自力）。
--假如不想掌握拜訪腳色，則@role_name必需顯式設置為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 這個表開啟變革捕捉。
USE GPOSDB
GO
EXEC sys.sp_cdc_enable_table @source_schema = 'DBO',
@source_name = 'SYSTEMPARA',@role_name = NULL
--然後查詢能否勝利：
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')
--對表開啟今後，可以鄙人圖中看到多了許多cdc架構開首的表：
--刷新一下GPOSDB數據庫，在體系表上面可以看到多了上面幾張表
[cdc].[DBO_SYSTEMPARA_CT]
[cdc].[change_tables]
[cdc].[captured_columns]
[cdc].[ddl_history]
[cdc].[index_columns]
[cdc].[lsn_time_mapping]
[dbo].[systranschemas]
[dbo].[dtproperties]
--啟動以後，可以看到SQLServer署理外面的功課，也湧現了這兩個功課：
[cdc.GPOSDB_capture]
[cdc.GPOSDB_cleanup]
--在可編程性-》函數-》表值函數裡，也多了兩個函數
[cdc].[fn_cdc_get_all_changes_DBO_SYSTEMPARA]
[cdc].[fn_cdc_get_net_changes_DBO_SYSTEMPARA]
--上面列出相干的存儲進程：
--Sys.sp_cdc_add_job
--解釋及例子
--Sys.sp_cdc_generate_wrapper_function
--解釋及例子
--Sys.sp_cdc_change_job
--解釋及例子
--Sys.sp_cdc_get_captured_columns
--解釋及例子
--Sys.sp_cdc_cleanup_change_table
--解釋及例子
--Sys.sp_cdc_get_ddl_history
--解釋及例子
--Sys.sp_cdc_disable_db
--解釋及例子 建議先禁用表，再禁用庫
--Sys.sp_cdc_help_change_data_capture
--解釋及例子
--Sys.sp_cdc_disable_table
--解釋及例子
--Sys.sp_cdc_help_jobs
--解釋及例子
--Sys.sp_cdc_drop_job
--解釋及例子
--Sys.sp_cdc_scan
--解釋及例子
--Sys.sp_cdc_enable_db
--解釋及例子
--Sys.sp_cdc_start_job
--解釋及例子
--Sys.sp_cdc_enable_table
--解釋及例子
--Sys.sp_cdc_stop_job
--解釋及例子
--函數：
--Cdc.fn_cdc_get_all_changes_<capture_instance>
--解釋及例子
--Sys.fn_cdc_has_column_changed
--解釋及例子
--Cdc.fn_cdc_get_net_changes_<capture_instance>
--解釋及例子
--Sys.fn_cdc_increment_lsn
--解釋及例子
--Sys.fn_cdc_decrement_lsn
--解釋及例子
--Sys.fn_cdc_is_bit_set
--解釋及例子
--Sys.fn_cdc_get_column_ordinal
--解釋及例子
--Sys.fn_cdc_map_lsn_to_time
--解釋及例子
--Sys.fn_cdc_get_max_lsn
--解釋及例子
--Sys.fn_cdc_map_time_to_lsn
--解釋及例子
--Sys.fn_cdc_get_min_lsn
--解釋及例子
--------------------上面開端從頭至尾做一個現實案例-------------------------
--上面開端從頭至尾做一個現實案例
--步調一:對目的庫顯式啟用CDC
USE GPOSDB --要啟用CDC的數據庫
GO
EXECUTE sys.sp_cdc_enable_db;
GO
--某些數據庫能夠存在一些存儲進程包括有：execute as 等語句，此時會報錯：
--文字描寫：
--新聞22830，級別16，狀況1，進程sp_cdc_enable_db_internal，第186 行
--沒法更新元數據來指導已對數據庫AdventureWorks 啟用了變革數據捕捉。履行敕令'SetCDCTracked(Value = 1)' 時掉敗。前往的毛病為15517: '沒法作為數據庫主體履行，由於主體"dbo" 不存在、沒法模仿這類類型的主體，或您沒有所需的權限。'。請應用此操作和毛病來肯定掉敗的緣由偏重新提交要求。
--新聞266，級別16，狀況2，進程sp_cdc_enable_db_internal，第0 行
--EXECUTE 後的事務計數指導BEGIN 和COMMIT 語句的數量不婚配。上一計數= 0，以後計數= 1。
--新聞266，級別16，狀況2，進程sp_cdc_enable_db，第0 行
--EXECUTE 後的事務計數指導BEGIN 和COMMIT 語句的數量不婚配。上一計數= 0，以後計數= 1。
--新聞3998，級別16，狀況1，第1 行
--在批處置停止時檢測到弗成提交的事務。該事務將回滾。
--假如湧現這個毛病，今朝的處理辦法是履行上面語句，緣由已在開首解釋，關於沒有應用EXECUTE AS的庫，普通不會有如許的成績：
ALTER AUTHORIZATION ON DATABASE::[GPOSDB] TO [sa]
--如今從新履行：
USE GPOSDB
GO
EXECUTE sys.sp_cdc_enable_db;
GO
--經由過程以下語句檢討能否勝利：
SELECT
is_cdc_enabled,
CASE WHEN is_cdc_enabled=0 THEN 'CDC功效禁用' ELSE 'CDC功效啟用' END 描寫
FROM sys.databases
WHERE NAME = 'GPOSDB'
--步調二：對表啟用CDC
USE GPOSDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = 'DBO',
@source_name = 'SYSTEMPARA',
@role_name = NULL,
@capture_instance=DEFAULT
GO
--然後查詢能否勝利：
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數據庫裡的體系內外新增了[cdc].[DBO_SYSTEMPARA_CT]表
--步調三：磨練，上面來修改數據
--先查詢一下DBO_SYSTEMPARA_CT表
SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
--可以看到一筆記錄都沒有，由於方才創立，並沒有對原表systempara做任何增刪改操作
--向[SystemPara]表拔出一筆記錄
INSERT INTO [dbo].[SystemPara]
( [ParaValue] ,
[Name] ,
[Description]
)
VALUES ( '中國' , -- ParaValue - varchar(50)
'中國' , -- Name - varchar(50)
'中國' -- Description - varchar(50)
)
--查詢一下DBO_SYSTEMPARA_CT表，可以看到多了一筆記錄
SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
--更新[SystemPara]表的一筆記錄
UPDATE [dbo].[SystemPara] SET [ParaValue]='德國' WHERE [Description]='中國'
--查詢一下DBO_SYSTEMPARA_CT表，可以看到多了兩筆記錄
SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
--刪除[SystemPara]表的一筆記錄
DELETE FROM [dbo].[SystemPara] WHERE [Description]='中國'
--查詢一下DBO_SYSTEMPARA_CT表，可以看到多了一筆記錄
SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]
--如今來剖析一下DBO_SYSTEMPARA_CT表
--可以在聯機叢書上檢查：
--cdc.<capture_instance>_CT
--可以看到，如許定名的表，是用於記載源表做過更改操作的表。
--關於insert/delete操作，會有對應的一行記載，而關於update，會有兩行記載。
--關於__$operation列：1 = 刪除、2= 拔出、3= 更新（舊值）、4= 更新（新值）
--update語句的__$operation列的值是3和4，所以一條update語句對應兩筆記錄
--關於__$start_lsn列：因為更改是起源與數據庫的事務日記，所以這裡會保留其事務日記的開端序列號（LSN）
--然則微軟不檢討直接查詢這類表，建議應用
--cdc.fn_cdc_get_all_changes_<捕捉實例>
--cdc.fn_cdc_get_net_changes_<capture_instance>
-- 來查詢
----------------------------------------------------------
--下文開端，來熟習各類函數、存儲進程的應用，並測驗考試一些不正常的操作。
--平常應用情形：
--1、查詢曾經開啟的捕捉實例：
--前往一切表的變革捕捉設置裝備擺設信息
USE [GPOSDB]
GO
EXECUTE sys.sp_cdc_help_change_data_capture;
GO
--檢查對某個實例(即表)的哪些列做了捕捉監控
USE [GPOSDB]
GO
EXEC sys.sp_cdc_get_captured_columns
@capture_instance = 'systempara' -- sysname
--也能夠從上面中查找設置裝備擺設信息
SELECT * FROM msdb.dbo.cdc_jobs
--2、檢查以後設置裝備擺設應用sp_cdc_help_jobs：
--從上文可以看到，啟用cdc以後會主動創立了兩個功課，可以先應用以下語句來檢查：
sp_cdc_help_jobs
--關於一個年夜型的OLTP體系，因為數據更改會異常頻仍，變革表中的數據會異常多，
--假如寄存太久（最久可以寄存100年），那對數據庫空間長短常年夜的挑釁。
--此時可以調劑上圖中cdc.AdventureWorks_cleanup 中retention（單元：分鐘）。
--3、修正設置裝備擺設：sp_cdc_change_job
--顯示原有設置裝備擺設
EXEC sp_cdc_help_jobs
GO
--更改數據保存時光為100分鐘
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention=100
GO
--重啟一下功課，以使設置失效
--停用功課
EXEC sys.sp_cdc_stop_job N'cleanup'
GO
--啟用功課
EXEC sys.sp_cdc_start_job N'cleanup'
GO
--再次檢查
EXEC sp_cdc_help_jobs
GO
--可以看到retention（單元：分鐘）的值變成100了
--4、停滯/啟用、刪除/創立功課
--停用功課
EXEC sys.sp_cdc_stop_job N'cleanup'
GO
--啟用功課
EXEC sys.sp_cdc_start_job N'cleanup'
GO
--刪除功課
EXEC sys.sp_cdc_drop_job @job_type = N'cleanup' -- nvarchar(20)
GO
--檢查功課
EXEC sys.sp_cdc_help_jobs
GO
--可以看到如今只剩下一個功課了:cdc.GPOSDB_capture
--創立功課
EXEC sys.sp_cdc_add_job
@job_type = N'cleanup',
@start_job = 0,
@retention = 5760
--檢查功課
EXEC sys.sp_cdc_help_jobs
GO
--5、DDL變革捕捉：
--CDC除捕捉數據變革以外，還能捕捉DDL操作的變更。
--條件是先要確保SQLServer 署理的啟用，其實CDC功效都須要確保sql 署理正常運轉
--由於一切操作都經由過程署理中的兩個功課來完成的。
--如今先來對SYSTEMPARA 表修正一下，把PARAVALUE的長度加長
USE [GPOSDB]
GO
ALTER TABLE [dbo].[SystemPara] ALTER COLUMN PARAVALUE VARCHAR(120) ;
GO
--然後查詢ddl記載表
SELECT * FROM cdc.ddl_history
--6、應用CDC的函數來獲得更改
--A、應用 [cdc].[fn_cdc_get_all_changes_DBO_SYSTEMPARA]
--函數申報捕捉實例確當前一切可用更改
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、獲得某個時光段的更改信息：
--先依據日記序列號（logsequence number ,LSN）來獲得跟蹤變革數據
--Sys.fn_cdc_map_time_to_lsn獲得變革規模內的最年夜、最小LSN值。可使用
Smallest greater than;
smallest greater than orequal;
largest less than;
largest less than or equal;
--如查詢某個時光段拔出的數據
INSERT INTO [dbo].[SystemPara]
( [ParaValue] ,
[Name] ,
[Description]
)
VALUES ( '中國' , -- ParaValue - varchar(50)
'中國' , -- Name - varchar(50)
'中國' -- Description - varchar(50)
)
GO
--檢討數據
--1刪除
--2拔出
--3、4更改
--已經拔出過的記載就算delete了也能夠查詢出來
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 查詢變革時光：
SELECT [__$operation] ,
CASE [__$operation]
WHEN 1 THEN '刪除'
WHEN 2 THEN '拔出'
WHEN 3 THEN '更新（捕捉的列值是履行更新操作前的值）'
WHEN 4 THEN '更新（捕捉的列值是履行更新操作後的值）'
END [類型] ,
sys.fn_cdc_map_lsn_to_time([__$start_lsn]) [更改時光] ,
*
FROM [cdc].[DBO_SYSTEMPARA_CT]
--D、獲得LSN界限
SELECT sys.fn_cdc_get_max_lsn() [數據庫級其余最年夜LSN] ,
sys.fn_cdc_get_min_lsn('cdc.DBO_SYSTEMPARA_CT') [捕捉實例的lsn]
這兩個值可以用於下面提到的函數外面用於挑選數據之用。
----------------------------------------------------------
--1. CDC的目標是甚麼？
--CDC就是極年夜處所便了我們獲得某個表數據更新情形的一個機制。它經由過程一個自力的過程，
--異步讀取日記文件，而不是觸發器的方法任務。並且它的數據是會耐久化保留到一個體系表的。
--2. CDC是否是SQL Server 2008獨有的功效，對其余數據庫或許晚期版本能否起感化？
--CDC是SQL Server 2008獨有的功效，並且是企業版獨有的功效。開辟版也有該功效，但僅用於測試場所。
--3. CDC讀取日記，那末假如日記被截斷了會怎樣樣？
--假如某部門日記，CDC的過程還沒有讀取，那末在截斷日記時就會疏忽這個部門,不克不及截斷!!
--捕捉過程是一個自力的，它跟著署理辦事啟動而啟動。兩次掃描之間距離5分鐘。
--4. 體系表中的數據能否會永遠存在？--不會，它會被保存3天。會有一個清算的功課，天天早晨2點停止掃描。
--最初，彌補一點的是，CDC功效依附Agent辦事，由於它有兩個操作都是經由過程功課來啟動的。
