SQL Server 跨服務器訪問數據：OPENROWSET、OPENDATASOURCE、鏈接服務器與同義詞最佳實踐

業務場景

服務器 A 需實時讀寫服務器 B 的數據庫 NZ、AU，避免 Replication 延遲與衝突。

四種方案對比

推薦落地：鏈接服務器 + 同義詞

1. 啟用 Ad Hoc Distributed Queries（一次性）

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

2. 創建鏈接服務器

EXEC sp_addlinkedserver 
    @server = 'SRV_B',              -- 邏輯名
    @srvproduct = '',               -- SQL Server 可空
    @provider = 'SQLNCLI',          -- Native Client Provider
    @datasrc = '192.168.1.100';     -- 目標實例 IP/主機名\實例名
GO

-- 配置安全映射：本地登錄映射到遠端 SQL 認證
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'SRV_B',
    @useself = 'FALSE',
    @locallogin = NULL,             -- 所有本地登錄
    @rmtuser = 'readonly_user',     -- 遠端用戶
    @rmtpassword = 'StrongPwd#2024';
GO

3. 創建同義詞屏蔽四部分命名

-- 數據庫上下文：目標庫
USE MasterDB;
GO

CREATE SYNONYM dbo.Help_Category_NZ FOR SRV_B.NZ.dbo.Help_Category;
CREATE SYNONYM dbo.Help_Category_AU FOR SRV_B.AU.dbo.Help_Category;
GO

4. 業務代碼零感知調用

SELECT * FROM Help_Category_NZ WHERE CategoryName LIKE '%Promo%';
INSERT INTO Help_Category_AU (CategoryName) VALUES ('New Year Sale');

進階配置提示

• 連接池：鏈接服務器複用會話，長連接減少握手開銷；但併發高時需監控遠端會話數。
• 分布式事務：跨服務器寫操作需啟用 MSDTC，並 SET XACT_ABORT ON; BEGIN DISTRIBUTED TRAN。
• 查詢下推：OPENQUERY 強制在遠端執行完整查詢再傳結果，避免大量數據拉回本地過濾：
  SELECT * FROM OPENQUERY(SRV_B, 'SELECT * FROM NZ.dbo.BigTable WHERE CreateDate > ''2024-01-01''')
• 同義詞不支持 DDL：無法 ALTER SYNONYM，需 DROP + CREATE 切換目標（如藍綠部署）。