AWS Database Blog

Create linked server access to Amazon RDS for MySQL and Amazon RDS for MariaDB

Linked servers allow Microsoft SQL Server to run SQL Server statements on other instances of database servers. They are a good solution when you need to implement database sharding without needing to create custom application code or directly load from remote data sources. In this post, we focus on creating linked server access to Amazon Relational Database Service (Amazon RDS) for MySQL and Amazon RDS for MariaDB.

Solution overview

We examine connectivity scenarios for each service with SQL Server on Amazon Elastic Compute Cloud (Amazon EC2) or on premises.

We use SQL Server Management Studio (SSMS) to connect to the SQL Server instance running in local as well as to external servers. SSMS requires system admin rights to create a linked server using the graphical interface.

For each of the following scenarios, you must allow network traffic by using the appropriate TCP port through the security group for each inbound instance of the database server. In other words, if you’re connecting Amazon EC2 SQL Server to Amazon RDS for MySQL, you must allow traffic from the IP address of the EC2 instance, as well as on the port that MySQL is using to listen for database communications.

Prerequisites

Make sure you have the following prerequisites:

For the purpose of this demo, the RDS DB instance is in a public subnet and SQL Server version is 15.0.

Connect SQL Server to Amazon RDS for MySQL

For this scenario, an EC2 or on-premises instance of SQL Server is connecting to an RDS for MySQL instance. Make sure you completed the prerequisite step to get the MySQL ODBC driver and install it on the EC2 instance or the on-premises machine where the SQL Server database is running along with SSMS.

The following is the sample architecture for establishing a linked server connection from SQL Server running on an EC2 instance inside a public subnet to an RDS database inside a private subnet within a same VPC.

For more information, refer to Scenarios for accessing a DB instance in a VPC.

Create a DSN using the ODBC connector

To create a DSN using the ODBC connector, complete the following steps:

  1. From the Windows Administrative Tools menu, choose Data Sources (ODBC) and then System DSN to add a DSN entry.
  2. Choose the MySQL driver for which you want to set up a data source.

For the purpose of the demo, we create an RDS for MySQL 8.0.28 database.

Next, we collect the MySQL connector and ODBC data source configuration details.

  1. On the Amazon RDS console, choose the MySQL database instance.
  2. On the Connectivity & security tab, copy the endpoint and port details.

The TCP/IP server name is the endpoint URL of MySQL database.

  1. Enter the DSN name, primary user name, and password of the RDS for MySQL database that was set up when the instance was created.

You should now have a successful ODBC connection to the MySQL server running on Amazon RDS.

Create and configure the linked server

To set up the linked server, complete the following steps:

  1. Open SSMS and connect to the local server using Windows authentication.
  2. In the navigation pane, expand Server Options and add a new linked server.
  3. For the data source name, use the DSN you created earlier.
  4. On the Security page, for the remote server login, use the current security context.
  5. On the Server Options page, set the values for RPC, RPC OUT, and Use Remote Collation to True.
  6. Choose OK to establish the connection.

Now you should be able to connect to the MySQL server running in Amazon RDS.

If the RDS for MySQL instance was a private instance (that is, an RDS instance that doesn’t have a public IP address), then the EC2 instance (SQL Server) needs to be in the same VPC as the RDS for MySQL instance for connectivity. If an on-premises connection is required, then routing must be established between the corporate network and the subnet on the VPC that the RDS for MySQL server installation is using. Securely connect your AWS environment to your on-premises data center with the help of AWS Direct Connect or AWS Site-to-Site VPN connection.

If the MySQL instance is publicly available (that is, available to the public internet), then this scenario also works with any SQL Server installation.

Connect SQL Server to Amazon RDS for MariaDB

For this scenario, an EC2 or on-premises instance of SQL Server is connecting to an RDS for MariaDB instance. Make sure you completed the prerequisite step to download the current release series MariaDB connector/ODBC 3.1.16. Get the MariaDB ODBC driver and install it on the EC2 instance or the on-premises machine where the SQL Server database is running along with SSMS.

Create a DSN using the ODBC connector

To create a DSN using the ODBC connector, complete the following steps:

  1. From the Windows Administrative Tools menu, choose Data Sources (ODBC) and then System DSN to add a DSN entry.
  2. Choose the MariaDB driver for which you want to set up a data source.

For the purpose of the demo, we use RDS for MariaDB engine version 10.6.7.

Next, we collect the MariaDB connector and ODBC data source configuration details.

  1. On the Amazon RDS console, choose the MariaDB database instance.
  2. On the Connectivity & security tab, copy the endpoint and port details.

The TCP/IP server name is the endpoint URL of MariaDB.

  1. Enter the DSN name for your data source.
  2. Enter the primary user name and password of the RDS for MariaDB database that was set up when the instance was created.

You should now have a successful ODBC connection to the MariaDB server running on Amazon RDS.

Create and configure the linked server

To set up the linked server, complete the following steps:

  1. Open SSMS and connect to the local server using Windows authentication.
  2. In the navigation pane, expand Server Options and add a new linked server.
  3. For the data source name, use the DSN you created earlier.
  4. On the Security page, for the remote server login, use the current security context.
  5. On the Server Options page, set the values for RPC, RPC OUT, and Use Remote Collation to True.
  6. Choose OK to establish the connection.

Now you should be able to connect to the MariaDB server running in Amazon RDS.

If the RDS for MariaDB instance was a private instance (an RDS instance that doesn’t have a public IP address), then the EC2 instance (SQL Server) needs to be in the same VPC as the RDS for MariaDB instance for connectivity. If an on-premises connection is required, then routing must be established between the corporate network and the subnet on the VPC that the RDS for MariaDB server installation is using. Securely connect your AWS environment to your on-premises data center with the help of Direct Connect or AWS Site-to-Site VPN connection.

If the MariaDB server instance is publicly available (available to the public internet), then this scenario also works with any SQL Server installation.

Conclusion

Linked servers allow Microsoft SQL Server to run SQL Server statements on other instances of database servers. Linked servers provide the ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

In this post, we focused on connectivity to other instances of SQL Server in Amazon RDS, hosted in Amazon EC2, or available from your data centers via a VPN or Direct Connect. You can use the solution in this post as a foundation to Implement Linked Servers with Amazon RDS for Microsoft SQL Server, and then extend it to include additional services like Amazon Aurora MySQL-Compatible Edition and Aurora MySQL Serverless.

We encourage you to try out this solution in your AWS account where you’re running your Amazon RDS. If you have any feedback, please leave a comment below.

About the authors

Nayan Karumuri is a Senior Solutions Architect at Amazon Web Services. He works with customers to design scalable, highly available and secure solutions in the AWS Cloud. He is passionate about diving deep with customers to architect creative solutions that support business innovation across different industries.