簡(jiǎn)述
SQL Server 是一個(gè)值得信賴的老牌數(shù)據(jù)庫(kù)系統(tǒng)，自從 1988 年由 Microsoft俺榆、Sybase 和 Ashton-Tate 三家公司共同推出之后就一直不斷迭代更新。而如今我們提到 SQL Server 通常是指 Microsoft 從 SQL Server 2000 之后的版本惭聂。至今 SQL Server 家族已經(jīng)非常繁茂涵蓋了 云上（Azure SQL Server）蒲肋、IoT 設(shè)備（邊緣 SQL Server）、以及經(jīng)典版本（本地 SQL Server）巧颈。
實(shí)現(xiàn) SQL Server 作為源端的實(shí)時(shí)數(shù)據(jù)同步畦木，一般都會(huì)用到它的 CDC 功能，這個(gè)功能是從 2008 版本才開始支持砸泛。因此本文主要也是基于 SQL Server 2008 版本介紹如何使用 CloudCanal 快速構(gòu)建一條穩(wěn)定高效運(yùn)行的 SQL Server 到 MySQL 數(shù)據(jù)同步鏈路十籍。
技術(shù)點(diǎn)
基于 SQL Server 的 CDC
SQL Server 將用戶的每一個(gè)數(shù)據(jù)操作都記錄在后綴為 ldf 日志文件中蛆封。這些日志會(huì)保存在 ldf 文件中。當(dāng)數(shù)據(jù)庫(kù)啟用 CDC 能力后勾栗，SQL Server 代理上會(huì)生成一個(gè)專門分析ldf文件的作業(yè)惨篱，再將具體的表啟用 CDC, 則該作業(yè)開始持續(xù)分析文件中的變更事件到指定的表中。
作業(yè)執(zhí)行用到 SQL Server 代理围俘，該組件如果處于非啟動(dòng)狀態(tài)砸讳，則生成任何可消費(fèi)的變更數(shù)據(jù)。通常界牡，我們可以在 Windows 對(duì)象資源管理器中查看是否已經(jīng)開啟了 SQL Server 代理簿寂。
由于 SQL Server 執(zhí)行作業(yè)時(shí)無(wú)法設(shè)置起始位置，因此對(duì)于一個(gè)表的變更記錄我們最早只能追溯到表啟用 CDC 的那個(gè)時(shí)間點(diǎn)宿亡。具體的起始位點(diǎn)可以在 “cdc.change_tables” 表中查詢得到常遂。
還需要注意的另外一個(gè)細(xì)節(jié)是 CDC 表也是一張普通的表它和用戶共享同一個(gè)數(shù)據(jù)空間。為了防止 CDC 表數(shù)據(jù)無(wú)限膨脹 SQL Server 會(huì)每天定時(shí)執(zhí)行清理作業(yè)挽荠，清理過(guò)期的數(shù)據(jù)（具體時(shí)間視數(shù)據(jù)庫(kù)配置而定）克胳。
SQL Server -> MySQL 的數(shù)據(jù)類型支持
CloudCanal 從 2021 年開始支持 SQL Server 同步后就不斷地豐富它的對(duì)端數(shù)據(jù)源，支持 SQL Server 到 MySQL 是一個(gè)非常重要的同步鏈路圈匆。
目前 CloudCanal 已經(jīng)可以支持的類型和映射關(guān)系如下：
| SQL Server 類型 | MySQL 類型 | 備注 |
|---|---|---|
| BIT | BIT | |
| DECIMAL | DECIMAL | |
| NUMERIC | DECIMAL | |
| SMALLINT | SMALLINT | |
| TINYINT | TINYINT | 映射為 tinyint unsigned |
| INT | INT | |
| BIGINT | BIGINT | |
| SMALLMONEY | FLOAT | |
| MONEY | FLOAT | |
| FLOAT | FLOAT | |
| REAL | DOUBLE | |
| DATE | DATE | |
| DATETIMEOFFSET | DATETIME | 由于 MySQL 類型限制漠另，會(huì)丟棄時(shí)區(qū)信息同時(shí)最多保留 6 位精度 |
| DATETIME2 | DATETIME | 由于 MySQL 類型限制，會(huì)保留最多 6 位精度 |
| SMALLDATETIME | DATETIME | |
| DATETIME | DATETIME | 由于 MySQL 類型限制跃赚，會(huì)保留最多 6 位精度 |
| TIME | TIME | 由于 MySQL 類型限制酗钞，會(huì)保留最多 6 位精度 |
| CHAR | CHAR | |
| VARCHAR | VARCHAR | 源端 SQL Server 如果為 VARCHAR(MAX)，則按照 TEXT 來(lái)處理 |
| TEXT | TEXT | |
| NCHAR | CHAR | |
| NVARCHAR | VARCHAR | 源端 SQL Server 如果為 NVARCHAR(MAX)来累，則按照 NTEXT 來(lái)處理 |
| NTEXT | TEXT | |
| BINARY | BINARY | |
| VARBINARY | VARBINARY | 源端 SQL Server 如果為 VARBINARY(MAX)砚作，則按照 IMAGE 來(lái)處理 |
| IMAGE | BLOB | |
| TIMESTAMP | BIGINT | 會(huì)映射為 bigint unsigned |
| ROWVERSION | BIGINT | 會(huì)映射為 bigint unsigned |
| HIERARCHYID | -- | 暫不支持 |
| UNIQUEIDENTIFIER | VARCHAR(36) | |
| SQL_VARIANT | -- | 暫不支持 |
| XML | TEXT | |
| GEOMETRY | -- | 暫不支持 |
| GEOGRAPHY | -- | 暫不支持 |
| SYSNAME | VARCHAR(128) |
操作示例
前置條件
- 登陸 CloudCanal SaaS版，使用參見(jiàn)快速上手文檔
- 準(zhǔn)備一個(gè) SQL Server 數(shù)據(jù)庫(kù)嘹锁，和 MySQL 實(shí)例（本例分別使用自建 SQL Server 2008 和 MySQL 8.0）
- 登錄 CloudCanal 平臺(tái) 葫录，添加 SQL Server 和 MySQL
- 創(chuàng)建一條 SQL Server -> MySQL 鏈路作為增量數(shù)據(jù)來(lái)源
任務(wù)創(chuàng)建
- 任務(wù)管理-> 任務(wù)創(chuàng)建
- 測(cè)試鏈接并選擇 源 和 目標(biāo) 數(shù)據(jù)庫(kù)
- 點(diǎn)擊下一步
- 選擇 數(shù)據(jù)同步，并勾選 全量數(shù)據(jù)初始化领猾，其他選項(xiàng)默認(rèn)
- 此時(shí)如果 SQL Server 上數(shù)據(jù)庫(kù)還沒(méi)有啟用 CDC 功能米同，則會(huì)在點(diǎn)擊下一步的時(shí)候提示如何啟用 CDC。只要按照提示的參考語(yǔ)句執(zhí)行即可摔竿。
- 選擇需要遷移同步的表和列
- 確認(rèn)創(chuàng)建任務(wù)
- 任務(wù)自動(dòng)做結(jié)構(gòu)遷移面粮、全量遷移、增量同步
校驗(yàn)數(shù)據(jù)
- 程序造數(shù)據(jù)继低， SQL Server -> MySQL熬苍，在源端以 1:1:1 的比例隨機(jī)執(zhí)行Insert、Update、Delete三種類型語(yǔ)句柴底。使用20個(gè)線程并發(fā)寫入變更婿脸。
image.png - 任務(wù)正常運(yùn)行一段時(shí)間后，停止造數(shù)據(jù)
- 點(diǎn)擊 SQLServer -> MySQL 任務(wù)詳情柄驻，功能列表 -> 創(chuàng)建相似任務(wù)狐树，在創(chuàng)建任務(wù)的第二步選擇數(shù)據(jù)校驗(yàn)
- 數(shù)據(jù)校驗(yàn) OK
- 下面這個(gè)是校驗(yàn)結(jié)果。如果我們對(duì)端和源端一旦出現(xiàn)數(shù)據(jù)不一致就會(huì)像下面這樣非常醒目的提示給用戶鸿脓，有多少數(shù)據(jù)不一致抑钟，有多少數(shù)據(jù)丟失。
常見(jiàn)問(wèn)題
支持什么版本的 SQL Server 和 MySQL 野哭？
- 目前源端 SQL Server 2008 及以上版本皆可使用 CloudCanal 進(jìn)行遷移同步（推薦使用 SQL Server 2016 或 SQL Server 2008）
- 對(duì)端 MySQL 支持 5.6在塔、5.7、8.0 版本虐拓，也可以選用 阿里云 RDS for MySQL 對(duì)應(yīng)的版本心俗，或者其它云服務(wù)商的 MySQL 版本
數(shù)據(jù)不同步了都有哪些情況傲武？
- SQL Server CDC 需要依賴 SQL Server 代理蓉驹，首先要確定 SQL Server 代理服務(wù)是否啟動(dòng)
- 表在啟動(dòng) CDC 的時(shí)候會(huì)確定要捕獲的列清單，此時(shí)如果修改列的類型可能會(huì)導(dǎo)致 CDC 中斷揪利。目前解決辦法只能重建任務(wù)态兴。
- 增/減 同一個(gè)列名的列，對(duì)一個(gè)列刪除后在增加疟位。雖然 CDC 表中字段依然存在但是也會(huì)導(dǎo)致整個(gè) CDC 中斷瞻润。
什么情況下會(huì)影響穩(wěn)定的數(shù)據(jù)同步？
- 如果任務(wù)在同步期間出現(xiàn)了異常導(dǎo)致任務(wù)延遲甜刻。這時(shí)候需要格外注意绍撞，如果過(guò)長(zhǎng)時(shí)間的延遲，即便是修復(fù)了延遲的問(wèn)題（比如對(duì)端數(shù)據(jù)庫(kù)長(zhǎng)時(shí)間出現(xiàn)不可用）在后續(xù)數(shù)據(jù)同步上也可能存在丟失數(shù)據(jù)的風(fēng)險(xiǎn)得院。
- SQL Server 為了防止 CDC 表數(shù)據(jù)無(wú)限膨脹 SQL Server 會(huì)每天定時(shí)執(zhí)行清理作業(yè)傻铣，清理超過(guò) 3天的數(shù)據(jù)。
- 為了增加延遲的容忍度可以執(zhí)行這條 SQL 來(lái)增加 CDC 數(shù)據(jù)的保存時(shí)間祥绞，代價(jià)是這些數(shù)據(jù)需要存放到數(shù)據(jù)庫(kù)表中非洲，如果每日數(shù)據(jù)變更很多對(duì)磁盤開銷會(huì)有額外的要求。
- execute sys.sp_cdc_change_job @job_type = n'cleanup', @retention = 4320
- msdb.dbo.cdc_jobs 表中保存了具體 捕獲任務(wù)的數(shù)據(jù)保存時(shí)間蜕径。
總結(jié)
本文簡(jiǎn)單介紹了如何使用 CloudCanal 進(jìn)行 SQL Server -> MySQL 數(shù)據(jù)遷移同步两踏。各位讀者朋友，如果你覺(jué)得還不錯(cuò)兜喻，請(qǐng)點(diǎn)贊梦染、評(píng)論加轉(zhuǎn)發(fā)吧。
