Configure Linked Servers on Amazon RDS Custom for SQL Server

Amazon Relational Database Service (Amazon RDS) Custom is a managed database service that automates the setup, operation, and scaling of databases in the cloud while granting you access to the underlying operating system and database environment. You can provision a Multi-AZ Amazon RDS Custom for SQL Server instance for a highly available environment with automatic failover capability.

The linked server feature within SQL Server provides flexibility to retrieve data from multiple remote data sources without application code changes. With database drivers installed, this feature extends further to pull data from heterogeneous database systems such as Oracle, PostgreSQL, and more. You must install appropriate drivers locally on the RDS Custom instance to connect with a heterogeneous linked server. RDS Custom for SQL Server provides granular control for DBAs to manage the database instance and the underlying operating system, allowing you to install the drivers and software required for your application.

In this post, we walk you through setting up a homogenous linked server from RDS Custom for SQL Server to a target RDS for SQL Server instance, and a heterogenous linked server from RDS Custom for SQL Server to a target Amazon RDS for Oracle instance. We also share the best practices to persist and update the database driver versions using a custom engine version (CEV). This will be helpful to support heterogenous linked server use cases such as with PostgreSQL or Amazon Redshift.

Solution overview

We deploy the Multi-AZ architecture shown in the following diagram to implement the linked server.

To implement the solution, complete the following high-level steps:

Prepare the source and remote data source running on SQL Server.
Create the linked server configuration.
Test the linked server connection and query the linked server to validate the data.

Prerequisites

You must have connectivity between the local RDS Custom for SQL Server DB instance and the remote target instance running on Amazon RDS for SQL Server or Amazon RDS for Oracle. If this is your first time using RDS Custom for SQL Server, you can use this guide to quickly set up pre-requisites with CloudFormation and get started.

Prepare the local and remote data source running SQL Server

To create a test database and table with sample data on RDS Custom for SQL Server, complete the following steps:

Open SQL Server Management Studio (SSMS).
Connect to the local data source running on RDS Custom for SQL Server.
Open a new query window.
Run the following query:

CREATE Database [SampleDB]
GO

USE [SampleDB]
GO

CREATE TABLE [SampleDB].[dbo].[Employee](
[EmployeeID] INT PRIMARY KEY,
[FirstName] VARCHAR(50) NOT NULL,
[LastName] VARCHAR(50) NOT NULL,
[Age] [INT] NOT NULL,
[Gender] VARCHAR(50) NOT NULL)
GO

INSERT INTO [SampleDB].[dbo].[Employee]
VALUES (1, 'John', 'Doe', 35, 'Male'),
(2, 'Bob', 'Doe', 45, 'Male'),
(3, 'Mary', 'Johnson', 35, 'Female'),
(4, 'Julie', 'Jordan', 30, 'Female'),
(5, 'James', 'Miller', 39, 'Male');
SELECT * FROM [SampleDB].[dbo].[Employee]

Now on the remote server, create a test database and table with sample data:

In SSMS, connect to the remote data source running on Amazon RDS for SQL Server.
Open a new query window.
Run the following query:

CREATE Database [RemoteSampleDB]
GO

USE [RemoteSampleDB]
GO

CREATE TABLE Stores (
StoreID INT PRIMARY KEY,
ManagerID INT,
StoreName VARCHAR(100) NOT NULL,
State VARCHAR(10)
);

INSERT INTO [RemoteSampleDB].[dbo].[Stores]
VALUES (101, '2', 'Store Tennesse', 'TN'),
(102, '5', 'Store California', 'CA'),
(103, '3', 'Store New York', 'NY'),
(104, '4', 'Store Maryland', 'MD'),
(105, '1', 'Store DC', 'DC');
SELECT * FROM [RemoteSampleDB].[dbo].[Stores]

Create the linked server configuration for Amazon RDS for SQL Server

To create the linked server configuration using Amazon RDS for SQL Server, complete the following steps:

Open SSMS and connect to RDS Custom for SQL Server.
Open a new query window.
Run the following query:

USE [master]
GO
 
EXEC sp_addlinkedserver
@server=N'LinkedServerName',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'replace_with_remote_server/endpoint_name'; 
GO
 
EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = N'LinkedServerName', 
@useself = N'False', 
@rmtuser = N'UserName', 
@rmtpassword = N'Your_Secure_Password'
GO

Update the following connection details before running the query:

The name for the linked server
Your SQL Server DB instance name
The login for your SQL database
The password for your login account

Test the linked server connection and query the linked server to validate the data

Complete the following steps to test the connection:

In the SSMS object explorer, expand Server Objects and Linked Servers.
Choose (right-click) the linked server, then choose Test Connection to verify.
When the connection is successful, choose OK.

Query the remote data source via the linked server:

--Return to the local server and run the following command to get data from the local and remote server

SELECT L.*, R.*
FROM [SampleDB].[dbo].[Employee] L 
    JOIN [LinkedServerName].[RemoteSampleDB].[dbo].[Stores] R 
    ON L.EmployeeID = R.ManagerID

Create the linked server configuration for Amazon RDS for Oracle

In this section, we show the steps to create the linked server configuration with Amazon RDS for Oracle as the target. Complete the following steps:

Confirm you have connectivity and DNS resolution between the RDS Custom for SQL Server and the target Oracle DB instance.
Open SSMS and connect to RDS Custom for SQL Server.
Open a new query window.
Modify and run the following query to create the linked server:

USE [master]
GO
 
EXEC sp_addlinkedserver
@server=N'OracleLinkedServer',
@srvproduct=N'',
@provider=N'OraOLEDB.Oracle',
@datasrc=N'replace_with_oracle_server_name:1521'; 
GO
 
EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = N'OracleLinkedServer', 
@useself = N'False', 
@rmtuser = N'Oracle_Login’, 
@rmtpassword = N'Your_Secure_Password'
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

Test the linked server connection and query the linked server to validate the data:

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

Best practices to update linked server drivers on RDS Custom for SQL Server

In this section, we share a few best practices of persisting and updating the software version on your RDS Custom DB instance:

To maintain and manage the software version of the required database drivers, you need to create a CEV.
We recommend reading the software vendor’s latest documentation for compatibility and installation best practices before preparing the CEV.
For detailed steps to install drivers and create the CEV, refer to Persist your OS-level customization within Amazon RDS Custom for SQL Server using Custom Engine Version (CEV).
To automate the process of CEV creation and distribution at scale with different AWS accounts and Regions, refer to Manage Amazon RDS Custom for SQL Server CEV AMIs using EC2 Image Builder.
Before implementing to a production RDS Custom DB instance, as a general practice, always test and validate the newest version of the driver software in your lower environment.

Clean up

Complete the following steps to clean up your resources:

On the Amazon RDS console, choose Databases in the navigation pane.
Select the DB instance to delete.
On the Actions menu, choose Delete.
Enter delete me to confirm deletion, then choose Delete.
When prompted to create a final snapshot and retain automated backup, choose the option appropriate for your needs.

Conclusion

In this post, we explained how you can configure linked servers on RDS Custom DB instances to support both homogenous and heterogenous linked server requirements. We showed configuration examples for Oracle and SQL Server as a target, but you can test and configure other targets such as PostgreSQL, Amazon Redshift, and DB2 as long as you install the supported OS and database version compatibility drivers. Additionally, we talked about the best practices to manage the future software updates to the linked server drivers on the RDS Custom DB instance using CEV. We strongly recommend you to read the product documentation Working with RDS Custom for SQL Server before you begin your database migration planning. You can also easily setup your PoC environment using the CloudFormation template available in this blog post.

About the authors

Mesgana Gormley is a Senior Database Specialist Solution Architect at Amazon Web Services. She works on the Amazon RDS team providing technical guidance to AWS customers and helping them migrate, design, deploy, and optimize relational database workloads on AWS.

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.

Santhosh Reddy Talla is an accomplished Database Engineer , works with RDS SQL Server team at AWS, with expertise in relational databases such as SQL Server, Oracle, Postgres, and MySQL. He has a strong ability to solve complex problems, optimize database performance, and automate tasks using Python, Shell, and PowerShell. Outside of work he is avid outdoor enthusiast, with a love for skiing and hiking.

AWS Database Blog