萬盛學電腦網

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

SQL Server 存取遠端資料庫完整指南:Linked Server、OPENROWSET、OPENQUERY、OPENDATASOURCE

前置作業:啟用 Ad Hoc Distributed Queries

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
-- 用畢立即關閉,降低攻擊面
EXEC sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;

四種存取方式比較

方式適用場景需建立物件效能/安全性
Linked Server頻繁、固定跨伺服器查詢是 (永久)最佳化工具可用遠端統計資訊,支援分散式交易
OPENQUERY複雜遠端邏輯、下推運算需 Linked Server在遠端執行完整查詢,減少網傳資料量
OPENROWSET臨時、一次性存取否即時連線,無法使用遠端統計,適合簡單查詢
OPENDATASOURCE臨時、一次性,連線字串彈性高否同 OPENROWSET,語法更接近連線字串

Linked Server 建立與使用

-- 建立
EXEC sp_addlinkedserver @server = N'ITSV', @srvproduct = N'', @provider = N'SQLNCLI', @datasrc = N'RemoteIP';
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'ITSV', @useself = N'FALSE', @locallogin = NULL, @rmtuser = N'user', @rmtpassword = N'pwd';

-- 查詢
SELECT * FROM ITSV.DatabaseName.dbo.TableName;

-- 匯入
SELECT * INTO LocalTable FROM ITSV.DatabaseName.dbo.TableName;

-- 刪除
EXEC sp_dropserver 'ITSV', 'droplogins';

OPENROWSET 範例集錦

-- OLEDB (SQLNCLI 推薦 2005+)
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=RemoteIP;UID=user;PWD=pwd;Database=DB', 'SELECT * FROM dbo.Table');

-- 信任連線
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=RemoteIP;Trusted_Connection=yes;', 'SELECT * FROM dbo.Table');

-- 匯入建表
SELECT * INTO LocalTable FROM OPENROWSET('SQLNCLI', 'Server=RemoteIP;UID=user;PWD=pwd;', 'SELECT * FROM DB.dbo.Table');

-- 寫入遠端
INSERT OPENROWSET('SQLNCLI', 'Server=RemoteIP;UID=user;PWD=pwd;', 'SELECT Col1,Col2 FROM DB.dbo.Table')
SELECT Col1, Col2 FROM LocalTable;

-- 更新遠端
UPDATE r SET r.ColA = l.ColA
FROM OPENROWSET('SQLNCLI', 'Server=RemoteIP;UID=user;PWD=pwd;', 'SELECT ColA,PK FROM DB.dbo.Table') r
JOIN LocalTable l ON r.PK = l.PK;

OPENQUERY 範例(需先有 Linked Server)

SELECT * FROM OPENQUERY(ITSV, 'SELECT * FROM DB.dbo.Table WHERE Status=1');

INSERT OPENQUERY(ITSV, 'SELECT Col1,Col2 FROM DB.dbo.Table')
SELECT Col1, Col2 FROM LocalTable;

OPENDATASOURCE 範例

SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteIP;User ID=user;Password=pwd').DB.dbo.Table;

INSERT OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteIP;User ID=user;Password=pwd').DB.dbo.Table
SELECT * FROM LocalTable;

安全與效能最佳實踐

  1. 最小權限:遠端登入帳號僅授予必要資料庫的 SELECT/INSERT/UPDATE。
  2. 優先 OPENQUERY:複雜篩選、聯結在遠端完成,僅傳回結果集。
  3. 避免 SELECT *:明確列出欄位,減少網路傳輸與鎖定範圍。
  4. 批次作業:大量資料移動用 SSIS/BCP/備份還原,勿用 T-SQL 循環。
  5. 防火牆/VPN:僅允許受信任 IP 存取 1433 埠。

常見錯誤排查

  • SQL Server 阻止了對組件 'Ad Hoc Distributed Queries' 的存取 → 未啟用選項。
  • 登入失敗 → 檢查遠端 SQL 驗證模式、防火牆、帳號密碼、SPN。
  • MSDTC 交易失敗 → 分散式交易需啟用 MSDTC 並配置網路存取。
copyright © 萬盛學電腦網 all rights reserved