AWS Database Blog

Create a heterogeneous linked server from Amazon RDS for SQL Server to an Oracle database

Amazon Relational Database Service (Amazon RDS) for SQL Server now supports the Oracle database engine as an external data source to implement heterogeneous linked server configuration and run distributed queries. With this feature, you can now create a linked server configuration between Amazon RDS for SQL Server and an Oracle database instance running in your network as an OLEDB target. This feature helps customers who want to access data using TSQL from an external Oracle database without application changes.

In this post, we walk you through the steps to configure a heterogeneous linked server between Amazon RDS for SQL Server and an Oracle database instance that is running on RDS, EC2, or on-premises.

Solution overview

Amazon RDS for SQL Server supports linked servers with Oracle OLEDB in all regions for SQL Server Standard and Enterprise Editions on the following versions:

  • SQL Server 2022, all versions
  • SQL Server 2019, all versions
  • SQL Server 2017, all versions

Linked servers with Oracle OLEDB is supported for the following Oracle Database versions:

  • Oracle Database 21c, all versions
  • Oracle Database 19c, all versions
  • Oracle Database 18c, all versions

The following diagram illustrates our solution architecture.

OLEDB_ORACLE can be activated on an RDS instance through an option group. For more information, see Working with Option Groups. You can create or use an existing option group for SQL Server based on the edition of your RDS DB instance.

In the following sections, we walk through the steps of creating an option group, associating the option group with your DB instance, and configuring the linked server.

Create an option group

To create an option group, complete the following steps:

  1. On the Amazon RDS console, choose Option groups. You can create or modify an existing option group.
  2. For this post we will create a new option group
  3. Choose Add option.
  1. For Option name, choose OLEDB_ORACLE.
  1. For Scheduling, select Immediately or During the next maintenance window.

Adding the option will only takes effect after rebooting the RDS instance.

Associate the option group with your DB instance

Modify the DB instance and associate the option group created in the previous step to the existing RDS for SQL Server instance. For more information, see Modifying an Amazon RDS DB instance.

After you successfully modify the option group, the status of the option group shows as Pending reboot, indicating a reboot of the RDS for SQL Server DB instance is required after enabling the feature.

For a Multi-AZ RDS instance, a reboot needs to happen on both the primary and secondary RDS instances to have the OLEDB_ORACLE option to take effect. Rebooting the RDS instance twice changes the status of the option group from Pending reboot to In sync.

After successful reboots, you can see the option group status change to In sync.

Set up an Amazon RDS for SQL Server to Oracle linked server

Linked server configuration depends on network connectivity and DNS resolution between Amazon RDS for SQL Server and Oracle Server. When you’re setting up a linked server, you must register the connection information and data source information with SQL Server properly. After registration, both the remote and local data source can be referred to with a single logical name. You can use stored procedures and catalog views to manage linked server definitions.

The following code is an example of using the stored procedure sp_addlinkedserver to create a linked server:

EXEC sp_addlinkedserver '<NameForTheLinkedServer>', 'Oracle', 'OraOLEDB.Oracle', '<end-point>:1521/ORCL'
Go

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'<NameForTheLinkedServer>', @useself=N'False', @locallogin=NULL, @rmtuser='<oracle-login>', @rmtpassword='<oracle-password>'
Go

USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'<NameForTheLinkedServer>', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'<NameForTheLinkedServer>', @optname=N'rpc out', @optvalue=N'true'
GO

The following connection information is required:

  • The name of the linked server
  • Your Oracle DB instance name
  • The login for your Oracle database
  • The password for your Oracle login to connect

The following code is an example of the linked server setup between two Amazon RDS DB instances running SQL Server and Oracle:

EXEC sp_addlinkedserver 'TestLink', 'Oracle', 'OraOLEDB.Oracle', 'persist-lhs.csqoy1gougcw.us-west-2-oracle.rds.amazonaws.com:1521/ORCL'
Go
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'TestLink', @useself=N'False', @locallogin=NULL, @rmtuser='anyuser', @rmtpassword='577t08PvDgQc'
Go

USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'TestLink', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TestLink', @optname=N'rpc out', @optvalue=N'true'
GO

Next, review the linked server using SQL Server Management Studio (SSMS), as shown in the following image.

You can also run the query using the linked server to verify if the remote data source is accessible. If the COUNT number returns a non-zero number, then the remote data source is accessible.

SELECT *
FROM OPENQUERY (TestLink, 'SELECT count(*) FROM ALL_TABLES')

Conclusion

In this post, we explained how to configure a heterogeneous linked server configuration between Amazon RDS for SQL Server and an Oracle database instance. We also showed how to access data from Oracle database using the TSQL statement against the configured linked server. After configuring your heterogeneous linked server, you should be able to run distributed queries including SELECT, UPDATE, and other commands to interact with an external Oracle database instance to meet requirements such as reporting.

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

About the authors

Vikas Babu Gali is a Sr. Specialist Solutions Architect, focusing on Microsoft Workloads at Amazon Web Services. As a native of India, Vikas enjoys playing Cricket and spending time with his family and friends outdoors.

Sudhir Amin is a Database Specialist Solutions Architect at Amazon Web Services. In his role based out of New York, he provides architectural guidance and technical assistance to enterprise customers across different industry verticals, accelerating their cloud adoption. He is a big fan of snooker, combat sports such as boxing and UFC, and loves traveling to countries with rich wildlife reserves where he gets to see world’s most majestic animals up close.