程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQLServer 2008 CDC功能實現數據變更捕獲腳本

SQLServer 2008 CDC功能實現數據變更捕獲腳本

編輯:關於SqlServer


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服務,因為它有兩個操作都是通過作業來啟動的。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved