Use an Oracle Database Gateway to connect Amazon RDS Custom for Oracle to SQL Server

Amazon Relational Database Service (Amazon RDS) Custom for Oracle allows you to run Oracle databases on AWS, giving you some of the benefits of Amazon RDS automation and the freedom of changing configurations to match different use cases. You can customize your database, underlying server, and operating system configurations to accommodate the specific needs of your workloads.

In the modern interconnected business landscape, enterprises frequently operate in heterogeneous environments, where multiple database systems coexist. Through interactions with customers, we have observed specific use cases that demand seamless connectivity between different database engines. In this post, we delve into Oracle database gateway which you can use in RDS Custom for Oracle, a robust tool that enables smooth communication between RDS Custom for Oracle and SQL Server.

Before delving into the implementation details, it is important to establish an understanding of the Oracle database gateway. The gateway serves as a vital link between Oracle databases and external systems, enabling seamless and efficient interaction between them. With the Oracle database gateway for SQL Server, you gain the ability to leverage the unique strengths of both Oracle and SQL Server. This includes facilitating cross-database queries, enabling data synchronization, facilitating real-time integration, and ultimately enhancing data analysis capabilities.

Solution Overview

The solution uses an Amazon RDS Custom for Oracle instance as the source instance and Amazon RDS for SQL server as the target. Alternatively you can also use RDS Custom for SQL Server and SQL server on EC2 instance.

The high-level steps for implementing this solution are as follows:

1. The RDS Custom for Oracle already comes preinstalled with Oracle database gateway software.
2. Install MSSQL ODBC Driver on the RDS Custom for Oracle instance.
3. Configure an ODBC Gateway to align with the MSSQL instance you intend to connect.
4. Test the connection between the instances using sqlcmd or isql (which comes pre-installed on the RDS Custom for Oracle instance)
5. Create a database link in the Oracle database on Amazon RDS Custom for Oracle to connect to the MSSQL database using the configured ODBC settings.
6. Test the database link.

Prerequisites

To get started, you must have the following prerequisites:

• An Amazon RDS Custom for Oracle instance.
• You should temporarily pause the automation of the instance to allow for the installation of the necessary tools and configurations. Pausing the automation ensures that the modifications do not interfere with the regular functioning of RDS Custom for Oracle instance. For more information, refer to Setting up your environment for Amazon RDS Custom for Oracle.
• A SQL server instance. For the purpose of this post, we use an Amazon RDS for SQL Server.
• Network connectivity between the instances.

Install ODBC Driver for SQL server

The ODBC driver for SQL Server provides a standardized interface for connecting, querying, and managing data stored in SQL Server databases. This section details the steps to install and configure the ODBC for SQL server on Amazon RDS Custom for Oracle instance.

1. Connect to the Amazon RDS Custom for Oracle instance using Session Manager or SSH. The choice of connection method will depend on factors such as local computer operating system, security requirements, network infrastructure and user preferences.
2. Verify the Linux version you are using to ensure you download the appropriate repositories.

   cat /etc/redhat-release 
   cat /etc/oracle-release

   

3. Download the appropriate package that corresponds to your specific operating system version. For the purpose of this post, we are using RHEL 7.9.

   curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

   

4. Install the ODBC Driver for SQL server version 18, automatically accepting the End-User License Agreement (EULA) to proceed with the installation. It is important to ensure that you install the correct version that matches your specific database and operating system requirements.

   sudo ACCEPT_EULA=Y yum install -y msodbcsql18

   

5. Install the UnixODBC development package which includes necessary files and libraries for applications that interact with databases using ODBC.

   sudo yum install -y unixODBC-devel

   

6. To test network connectivity, download and install MSSQL tools.

   sudo ACCEPT_EULA=Y yum install -y mssql-tools18

7. Add the path of the MSSQL tools to the PATH environment variable and reload the bashrc file to ensure that the changes take effect in the current session.

   echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc 
   source ~/.bashrc
   

   

Configure an ODBC Gateway

The ODBC Gateway acts as an intermediary between the Amazon RDS Custom for Oracle instance and the target SQL server database. It translates the ODBC calls made by the Amazon RDS Custom for Oracle instance into the specific database-specific calls required by the target SQL server instance. This enables the Amazon RDS Custom for Oracle instance to seamlessly connect and interact with the target SQL Server instance.

The following files are necessary for the ODBC Gateway configuration.

• /etc/odbcinst.ini – This file contains information about the ODBC installation and should be configured based on the specific locations where ODBC and the SQL Server ODBC driver were installed on your instance.
• /etc/odbc.ini – This file holds the necessary details for establishing and managing the connection to the desired database, providing the required configuration information for the database link to function properly.

This section outlines the steps to configure the ODBC Gateway for connecting to SQL server.

1. Connect to the Amazon RDS Custom for Oracle instance using Session Manager or SSH. The choice of connection method will depend on factors such as local computer operating system, security requirements, network infrastructure and user preferences.
2. Edit the file located at /etc/odbcinst.ini to specify the location of the SQL server driver installation.
3. You have the option to modify the name enclosed within brackets. It is important to make a note of this name as it will be referenced in the configuration of the file /etc/odbc.ini

The following template is an example of the /etc/odbcinst.ini

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1
UsageCount=1

4. Edit the file located at /etc/odbc.ini to specify the name of the data source. The value within the brackets is the name of the data source.
5. Configure the remaining parameters within the file based on the specifications of your SQL server instance.

In this particular example, the password is configured in plain text within the file. However, it is recommended to adhere to best practices by encrypting this sensitive information using the dg4pwd utility.

The following template is an example of the /etc/odbc.ini

[MSSQL]
Driver=ODBC Driver 18 for SQL Server 
Description=Microsoft ODBC Driver 18 for SQL Server
Server=sqlserver-odbc.car1erixgf0v.us-east-2.rds.amazonaws.com
Port=1433
Database=heterogenous_database
User=admin
Password=sqlserver123
TrustServerCertificate=yes
QuotedId=YES
AnsiNPW=YES
Threading=1
UsageCount=1
AutoTranslate=No

For a deeper understanding of the parameters in the odbc.ini file, refer to the documentation on ODBC DSN and connection attributes. If you require further details about the odbc.ini file and troubleshooting steps, refer to the documentation on driver files and troubleshooting.

Test the connection with sqlcmd and isql

You can use the sqlcmd utility to verify connectivity by running the following command.

/opt/mssql-tools18/bin/sqlcmd -D -S MSSQL -U sqlserveruser

After successfully establishing the connection, you can test by running a SELECT to retrieve data.

Alternatively, you can also use the isql utility to test the connection with the following command.

/bin/isql -v MSSQL <username> <password>

If the connection fails, it is possible that the file configuration was not done correctly. To troubleshoot and gather additional information, you can utilize the -v option of the isql command. This option will provide you with more detailed output that can assist in identifying and resolving any configuration issues.

Once the connection has been established successfully, we proceed with setting up our database link.

Create and configure database link

A database link establishes a connection between two databases, enabling users to access and manipulate data across multiple databases as if they were a unified database. It facilitates distributed queries and data integration, supporting interactions between Oracle databases or between Oracle and non-Oracle databases.

First, we configure the Oracle Database service handler to provide connection information for the ODBC driver used by our database link.

1. Connect to the Amazon RDS Custom for Oracle instance using Session Manager or SSH. It’s important to note that you must utilize sudo su <user> to switch to the Oracle Home owner user (typically rdsdb on Amazon RDS Custom for Oracle)
2. Navigate to the $ORACLE_HOME/hs/admin directory on the RDS Custom for Oracle database. This directory, located within the ORACLE_HOME, contains essential files and configurations related to the Oracle Heterogeneous Service. It enables the seamless operation of Oracle with various heterogeneous database engines. For detailed guidance, consult the Database Heterogeneous Connectivity User’s Guide.
3. Create a file called init<datasource>.ora, replacing <datasource> with the name of the configured data source from the odbc.ini file. In this instance, we name the file initMSSQL.ora.
4. Open the init<datasource>.ora file and modify the following parameters:
   • HS_FDS_CONNECT_INFO – Set this parameter to the data source name configured in the odbc.ini file.
   • HS_FDS_SHAREABLE_NAME – Specify the path to the ODBC Driver Manager library. In this example, it is located at /usr/lib64/libodbc.so. The location may vary depending on the operating system.
   • ODBCINI – Set the path to the odbc.ini file.
   • HS_FDS_TRACE_LEVEL – Adjust this initialization parameter to control the level of tracing and logging for the gateway.
   • HS_LANGUAGE – Use this parameter to specify the language for character conversion and other language-specific settings during data exchange between Oracle databases and the external system.
   • HS_NLS_LENGTH_SEMANTICS – Utilize this parameter in the Oracle database gateway initialization file to define the length semantics for character data when communicating with an external system, such as SQL Server.

The following template is an example of what the file should look like.

In addition, we configure the HS_LANGUAGE and HS_NLS_NCHAR parameters to address potential character set incompatibility between the Oracle database and the ODBC driver. This step is necessary as the driver may not inherently support the character set used in our Oracle database. For detailed instructions and further insights, consult the support document labeled Doc ID 2325424.1. This requires an oracle account with active support. The recommended workaround involves explicitly configuring the character settings by utilizing these two parameters.

Next, we configure two additional files, namely tnsnames.ora and listener.ora. These files are located within the $ORACLE_HOME/network/admin directory.

5. Edit the tnsnames.ora file and add a new entry. This file contains the information that is used by the database link to open the connection.
   • Since we are utilizing an ODBC Gateway installed on the same machine as our Oracle database, we can conveniently set the HOST parameter to "localhost".
   • To ensure that Oracle recognizes the usage of Oracle heterogeneous services, it is essential to specify the HS=OK parameter. By specifying this parameter, you inform Oracle that the Heterogeneous Services component is active and can be used for connecting to non-Oracle databases.

The tnsnames entry should look like the following:

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

The port configuration must match the port used by your listener, with the default value being 1521.

6. Configure the listener.ora file and add an entry for your data source, which will direct connections to the ODBC Gateway. For this post, we added a LISTENER entry with a new entry called SID_LIST_LISTENER. Additionally, it is also advisable to specify the LD_LIBRARY_PATH parameter to prevent any potential conflicts with other installed ODBC drivers on your instance. This parameter should include the path to $ORACLE_HOME/lib and the ODBC drivers, as demonstrated in the given example.
   

7. To apply the changes made to the listener configuration, you need to reload the listener. Use the following commands to reload the listener and check its status.

   lsnrctl reload
   lsnrctl status

   

8. Test the connectivity to the service name using tnsping.

   tnsping MSSQL

   

9. Log in to the oracle database using SQL*Plus and create the database link with reference to the service name MSSQL from the tnsnames.ora

   create public database link MSSQL_DBLINK connect to sqlserveruser identified by sqlserver123 using 'MSSQL';

   

Test the database link

Now that we have created the database link, let’s retrieve date from the SQL Server Dynamic Management Views, such as sys.databases. It is important to enclose the SQL Server objects in double quotation marks for successful execution.

Finally, resume the RDS Custom automation manually after all the customizations are complete from the Amazon RDS console or using the API.

Best Practices

Here are some recommended practices for optimizing performance, enhancing security, and resolving issues when establishing a connection between Oracle and SQL Server using Oracle Database Gateway.

Performance Tuning

1. SQL statement optimization: Optimize SQL statements accessing the SQL Server database for efficiency and timely data retrieval.
2. Index utilization: Create suitable indexes on SQL Server tables to improve query performance.
3. Performance monitoring: Monitor the connection’s performance and collect metrics to identify bottlenecks and areas for optimization.
4. Connection pooling: Implement connection pooling to reduce the overhead of establishing and closing connections.

Security

1. Secure communication: Utilize secure communication protocols to encrypt data transmitted between Oracle and SQL Server.
2. Strong authentication: Implement robust authentication mechanisms like Kerberos or Active Directory to verify the identity of users accessing the SQL Server database.
3. Access control: Ensure proper access control by granting appropriate permissions to users and groups for the SQL Server database.
4. Audit trails: Establish audit trails to track access to the SQL Server database, enabling detection of any unauthorized access or data tampering.

Troubleshooting

1. Configuration file: Check the configuration file for the Oracle Database Gateway to ensure it contains accurate information for connecting to the SQL Server database.
2. Connectivity verification: Confirm that the Oracle database can successfully establish a connection to the SQL Server database through the gateway.
3. Log analysis: Review the logs of both the Oracle Database Gateway and SQL Server for any error messages or warnings that may indicate connectivity or other related issues.
4. Sample data testing: Perform tests using sample data to identify any problems with SQL statements or data transfer between the Oracle and SQL Server databases.

Clean up

To uninstall Oracle Database Gateway on RDS Custom for Oracle, you can follow these cleanup steps:

1. Remove the Oracle Database Gateway software directory.
   • Identify the directory where the Oracle Database Gateway software is installed. This is typically the <ORACLE_HOME>/hs specific to the gateway installation.
   • Use the following command to remove the directory and its contents.

     sudo rm -rf <ORACLE_HOME>/hs

   • Replace <ORACLE_HOME> with the actual path

2. Remove any environment variables related to Oracle Database Gateway like .bash_profile or .bashrc.
3. Remove any gateway-specific entries from the Oracle Net Configuration files.
   • Open the tnsnames.ora , sqlnet.ora and listener.ora files located in the $ORACLE_HOME/network/admin directory.
   • Search for any entries related to the gateway and remove them.
   • Save the changes to the files

After completing these steps, the Oracle Database Gateway should be uninstalled from the RDS Custom for Oracle. It’s important to exercise caution and ensure you have the appropriate backups before making any changes. If you encounter any issues, please refer to the official Oracle documentation. Resume the RDS Custom automation manually after all the customizations are complete from the Amazon RDS console or using the API.

Conclusion

In this post, we covered the configuration of a database connection between Amazon RDS Custom for Oracle and SQL Server. By utilizing the Oracle Database Gateway for SQL Server, organizations can establish a reliable and seamless integration between Oracle and SQL Server databases. This integration opens up new possibilities for leveraging the strengths of both platforms, enabling data-driven insights and improving operational efficiency.

If you have any comments or questions, leave them in the comments section.

About the Authors

Sid Vantair is a Solutions Architect with AWS covering Strategic accounts. With over a decade of experience in working with relational databases, he thrives on resolving complex technical issues to overcome customer hurdles. Outside of work, he cherishes spending time with his family and fostering inquisitiveness in his children.

Sharath Chandra Kampili is a Database Specialist Solutions Architect with Amazon Web Services. He works with AWS RDS team, focusing on commercial database engines like Oracle. Sharath works directly with AWS customers to provide guidance and technical assistance on the database projects, helping them improve the value of their solutions when using AWS.