Migrate an on-premises Microsoft SQL Server database to Amazon EC2

Created by Mark Szalkiewicz (AWS)

R Type: Rehost	Source: Databases: Relational	Target: Microsoft SQL Server on Amazon EC2
Created by: AWS	Environment: PoC or pilot	Technologies: Databases; Migration
Workload: Microsoft	AWS services: Amazon EC2

Summary

This pattern describes how to migrate an on-premises Microsoft SQL Server database to Microsoft SQL Server on an Amazon Elastic Compute Cloud (Amazon EC2) instance. It covers two options for migration: using AWS Data Migration Service (AWS DMS) or using native Microsoft SQL Server tools such as backup and restore, Copy Database Wizard, or copy and attach database.

Prerequisites and limitations

Prerequisites

• An active AWS account

• An operating system supported by Amazon EC2 (for a complete list of supported operating system versions, see Amazon EC2 FAQs)

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

Product versions

• Microsoft SQL Server versions 2005, 2008, 2008R2, 2012, 2014, 2016, and 2017 for the Enterprise, Standard, Workgroup, and Developer editions, if you're using AWS DMS. To migrate Microsoft SQL Server Web or Express edition, use native or third-party tools. For the latest list of supported versions, see Using a Microsoft SQL Server Database as a Target for AWS DMS.  

Architecture

Source technology stack

• On-premises Microsoft SQL Server database

Target technology stack

• Microsoft SQL Server database on an EC2 instance

Target architecture

Data migration architecture

• Using AWS DMS

• Using native SQL Server tools

Tools

• AWS DMS - AWS Data Migration Service (AWS DMS) helps you migrate your data to and from widely used commercial and open-source databases, including Oracle, SQL Server, MySQL, and PostgreSQL. You can use AWS DMS to migrate your data into the AWS Cloud, between on-premises instances (through an AWS Cloud setup), or between combinations of cloud and on-premises setups.

• Native Microsoft SQL Server tools - These include backup and restore, Copy Database Wizard, and copy and attach database.

Epics

Plan the migration

Task	Description	Skills required
Validate the source and target database versions.		DBA
Identify the target operating system version.		DBA, SysAdmin
Identify the hardware requirements for the target server instance based on the Microsoft SQL Server compatibility list and capacity requirements.		DBA, SysAdmin
Identify the storage requirements for type and capacity.		DBA, SysAdmin
Identify the network requirements, including latency and bandwidth.		DBA, SysAdmin
Choose the EC2 instance type based on capacity, storage features, and network features.		DBA, SysAdmin
Identify the network and host access security requirements for the source and target databases.		DBA, SysAdmin
Identify a list of users required for the Microsoft SQL Server software installation.		DBA, SysAdmin
Determine the backup strategy.		DBA
Determine the availability requirements.		DBA
Identify the application migration and cutover strategy.		DBA, SysAdmin

Configure the infrastructure

Task	Description	Skills required
Create a virtual private cloud (VPC) and subnets.		SysAdmin
Create security groups and network access control list (ACL).		SysAdmin
Configure and start an EC2 instance.		SysAdmin

Install the software

Task	Description	Skills required
Create the users and groups required for Microsoft SQL Server software.		DBA, SysAdmin
Download the Microsoft SQL Server software.		DBA, SysAdmin
Install Microsoft SQL Server software on the EC2 instance and configure the server.		DBA, SysAdmin

Migrate the data - option 1

Task	Description	Skills required
Use native Microsoft SQL Server tools or third-party tools to migrate the database objects and data.	Tools include backup and restore, Copy Database Wizard, and copy and attach database.	DBA

Migrate the data - option 2

Task	Description	Skills required
Migrate the data by using AWS DMS.	For detailed information on using AWS DMS, see the links in the References and Help section.	DBA

Migrate the application

Task	Description	Skills required
Follow the application migration strategy.	Use AWS Schema Conversion Tool (AWS SCT) to analyze and modify SQL code embedded in application source code.	DBA, App owner

Cut over

Task	Description	Skills required
Follow the application switch-over strategy.		DBA, SysAdmin, App owner

Close the project

Task	Description	Skills required
Shut down all temporary AWS resources.	Temporary resources include the AWS DMS replication instance and the EC2 instance for AWS SCT.	DBA, SysAdmin
Review and validate the project documents.		DBA, SysAdmin, App owner
Gather metrics around time to migrate, percent of manual versus tool cost savings, and so on.		DBA, SysAdmin, App owner
Close the project and provide feedback.		DBA, SysAdmin, App owner

Related resources

References

• Deploying Microsoft SQL Server on Amazon Web Services 

• Amazon EC2

• Amazon EC2 FAQs

• AWS Database Migration Service

• Amazon EC2 pricing

• Microsoft Products on AWS

• Microsoft Licensing on AWS

• Microsoft SQL Server on AWS

Tutorials and videos

• Getting Started with Amazon EC2

• Getting Started with AWS Database Migration Service

• Add an Amazon EC2 Instance to Your Directory (Simple AD and Microsoft AD)  

• AWS Database Migration Service (video) 

• Introduction to Amazon EC2 - Elastic Cloud Server & Hosting with AWS (video)