【實戰】SQL Server 到 MySQL 實時同步完整部署腳本

【實戰】SQL Server 到 MySQL 實時同步完整部署腳本

本文提供可直接在 SQL Server Management Studio (SSMS) 執行的完整 T-SQL 腳本，實現基於連接服務器、Loopback、觸發器與存儲過程的 SQL Server 到 MySQL 單向實時同步。腳本已按執行順序組織，含詳細註釋。

完整部署腳本

/* ========== 1. 安裝 MySQL ODBC 5.3 ANSI Driver 並配置系統 DSN 名為 'jt' ==========
   步驟：控制面板 -> 管理工具 -> ODBC 數據源 (64位) -> 系統 DSN -> 添加
   Data Source Name: jt
   Server: 192.168.5.188
   User: root
   Password: ******
   Database: suzhou
   Port: 3306
   ================================================================================ */

/* ========== 2. 創建指向 MySQL 的連接服務器 ========== */
EXEC sp_addlinkedserver
    @server = 'jt',
    @srvproduct = 'mysql',
    @provider = 'MSDASQL',
    @datasrc = NULL,
    @location = NULL,
    @provstr = 'DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=192.168.5.188;DATABASE=suzhou;UID=root;PORT=3306;',
    @catalog = NULL;

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'jt',
    @useself = 'false',
    @rmtuser = 'root',
    @rmtpassword = 'password';

/* 測試連通性 */
SELECT * FROM OPENQUERY(jt, 'SELECT * FROM sz;');

/* ========== 3. 配置連接服務器選項（關鍵：防止分佈式事務提升） ========== */
EXEC master.dbo.sp_serveroption @server = N'jt', @optname = N'rpc out', @optvalue = N'TRUE';
EXEC master.dbo.sp_serveroption @server = N'jt', @optname = N'remote proc transaction promotion', @optvalue = N'false';

/* ========== 4. 在 SQL Server 與 MySQL 分別創建目標庫表 ========== */
/* --- SQL Server 端 --- */
CREATE DATABASE suzhou;
GO
USE suzhou;
GO
CREATE TABLE sz (
    id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    orderno CHAR(20) NOT NULL,
    ordertime DATETIME NOT NULL DEFAULT GETDATE(),
    remark VARCHAR(200)
);
GO

/* --- MySQL 端 (在 MySQL 客戶端執行) --- */
CREATE DATABASE suzhou;
USE suzhou;
CREATE TABLE sz (
    id INT(11) NOT NULL,
    orderno CHAR(20) NOT NULL,
    ordertime DATETIME(6) NOT NULL,
    remark VARCHAR(200),
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/* ========== 5. 建立 Loopback 連接服務器（指向 SQL Server 自身） ========== */
EXEC sp_addlinkedserver
    @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLNCLI',
    @datasrc = @@SERVERNAME;
GO

EXEC master.dbo.sp_serveroption @server = N'loopback', @optname = N'rpc out', @optvalue = N'TRUE';
EXEC master.dbo.sp_serveroption @server = N'loopback', @optname = N'remote proc transaction promotion', @optvalue = N'false';
GO

/* ========== 6. 在 Loopback 上創建同步存儲過程 ========== */
USE suzhou;
GO

/* --- INSERT 同步過程 --- */
CREATE PROCEDURE sp_insert
    @id INT,
    @orderno CHAR(20),
    @ordertime DATETIME,
    @remark VARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT OPENQUERY(jt, 'SELECT id, orderno, ordertime, remark FROM sz')
    (id, orderno, ordertime, remark)
    VALUES (@id, @orderno, @ordertime, @remark);
END;
GO

/* --- UPDATE 同步過程 --- */
CREATE PROCEDURE sp_update
    @orderno CHAR(20),
    @remark VARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE OPENQUERY(jt, 'SELECT orderno, remark FROM sz')
    SET remark = @remark
    WHERE orderno = @orderno;
END;
GO

/* --- DELETE 同步過程 --- */
CREATE PROCEDURE sp_delete
    @orderno CHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    DELETE OPENQUERY(jt, 'SELECT orderno FROM sz')
    WHERE orderno = @orderno;
END;
GO

/* ========== 7. 在源表 sz 上創建觸發器 ========== */
/* --- INSERT 觸發器 --- */
ALTER TRIGGER tr_insert_sz ON suzhou.dbo.sz
FOR INSERT
AS
BEGIN
    DECLARE @id INT, @orderno CHAR(20), @ordertime DATETIME, @remark VARCHAR(200);
    SELECT @id = id, @orderno = orderno, @ordertime = ordertime, @remark = remark FROM inserted;
    EXEC loopback.suzhou.dbo.sp_insert @id, @orderno, @ordertime, @remark;
END;
GO

/* --- UPDATE 觸發器 --- */
CREATE TRIGGER tr_update_sz ON suzhou.dbo.sz
FOR UPDATE
AS
BEGIN
    DECLARE @orderno CHAR(20), @remark VARCHAR(200);
    SELECT @orderno = orderno, @remark = remark FROM inserted;
    EXEC loopback.suzhou.dbo.sp_update @orderno, @remark;
END;
GO

/* --- DELETE 觸發器 --- */
CREATE TRIGGER tr_delete_sz ON suzhou.dbo.sz
FOR DELETE
AS
BEGIN
    DECLARE @orderno CHAR(20);
    SELECT @orderno = orderno FROM deleted;
    EXEC loopback.suzhou.dbo.sp_delete @orderno;
END;
GO

/* ========== 8. 測試驗證 ========== */
USE suzhou;
GO

/* 測試 INSERT */
INSERT INTO sz (orderno, remark) VALUES ('a001', 'test insert');
/* 在 MySQL 查詢: SELECT * FROM sz; */

/* 測試 UPDATE */
UPDATE sz SET remark = 'ocpyang' WHERE orderno = 'a001';

/* 測試 DELETE */
DELETE FROM sz WHERE orderno = 'a001';

關鍵點提示

1. ODBC 驅動位數必須與 SQL Server 實例位數一致（均 64 位）。
2. remote proc transaction promotion = false 至關重要，否則觸發器內跨服務器操作會觸發 MSDTC 分佈式事務，導致性能下降甚至失敗。
3. Loopback 連接服務器使觸發器調用「本地」存儲過程，由存儲過程再跨服務器寫入 MySQL，有效隔離事務範圍。
4. MySQL 表 id 無 AUTO_INCREMENT，同步時需顯式傳遞 SQL Server 生成的身份值，保證主鍵一致。
5. 字符集：MySQL 建議 utf8mb4，SQL Server 列排序規則需兼容。

適用場景與局限

• ✅ 適合：中小數據量、強一致性要求、異構庫單向同步、報表庫更新。
• ❌ 不適合：高併發寫入（觸發器增加延遲）、雙向同步、大批量歷史數據遷移、跨公網不穩定網絡。
• 🔄 替代方案：CDC 工具、消息隊列異步同步、專業 ETL 平台。

總結

本腳本提供了開箱即用的 SQL Server 到 MySQL 實時同步實現，核心在於 Linked Server + Loopback 隔離事務 + Trigger 驅動 + OPENQUERY 執行遠端 DML。按序執行並根據實際 IP、賬號、庫表調整參數即可部署上線。