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.

Append following lines to listener.ora file

Restart listener

Check Listener Status

Append following lines to TNSNAMES.ora file :

Check tnsping :

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

Now fetch data using Database Link :

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

Leave a Reply