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

Created by Mark Szalkiewicz (AWS)

R Type: Replatform	Source: Databases: Relational	Target: Amazon RDS for Microsoft SQL Server
Created by: AWS	Environment: PoC or pilot	Technologies: Databases; Migration
Workload: Microsoft	AWS services: Amazon RDS

Summary

This pattern provides guidance for migrating from an on-premises Microsoft SQL Server database to Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server. It describes two options for migration: using AWS Data Migration Service (AWS DMS) or using native Microsoft SQL Server tools such as Copy Database Wizard.

Prerequisites and limitations

Prerequisites

• An active AWS account

• A source Microsoft SQL Server database in an on-premises data center

Limitations

• Database size limit: 16 TB

Product versions

• SQL Server 2012-2017, Enterprise, Standard, Workgroup, and Developer editions. For the latest list of supported versions and features, see Microsoft SQL Server on Amazon RDS in the AWS documentation. If you're using AWS DMS, see also Using a Microsoft SQL Server Database as a Target for AWS DMS for SQL Server versions supported by AWS DMS.

Architecture

Source technology stack

• An on-premises Microsoft SQL Server database

Target technology stack

• An Amazon RDS for Microsoft SQL Server DB instance 

Source and target architecture

Using AWS DMS:

Using native Microsoft SQL Server tools:

Tools

• AWS DMS - AWS Database Migration Service (AWS DMS) supports several types of source and target databases. For details, see AWS DMS Step-by-Step Walkthroughs. If AWS DMS doesn't support the source database, select another method for migrating the data. 

• Native Microsoft SQL Server tools - Backup and restore; Copy Database Wizard: copy and attach database

Epics

Plan the migration

Task	Description	Skills required
Validate the source and target database version and engine.		DBA
Identify the hardware requirements for the target server instance.		DBA, SysAdmin
Identify the storage requirements (storage type and capacity).		DBA, SysAdmin
Choose the proper instance type based on capacity, storage features, and network features.		DBA, SysAdmin
Identify the network access security requirements for source and target databases.		DBA, SysAdmin
Identify the application migration strategy.		DBA, SysAdmin

Configure the infrastructure

Task	Description	Skills required
Create a virtual private cloud (VPC).		SysAdmin
Create security groups.		SysAdmin
Configure and start an Amazon RDS DB instance.		DBA, SysAdmin

Migrate data - option 1

Task	Description	Skills required
Use native Microsoft SQL Server tools or third-party tools to migrate database objects and data.		DBA

Migrate data - option 2

Task	Description	Skills required
Migrate data with AWS DMS.		DBA

Migrate the application

Task	Description	Skills required
Follow the application migration strategy.		DBA, SysAdmin, App owner

Cut over

Task	Description	Skills required
Switch the application clients over to the new infrastructure.		DBA, SysAdmin, App owner

Close the project

Task	Description	Skills required
Shut down the temporary AWS resources.		DBA, SysAdmin
Review and validate the project documents.		DBA, SysAdmin, App owner
Gather metrics around time to migrate, % of manual vs. tool, cost savings, etc.		DBA, SysAdmin, App owner
Close out the project and provide feedback.		DBA, SysAdmin, App owner

Related resources

References

• Deploying Microsoft SQL Server on Amazon Web Services 

• AWS DMS website

• Amazon RDS Pricing

• Microsoft Products on AWS

• Microsoft Licensing on AWS

• Microsoft SQL Server on AWS

• Using Windows Authentication with a Microsoft SQL Server DB Instance

• Amazon RDS Multi-AZ Deployments

Tutorials and videos

• Getting Started with AWS DMS

• Getting Started with Amazon RDS

• AWS DMS (video) 

• Amazon RDS (video)