SQL Server 跨伺服器資料存取完整指南：OPENROWSET、連結伺服器、同義詞三種方案對比

業務場景

主伺服器 A 需即時查詢伺服器 B 上的資料庫 NZ、AU（即時網站數據），避免 Replication 覆蓋衝突。

方案一：Ad Hoc 分散式查詢（臨時、一次性）

啟用選項

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

OPENDATASOURCE

SELECT * FROM OPENDATASOURCE(
  'SQLOLEDB',
  'Data Source=SQLServerName;User ID=sa;Password=xxx'
).Help.dbo.Help_Category;

OPENROWSET

SELECT * FROM OPENROWSET(
  'SQLOLEDB',
  'SQLServerName'; 'sa'; 'xxx',
  'Help.dbo.Help_Category'
);

用畢關閉

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

缺點：連線字串硬編碼，密碼變更需全域搜尋替換，維護困難。

方案二：連結伺服器（標準、可管理）

建立連結伺服器

EXEC sp_addlinkedserver
  @server = 'XLING',
  @srvproduct = '',
  @provider = 'SQLNCLI',
  @datasrc = 'SQLServerName2';

SSMS 物件總管 → 伺服器物件 → 連結伺服器 可見 XLING。

查詢語法

SELECT * FROM XLING.PricemeHelp.dbo.Help_Category;

缺點：四段式命名 Server.Database.Schema.Object 仍嫌冗長。

方案三：同義詞（最佳實踐：簡潔、解耦、可維護）

建立同義詞

CREATE SYNONYM dbo.Help_Category_NZ FOR XLING.PricemeHelp.dbo.Help_Category;
CREATE SYNONYM dbo.Help_Category_AU FOR XLING.PricemeHelpAU.dbo.Help_Category;

使用同義詞

SELECT * FROM Help_Category_NZ;
SELECT * FROM Help_Category_AU;

方案對比總結

最佳實踐建議

1. 建立連結伺服器統一管理連線與認證
2. 為每個遠端物件建立同義詞，應用程式僅依賴同義詞名稱
3. 遠端結構變更時，僅需 DROP/CREATE SYNONYM，應用程式碼零修改
4. 啟用 Ad Hoc Distributed Queries 後務必關閉，減少攻擊面