連結伺服器讓本地 SQL Server 以透明方式存取遠端資料源 (SQL Server、Oracle、PostgreSQL、MySQL、CSV 等)。本文涵蓋 GUI 與 T-SQL 兩種建立方式、查詢語法差異、權限模型與效能陷阱。
LINK_PROD (自訂邏輯名,後續查詢用)。Microsoft OLE DB Provider for SQL Server (SQLOLEDB) / Microsoft OLE DB Provider for Oracle (MSDAORA) / Oracle Provider for OLE DB (OraOLEDB.Oracle - 推薦)。remote_user/Pwd123)。-- 建立連結伺服器
EXEC sp_addlinkedserver
@server = 'LINK_PROD',
@srvproduct = '',
@provider = 'SQLOLEDB',
@datasrc = '192.168.10.50'; -- 或主機名、具名實例: SERVER\INST
-- 建立登入映射 (本地登入 remote_user 對應遠端 login/pwd)
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'LINK_PROD',
@useself = 'FALSE',
@locallogin = NULL, -- NULL 表示所有本地登入
@rmtuser = 'remote_user',
@rmtpassword = 'RemotePwd123';
-- 需先安裝 Oracle Client 並配置 TNSNAMES.ORA
EXEC sp_addlinkedserver
@server = 'LINK_ORA',
@srvproduct = 'Oracle',
@provider = 'OraOLEDB.Oracle', -- 推薦 Oracle 官方 Provider
@datasrc = 'ORCL_TNS'; -- TNS 別名
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'LINK_ORA',
@useself = 'FALSE',
@locallogin = 'sa',
@rmtuser = 'ora_user',
@rmtpassword = 'OraPwd123';
CREATE VIEW v_OraUsers AS SELECT * FROM LINK_ORA..SCOTT.EMP; -- [LinkedServer]..[Schema].[Table] SELECT * FROM v_OraUsers WHERE DEPTNO = 10;
關鍵差異: Oracle 連結伺服器**必須使用雙點號 `..`**,且 Schema/Table **必須大寫** (Oracle 預設大寫存儲)。不支援同義詞查詢。
-- GUI: 右鍵連結伺服器 → 刪除 -- T-SQL: EXEC sp_dropserver 'LINK_PROD', 'droplogins';
| 場景 | 建議 |
|---|---|
| 頻繁跨伺服器 JOIN | 改用複製/同步 (Replication、CDC、ETL) 至本地,避免分散式查詢計畫不穩定 |
| 大量資料拉取 | 使用 OPENQUERY 將過濾下推遠端:SELECT * FROM OPENQUERY(LINK_PROD, 'SELECT * FROM ERP.dbo.Orders WHERE OrderDate > ''2024-01-01''') |
| 分散式交易 | 啟用 MSDTC,但極度謹慎:網路抖動易導致交易不確定狀態,優先考慮最終一致性模式 |
| 權限最小化 | 遠端僅授予 db_datareader 或特定檢視表權限,嚴禁 sysadmin |
| 連線池 | Provider String 設定 Pooling=true;Connection Lifetime=300 |
7399 / 7303:Provider 未註冊/路徑錯 → 確認 OLE DB Provider 已安裝 (32/64 位元匹配)。7314:OLE DB Provider 未正確初始化 → 檢查 TNSNAMES.ORA、防火牆、Oracle Listener。7416:無法存取遠端資料表 → 權限不足或名稱大小寫錯誤 (Oracle)。