萬盛學電腦網

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

SQL Server 跨伺服器分散式查詢:Linked Server 建立與查詢實戰

建立連結伺服器

-- 1. 建立 Linked Server (SQL Server 目標)
EXEC sp_addlinkedserver 
  @server = 'SRV_LNK',           -- 本地別名
  @srvproduct = '',              -- 空字串表示 SQL Server
  @provider = 'SQLNCLI',         -- 或 'MSOLEDBSQL' (新版驅動)
  @datasrc = '192.168.1.200';    -- 遠端伺服器 IP/主機名

-- 2. 設定登入對應 (SQL 驗證)
EXEC sp_addlinkedsrvlogin 
  @rmtsrvname = 'SRV_LNK',
  @useself = 'FALSE',            -- 不使用當前登入
  @locallogin = NULL,            -- 所有本地登入對應同一遠端帳號
  @rmtuser = 'remote_user',      -- 遠端登入帳號
  @rmtpassword = 'RemotePass123';-- 遠端密碼
GO

分散式查詢語法 (四部份命名)

-- 格式: [LinkedServer].[Database].[Schema].[Table]
SELECT A.ListCode
FROM SRV_LNK.BarcodeDB.dbo.ME_ListCode A
INNER JOIN LocalDB.dbo.IM_BarLend B ON A.ListCode = B.ListCode;

刪除連結伺服器

EXEC sp_dropserver 'SRV_LNK', 'droplogins';
GO

進階配置選項

-- 允許 RPC (預存程序遠端執行)
EXEC sp_serveroption 'SRV_LNK', 'rpc', 'true';
EXEC sp_serveroption 'SRV_LNK', 'rpc out', 'true';

-- 設定查詢逾時 (秒)
EXEC sp_serveroption 'SRV_LNK', 'query timeout', '300';

-- 設定連線逾時 (秒)
EXEC sp_serveroption 'SRV_LNK', 'connect timeout', '30';

-- 啟用資料排序規則相容
EXEC sp_serveroption 'SRV_LNK', 'collation compatible', 'true';

OPENQUERY / OPENROWSET 替代方案

OPENQUERY (推薦:遠端執行、回傳結果)

SELECT * 
FROM OPENQUERY(SRV_LNK, 'SELECT ListCode, CreatedDate FROM BarcodeDB.dbo.ME_ListCode WHERE Status = 1');
  • 查詢在**遠端執行**,僅傳回結果集,減少網路傳輸
  • 可使用遠端原生 T-SQL 語法

OPENROWSET (臨時連線,無需永久 Linked Server)

SELECT * 
FROM OPENROWSET('MSOLEDBSQL', 
  'Server=192.168.1.200;UID=remote_user;PWD=RemotePass123;Database=BarcodeDB',
  'SELECT ListCode FROM dbo.ME_ListCode WHERE Status = 1');

效能與最佳實踐

建議理由
優先用 OPENQUERY 推算下推在遠端完成過濾/連接,僅傳回必要資料
避免在本地 JOIN 遠端大表會將遠端全表拉回本地,網路與記憶體壓力極大
遠端建立適當索引支援 WHERE/JOIN/ORDER BY 欄位
限制結果集大小加 TOP、分頁、篩選條件
監控 wait_type = OLEDB偵測分散式查詢瓶頸
考慮複寫/ETL/資料虛擬化替代高頻、大量跨庫查詢應架構層面解決

安全性注意

  • **最小權限原則**:遠端帳號僅授 SELECT、EXECUTE 必需物件
  • **加密連線**:確保遠端伺服器啟用 Force Encryption 或憑證
  • **稽核**:啟用 Linked Server 登入稽核

總結

Linked Server 實現 SQL Server 跨伺服器查詢,**OPENQUERY 為效能首選**。生產環境需嚴格控制權限、加密傳輸、監控效能,並評估架構層面替代方案 (複寫、API、資料湖)。

copyright © 萬盛學電腦網 all rights reserved