2017-06-29

使用 SQL Server CDC (Change Data Capture) 來追蹤資料變更

同事在追查一個 issue,但追一追總是卡在 db 資料面,不知道 db 的資料變化歷程,所以遲遲找不出問題發生原因,所以想要在 table 上加 trigger 來紀錄資料被異動的歷程

查資料過程中,發現一個以前沒留意過的功能 CDC (Change Data Capture),立馬來嘗試看看合不合用,隨手筆記一下


限制條件

只有下列版本 SQL Server 可以使用 CDC - Change Data Capture 功能

  1. SQL Server 2008 以上
  2. Enterprise
  3. Developer
  4. Enterprise Evaluation

0notavailable

如何使用 SQL Server 範本

  1. SQL Server Management Studio (SSMS) 主選單 View --> Template Explorer

    1templateexplorer

  2. Template Browser --> Change Data Capture

    2CDC

為 database 啟用 CDC

  1. Template
    -- ================================
    -- Enable Database for CDC Template
    -- ================================
    USE <Database_Name,sysname,Database_Name>
    GO
    
    EXEC sys.sp_cdc_enable_db
    GO
    
  2. 範例
    -- ================================
    -- Enable Database for CDC Template
    -- ================================
    USE TestCDC
    GO
    
    EXEC sys.sp_cdc_enable_db
    GO
    
  3. 成功啟用後在 System Table 下加入 CDC 相關的系統 table

    3CDCtable

    • cdc.captured_columns

      記錄 CDC 包含的欄位清單

    • cdc.change_tables

      紀錄啟用 CDC 的 table 清單

    • cdc.ddl_history

      紀錄啟用 CDC 後所有 DDL 操作歷程

    • cdc.index_columns

      紀錄啟用 CDC table 的 index 資訊

    • cdc.lsn_time_mapping

      紀錄 LSN 號碼跟時間

確認 database 是否啟用 CDC

  1. Template
    -- ========================================================
    -- Determine Whether a Database is Enabled for CDC Template
    -- ========================================================
    USE <Database_Name,sysname,Database_Name>
    GO
    
    SELECT is_cdc_enabled FROM sys.databases
    WHERE name = N'<Database_Name,sysname,Database_Name>'
    GO
    
  2. 範例
    -- ========================================================
    -- Determine Whether a Database is Enabled for CDC Template
    -- ========================================================
    USE TestCDC
    GO
    
    SELECT is_cdc_enabled FROM sys.databases
    WHERE name = N'TestCDC'
    GO
    

為 table 啟用 CDC

  1. template
    -- ===============================================
    -- Enable a Table for All Changes Queries Template
    -- ===============================================
    USE <Database_Name,sysname,Database_Name>
    GO
    
    EXEC sys.sp_cdc_enable_table
     @source_schema = N'<source_schema,sysname,source_schema>',
     @source_name   = N'<source_name,sysname,source_name>',
     @role_name     = N'<role_name,sysname,role_name>',
     @supports_net_changes = 0
    GO
    
  2. 範例
    -- ===============================================
    -- Enable a Table for All Changes Queries Template
    -- ===============================================
    USE TestCDC
    GO
    
    EXEC sys.sp_cdc_enable_table
     @source_schema = N'dbo',
     @source_name   = N'Users',
     @role_name     = N'CDCRole',
     @supports_net_changes = 0
    GO
    
  3. 成功啟用後在 System Table 下加入 table 對應的 CDC table

    4CDCtable

確認 table 是否啟用 CDC

  1. template
    -- =====================================================
    -- Determine Whether a Table is Enabled for CDC Template
    -- =====================================================
    USE <Database_Name,sysname,Database_Name>
    GO
    
    SELECT is_tracked_by_cdc FROM sys.tables
    WHERE object_id = object_id(N'<source_schema,sysname,source_schema>.<source_name,sysname,source_name>')
    GO
    
  2. 範例
    -- =====================================================
    -- Determine Whether a Table is Enabled for CDC Template
    -- =====================================================
    USE TestCDC
    GO
    
    SELECT is_tracked_by_cdc FROM sys.tables
    WHERE object_id = object_id(N'Users')
    GO
    

變更擷取

5capture

其中的 __$operation 數字意義如下

  • 1 ==> Delete
  • 2 ==> Insert
  • 3 ==> Before Update
  • 4 ==> After Update

停用 table CDC 功能

  1. template
    -- ===============================================
    -- Disable a Capture Instance for a Table Template
    -- ===============================================
    USE <Database_Name,sysname,Database_Name>
    GO
    
    EXEC sys.sp_cdc_disable_table
     @source_schema = N'<source_schema,sysname,source_schema>',
     @source_name   = N'<source_name,sysname,source_name>',
     @capture_instance = N'<capture_instance,sysname,capture_instance>'
    GO
    
  2. 範例
    -- ===============================================
    -- Disable a Capture Instance for a Table Template
    -- ===============================================
    USE TestCDC
    GO
    
    EXEC sys.sp_cdc_disable_table
     @source_schema = N'dbo',
     @source_name   = N'Users',
     @capture_instance = N'dbo_Users'
    GO
    
  3. 停用特定 table CDC 功能後,對應的 CDC table 就會被刪除,這個要特別留意

停用 database CDC 功能

  1. template
    -- =================================
    -- Disable Database for CDC Template
    -- =================================
    USE <Database_Name,sysname,Database_Name>
    GO
    
    EXEC sys.sp_cdc_disable_db
    GO
    
  2. 範例:
    -- =================================
    -- Disable Database for CDC Template
    -- =================================
    USE TestCDC
    GO
    
    EXEC sys.sp_cdc_disable_db
    GO
    
  3. 與停用 table CDC 類似,停用 database CDC 功能也會刪除相關 CDC table

心得

透過幾個簡單設定,馬上就能達到資料備份的功用,SQL Server 真是友善呀

雖然簡單幾個動作就可以達到資料備份,但還沒有真正確認對效能的影響程度前還是不該隨意在 production server 上啟用,加上只有基本欄位還是不夠的,要再找找看有沒有客製空間,否則還是只能用舊方法 - Trigger 進行了,但至少多了個選擇可用

參考資訊

  1. 關於異動資料擷取 (SQL Server)
  2. Introduction to Change Data Capture (CDC) in SQL Server 2008

沒有留言:

張貼留言