Migrate an on-premises Microsoft SQL Server database to Amazon EC2
Created by Senthil Ramasamy (AWS)
Environment: PoC or pilot | Source: Microsoft SQL Server on premises | Target: Microsoft SQL Server on Amazon EC2 |
R Type: Rehost | Workload: Microsoft | Technologies: Migration; Databases |
AWS services: AWS DMS; Amazon EC2; AWS SCT |
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 Database 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
For on-premises and Amazon EC2 instance databases, AWS DMS supports:
SQL Server versions 2005, 2008, 2008R2, 2012, 2014, 2016, 2017, and 2019
Enterprise, Standard, Workgroup, Developer, and Web editions
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 Database 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.
AWS Schema Conversion Tool (AWS SCT) supports heterogeneous database migrations by automatically converting the source database schema and a majority of the custom code to a format that’s compatible with the target database.
Native Microsoft SQL Server tools include backup and restore, Copy Database Wizard, and copy and attach database.
Epics
| Task | Description | Skills required |
|---|---|---|
Validate the source and target database versions. | DBA | |
Identify the target operating system version. | DBA, Systems administrator | |
Identify the hardware requirements for the target server instance based on the Microsoft SQL Server compatibility list and capacity requirements. | DBA, Systems administrator | |
Identify the storage requirements for type and capacity. | DBA, Systems administrator | |
Identify the network requirements, including latency and bandwidth. | DBA, Systems administrator | |
Choose the EC2 instance type based on capacity, storage features, and network features. | DBA, Systems administrator | |
Identify the network and host access security requirements for the source and target databases. | DBA, Systems administrator | |
Identify a list of users required for the Microsoft SQL Server software installation. | DBA, Systems administrator | |
Determine the backup strategy. | DBA | |
Determine the availability requirements. | DBA | |
Identify the application migration and cutover strategy. | DBA, Systems administrator |
| Task | Description | Skills required |
|---|---|---|
Create a virtual private cloud (VPC) and subnets. | Systems administrator | |
Create security groups and network access control list (ACL). | Systems administrator | |
Configure and start an EC2 instance. | Systems administrator |
| Task | Description | Skills required |
|---|---|---|
Create the users and groups required for Microsoft SQL Server software. | DBA, Systems administrator | |
Download the Microsoft SQL Server software. | DBA, Systems administrator | |
Install Microsoft SQL Server software on the EC2 instance and configure the server. | DBA, Systems administrator |
| 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. For more information, see the guide Migrating Microsoft SQL Server databases to the AWS Cloud. | DBA |
| Task | Description | Skills required |
|---|---|---|
Migrate the data by using AWS DMS. | For more information about using AWS DMS, see the links in the Related resources section. | DBA |
| Task | Description | Skills required |
|---|---|---|
Follow the application migration strategy. | Use AWS Schema Conversion Tool (AWS SCT) to analyze and modify SQL code that’s embedded in application source code. | DBA, App owner |
| Task | Description | Skills required |
|---|---|---|
Follow the application switch-over strategy. | DBA, App owner, Systems administrator |
| 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, Systems administrator |
Review and validate the project documents. | DBA, App owner, Systems administrator | |
Gather metrics around time to migrate, percent of manual versus tool cost savings, and so on. | DBA, App owner, Systems administrator | |
Close the project and provide feedback. | DBA, App owner, Systems administrator |
Related resources
References
Tutorials and videos
Getting Started with
Amazon EC2 Getting Started with
AWS Database Migration Service Join an Amazon EC2 instance to your Simple AD Active Directory
Join an Amazon EC2 instance to your AWS Managed Microsoft AD Active Directory
AWS Database Migration Service
(video) Introduction to Amazon EC2 – Elastic Cloud Server & Hosting with AWS
(video)
