AWS Database Blog

Implement Linked Servers with Amazon RDS for Microsoft SQL Server

Richard Waymire is a principal database specialist solutions architect for Amazon Web Services

Linked servers allow Microsoft SQL Server to run SQL Server statements on other instances of database servers. In this blog post, we will focus on connectivity either to other instances of SQL Server in Amazon RDS or to SQL Server instances hosted in Amazon EC2. We will examine three connectivity scenarios:

  • EC2 SQL Server to RDS SQL Server
  • RDS SQL Server to EC2 SQL Server
  • RDS SQL Server to RDS SQL Server

For each of these scenarios to succeed, we assume that all network traffic is happening within a single VPC. Also, for the RDS SQL Server to RDS SQL Server scenario, we assume that both instances are not publicly available, because communications between them need to use the private IP addresses within the VPC. AWS recommends that RDS SQL Server installations should generally be only privately accessible (that is, not directly exposed to the Internet).

SQL Server Management Studio (SSMS) requires system admin rights to create a linked server using the graphical interface, which isn’t available in an RDS environment. Unfortunately, this approach prevents users from even launching the dialog box to use to create the needed Transact-SQL script. However, calling the stored procedure sp_addlinkedserver and then the stored procedure sp_addlinkedserverlogin directly from a query window lets an administrator add linked servers to the RDS installation of SQL Server.

For each of the examples following, you must allow network traffic by using the appropriate TCP port through the Security group for each inbound instance of SQL Server. In other words, if you’re connecting EC2 SQL Server to RDS SQL Server, you must allow traffic from the IP address of the EC2 instance.

EC2 SQL Server to RDS SQL Server

For this scenario, an EC2 instance of SQL Server is connecting to an instance of RDS SQL Server. This scenario is the simplest one, because you can use either use the SSMS graphical interface or just submit the Transact-SQL statements to create the linked server. The example code here is a connection to an RDS server in the cloud. Variables you should replace are listed in angle brackets. Note that in this example we are impersonating a single RDS standard user account.

EXEC master.dbo.sp_addlinkedserver @server = N’LinkedtoRDS’, @srvproduct=N’‘, @provider=N’SQLNCLI’, @datasrc=N'<myserver>.CB2XKFSFFMY7.US-WEST-2.RDS.AMAZONAWS.COM’;

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’LinkedtoRDS‘,@useself=N’False’,@locallogin=NULL,@rmtuser=N'<username>’,@rmtpassword=‘<password>’;

If you are using a common active directory (the AWS Directory Service), then you can change the linked server security to something like this:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’LinkedtoRDS’,@useself=N’True’;

When the linked server is in place, you then use standard four-part naming to reference a table, view, and so on, on the remote server:

SELECT * FROM LinkedtoRDS.TestDB.dbo.t1;

If the RDS instance of SQL Server was a private instance (that is, not publicly available), then the EC2 instance needs to be in the same VPC as the RDS SQL Server instance for connectivity. If the RDS SQL Server instance is publicly available, then this scenario also works with an on-premises SQL Server installation.

RDS SQL Server to EC2 SQL Server

For this scenario, both the RDS instance of SQL Server and the EC2 SQL Server should be available within the same VPC. However, this time the EC2 instance should not be publicly accessible. If the EC2 instance is publicly accessible, then you need to refer to the private IP address of the EC2 instance within the VPC.

InstanceIP

The code now looks like this:

EXEC master.dbo.sp_addlinkedserver @server = N’REPLTest2′, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N’10.0.0.135′;

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’REPLTest2′,@useself=N’False‘,@locallogin=NULL,@rmtuser=N'<username>’,@rmtpassword=‘<password>’;

GO

This change is required due to the current network setup of the RDS SQL Server installation. Be aware that this setup might change in the future.

RDS SQL Server to RDS SQL Server

For this scenario, both instances of RDS SQL Server must be private instances (not publicly accessible) within the same VPC. If the RDS SQL Server instances are publicly accessible, then you need to refer to the private IP of the RDS instances when creating the linked servers. Because you don’t have access to the private IP addresses of the servers, you need to use an EC2 instance in the same VPC to run NSLookup on the private instances of RDS. Do this for each RDS instance name.

> NSlookup privatesql.cb2xkfsffmy7.us-west-2.rds.amazonaws.com

Address: 10.0.4.236

From there, the code is very much like the RDS to EC2 code, except now you use the private IP address of the remote RDS instance.

EXEC master.dbo.sp_addlinkedserver @server = N’RDSPrivate‘, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N’10.0.4.236′;

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’RDSPrivate’,@useself=N’False’,@locallogin=NULL,@rmtuser=N'<username>’,@rmtpassword=‘<password>’;

GO

Just as we did preceding, use a four-part name to reference the remote RDS instance:

SELECT * from RDSPrivate.TestDB.dbo.t1;

Note one limitation for all RDS instances: If the physical server supporting your RDS SQL Server instance changes (such as when upgrading to a new version of SQL Server or changing the instance type), the private IP address of the RDS instance of SQL Server might change. This change can happen with no alerts or notifications to your administrative team. Thus, if the linked server connection fails, your first troubleshooting steps should include verifying that the private IP address of the RDS instance has not changed. If this risk is unacceptable in your environment, then AWS doesn’t recommend using the linked server feature with RDS SQL Server.

Note: RDS SQL Server currently doesn’t replicate linked servers to the mirrored database server in a Multi-AZ deployment. If the linked servers are added before the configuration is changed to add mirroring, then the linked servers are copied, but only once. Alternatively, you can create the linked servers on the primary instance, fail over to the mirrored server instance, and then create the linked servers again so that they are on both instances of RDS SQL Server.

Although the documentation states that in general linked servers are not supported, as shown in this post there are in fact several supported scenarios with linked servers for RDS SQL Server. However, these are the only supported scenarios at this time.