SQL Server 分散式查詢與連結伺服器完整實作指南

應用場景

跨多台 SQL Server 實例查詢、匯入資料，常見於微服務、資料倉庫 ETL、跨庫報表等架構。

兩種核心方案

方案一：連結伺服器 — 適合頻繁存取、大量資料

建立連結伺服器

-- 建立連結伺服器
EXEC sp_addlinkedserver 
    @server = 'srv_lnk',           -- 連結伺服器別名
    @srvproduct = '',              -- 產品名稱（SQL Server 可留空）
    @provider = 'SQLOLEDB',        -- OLE DB 提供者
    @datasrc = '遠端伺服器名或 IP'; -- 遠端位址

-- 設定登入對應
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'srv_lnk',
    @useself = 'false',            -- 不使用當前登入上下文
    @locallogin = NULL,            -- 所有本機登入
    @rmtuser = '用戶名',            -- 遠端登入帳號
    @rmtpassword = '密碼';          -- 遠端登入密碼

查詢與匯入範例

-- 查詢
SELECT * FROM srv_lnk.資料庫名.dbo.表名;

-- 匯入（建立新表）
SELECT * INTO 本地表 FROM srv_lnk.資料庫名.dbo.表名;

移除連結伺服器

EXEC sp_dropserver 'srv_lnk', 'droplogins';

企業管理器圖形化設定

1. SQL 實例 → 安全性 → 連結伺服器 → 右鍵新增
2. 一般頁：名稱、選「其他資料來源」→ SQLOLEDB、資料來源填 IP
3. 安全性頁：選「使用此安全性內容登入」→ 輸入遠端帳密

方案二：OPENROWSET — 適合臨時、一次性存取

查詢範例

SELECT * FROM OPENROWSET(
    'SQLOLEDB',
    'SQL伺服器名';'用戶名';'密碼',
    '資料庫名.dbo.表名'
);

匯入範例

SELECT * INTO 本地表 
FROM OPENROWSET(
    'SQLOLEDB',
    'SQL伺服器名';'用戶名';'密碼',
    '資料庫名.dbo.表名'
);

常見問題與解決

問題 1：ANSI_NULLS / ANSI_WARNINGS 錯誤

錯誤：異類查詢要求為連接設置 ANSI_NULLS 和 ANSI_WARNINGS 選項...

解決： 查詢前開啟選項

SET ANSI_NULLS ON;
SET ANSI_WARNINGS ON;
SELECT * FROM srv_lnk.資料庫名.dbo.表名;

問題 2：MSDTC 服務不可用

錯誤：某機器 MSDTC 服務不可用

解決： **雙向**啟用 MSDTC 服務（預設未啟用）

• 控制台 → 管理工具 → 服務 → Distributed Transaction Coordinator → 啟動
• 必要時設定 MSDTC 安全性：網路 DTC 存取、允許遠端用戶端/管理

效能與安全建議

• 🔒 最小權限：連結伺服器登入帳號僅授予必要資料庫的 SELECT/INSERT 權限
• ⚡ 避免 SELECT *：明確指定欄位，減少網路傳輸
• 📦 批次處理：大量資料用 `INSERT INTO ... SELECT FROM OPENROWSET` 而非逐列處理
• 🔍 監控：定期檢查連結伺服器連線狀態與查詢效能
• 🧹 清理：不再使用的連結伺服器及時移除

總結

連結伺服器適合長期、高頻跨伺服器存取，管理集中；OPENROWSET適合臨時、Ad-hoc 查詢。兩者核心差異在於連線管理與安全性模型。務必正確設定 ANSI 選項與 MSDTC，否則將遭遇最常見的兩大阻礙。