Oracle Database Gateway for Microsoft SQL Server

The gateway system identifier (SID) is an alphanumeric character string that identifies a gateway instance. You need one gateway instance, and therefore one gateway SID, for each SQL Server database you are accessing. The SID is used as part of the file name for the initialization parameter file. The default SID is dg4msql.

You can define a gateway SID, but using the default of dg4msql is easier because you do not need to change the initialization parameter file name. However, if you want to access two SQL Server databases, you need two gateway SIDs, one for each instance of the gateway. If you have only one SQL Server database and want to access it sometimes with one set of gateway parameter settings, and other times with different gateway parameter settings, then you will need multiple gateway SIDs for the single SQL Server database.

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
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

Make Sure Microsoft SQL Server Database details are correct in initdg4msql.ora file.

cat $ORACLE_HOME/dg4msql/admin/initdg4msql.ora

# 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=[10.0.33.30]:1433//nnsb070917
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
#HS_FDS_RECOVERY_ACCOUNT=RECOVER
#HS_FDS_RECOVERY_PWD=RECOVER

Append following lines to listener.ora file

# listener.ora Network Configuration File: /u01/app/db/prod/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4msql)
(ORACLE_HOME=/u01/app/db/prod/db_1)
(PROGRAM=dg4msql)
)
)

Restart listener

[oracle@cbsdb01 db_1]$ lsnrctl reload

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 04-NOV-2017 16:40:27

Copyright (c) 1991, 2016, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully

Check Listener Status

[oracle@cbsdb01 db_1]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 04-NOV-2017 16:40:52

Copyright (c) 1991, 2016, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 10-OCT-2017 03:08:06
Uptime 25 days 13 hr. 32 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/db/prod/db_1/network/admin/listener.ora
Listener Log File /u01/app/db/diag/tnslsnr/cbsdb01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5500))(Security=(my_wallet_directory=/u01/app/db/admin/nnsb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "dg4msql" has 1 instance(s).
Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
Service "nnsb" has 1 instance(s).
Instance "nnsb", status READY, has 1 handler(s) for this service...
Service "nnsbXDB" has 1 instance(s).
Instance "nnsb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@cbsdb01 db_1]$

Append following lines to TNSNAMES.ora file :

DG4MSQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SID = dg4msql)
)
(HS = OK)
)

Check tnsping :

[oracle@cbsdb01 admin]$ tnsping dg4msql

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 04-NOV-2017 16:52:17

Copyright (c) 1997, 2016, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID = dg4msql)) (HS = OK))
OK (10 msec)
[oracle@cbsdb01 admin]$

Create a Database link using above mentioned tnsnames entry and Microsoft SQL Server Credential :

SQL> CREATE DATABASE LINK MSQ01 CONNECT TO "sa" IDENTIFIED BY "sa123" USING 'dg4msql';
Database link created.

Now fetch data using Database Link :

SQL> select count(*) from d010153@msq01;

COUNT(*)
----------
239493

SQL>

In next article we can configure Oracle Database Gateway for PostgreSQL.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez

Anuradha’s LinkedIn: https://www.linkedin.com/in/dbaanuradhamudgal/

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp