Migrate an on-premises Microsoft SQL Server database to Amazon RDS for SQL Server using linked servers
Created by Kevin Yung (AWS), Vishal Singh (AWS), and Viqash Adwani (AWS)
Summary
Linked servers enable Microsoft SQL Server to run SQL statements on other instances of database servers. This pattern describes how you can migrate your on-premises Microsoft SQL Server database to Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server to achieve lower cost and higher availability. Currently, Amazon RDS for Microsoft SQL Server doesn't support connections outside an Amazon Virtual Private Cloud (Amazon VPC) network.
You can use this pattern to achieve the following objectives:
To migrate Microsoft SQL Server to Amazon RDS for Microsoft SQL Server without breaking linked server capabilities.
To prioritize and migrate linked Microsoft SQL Server in different waves.
Prerequisites and limitations
Prerequisites
Check whether Microsoft SQL Server on Amazon RDS supports the features you require.
Make sure that you can use either Amazon RDS for Microsoft SQL Server with default collations or collations set over database levels.
Architecture
Source technology stack
On-premises databases (Microsoft SQL Server)
Target technology stack
Amazon RDS for SQL Server
Source state architecture
Target state architecture
In the target state, you migrate Microsoft SQL Server to Amazon RDS for Microsoft SQL Server by using linked servers. This architecture uses a Network Load Balancer to proxy the traffic from Amazon RDS for Microsoft SQL Server to on-premises servers running Microsoft SQL Server. The following diagram shows the reverse proxy capability for the Network Load Balancer.
Tools
AWS CloudFormation
Network Load Balancer
Amazon RDS for SQL Server in multiple Availability Zones (Multi-AZs)
AWS Database Migration Service (AWS DMS)
Epics
| Task | Description | Skills required |
|---|---|---|
| Create the CIDR allocation. | AWS SysAdmin | |
| Create a virtual private cloud (VPC). | AWS SysAdmin | |
| Create the VPC subnets. | AWS SysAdmin | |
| Create the subnet access control lists (ACLs). | AWS SysAdmin | |
| Create the subnet route tables. | AWS SysAdmin | |
| Create a connection with AWS Direct Connect or AWS Virtual Private Network (VPN). | AWS SysAdmin |
| Task | Description | Skills required |
|---|---|---|
| Create an Amazon RDS for Microsoft SQL Server DB instance. | AWS SysAdmin | |
| Create an AWS DMS replication instance. | AWS SysAdmin | |
| Create the source and target database endpoints in AWS DMS. | AWS SysAdmin | |
| Create the migration task and set continuous replication to ON after a full load. | AWS SysAdmin | |
| Request a firewall change to allow Amazon RDS for Microsoft SQL Server to access the on-premises SQL Server databases. | AWS SysAdmin | |
| Create a Network Load Balancer. | AWS SysAdmin | |
| Create a target group that targets the database servers in your data center | We recommend that you use hostnames in the target setup to incorporate data center (DC) failover events. | AWS SysAdmin |
| Run the SQL statement for linked server setup. | Run the SQL statements for adding a linked server by using the Microsoft SQL management tool against the Amazon RDS for Microsoft SQL Server DB instance. In the SQL statement, set @datasrc to use the Network Load Balancer hostname. Add linked server login credentials by using the Microsoft SQL management tool against the Amazon RDS for Microsoft SQL Server DB instance. | AWS SysAdmin |
| Test and validate the SQL Server functions. | AWS SysAdmin | |
| Create a cutover. | AWS SysAdmin |
Related resources
