簡(jiǎn)介

SQL Server 是一款老牌關(guān)系型數(shù)據(jù)庫(kù),自 1988 年由 Microsoft、Sybase 和 Ashton-Tate 三家公司共同推出，不斷迭代更新至今，擁有相當(dāng)廣泛的用戶群體。

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

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

本文將介紹 CloudCanal 在新版本中對(duì)于 SQL Server 數(shù)據(jù)同步更進(jìn)一步的優(yōu)化和實(shí)踐。

SQL Server CDC 長(zhǎng)什么樣？

原始日志

常見(jiàn)的數(shù)據(jù)庫(kù)往往存在以下兩種日志

• redo 日志
  • 記錄數(shù)據(jù)的正向變更，簡(jiǎn)單來(lái)說(shuō)，事務(wù)的 commit 通常先記錄在這個(gè)文件，再返回應(yīng)用程序成功，可確保數(shù)據(jù) 持久性
• undo 日志
  • 用于保證事務(wù)的 原子性，如執(zhí)行 rollback 命令即反向執(zhí)行 undo 日志中內(nèi)容以達(dá)成數(shù)據(jù)回滾

一條 DML 語(yǔ)句寫入數(shù)據(jù)庫(kù)流程如下

• 大部分關(guān)系型數(shù)據(jù)庫(kù)中，一個(gè)或多個(gè)變更會(huì)被隱式或顯式包裝成一個(gè)事務(wù)
• 事務(wù)開(kāi)始，數(shù)據(jù)庫(kù)引擎定位到數(shù)據(jù)行所在的 文件位置 并根據(jù)已有的數(shù)據(jù)生成 前鏡像 和 后鏡像
• 后鏡像 數(shù)據(jù)記錄到 redo 日志中，前鏡像 數(shù)據(jù)記錄到 undo 日志中
• 事務(wù)提交后，日志提交位點(diǎn)（檢查點(diǎn)）向前推進(jìn)，已提交的日志內(nèi)容即可能被覆蓋或者釋放

SQL Server redo/undo 日志采用了 ldf 格式 ,文件循環(huán)使用。

• ldf 日志文件由多個(gè) VLF(邏輯日志) 組合在一起，這些 VLF 首尾相連形成完整的數(shù)據(jù)庫(kù)日志記錄
• ldf 在邏輯日志末端到達(dá)物理日志文件末端時(shí)，新的日志記錄將回到物理日志文件開(kāi)始，復(fù)寫舊的數(shù)據(jù)

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

啟用 CDC

在數(shù)據(jù)庫(kù)上執(zhí)行 exec [console].sys.sp_cdc_enable_db 命令為 console 數(shù)據(jù)庫(kù)啟用 CDC 功能，這個(gè)語(yǔ)句實(shí)際上會(huì)創(chuàng)建兩個(gè)作業(yè): cdc.console_capture , cdc.console_cleanup

使用 exec sp_cdc_help_jobs 命令可查看這兩個(gè)作業(yè)詳細(xì)信息。

• cdc.console_capture
  • 負(fù)責(zé)分析 ldf 日志 并解析 console 數(shù)據(jù)庫(kù)事件,再將其寫入到 CDC 表中
  • 間隔 5 秒鐘執(zhí)行一次掃描，每次掃描 10 輪，每輪掃描最多 500 個(gè)事務(wù)
• cdc.console_cleanup
  • 負(fù)責(zé)定期清理 CDC 表中較老的數(shù)據(jù)
  • 默認(rèn)保留 3 天 CDC 日志數(shù)據(jù)（4320秒）

開(kāi)啟 CDC 功能后，SQL Server 數(shù)據(jù)庫(kù)會(huì)多出一個(gè)名稱為 cdc 的 schema，里面會(huì)多出下列這些表。

• change_tables
  • 記錄每一個(gè)啟用了 CDC 的 源表 及其對(duì)應(yīng)的 捕獲表
• captured_columns
  • 記錄對(duì)應(yīng) 捕獲表 中每個(gè)列的信息
• index_columns
  • 記錄 源表 含有的主鍵信息(如果有)
• lsn_time_mapping
  • 記錄每個(gè)事務(wù)的開(kāi)始/結(jié)束時(shí)間及 LSN 位置信息
• ddl_history
  • 記錄源表發(fā)生的 增/減列 對(duì)應(yīng)的 DDL 信息，除此之外的 DDL 都不會(huì)被記錄

有了上述準(zhǔn)備動(dòng)作和信息，即可開(kāi)始對(duì)原始表開(kāi)啟 change data capture(CDC)，即增量數(shù)據(jù)捕獲了。

捕獲表變更

有如下 源表

create table [dbo].[test_table] (
  [id] [bigint] NOT NULL primary key,
  [test] [nchar](10) NULL
)

執(zhí)行下列命令即可為它啟用 CDC

exec [console].[sys].[sp_cdc_enable_table]
    @source_schema = [dbo],
    @source_name = [test_table],
    @role_name = NULL,
    @capture_instance = [dbo_test_table], -- 可選項(xiàng)
    @supports_net_changes = 0;

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

• 對(duì) 源表 [dbo].[test_table] 做若干 DML 操作，通常是 5 秒內(nèi)就可在捕獲表中看到變更記錄
• 對(duì) 源表 做一些 增/減 列 操作,對(duì)應(yīng)的 DDL 會(huì)出現(xiàn)在 ddl_history 表中

其他表也可通過(guò)類似設(shè)置，獲取到相應(yīng)的增量變更。整個(gè)機(jī)制看上去相當(dāng)直觀和簡(jiǎn)單。

挑戰(zhàn)是什么？

難點(diǎn)1：DDL 同步困難

CDC 捕獲表只反饋數(shù)據(jù)的變化，無(wú) DDL 信息

DDL 需額外獲取即和 DML 的順序關(guān)系要額外處理

解決這個(gè)問(wèn)題，需要通過(guò)執(zhí)行以下的 SQL 將 DDL 和 DML 事件混合到一起并保證順序，但是實(shí)際使用中會(huì)面臨嚴(yán)重的性能問(wèn)題。

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

難點(diǎn)2：無(wú)法獲取新增列數(shù)據(jù)

CDC 捕獲表的結(jié)構(gòu)并不會(huì)隨著 DDL 事件的發(fā)生而變化，這意味著無(wú)法獲取新增列的數(shù)據(jù)

難點(diǎn)3：數(shù)據(jù)庫(kù)限制

使用 CDC 功能本身也會(huì)產(chǎn)生一些硬性的限制，大致可以分為兩類

硬性限制

• 已經(jīng)啟用 CDC 捕獲的源表上不能執(zhí)行 truncate table 語(yǔ)句,執(zhí)行即報(bào)錯(cuò)

• CDC 捕獲表本質(zhì)上也是一個(gè)普通的表，大量訂閱會(huì)導(dǎo)致整庫(kù)表的數(shù)量擴(kuò)大

• 依賴 SQL Server 代理,如沒(méi)啟動(dòng)或作業(yè)運(yùn)行失敗，捕獲表中不會(huì)有任何新數(shù)據(jù)寫入

• 一張表只能創(chuàng)建 2 張對(duì)應(yīng)的 CDC 捕獲表,即無(wú)法做超過(guò) 2 個(gè)以上的增量訂閱

• 一張表的 CDC 捕獲只能設(shè)置啟動(dòng)和禁止，即不能通過(guò)重建 CDC 并指定 LSN 來(lái)獲取新數(shù)據(jù)

軟性限制

• CDC 捕獲表中的數(shù)據(jù)存留時(shí)間默認(rèn) 3 天

• 在插入或更新超大字段時(shí)默認(rèn) CDC 只會(huì)處理最大 64KB 個(gè)字節(jié)的數(shù)據(jù)

  • 數(shù)據(jù)內(nèi)容如果超過(guò)這個(gè)限制會(huì)導(dǎo)致 CDC 捕獲任務(wù)報(bào)錯(cuò)并停止工作
  • 受影響的類型有 7 個(gè)：text、ntext、varchar(max)、nvarchar(max)、varbinary(max)、xml、image

CloudCanal 的解決方法

CloudCanal SQL Server 增量消費(fèi)基礎(chǔ)處理模型如下所述，保證單個(gè)表的數(shù)據(jù)變更順序,滿足大部分場(chǎng)景

• 根據(jù) change_tables 表確定一個(gè)工作隊(duì)列
• 確定起始位點(diǎn)，對(duì)于捕獲表的增量數(shù)據(jù)掃描從起始位點(diǎn)開(kāi)始
• 并發(fā)處理工作隊(duì)列上的事件
• 每個(gè) Worker 會(huì)根據(jù)起始 LSN 掃描自身要處理的 CDC 捕獲表
• 每個(gè) Worker 掃描都會(huì)維護(hù)自身的 LSN 進(jìn)度

解決難點(diǎn)1：DML/DDL重排序

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

1. 對(duì) ddl_history 表進(jìn)行預(yù)查詢，獲取到的 DDL 事件在稍后的處理中會(huì)進(jìn)行位點(diǎn)比對(duì)處理
2. 查詢 dbo_test_table_CT 數(shù)據(jù)捕獲表
3. 處理每一條的捕獲表的數(shù)據(jù)時(shí)檢測(cè) DDL 事件是否可以被插入
4. 形成完整的事件流

解決難點(diǎn)2：反查補(bǔ)充缺失數(shù)據(jù)

SQL Server CDC 捕獲表最多只能創(chuàng)建 2 張是硬性限制，但剛好能解決這個(gè)問(wèn)題，在 DDL 發(fā)生后創(chuàng)建第二個(gè) CDC 捕獲表可以感知到 DDL 對(duì)數(shù)據(jù)的變化

1. 創(chuàng)建第一個(gè) CDC 捕獲表 dbo_test_table_1_CT
2. 在兩次數(shù)據(jù)插入的中間增加一個(gè)新的列
3. 創(chuàng)建第二個(gè) CDC 捕獲表 dbo_test_table_2_CT
4. 在插入一條新數(shù)據(jù)

通過(guò)上圖可看到 dbo_test_table_2_CT 相比 dbo_test_table_1_CT 已經(jīng)可以感知到新增的列數(shù)據(jù)

遺憾的是 DDL 發(fā)生后到第二個(gè) CDC 捕獲表創(chuàng)建出來(lái)之前這中間的數(shù)據(jù)仍然是缺失的

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

以 DDL 發(fā)生的 LSN 為分界點(diǎn)

• 在 DDL 發(fā)生之前 dbo_test_table_1_CT 表中的數(shù)據(jù)是完全可信的
• 在 DDL 發(fā)生之后由于 dbo_test_table_1_CT 表中并沒(méi)有新列字段，因此它的數(shù)據(jù)是殘缺的，不能完全信任
• 而 dbo_test_table_2_CT 是由于在 DDL 發(fā)生后才被創(chuàng)建出來(lái)，因此相比較 dbo_test_table_1_CT 它的數(shù)據(jù)是缺失的
• 此外 dbo_test_table_1_CT 和 dbo_test_table_2_CT 之間還存在一個(gè)盲區(qū)導(dǎo)致這個(gè) INSERT 事件兩個(gè)表都不可信

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

有一個(gè)極端情況是在第二張 CDC 捕獲表創(chuàng)建過(guò)程中發(fā)生了新的 DDL ,這會(huì)導(dǎo)致新創(chuàng)建的捕獲表也不可靠，因此需要重新創(chuàng)建第二個(gè) CDC 捕獲表,并且擴(kuò)大中間需要反查補(bǔ)齊的數(shù)據(jù)范圍（下圖中深灰色部分）

CloudCanal 正是基于上述一系列機(jī)制才解決了 DDL 事件導(dǎo)致無(wú)法獲取增量數(shù)據(jù)的難題

解決難點(diǎn)3：提供專業(yè)優(yōu)化方案

對(duì)于硬性限制，CloudCanal 沒(méi)有正面解決的方案，而是后續(xù)提供更多樣的方式(如 trigger,定時(shí)增量掃描,新版本SQL Server CDC方案 等)進(jìn)行補(bǔ)充。

而 軟性限制，可通過(guò)以下方式優(yōu)化

• 通過(guò)以下命令中的 retention 參數(shù)來(lái)設(shè)置 CDC 捕獲表中的數(shù)據(jù)存留時(shí)間

  exec console.sys.sp_cdc_change_job 
      @job_type = 'cleanup',
      @retention=4320 -- 單位:秒
  

• 通過(guò)以下命令調(diào)整 CDC 處理的最大數(shù)據(jù)字節(jié)

  exec sp_configure 'show advanced options', 1 ;   
  reconfigure;
  exec sp_configure 'max text repl size', -1; -- -1 表示不限制
  reconfigure;
  

總結(jié)

本文簡(jiǎn)單介紹了 SQL Server CDC 技術(shù)，然后基于此能力，CloudCanal 是如何實(shí)現(xiàn)穩(wěn)定的增量 DML + DDL 同步， 并且解決了其中遇到的難題。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-532019.html

到了這里，關(guān)于深入淺出 SQL Server CDC 數(shù)據(jù)同步的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！