Oracle - SQL Server Gateway Installation and configuration

1) Download the gateway binaries

2) SQL Server IP Address and hostname has to registered in /etc/hosts of the oracle server.

3) Unzip the gateway software

4) Install the gateway binaries

Duing installation it prompts for the SQL Server Configuration Information

- SQL Server Database server host name

- SQL Server Database server port number

- SQL Server instance name

- SQL Server Database name

5) Run script root.sh as root user from gateway home.

6) runInstaller invoke NETCA to configure the listener, Cancel it.


---------------------------------------------------
Configuring Oracle Database gateway for SQL Server:
---------------------------------------------------

1) Configure gateway initalization parameter file

- Choose System Identifier (SID) for the gateway

* We need one gateway instance for each sql server database we are accessing.

* The default SID is dg4msql

* If we need to access two SQL server databases the we require two gateways

- Customize the initalization parameter file

* The parameter file must be avilable when the gateways is started.

* Location of the parameter file is $ORACLE_HOME/dg4msql/admin/initdg4msql.ora

* HS_FDS_CONNECT_INFO=hostname:port number//database_name or instance_name

e.g: HS_FDS_CONNECT_INFO=[10.1.100.40]:1433//SSEM

2) Configure Oracle Net for the Gateway

- Gateway information needed to be added to the oracle net listener configuration file listener.ora resides in $ORACLE_HOME/rdbms/admin, Where $ORACLE_HOME is the location where Gateway has been installed.

LISTENER =
(ADDRESS=
(PROTOCOL=TCP)
(HOST=HOSTNAME)
(PORT=1530))

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4mssql)
(ORACLE_HOME=Gateway_HOME)
(PROGRAM=dg4msql)
)
)

3) Stop and start the gateway listener

- export ORACLE_HOME and PATH environment variables pointing to GATEWAY_HOME

- lsnrctl stop

- lsnrctl start


4) Configure the Oracle database for gateway access

- Configure tnsnames.ora of the database home

dg4msql=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=HOSTNAME)(PORT=1530))

(CONNECT_DATA=
(SID=dg4msql)
)
(HS=OK)
)


5) Create database links



- Create public database link dg4msql connect to "sa" identified by "P@55w0rd!" using 'dg4msql';