AWS Prescriptive Guidance
Patterns

Migrate an on-premises Microsoft SQL Server database to Amazon Redshift using AWS DMS and AWS SCT

R Type :RePlatform

source :Databases: Relational

target :Amazon Redshift

tags :microsoft sql server, database, redshift

categories :Software Infrastructure

Summary

This pattern provides guidance for migrating an on-premises Microsoft SQL Server database to Amazon Redshift using AWS Data Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT).

Assumptions and Prerequisites

Prerequisites

  • An active AWS account

  • A source Microsoft SQL Server database (version 2005, 2008, 2008 R2, 2012, or 2014 for the Enterprise, Standard, Workgroup, or Developer edition) in an on-premises data center

Architecture

Source technology stack

  • An on-premises Microsoft SQL Server database (version 2005, 2008, 2008 R2, 2012, or 2014 for the Enterprise, Standard, Workgroup, or Developer edition). For a complete, up-to-date list of sources, see Sources for Data Migration in the AWS DMS documentation.

Target technology stack

  • Amazon Redshift

Target architecture

Data migration architecture

Tools Used

AWS DMSAWS Database Migration Services (AWS DMS) supports several types of source and target databases. For information about the Microsoft SQL Server database versions and editions that are supported for use with AWS DMS, see Using a Microsoft SQL Server Database as a Source for AWS DMS in the AWS DMS documentation. If AWS DMS doesn't support your source database, you must select an alternative method for data migration. 

AWS SCT - 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.

Epics

Plan the migration

Tasks

Title Description Skills Predecessor
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 the source and target databases. DBA, SysAdmin
Identify the application migration strategy. DBA, SysAdmin, AppOwner

Configure the infrastructure

Tasks

Title Description Skills Predecessor
Create a virtual private cloud (VPC). See https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.WorkingWithRDSInstanceinaVPC.html for how to work with a DB instance in a VPC. SysAdmin
Create security groups. SysAdmin
Configure and start an Amazon Redshift cluster. DBA, SysAdmin

Migrate data

Tasks

Title Description Skills Predecessor
Migrate the data from the Microsoft SQL Server database by using AWS DMS. DBA

Migrate the application

Tasks

Title Description Skills Predecessor
Follow the application migration strategy. DBA, SysAdmin, AppOwner

Cut over

Tasks

Title Description Skills Predecessor
Switch the application clients over to the new infrastructure. DBA, SysAdmin, AppOwner

Close the project

Tasks

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

References and Help

References

Tutorials and videos

Contact and help

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