標簽：SQL SERVER/MSSQL SERVER/數據庫/DBA/字段/對象更改

變更數據捕獲用于捕獲應用到 SQL Server 表中的插入、更新和刪除活動，并以易于使用的關系格式提供這些變更的詳細信息。變更數據捕獲所使用的更改表中包含鏡像所跟蹤源表列結構的列，同時還包含了解所發生的變更所需的元數據。

對表開啟了變更捕獲之后，對該表的所有DML和DDL操作都會被記錄，有助于跟蹤表的變化。

測試環境：SQL SERVER 2008 R2,案例庫AdventureWorks2008R2

目錄

• 概述
• 配置變更數據捕獲
  • 啟動數據庫捕獲
  • 設置跟蹤表
  • 數據庫的變化
• 對象分析
  • 插入測試數據
  • 分析系統表
  • 分析存儲過程
  • 分析函數
  • 分析系統視圖
• 總結

--開啟cdcUSE [AdventureWorks2008R2]EXECUTE sys.sp_cdc_enable_dbGO---如果開啟數據庫捕獲報錯誤:15517，使用下面的方法處理ALTER AUTHORIZATION ON DATABASE::[AdventureWorks2008R2] TO [sa]---查看是否設置成功，1代表開啟cdcSELECT is_cdc_enabled  FROM SYS.databases WHERE name='AdventureWorks2008R2'

為當前數據庫中指定的源表啟用變更數據捕獲。對表啟用變更數據捕獲時，應用于此表的每個數據操縱語言 (DML) 操作的記錄都將寫入事務日志中。變更數據捕獲進程將從日志中檢索此信息，并將其寫入可通過使用一組函數訪問的更改表中。

格式：

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 參數進行標識的唯一索引，則此參數的默認值為 1。否則，此參數默認為 0。    [,[ @index_name = ] 'index_name' ]--用于唯一標識源表中的行的唯一索引的名稱。index_name 為 sysname，并且可以為 NULL。如果指定，則 index_name 必須是源表的唯一有效索引。如果指定 index_name，則標識的索引列優先于任何定義的主鍵列，就像表的唯一行標識符一樣。    [,[ @captured_column_list = ] 'captured_column_list' ]--需要對哪些列進行捕獲。captured_column_list 的數據類型為 nvarchar(max)，并且可以為 NULL。如果為 NULL，則所有列都將包括在更改表中。    [,[ @filegroup_name = ] 'filegroup_name' ]--是要用于為捕獲實例創建的更改表的文件組。  [,[ @partition_switch = ] 'partition_switch' ]--指示是否可以對啟用了變更數據捕獲的表執行 ALTER TABLE 的 SWITCH PARTITION 命令。allow_partition_switch 為 bit，默認值為 1。  

注意:對表啟用變更數據捕獲時，將生成一個更改表以及一個或兩個查詢函數。更改表充當捕獲進程從事務日志中提取的源表更改的存儲庫。查詢函數則用于從更改表中提取數據。這些函數的名稱按以下方式派生自 capture_instance 參數：

所有更改函數： cdc.fn_cdc_get_all_changes_ < capture_instance >凈更改函數： cdc.fn_cdc_get_net_changes_ < capture_instance >

1.如果源表是數據庫中第一個要啟用變更數據捕獲的表，并且數據庫不存在事務發布，則 sys.sp_cdc_enable_table 還將為數據庫創建捕獲和清理作業。它將 sys.tables 目錄視圖中的 is_tracked_by_cdc 列設置為 1。2.對表啟用變更數據捕獲時，SQL Server 代理不必正在運行。但是，只有當 SQL Server 代理正在運行時，捕獲進程才會處理事務日志并將條目寫入更改表。

USE AdventureWorks2008R2;GOEXEC sys.sp_cdc_enable_table    @source_schema = N'HumanResources'  , @source_name = N'Department'  , @role_name = N'cdc_admin'--增加的角色  , @capture_instance = N'HR_Department'--實例名   , @supports_net_changes = 1  , @index_name = N'AK_Department_Name'   , @captured_column_list = N'Name, GroupName'--跟蹤的字段  , @filegroup_name = N'PRIMARY';GO---查詢表是否啟動跟蹤,值為1標示已啟動SELECT is_tracked_by_cdc  FROM SYS.tables WHERE name='Department'

• 增加的系統表

• 增加的系統視圖

• 增加的系統存儲過程

• 增加的函數

• 增加的用戶與角色

---所有cdc有關的對象SELECT * FROM SYS.all_objects  WHERE name LIKE '%CDC%'OR SCHEMA_ID IN(SELECT SCHEMA_ID FROM SYS.schemas WHERE name='CDC') ORDER BY TYPESELECT * FROM msdb.SYS.objects WHERE name='cdc_jobs'  

---查詢插入數據之前表中的內容USE [AdventureWorks2008R2] SELECT * FROM [HumanResources].[Department]--1.插入一條記錄INSERT INTO [AdventureWorks2008R2].[HumanResources].[Department]([Name],[GroupName],[ModifiedDate])VALUES('TEST','TEST',GETDATE())GO--更新一條記錄UPDATE [HumanResources].[Department]SET GroupName='TEST1'WHERE GroupName='TEST'--增加一個字段ALTER TABLE [HumanResources].[Department]ADD Dcolumn INT

SELECT * FROM cdc.ddl_history---與DDL有關的操作記錄

SELECT * FROM cdc.HR_Department_CT/*__$Operation字段的值:1 = 刪除2 = 插入3 = 更新（舊值）列數據中具有執行更新語句之前的行值。4 = 更新（新值）列數據中具有執行更新語句之后的行值。*/

為每個在更改表中存在行的事務返回一行。該表用于在日志序列號 (LSN) 提交值和提交事務的時間之間建立映射。沒有對應的更改表項的項也可以記錄下來，以便表在變更活動少或者無變更活動期間將 LSN 處理的完成過程記錄下來。SELECT * FROM cdc.lsn_time_mapping

---查詢當前作業配置SELECT * FROM MSDB.dbo.cdc_jobs--或者使用USE AdventureWorks2008R2;GOEXEC sys.sp_cdc_help_jobs;GO1.sys.sp_cdc_add_job在當前數據庫中創建變更數據捕獲清理或捕獲作業    1.創建捕獲作業    USE AdventureWorks2008R2;    GO    EXEC sys.sp_cdc_add_job          @job_type = N'capture';    GO    2.創建清理作業    ---創建清理作業,作業連續運行,更改數據行將在更改表中保留2880分鐘,清除時使用一條語句最多刪除4000條記錄    USE AdventureWorks2008R2;    GO    EXEC sys.sp_cdc_add_job         @job_type = N'cleanup'         ,@start_job=1         ,@retention=2880         ,@threshold =40002.sys.sp_cdc_change_job修改當前數據庫中變更數據捕獲清除或捕獲作業的配置--僅在使用 sp_cdc_stop_job 停止作業并使用 sp_cdc_start_job 重新啟動該作業后，對該作業所做的更改才會生效    1.更改捕獲作業    --將每個循環掃描最多處理的事務數更改為200，為了從日志中提取所有行而要執行的最大掃描循環50次    USE AdventureWorks2008R2;    GO    EXECUTE sys.sp_cdc_change_job         @job_type = N'capture',        @maxtrans = 200,        @maxscans = 50;            GO    2.更改清除作業,將記錄保留時間更改為3440分鐘    USE AdventureWorks2008R2;    GO    EXECUTE sys.sp_cdc_change_job         @job_type = N'cleanup',        @retention = 3440;    GO3.sys.sp_cdc_cleanup_change_table根據指定的 low_water_mark 值從當前數據庫的更改表中刪除行,重置更改表中的最小 __$start_lsn,并刪除小于該值的數據.將同時清除cdc.HR_Department_CT,cdc.lsn_time_mapping表的記錄    USE AdventureWorks2008R2;    GO    EXEC sys.sp_cdc_cleanup_change_table     @capture_instance =N'HR_Department',    @low_water_mark=0x0000037D000000D30008,    @threshold=2000;            SELECT sys.fn_cdc_increment_lsn(sys.fn_cdc_get_max_lsn())4.sys.sp_cdc_drop_job從 msdb 中刪除當前數據庫的變更數據捕獲清除或捕獲作業。--下例刪除 AdventureWorks2008R2 數據庫的清除作業和捕獲作業    USE AdventureWorks2008R2;    GO    EXEC sys.sp_cdc_drop_job @job_type = N'cleanup';    USE AdventureWorks2008R2;    GO    EXEC sys.sp_cdc_drop_job @job_type = N'capture';5.sys.sp_cdc_disable_db    對當前數據庫禁用變更數據捕獲禁用當前對數據庫中的所有表啟用的變更數據捕獲。與變更數據捕獲相關的所有系統對象（如更改表、作業、存儲過程和函數）都將被刪除。sys.databases 目錄視圖中的數據庫條目的 is_cdc_enabled 列設置為 0。如果在禁用變更數據捕獲時為數據庫定義了很多捕獲實例，則長時間運行事務可能導致 sys.sp_cdc_disable_db 的執行失敗。通過在運行 sys.sp_cdc_disable_db 之前使用 sys.sp_cdc_disable_table 禁用單個捕獲實例，可以避免此問題。    USE AdventureWorks2008R2;    GO    EXECUTE sys.sp_cdc_disable_db;    GO6.sys.sp_cdc_disable_table對當前數據庫中指定的源表和捕獲實例禁用變更數據捕獲刪除與指定的源表和捕獲實例相關聯的變更數據捕獲更改表和系統函數。它會刪除任何與來自變更數據捕獲系統表的指定捕獲實例相關聯的行，并將 sys.tables 目錄視圖中的表項的 is_tracked_by_cdc 列設置為 0。---下例對 HumanResources.Department 表禁用了變更數據捕獲    USE AdventureWorks2008R2;    GO    EXEC sys.sp_cdc_disable_table        @source_schema = N'HumanResources'      , @source_name = N'Department'      , @capture_instance = N'HR_Department' ---這里是定義的實例名稱，在一開始創建捕獲的時候創建的，這里也可以制定ALL（禁用表HumanResources.Department的所有捕獲）,7.sys.sp_cdc_enable_db對當前數據庫啟用變更數據捕獲。必須先對數據庫執行此過程，然后才能對該數據庫中的任何表啟用變更數據捕獲。變更數據捕獲可記錄應用到所啟用的表中的插入、更新和刪除活動，同時采用易于使用的關系格式提供變更詳細信息。此操作將為已修改的行捕獲反映了所跟蹤源表列結構的列信息，同時還捕獲將更改應用到目標環境所需的元數據。將創建以全數據庫為作用域的變更數據捕獲對象，包括元數據表和 DDL 觸發器。它還會創建 cdc 架構和 cdc 數據庫用戶，并將 sys.databases 目錄視圖中的數據庫條目的 is_cdc_enabled 列設置為 1。    USE AdventureWorks2008R2;    GO    EXECUTE sys.sp_cdc_enable_db;    GO8.sys.sp_cdc_enable_table--對需要進行跟蹤的表啟動CDC,sys.sp_cdc_enable_table/*為當前數據庫中指定的源表啟用變更數據捕獲。對表啟用變更數據捕獲時，應用于此表的每個數據操縱語言 (DML) 操作的記錄都將寫入事務日志中。變更數據捕獲進程將從日志中檢索此信息，并將其寫入可通過使用一組函數訪問的更改表中。*/sys.sp_cdc_enable_table     [ @source_schema = ] 'source_schema', ---表所屬的架構名    [ @source_name = ] 'source_name' ,----表名    [ @role_name = ] 'role_name'---是用于控制更改數據訪問的數據庫角色的名稱。    [,[ @capture_instance = ] 'capture_instan