Wednesday, April 16, 2008

Oracle Transparent Gateway for MS SQL Server - Part II

Following my last post about Oracle Transparent Gateways which was theoretical post, this post will be more practical.

In this post I'll show an example of configuring Oracle Transparent Gateway for MS SQL Server.
The goal is to make it possible to retrieve data from MS SQL server database by querying from Oracle database.

The steps are:

  1. install Oracle Transparent Gateway for MS SQL Server (not explained here)
  2. Configure the gateway initialization parameter file
  3. Configure the listener
  4. Configure the Oracle database tnsnames.ora
  5. Creating DB link from Oracle database to MS SQL Server

In my environment there are 3 servers involved:

  1. Oracle database server - name: orclsrv01, with Oracle database installed name: orcldb
  2. Microsoft SQL server - name: msqlsrv01, with MS SQL server installed name: msqldb
  3. Oracle Gateway server - name: oragwsrv01, with Oracle Transparent Gateway installed.


Configure the gateway initialization parameter file

After Oracle Transparent Gateway for MS SQL server installed on ORAGWSRV01 server, a new default initialization file is created at: $ORACLE_HOME\tg4msql\admin\inittg4msql.ora.

$ORACLE_HOME - is the directory where Transparent Gateway installed.
inittg4msql.ora - is the default initialization parameter file, as tg4msql represents the Gateway SID.
If you choose to not use the default SID (or when you have more than one MS Sql server to connect to), the initialization parameter file should be named as follow: init[GW_SID].ora

Edit the inittg4msql.ora file and specify the MS Sql Server connection details as follow:
HS_FDS_CONNECT_INFO=server_name.database_name

in our case:
HS_FDS_CONNECT_INFO=msqlsrv01.msqldb

Alternatively you can set it as follow:
HS_FDS_CONNECT_INFO="SERVER=msqlsrv01; DATABASE=msqldb"

Configure the listener

In order to make the listener work with the gateway we should configure it.
The listener have to be on the same server like the Gateway.
We should edit the listener.ora within the Oracle home on which the Gateway installed.

The listener.ora must have at least 2 entries:

  • The address to listen on
  • the Gateway to start

On ORAGWSRV01 server at $ORACLE_HOME\network\admin edit the listener.ora as follow:

LISTENER=
               (ADDRESS=
                   (PROTOCOL=TCP)
                   (HOST=host_name)
                   (PORT=port_number))

SID_LIST_LISTENER=
      (SID_LIST=
           (SID_DESC=
                 (SID_NAME=gateway_sid)
                 (ORACLE_HOME=oracle_home)
                 (PROGRAM=tg4msql)
           ) 
      )

Where:
host_name - is the server name on which the gateway is installed
port_number - is the listener port
gateway_sid - is the gateway SID (default is tg4msql)
oracle_home - is the Oracle directory on which the gateway is installed

In our case:

LISTENER=
              (ADDRESS= 
              (PROTOCOL=TCP)
              (HOST=oragwsrv01)
              (PORT=1521))

SID_LIST_LISTENER=
      (SID_LIST=
          (SID_DESC= 
               (SID_NAME=tg4msql)
               (ORACLE_HOME=C:\oracle\product\10.2.0)
               (PROGRAM=tg4msql)
          )
      )

After finishing the Gateway configuration, restart the listener (lsnrctl stop; lsnrctl start).

Configure the Oracle database tnsnames.ora

Now, when the Gateway configured, we need to configure the Oracle database server to communicate with the Gateway via Oracle Net services.
On ORCLSRV01 server at $TNS_ADMIN ($ORACLE_HOME/network/admin) edit the tnsnames.ora file and add the Gateway's connect descriptor as follow:

connect_descriptor=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=host_name)
         (PORT=port_number)
      )
      (CONNECT_DATA=
         (SID=gateway_sid))
      (HS=OK))

Where:

connect_descriptor - you can specify a name which will be used later when we create a db link
host_name - is the server on which the Gateway installed
port_number - is the port used by the listener that listening for the Gateway
gateway_sid - is the gateway SID (default tg4msql)
HS=OK - indicates this connect descriptor is using heterogeneous Services

In our case:

mytg4msql=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=oragwsrv01)
         (PORT=1521)
      )
      (CONNECT_DATA=
         (SID=tg4msql))
      (HS=OK))

Creating DB link from Oracle database to MS SQL Server

Now, when all configurations were done, we only need to create a Database Link from Oracle to the Gateway.
Connect to orcldb database and create a db link as follow:

create database link ORCLDB2MSQLDB
  connect to [MSQLUSER] identified by [MSQLPWD]
  using 'mytg4msql';

Where MSQLUSER & MSQLPWD are the user name and password to connect to the MS SQL server.

Test it...

At this stage we can get data from MS SQL Server via Oracle Database as if we query from Oracle database.

For example, if we have a RESERVATIONS table in MSQLDB, we can retrieve RESERVATIONS rows from ORCLDB by issuing the following SQL statement:

select *
from reservations@ORCLDB2MSQLDB;

For more information about Transparent Gateway for Microsoft SQL Server:
Oracle® Transparent Gateway for Microsoft SQL Server Administrator's Guide 10g Release 2 (10.2) for Microsoft Windows (32-bit)

For more information about other Transparent Gateways for other Database systems:
Oracle Database Documentation Library 10g release 2 (10.2) - Information Integration

You are welcome to leave a comment.

Aviad

10 comments:

Anonymous said...

And what about a sql server with two different instances?
How can I call one of these instances whith HS_FDS_CONNECT_INFO??

Aviad said...

I didn’t test it but try the following:

HS_FDS_CONNECT_INFO="[SERVER NAME]\\[INSTANCE NAME].[DATABASE]"
or
HS_FDS_CONNECT_INFO="SERVER=[SERVER NAME]\\[INSTANCE NAME];DATABASE=[DATABASE]"

Aviad

Jan Matusiewicz said...

Very useful article, but I am somehow confused.
Must the gateway be located on the same server as the SQL Server instance?
Firstly you write about 3 server instances.
Then you write "The listener have to be on the same server like the Gateway", which is oragwsrv01.
But later you assume that the listener is on msqlsrv01: "On MSQLSRV01 server at $ORACLE_HOME\network\admin edit the listener.ora"

Aviad said...

Jan, you are right.
I had a typo - the listener have to be on the same server like the Gateway.
So I changed the original post from:
"On MSQLSRV01 server at $ORACLE_HOME\network\admin edit the listener.ora"
to
"On ORAGWSRV01 server at $ORACLE_HOME\network\admin edit the listener.ora"

Thanks !

Aviad

Jeem said...

Great article! Just want I need! Just a quick question.

Can the Oracle database and the transparent gateway to be on the same server?

Aviad said...

Hi Jeem,

You can install Oracle Transparent Gateway in either of the following configurations:
1. On the same computer as an existing Oracle Server but in a different Oracle home.
2. On a system with no Oracle Server.
3. On the same computer as the Oracle Server and in the same Oracle home directory. Note that in this case, the Oracle Server and the gateway must be at the same release level.

[From http://youngcow.net/doc/oracle10g/gateways.102/b19083/overview.htm]

Aviad

Steven said...

Hi Aviad,

What version of SQL Server are you using? I successfully connect a gateway to a SQL Server 2000 database, but failed to connect a SQL Server 2005 database. The error message is:

[Transparent gateway for MSSQL][Microsoft][ODBC SQL Server][SQL Server]Login failed.

The user I used to connect is even a db owner.

Thanks.

Steven

Aviad said...

Hi Steven,

What version of TG4MSQL are you using?
Microsoft SQL*Server 2005 is certified with Oracle version 10.1 and 10.2 of the Transparent Gateway for SQL*Server (TG4MSQL).
If you're using a version lower than the above it might be your problem.

Aviad

Anonymous said...

Hi Aviad,

Great Article!!

One thing I am little confused with is "have we considered Oracle Database server running on Windows Platform?"
What if Oracle Database Server is running on Linux can we connect to sql servers using the method you have described?

Regards,
ChetanZ

Aviad said...

Hi ChetanZ,

You can have your dababase server running on Linux or Windows and use the method described here.

Regards,
Aviad