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: install Oracle Transparent Gateway for MS SQL Server (not explained here) Configure the gateway initialization parameter file Configure the listener Configure the Oracle database tnsnames.ora Creating DB link from Oracle database to MS SQL Server In my environment there are 3 servers involved: Oracle database server - name: orclsrv01, with Oracle database installed name: orcldb Microsoft SQL server - name: msqlsrv01, with MS SQL server installed name: msqldb 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