簡介

SQL Server 是一款老牌關係型資料庫,自 1988 年由 Microsoft、Sybase 和 Ashton-Tate 三家公司共同推出，不斷疊代更新至今，擁有相當廣泛的用戶群體。

如今，我們提到 SQL Server 通常指 Microsoft SQL Server 2000 之後的版本。

SQL Server 2008 是一個里程碑版本，加入了大量新特性,包括 新的語法、更豐富的類型 以及本文所提及的 CDC 能力，這個能力讓數據從 SQL Server 實時同步到外部更加方便。

本文將介紹 CloudCanal 在新版本中對於 SQL Server 數據同步更進一步的優化和實踐。

SQL Server CDC 長什麼樣？

原始日誌

常見的資料庫往往存在以下兩種日誌

redo 日誌 記錄數據的正向變更，簡單來說，事務的 commit 通常先記錄在這個文件，再返回應用程式成功，可確保數據 持久性
undo 日誌 用於保證事務的 原子性，如執行 rollback 命令即反向執行 undo 日誌中內容以達成數據回滾

一條 DML 語句寫入資料庫流程如下

大部分關係型資料庫中，一個或多個變更會被隱式或顯式包裝成一個事務
事務開始，資料庫引擎定位到數據行所在的 文件位置 並根據已有的數據生成 前鏡像 和 後鏡像
後鏡像 數據記錄到 redo 日誌中，前鏡像 數據記錄到 undo 日誌中
事務提交後，日誌提交位點（檢查點）向前推進，已提交的日誌內容即可能被覆蓋或者釋放

SQL Server redo/undo 日誌採用了 ldf 格式 ,文件循環使用。

ldf 日誌文件由多個 VLF(邏輯日誌) 組合在一起，這些 VLF 首尾相連形成完整的資料庫日誌記錄
ldf 在邏輯日誌末端到達物理日誌文件末端時，新的日誌記錄將回到物理日誌文件開始，複寫舊的數據

ldf 文件即 CDC 所分析的增量日誌文件。

啟用 CDC

在資料庫上執行 exec [console].sys.sp_cdc_enable_db 命令為 console 資料庫啟用 CDC 功能，這個語句實際上會創建兩個作業: cdc.console_capture , cdc.console_cleanup

使用 exec sp_cdc_help_jobs 命令可查看這兩個作業詳細信息。

cdc.console_capture 負責分析 ldf 日誌 並解析 console 資料庫事件,再將其寫入到 CDC 表中間隔 5 秒鐘執行一次掃描，每次掃描 10 輪，每輪掃描最多 500 個事務
cdc.console_cleanup 負責定期清理 CDC 表中較老的數據默認保留 3 天 CDC 日誌數據（4320秒）

開啟 CDC 功能後，SQL Server 資料庫會多出一個名稱為 cdc 的 schema，裡面會多出下列這些表。

change_tables 記錄每一個啟用了 CDC 的源表及其對應的 捕獲表
captured_columns 記錄對應 捕獲表 中每個列的信息
index_columns 記錄源表含有的主鍵信息(如果有)
LSN_time_mapping 記錄每個事務的開始/結束時間及 LSN 位置信息
ddl_history 記錄源表發生的 增/減列 對應的 DDL 信息，除此之外的 DDL 都不會被記錄

有了上述準備動作和信息，即可開始對原始表開啟 change data capture(CDC)，即增量數據捕獲了。

捕獲表變更

有如下源表

SQL複製代碼create table [dbo].[test_table] (
  [id] [bigint] NOT NULL primary key,
  [test] [nchar](10) NULL
)

執行下列命令即可為它啟用 CDC

sql複製代碼exec [console].[sys].[sp_cdc_enable_table]
    @source_schema = [dbo],
    @source_name = [test_table],
    @role_name = NULL,
    @capture_instance = [dbo_test_table], -- 可選項
    @supports_net_changes = 0;

cdc schema 下多出一個名為 dbo_test_table_CT 的表，即 捕獲表

對源表 [dbo].[test_table] 做若干 DML 操作，通常是 5 秒內就可在捕獲表中看到變更記錄
對源表做一些 增/減列 操作,對應的 DDL 會出現在 ddl_history 表中

其他表也可通過類似設置，獲取到相應的增量變更。整個機制看上去相當直觀和簡單。

挑戰是什麼？

難點1：DDL 同步困難

CDC 捕獲表只反饋數據的變化，無 DDL 信息

DDL 需額外獲取即和 DML 的順序關係要額外處理

解決這個問題，需要通過執行以下的 SQL 將 DDL 和 DML 事件混合到一起並保證順序，但是實際使用中會面臨嚴重的性能問題。

sql複製代碼select * from (
        select __$start_lsn lsn,__$operation oper,__$update_mask mask, null ddl ,id data_id,test data_test 
        from [console].[cdc].[dbo_test_table_CT]
        union
        select ddl_lsn lsn, -1 oper,null mask,  ddl_command ddl ,null data_id,null data_test 
        from [console].[cdc].[ddl_history]
) t order by lsn

難點2：無法獲取新增列數據

CDC 捕獲表的結構並不會隨著 DDL 事件的發生而變化，這意味著無法獲取新增列的數據

難點3：資料庫限制

使用 CDC 功能本身也會產生一些硬性的限制，大致可以分為兩類

硬性限制

已經啟用 CDC 捕獲的源表上不能執行 truncate table 語句,執行即報錯
CDC 捕獲表本質上也是一個普通的表，大量訂閱會導致整庫表的數量擴大
依賴 SQL Server 代理,如沒啟動或作業運行失敗，捕獲表中不會有任何新數據寫入
一張表只能創建 2 張對應的 CDC 捕獲表,即無法做超過 2 個以上的增量訂閱
一張表的 CDC 捕獲只能設置啟動和禁止，即不能通過重建 CDC 並指定 LSN 來獲取新數據

軟性限制

CDC 捕獲表中的數據存留時間默認 3 天
在插入或更新超大欄位時默認 CDC 只會處理最大 64KB 個字節的數據

數據內容如果超過這個限制會導致 CDC 捕獲任務報錯並停止工作
受影響的類型有 7 個：text、ntext、varchar(max)、nvarchar(max)、varbinary(max)、xml、image

CloudCanal 的解決方法

CloudCanal SQL Server 增量消費基礎處理模型如下所述，保證單個表的數據變更順序,滿足大部分場景

根據 change_tables 表確定一個工作隊列
確定起始位點，對於捕獲表的增量數據掃描從起始位點開始
並發處理工作隊列上的事件
每個 Worker 會根據起始 LSN 掃描自身要處理的 CDC 捕獲表
每個 Worker 掃描都會維護自身的 LSN 進度

解決難點1：DML/DDL重排序

CDC 捕獲表中的每一條記錄都有一個 LSN 信息，ddl_history 表也有 LSN 信息。因此可以藉助插值的思想將 DDL 事件插入到正常的 DML 事件序列中去，原理如下圖：

對 ddl_history 表進行預查詢，獲取到的 DDL 事件在稍後的處理中會進行位點比對處理
查詢 dbo_test_table_CT 數據捕獲表
處理每一條的捕獲表的數據時檢測 DDL 事件是否可以被插入
形成完整的事件流

解決難點2：反查補充缺失數據

SQL Server CDC 捕獲表最多只能創建 2 張是硬性限制，但剛好能解決這個問題，在 DDL 發生後創建第二個 CDC 捕獲表可以感知到 DDL 對數據的變化

創建第一個 CDC 捕獲表 dbo_test_table_1_CT
在兩次數據插入的中間增加一個新的列
創建第二個 CDC 捕獲表 dbo_test_table_2_CT
在插入一條新數據

通過上圖可看到 dbo_test_table_2_CT 相比 dbo_test_table_1_CT 已經可以感知到新增的列數據

遺憾的是 DDL 發生後到第二個 CDC 捕獲表創建出來之前這中間的數據仍然是缺失的

上面的例子如下圖所示（灰色的 Event 表示事件或者數據有缺損）

以 DDL 發生的 LSN 為分界點

在 DDL 發生之前 dbo_test_table_1_CT 表中的數據是完全可信的
在 DDL 發生之後由於 dbo_test_table_1_CT 表中並沒有新列欄位，因此它的數據是殘缺的，不能完全信任
而 dbo_test_table_2_CT 是由於在 DDL 發生後才被創建出來，因此相比較 dbo_test_table_1_CT 它的數據是缺失的
此外 dbo_test_table_1_CT 和 dbo_test_table_2_CT 之間還存在一個盲區導致這個 INSERT 事件兩個表都不可信

CloudCanal 解決辦法是在此基礎上將兩張表都缺損的位點 反向使用 PK 從源表中補齊 的方式解決這個問題（上圖中深灰色部分）

有一個極端情況是在第二張 CDC 捕獲表創建過程中發生了新的 DDL ,這會導致新創建的捕獲表也不可靠，因此需要重新創建第二個 CDC 捕獲表,並且擴大中間需要反查補齊的數據範圍（下圖中深灰色部分）

CloudCanal 正是基於上述一系列機制才解決了 DDL 事件導致無法獲取增量數據的難題

解決難點3：提供專業優化方案

對於硬性限制，CloudCanal 沒有正面解決的方案，而是後續提供更多樣的方式(如 trigger,定時增量掃描,新版本SQL Server CDC方案 等)進行補充。

而 軟性限制，可通過以下方式優化

通過以下命令中的 retention 參數來設置 CDC 捕獲表中的數據存留時間
sql複製代碼
exec console.sys.sp_cdc_change_job @job_type = 'cleanup', @retention=4320 -- 單位:秒
通過以下命令調整 CDC 處理的最大數據字節
sql複製代碼
exec sp_configure 'show advanced options', 1 ; reconfigure; exec sp_configure 'max text repl size', -1; -- -1 表示不限制 reconfigure;

總結

本文簡單介紹了 SQL Server CDC 技術，然後基於此能力，CloudCanal 是如何實現穩定的增量 DML + DDL 同步，並且解決了其中遇到的難題。

深入淺出 SQL Server CDC 數據同步

簡介