Cheap DB link from Oracle on AIX to MS SQL Server

Database links from and to different database vendors are very common in today's complex environments.

I had a task to make a database link from Oracle running on AIX to MS SQL Server. This can easily be achievable with ODBC for AIX, but driver for MS SQL Server on AIX was not open-source or free at that time. As you can expect, the budget for this task was very low with tendency towards 0,00 currency :).
This may have changed those days, but I didn't explore that. Please leave comments if you know more.

ODBC driver for MS SQL Server for Windows is free of charge as well as Oracle XE database for Windows, so I got the idea was to make a database gateway which would accept Oracle connections and "forward" it to MS SQL Server. In our case we took a virtual machine with Windows 2008 Server license which we already had unused in our license pool.

Shortage of this solution is that you must have additional Windows OS, adding license cost and cost for managing additional server. If you wish, you can put the whole Oracle XE stuff on server which runs SQL Server RDBMS, that way avoiding additional server in the middle. In our case, multiple SQL Servers were involved in story so it was easier to maintain and configure all in one place.

To implement connectivity from Oracle on AIX to MS SQL Server, follow the steps:

Server which runs MS SQL Server
We need to connect to a database, so create login and corresponding database user. Let's name it "orahs" (ORAcle Heterogeneous Services) and assign to this user necessary rights on needed database(s).

Database Gateway server (DBGW)
Create a VPS with any Windows OS supported (look at section "2.1.1 System Requirements") by Oracle XE.
Name that machine/hostname DBGW (DataBase GateWay) or any other name you find suitable.
Install Oracle XE with all default options, in our case that was Oracle Database Express Edition 11g Release 2, 32 bit version. For this purpose we only need the listener service, so you can minimize all your datafiles or even compress them with Windows NTFS compress option.

Open "services.msc" and put Startup type to "manual" or "disabled" for all new services created by XE, except the listener service which should remain "automatic".
Start ODBC Data Source Administrator and create ODBC Data Source which points to your MS SQL Server. Use user we created on MS SQL Server (orahs) and remember ODBC Data Source name, as we'll need it in the next step. For this example we will use ODBC DSN "OURSQLSRV".

Now it is time to open the listener.ora file, so enter in the "Run...":

notepad %ORACLE_HOME%/network/admin/listener.ora

Listener.ora should look something like this:

SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC = (SID_NAME = OURSQLSRV)(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)(PROGRAM = dg4odbc))
    )
LISTENER=
    (DESCRIPTION_LIST=
        (DESCRIPTION=
            (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))
            (ADDRESS=(PROTOCOL=TCP)(HOST=DBGW)(PORT=1521))
        )
    )
DEFAULT_SERVICE_LISTENER=(XE)

Now we need to add a HS file, so type from "Run...":

notepad %ORACLE_HOME%/hs/admin/initOURSQLSRV.ora

Minimal HS file should look like:

HS_FDS_CONNECT_INFO=OURSQLSRV
HS_FDS_TRACE_LEVEL=OFF

Restart the listener:

net stop OracleXETNSListener
net start OracleXETNSListener

Oracle database server
In tnsnames.ora we should add entry to our XE listener/gateway:

vi $ORACLE_HOME/network/admin/tnsnames.ora

Add new TNS entry:

SQL_OURSQLSRV = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbgw)(PORT=1521))(CONNECT_DATA=(SID=OURSQLSRV))(HS=OK))

...and check if TNSPING works:

tnsping SQL_OURSQLSRV

Now you can connect to the database and create database link:

create public database link sql_oursqlsrv connect to "orahs" identified by "<password>" using 'SQL_OURSQLSRV';

You can check if we have access with some test-query (take note that remote table name is quoted with double quotes):

select * from "Some_SQLServer"@sql_oursqlsrv;

This was working very stable and without interruptions for long time. The only interruptions were when OS patching of "database gateway" OS was performed or we had a connectivity issues, but that was so rare that it can be ignored.

Menu