SQLServer開啟CDC功能（2）

SQLServer利用CDC功能實時同步表資料。

一、適用環境

僅在SQLServer2008企業版、開發版和評估版以後可用。

確保SQLServer資料庫已經開啟SQL Server代理。

二、CDC功能介紹

CDC（change data capture）功能主要捕獲SQLServer指定表的增刪改操作，由於任何操作都會寫日誌（哪怕truncate），所以CDC的捕獲來源於日誌檔案。日誌檔案會把更改應用到資料檔案中，同時也會標記符合要求的資料標記為需要新增跟蹤的項。然後透過一些配套函式，最後寫入到資料倉儲中。

三、具體同步步驟

1、確定哪些表進行CDC同步

2、對資料庫開啟CDC功能

USE  [Finance]

GO

EXEC  sys . sp_cdc_enable_db

GO

檢視CDC是否開啟：

SELECT   is_cdc_enabled  ,

         CASE  WHEN  is_cdc_enabled  = 0  THEN  'CDC 功能禁用 '

              ELSE  'CDC 功能啟用 '

         END  描述

FROM     sys . databases

WHERE    NAME  =  'finance'

1表示開啟。

發現資料庫安全性多了cdc，架構多了cdc

出現6個系統表：

cdc.captured_columns

cdc.change_tables

cdc.ddl_history

cdc.index_columns

cdc.lsn_time_mapping

dbo.systranschemas

對某些表開啟捕獲：

USE  [Finance] ;

GO

EXECUTE sys.sp_cdc_enable_table

    @source_schema = N'dbo'

  , @source_name = N'DO'

  , @role_name = N'cdc_Admin'--可以自動建立

  , @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 IN( OBJECT_ID('dbo.DO'))

系統表會增加開啟CDC功能的表。

SQLServer代理出現作業：

相關儲存過程：

Sys.sp_cdc_disable_db： 建議先禁用表，再禁用庫

函式：

可以對錶insert資料，檢視資料變化：

select * from [cdc].[dbo_DO_CT];

如果有資料的插入、更新、刪除會在這裡記錄。

對於__$operation列：1 = 刪除、2= 插入、3= 更新（舊值）、4= 更新（新值）

對於__$start_lsn列：由於更改是來源與資料庫的事務日誌，所以這裡會儲存其事務日誌的開始序列號（LSN）

但是微軟不建議直接查詢這類表，建議使用cdc.fn_cdc_get_all_changes_<捕獲例項> 和cdc.fn_cdc_get_net_changes_<capture_instance>  來查詢。

查詢已經開啟的捕獲：

EXECUTE sys.sp_cdc_help_change_data_capture;

GO

USE Finance;

GO

cdc.fn_cdc_get_all_changes_<捕獲例項>用法：

DECLARE @from_lsn binary(10), @to_lsn binary(10)

SET @from_lsn =

   sys.fn_cdc_get_min_lsn('dbo_DO')

SET @to_lsn   = sys.fn_cdc_get_max_lsn()

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_dept

  (@from_lsn, @to_lsn, N'all');

GO

--返回所有表的變更捕獲配置資訊

EXECUTE sys.sp_cdc_help_change_data_capture;

GO

檢視對某個例項(即表)的哪些列做了捕獲監控:

EXEC sys.sp_cdc_get_captured_columns

@capture_instance = 'HumanResources_Department' -- sysname

也可以從下面中查詢配置資訊：

SELECT * FROM msdb.dbo.cdc_jobs

啟用cdc之後會自動建立了兩個作業，可以先使用以下語句來檢視：

sp_cdc_help_jobs

--顯示原有配置：

EXEC sp_cdc_help_jobs

GO

--更改資料保留時間為分鐘

EXECUTE sys.sp_cdc_change_job

    @job_type = N'cleanup',

    @retention=100

GO

--停用作業

EXEC sys.sp_cdc_stop_jobN'cleanup'

GO

--啟用作業

EXEC sys.sp_cdc_start_jobN'cleanup'

GO

--再次檢視

EXEC sp_cdc_help_jobs

GO

停止/開始作業，可以使用以下語句：

--停用作業

EXEC sys.sp_cdc_stop_jobN'cleanup'

GO

--啟用作業

EXEC sys.sp_cdc_start_jobN'cleanup'

GO

刪除作業：

EXEC sys.sp_cdc_drop_job@job_type = N'cleanup' -- nvarchar(20)

GO

--檢視作業

EXEC sys.sp_cdc_help_jobs

GO

建立作業：

EXEC sys.sp_cdc_add_job

    @job_type = N'cleanup',

    @start_job = 0,

    @retention = 5760

--檢視作業

EXEC sys.sp_cdc_help_jobs

GO

DDL變更捕獲：

CDC除了捕獲資料變更之外，還能捕獲DDL操作的變化。前提是先要確保SQLServer 代理的啟用，其實CDC功能都需要確保sql 代理正常執行，因為所有操作都透過代理中的兩個作業來實現的。

現在先來對HumanResources.Department 表修改一下，把name的長度加長：

ALTER TABLE HumanResources.Department ALTER COLUMN Name NVARCHAR(120) ;

GO

然後查詢ddl記錄表：

SELECT  * FROM    cdc.ddl_history

使用CDC的函式來獲取更改：

A、使用cdc.fn_cdc_get_all_changes_HumanResources_Department 函式報告捕獲例項HumanResources_Department 的當前所有可用更改：

DECLARE @from_lsn binary(10), @to_lsn binary(10)

SET @from_lsn =

   sys.fn_cdc_get_min_lsn('HumanResources_Department')

SET @to_lsn   = sys.fn_cdc_get_max_lsn()

SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Department

  (@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 HumanResources.Department(name,GroupName,ModifiedDate)

VALUES('test','abc',GETDATE())

INSERT INTO HumanResources.Department(name,GroupName,ModifiedDate)

VALUES('test1','abc1',GETDATE())

go

--檢查資料

DECLARE @bglsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal','2012-10-12 12:00:00.997')

DECLARE @edlsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('largest less than or equal',GETDATE())

SELECT DepartmentID,GroupName,Name

FROM cdc.HumanResources_Department_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]) [更改時間] ,

        name ,

        DepartmentID ,

        GroupName ,

        ModifiedDate

FROM    cdc.HumanResources_Department_CT

注意,由於該表剛好有一個modfieddate欄位,所以和更改時間相同.

D、獲取LSN邊界：

SELECT sys.fn_cdc_get_max_lsn()[資料庫級別的最大LSN],

sys.fn_cdc_get_min_lsn('cdc.HumanResources_Department_CT')[捕獲例項的lsn]

增加刪除欄位時候開啟關閉表級CDC：

查詢capture_instance：

EXECUTE sys.sp_cdc_help_change_data_capture;

GO

關閉：

USE  [Sales] ;

GO

EXECUTE sys.sp_cdc_disable_table

    @source_schema = N'dbo'

  , @source_name = N'SO'

 -- , @role_name = N'cdc_Admin'--可以自動建立

  , @capture_instance=N'dbo_SO'

GO

開啟：

USE  [Sales] ;

GO

EXECUTE sys.sp_cdc_enable_table

    @source_schema = N'dbo'

  , @source_name = N'SO'

  , @role_name = N'cdc_Admin'--可以自動建立

  , @capture_instance=N'dbo_SO'

GO