SQL Server 到 MySQL 實時數據同步方案：基於連接服務器與觸發器

SQL Server 到 MySQL 實時數據同步方案：基於連接服務器與觸發器

本文介紹一種利用 SQL Server 連接服務器、ODBC 驅動、觸發器與存儲過程實現 SQL Server 到 MySQL 單向實時同步的方案，適用於異構數據庫集成、報表系統數據同步等場景。

架構原理

1. SQL Server 安裝 MySQL ODBC 驅動，配置系統 DSN。
2. 在 SQL Server 創建連接服務器指向 MySQL。
3. 在 SQL Server 創建 Loopback 連接服務器指向自身，防止分佈式事務提升。
4. 在 SQL Server 目標表上創建 INSERT/UPDATE/DELETE 觸發器。
5. 觸發器調用 Loopback 上的存儲過程，通過 OPENQUERY 向 MySQL 執行對應 DML。

詳細實施步驟

1. 安裝 MySQL ODBC 驅動並配置 DSN

• 下載安裝 MySQL Connector/ODBC 5.3（32/64 位匹配 SQL Server 版本）。
• 打開 ODBC 數據源管理器 (64-bit) → 系統 DSN → 添加 → 選擇 MySQL ODBC 5.3 ANSI Driver。
• 配置：
  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. 建立 Loopback 連接服務器（指向 SQL Server 自身）

EXEC sp_addlinkedserver
    @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLNCLI',
    @datasrc = @@SERVERNAME;

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';

作用： 觸發器調用 Loopback 上的存儲過程，避免觸發器直接跨服務器操作導致本地事務升級為分佈式事務（MSDTC），提升穩定性與效能。

5. 在 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)
);

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;

6. 創建存儲過程（部署在 SQL Server Loopback 上）

INSERT 同步過程：

USE suzhou;
GO
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. 創建觸發器（部署在 SQL Server 表上）

INSERT 觸發器：

USE suzhou;
GO
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

測試驗證