AWS Prescriptive Guidance
Patterns

Migrate an on-premises Microsoft SQL Server database to Amazon RDS for SQL Server using linked servers

R Type :RePlatform

source :Databases: Relational

target :Amazon RDS for Microsoft SQL Server

tags :sql server, linked server, integration, amazon cloudformation, amazon network load balancer, aws dms

categories :Business Software, Data and Analytics, Independent Software Vendor, LifeCycle, Pattern Accreditation, Pattern Provenance, Pattern Type, Software Infrastructure

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.

Assumptions and Prerequisites

Prerequisites

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 Used

  • AWS CloudFormation

  • Network Load Balancer 

  • Amazon RDS for SQL Server in multiple Availability Zones (Multi-AZs)

  • AWS Database Migration Service (AWS DMS)

Epics

Create a landing zone VPC

Tasks

Title Description Skills Predecessor
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

Migrate the database to Amazon RDS

Tasks

Title Description Skills Predecessor
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

References and Help

Referenceshttps://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General

Contact and help

Migration Pattern Library Support: aws-mpl@amazon.com