Thursday, January 26, 2012

DB link from Oracle to SQL Server

We had a need for a DB link from an Oracle database to a SQL Server database. I initially feared that this might be a huge task, but it turned out to be quite a easy setup. Thanks Oracle for making it almost seamless!

There are basically 4 steps involved 

1.) Install & configure the Gateway

The Oracle Gateway can be found in the Oracle Database 11g Release 2 Enterprise Edition software. Installation is straight forward. You basically have to make 2 choices during installation. What Gateway you want to install (SQL Server in our case) and which ORACLE_HOME you want to use. You can create a separate Gateway home or just use the current ORACLE_HOME, which is what we did.

After installation you will find a new directory in ORACLE_HOME called dg4msql. Configure the Gateway by creating a agent init.ora file in $ORACLE_HOME/dg4msql/admin. The name of the file is important, as the name you use will be used in your listener as well. We kept it to the default initdg4msql.ora. The information you need to supply in the agent init.ora file are SQL Server Database Server Host Name, SQL Server Database Server Port number and SQL Server Database. The information all goes into one parameter called HS_FDS_CONNECT_INFO.

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

# HS init parameters
# HS_FDS_CONNECT_INFO=[server_name]:port//mssql_db
# alternate connect format is hostname/serverinstance/databasename

2.) Listener & names lookup
We used port 1524 to setup the gateway

    (ADDRESS = (PROTOCOL = tcp)(HOST = = 1521))
    (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1524))

    (SID_DESC =
      (SID_NAME = dg4msql)
      (ORACLE_HOME = /opt/apps/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = dg4msql)

And Local Naming

    (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1524))
      (SID = dg4msql)
    (HS = OK)

HS=OK - indicates this connect descriptor is using heterogeneous Service.

3.) Database Link
Once the SQL Server team gave us a username and password we could just create a DB link


4.) Data dictionary translation support
Oracle has also supplied a script in $ORACLE_HOME/ dg4msql/admin/ called dg4msql_cvw.sql that must be run on the SQL Server side. The script creates views on SQL Server for usage by the Oracle Data Dictionary. 4 views called ALL_IND_COLUMNS, USER_IND_COLUMNS, ALL_CONS_COLUMNS, and USER_CONS_COLUMNS will be created on the SQL Server db making it easier for someone familiar with Oracle Data Dictionary views to query the SQL Server database.

No comments: