使用LINKED SERVER整合64位元的SQL Server與Oracle資料庫

jeffluo

SQL Server 2016 Development Topic IV- Linked Server of X64 SQL Sever connect to Oracle

SQL Server 2016 開發主題IV –使用LINKED SERVER整合64位元的SQL Server與Oracle資料庫

文章園地 : Super SQL Server  https://www.facebook.com/groups/222546864546011/files/

Version: SQL Server 任何版本

Level: 200

By Lewis Yang (MVP of SQL Server, MCT, OCM) @ TW, lewisdba@gmail.com

[/url]

Keyword: linked server, ODAC

DMV:      

安裝好的SQL Server 2016如果要連接到其他異質資料庫的時候，可以使用LINKED SERVER(連結的伺服器)的方式，

搭配驅動程式與設定，就可以直接從SQL Server執行TSQL指令，進行遠端異質資料庫的資料處理。

本範例將使用LINKED SERVER (連結的伺服器) 查詢Linux上面的Oracle資料庫。

首先需要設定以下的幾個步驟。

第一就是在該SQL Server 2016的機器上面，安裝Oracle提供的Windows應用程式使用的驅動程式。

此部分驅動程式可以從[url=http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html]http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html 取得

它就是Oracle Data Access Components (ODAC)，如下圖

之後將此下載檔案解壓縮到指定暫時目錄，譬如說C:\sft\ODAC

接下來就要執行[install.bat]安裝，將Oracle OLEDB驅動程式安裝到C:\oracle目錄，這樣一來就可以將Oracle OLEDB順利安裝到Windows作業系統。

完成後就要設定PATH參數，加入【c:\oracle\odac64;c:\oracle\odac64\bin;】到以下的環境變數。

完成啟動後，就可以再一次打開SQL Server Managerment Studio檢查是否有【Oracle Provier for OLE DB】驅動程式，該部分在尚未安裝ODAC之前，是無法看到此選項。

當要開始使用LINKED SERVER連結到Oracle資料庫之前，需要在SQL Server階層，額外設定以下的步驟，讓SQL Server內部程序可以使用並且接受參數設定。

EXEC master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1

EXEC master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1

最後開始進入SQL SERVER階層驗證之前，請先啟動Windows讓整個設定生效。

現在就開始準備設定LINKED SERVER，來連結到後端的Oracle的伺服器，當然需要請ORACLE DBA給予以下的資訊。

1.      ORACLE連線的帳號

2.      ORACLE帳號的對應密碼

3.      ORACLE的IP與連接PORT(預設1521可以省略)

4.      ORACLE的Global Name，譬如以下的Oracle的資料庫名稱為ORCL.EXAMPLE.COM

ORACLE IP: 192.168.56.103 ORACLE PORT: 1521(可以省略)，否則要標明如下 //192.168.56.103:1522/orcl.example.com ORACLE GLOBAL NAME: orcl.example.com

--使用T-SQL建立LINKED SERVER與設定連線到ORACLE的帳號與密碼 USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'MYORCL', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'//192.168.56.103/orcl.example.com' GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'use remote collation', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'MYORCL', @optname=N'remote proc transaction promotion', @optvalue=N'true' GO USE [master] GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MYORCL', @locallogin = NULL , @useself = N'False', @rmtuser = N'hr', @rmtpassword = N'oracle_4U' GO

當完成上述的LINKED SERVER的設定後，可以很簡易驗證該LINKED SERVER是否設定正確，就是點選該【MYORCL】。

若是設定正常就會看到以下的結果。

如果設定錯誤，譬如是PORT非1521或是ORACLE的帳號密碼錯誤，就會看到以下的錯誤訊息。

這樣一來可以從後端ORACLE資料庫是否啟動，LISTENER是否啟動，帳號密碼是否正確。

最後就可以使用T-SQL搭配LINKED SERVER查詢ORACLE資料庫，更可以將兩邊的資料進行合併查詢。

--使用T-SQL建立LINKED SERVER與設定連線到ORACLE的帳號與密碼 --第一種使用EXEC AT --第二種使用OPENQUERY exec ('select sysdate from dual') at [MYORCL] GO select top(2) o.FIRST_NAME,o.LAST_NAME,o.EMAIL+'@example.com',s.BusinessEntityID,s.FirstName,s.LastName,s.[EmailAddress] from openquery([MYORCL],'select * from hr.employees')  o inner join [AdventureWorks2016].[HumanResources].[vEmployee] s on o.EMPLOYEE_ID=s.BusinessEntityID order by newid() --結果

結論

多種異質資料庫的整合一直都是程式開發人員要解決的問題。

從SQL SERVER層面，可以藉由LINKED SERVER的方式，直接從後端資料庫茶整合起來，確實可以省下很多麻煩的事情。

本文章使用的是整合ORACLE資料庫，過程中需要額外安裝ODAC才有機會讓X64位元SQL Server有Oracle Provider for OLE DB的驅動程式。

另外有關連接ORACLE資料庫時候，需要特別留意ORACLE的LISTENER PORT與GLOBAL NAME，如此一來可以省下很多錯誤的摸索時間。

Lewis Yang @ TW 2016/07/17