關于開啟CDC執行個體的二三事

注：關于SQL SERVER 2012在SP3更新檔級别下對表開啟CDC， 
 若該表有頻繁的MERGE操作，則向CDC表中插入資料時會報違反唯一限制錯誤，盡管此時表中 
 并無資料。及時在關閉CDC後重新開啟，仍不能解決。此問題系SQL SERVER的一個BUG,已在 
 SP4更新檔中修複。是以若遇到此問題，更新更新檔即可。

use test

--STEP 1:
--對目标庫顯式啟用CDC：該存儲過程的作用域是整個目标庫。
包含中繼資料、DDL觸發器、cdc架構和cdc使用者。
EXEC sys.sp_cdc_enable_db
/*錯誤号 15517 :某個/些存儲過程使用了具有WITHEXECUTE AS 的選項。
使其在目前庫具有了某個架構，但是當在别的地方執行時，由于沒有這個架構，是以就報錯
解決方法:  ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa] */
--禁用資料庫
EXEC sys.sp_cdc_disable_db
--查詢資料庫是否啟用成功：1  成功； 0   失敗
select is_cdc_enabled  from sys.databases  where name='test'
--STEP :
--對目标表啟用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 必須存在于目前資料庫中。
    --不能對 cdc 架構中的表啟用變更資料捕獲。
    [ @role_name = ] 'role_name'
    --是用于控制更改資料通路的資料庫角色的名稱。
    --如果顯式設定為 NULL，則沒有控制角色用于限制對更改資料的通路。
    [,[ @capture_instance = ] 'capture_instance' ]
    --用于命名特定于執行個體的變更資料捕獲對象的捕獲執行個體的名稱，源表最多兩個捕獲執行個體
    [,[ @supports_net_changes = ] supports_net_changes ]
    [,[ @index_name = ] 'index_name' ]
    [,[ @captured_column_list = ] 'captured_column_list' ]
    --辨別将包括在更改表中的源表列。如果為 NULL，則所有列都将包括在更改表中。
    --captured_column_list 是以逗号分隔的列名稱清單。
    [,[ @filegroup_name = ] 'filegroup_name' ]
    --是要用于為捕獲執行個體建立的更改表的檔案組。如果為 NULL，則使用預設檔案組。
    --是否可以對啟用了變更資料捕獲的表執行 ALTER TABLE 的 SWITCH PARTITION 指令。

  --為test表開啟變更捕獲
  exec sys.sp_cdc_enable_table @source_schema='dbo',
  @source_name='test',
  @role_name=N'cdc_Admin',
  @capture_instance=DEFAULT
  --查詢标是否啟用成功：  成功；    失敗
  SELECT  name ,
        is_tracked_by_cdc ,
        CASE WHEN is_tracked_by_cdc =  THEN 'CDC功能禁用'
             ELSE 'CDC功能啟用'
        END 描述
FROM    sys.tables
WHERE   OBJECT_ID IN( OBJECT_ID('dbo.test'))

cdc.<capture_instance>_CT   
可以看到，這樣命名的表，是用于記錄源表更改的表。
對于insert/delete操作，會有對應的一行記錄，而對于update，會有兩行記錄。
對于```__$operation```列： = 删除、= 插入、= 更新（舊值）、= 更新（新值）
對于__$start_lsn列：由于更改是來源與資料庫的事務日志，是以這裡會儲存其事務日志的開
始序列号（LSN）

--查詢已經開啟的捕獲執行個體
exec sys.sp_cdc_help_change_data_capture
--檢視對某個執行個體(即表)的哪些列做了捕獲監控:
EXEC sys.sp_cdc_get_captured_columns
@capture_instance = 'dbo_eco_data_ind_machineryequipment' -- sysname
--查找配置資訊
SELECT * FROM msdb.dbo.cdc_jobs
--檢視目前配置
EXEC sp_cdc_help_jobs
--maxtrans:捕獲作業每次循環時要處理的最大事務數
--maxscans:每次循環次數
--continuous::連續運作；:間隔運作 
--pollinginterval：日志掃描周期之間的秒數
--retention:變更資料保留的分鐘數
--threshold：删除項，可以通過使用上清除的單個語句删除系統資料庫項的最大數量  ？？
--更改資料保留時間為分鐘
EXECUTE sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention=
GO
--禁用CDC
EXEC sys.sp_cdc_disable_table  
    @source_schema = 'dbo',  
    @source_name = 'test',  
    @capture_instance = 'all'; 
--啟用
EXEC sys.sp_cdc_enable_table  
    @source_schema = 'dbo',  
    @source_name = 't1',  
    @role_name = null;  
    -- 
--停用作業
EXEC sys.sp_cdc_stop_job N'cleanup'
GO
--啟用作業
EXEC sys.sp_cdc_start_job N'cleanup'
GO
--再次檢視
EXEC sp_cdc_help_jobs
GO
--删除作業
EXEC sys.sp_cdc_drop_job @job_type = N'cleanup' -- nvarchar(20)
GO
--建立作業
EXEC sys.sp_cdc_add_job
    @job_type = N'cleanup',
    @start_job = 0,
    @retention = 5760
--DDL變更捕獲,保證代理處于開啟狀态
CDC除了捕獲資料變更之外，還能捕獲DDL操作的變化。前提是先要確定SQLServer 代理的
啟用，其實CDC功能都需要確定sql 代理正常運作，因為所有操作都通過代理中的兩個作業來
實作的。
--查詢DDL記錄
SELECT  * FROM cdc.ddl_history
--使用CDC函數來擷取更改
--
DECLARE @from_lsn binary(), @to_lsn binary()
SET @from_lsn =
   sys.fn_cdc_get_min_lsn('dbo_eco_data_ind_machineryequipment')
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
--擷取某個時間段的更改資訊：
--先根據日志序列号（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.test(id,name)
VALUES('1','abc')
INSERT INTO dbo.test(id,name)
VALUES('4','abc1')
go
--檢查資料


DECLARE @bglsn VARBINARY()=sys.fn_cdc_map_time_to_lsn(
'smallest greater than or equal',
'2012-10-12 12:00:00.997')
DECLARE @edlsn VARBINARY()=sys.fn_cdc_map_time_to_lsn(
'largest less than or equal',GETDATE())
SELECT id,name
FROM cdc.dbo_test_CT
WHERE [__$operation]=  AND [__$start_lsn] BETWEEN @bglsn AND @edlsn



--sys.fn_cdc_map_lsn_to_time 查詢變更時間：

SELECT  [__$operation] ,
       CASE [__$operation] WHEN  THEN '删除' 
       WHEN  THEN '插入' WHEN  THEN '更新（捕獲的列值是執行更新操作前的值）'
       WHEN  THEN '更新（捕獲的列值是執行更新操作後的值）' END [類型],
        sys.fn_cdc_map_lsn_to_time([__$start_lsn]) [更改時間] ,
       ID,
       NAME
FROM    cdc.dbo_test_CT

–擷取LSN邊界

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

sys.fn_cdc_get_min_lsn(‘cdc.dbo_test_CT’)–[捕獲執行個體的lsn]

--添加新列
alter table test add loc varchar() null
--添加新的cdc-capture inctance
exec sp_cdc_enable_table
 @source_schema = N'dbo',
    @source_name = N'test',
    @role_name = NULL,
    @filegroup_name =null,
    @capture_instance = 'loc'--新的系統表名稱（cdc.loc_ct）
    ```

–将資料從舊表移動到新表

INSERT INTO cdc.loc_ct
(__$start_lsn, __$end_lsn,__$seqval,__$operation,__$update_mask,Id,Name)
SELECT
    __$start_lsn, 
    __$end_lsn,
    __$seqval,
    __$operation,
    __$update_mask,
    Id,
    Name
FROM cdc.dbo_test_CT

–禁用舊的cdc執行個體

EXEC sp_cdc_disable_table

@source_schema = N’dbo’,

@source_name = N’test’,

@capture_instance = ‘dbo_test’

–此操作會更改捕獲執行個體名稱,禁用cdc執行個體後，原來的’cdc.dbo_test_ct’系統表會被删除，隻有建立的’loc_ct’.

–若想保證cdc執行個體名稱不變，可以進行第二步反操作。建立cdc執行個體為’dbo_test’,插入資料，并禁用’loc’.

exec sp_cdc_enable_table

@source_schema = N’dbo’,

@source_name = N’test’,

@role_name = NULL,

@filegroup_name =null,

@capture_instance = ‘dbo_test’–新的系統表名稱（cdc.loc_ct）

–将資料從舊表移動到新表

INSERT INTO cdc.dbo_test_CT 
                (__$start_lsn, __$end_lsn,
                __$seqval,__$operation,__$update_mask,Id,Name)
                SELECT
                    __$start_lsn, 
                    __$end_lsn,
                    __$seqval,
                    __$operation,
                    __$update_mask,
                    Id,
                    Name
                FROM cdc.loc_CT

--禁用舊的cdc執行個體
            EXEC sp_cdc_disable_table
                @source_schema = N'dbo',
                @source_name = N'test',
                @capture_instance = 'loc'

–原表中資料不需要保留,則直接禁用cdc執行個體後，在啟用cdc.此時系統表中的表也将删除并重建。

–禁用cdc執行個體

EXEC sp_cdc_disable_table

@source_schema = N’dbo’,

@source_name = N’test’,

@capture_instance = ‘dbo_test’

–啟用cdc執行個體

EXECUTE sys.sp_cdc_enable_table

@source_schema = N’dbo’

, @source_name = N’eco_info_pro’

, @role_name = N’cdc_Admin’–可以自動建立

, @capture_instance=DEFAULT

關于開啟CDC執行個體的二三事

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述