萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 跨資料庫同步資料:連結伺服器與存儲過程實作全紀錄

業務場景

  • 兩台物理伺服器:IP1: 192.168.0.3(來源)、IP2: 192.168.0.10(目標)
  • 需定期將 IP1.dbPM.dbo.v_custom 視圖資料同步至 IP2.dbCRM.dbo.t_custom 表
  • 邏輯:不存在則 INSERT,存在則 UPDATE
  • 透過 SQL Server Agent Job 排程自動執行

核心存儲過程

CREATE PROCEDURE [dbo].[p_pm_項目平台客戶批量同步到報銷平台]
    @destserver nvarchar(50),   -- 目標伺服器 IP
    @sourceserver nvarchar(50)  -- 來源伺服器 IP
AS
BEGIN
    SET NOCOUNT ON;

    -- 動態建立連結伺服器
    IF NOT EXISTS (SELECT * FROM sys.servers WHERE server_id!=0 AND data_source=@destserver)
        EXEC sp_addlinkedserver @server=@destserver;
    IF NOT EXISTS (SELECT * FROM sys.servers WHERE server_id!=0 AND data_source=@sourceserver)
        EXEC sp_addlinkedserver @server=@sourceserver;

    BEGIN TRY
        SET XACT_ABORT ON;
        BEGIN TRANSACTION;

        -- 1. 插入新資料
        INSERT INTO [192.168.0.10].[dbCRM].[dbo].[t_custom] 
            (客戶ID, 客戶名稱, 客戶簡稱, 輸入碼, 查詢碼, 地址, 錄入登錄名, 錄入時間, 
             修改登錄名, 修改時間, 審批狀態ID, 審批狀態名稱, 是否審批結束, 審批操作時間, 項目管理客商編碼, 序號)
        SELECT 
            A.客戶ID, A.客戶名稱, A.客戶簡稱, 
            dbo.fn_pm_GetPy(A.客戶名稱),
            A.客戶編號+','+A.客戶名稱+','+dbo.fn_pm_GetPy(A.客戶名稱)+','+A.客戶簡稱+','+dbo.fn_pm_GetPy(A.客戶簡稱),
            A.地址, 'admin', GETDATE(), NULL, NULL,
            'D65F87A8-79C8-4D1C-812D-AE4591E056A8', '已審批', 1, A.審批操作時間, A.項目管理客商編碼, 0
        FROM [dbPM].[dbo].[v_custom] A
        WHERE A.客戶ID NOT IN (SELECT 客戶ID FROM [192.168.0.10].[dbCRM].[dbo].[t_custom]);

        -- 2. 更新既有資料
        UPDATE A SET
            A.客戶名稱 = B.客戶名稱,
            A.客戶簡稱 = B.客戶簡稱,
            A.輸入碼 = dbo.fn_pm_GetPy(B.客戶名稱),
            A.查詢碼 = B.客戶編號+','+B.客戶名稱+','+dbo.fn_pm_GetPy(B.客戶名稱)+','+B.客戶簡稱+','+dbo.fn_pm_GetPy(B.客戶簡稱),
            A.地址 = B.地址,
            A.修改登錄名 = 'admin',
            A.修改時間 = GETDATE(),
            A.項目管理客商編碼 = B.項目管理客商編碼
        FROM [192.168.0.10].[dbCRM].[dbo].[t_custom] A
        INNER JOIN [dbPM].[dbo].[v_custom] B ON A.客戶ID = B.客戶ID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS errornumber, ERROR_MESSAGE() AS errormsg, ERROR_LINE() AS errorline;
        ROLLBACK TRANSACTION;
    END CATCH
END

分散式交易 (MSDTC) 設定關鍵

若出現 訊息 7391:OLE DB 提供者 "SQLNCLI" 無法啟動分散式交易,必須在兩台伺服器皆完成:

  1. 服務:Distributed Transaction Coordinator 設為自動啟動並執行中
  2. 元件服務 → 我的電腦 → Distributed Transaction Coordinator → 本地 DTC → 內容 → 安全性:
  • 勾選 網路 DTC 存取
  • 勾選 允許遠端用戶端
  • 勾選 允許輸入/輸出
  • 勾選 無需驗證
  • 啟用 XA 交易

效能優化建議

  • 來源視圖 v_custom 加上適當索引(客戶ID)
  • 目標表 t_custom 客戶ID 建立唯一索引/主鍵
  • 大量資料考慮分批次處理(如每批 5000 筆),避免交易日誌暴漲
  • 監控 sys.dm_tran_active_transactions 觀察長時間交易

結語

連結伺服器 + 動態建立 + 交易包裝,構成 SQL Server 跨實例同步的標準模式。關鍵在於 MSDTC 正確配置與冪等的 INSERT/UPDATE 邏輯。生產環境建議加入同步狀態記錄表、失敗重試機制與告警通知。

copyright © 萬盛學電腦網 all rights reserved