Migrate an on-premises Microsoft SQL Server database to Amazon Redshift using AWS DMS
Created by Mark Szalkiewicz (AWS)
R Type: Re-architect | Source: Databases: Relational | Target: Amazon Redshift |
Created by: AWS | Environment: PoC or pilot | Technologies: Databases; Migration |
Workload: Microsoft | AWS services: Amazon Redshift |
Summary
This pattern provides guidance for migrating an on-premises Microsoft SQL Server database to Amazon Redshift using AWS Data Migration Service (AWS DMS).
Prerequisites and limitations
Prerequisites
An active AWS account
A source Microsoft SQL Server database in an on-premises data center
Product versions
SQL Server 2005-2017, Enterprise, Standard, Workgroup, and Developer editions. For the latest list of supported versions, see Using a Microsoft SQL Server Database as a Source for AWS DMS in the AWS documentation/
Architecture
Source technology stack
An on-premises Microsoft SQL Server database
Target technology stack
Amazon Redshift
Data migration architecture
Tools
AWS DMS - AWS 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.
Epics
| 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 the source and target databases. | DBA, SysAdmin | |
| Identify the application migration strategy. | DBA, SysAdmin, App owner |
| Task | Description | Skills required |
|---|---|---|
| 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 |
| Task | Description | Skills required |
|---|---|---|
| Migrate the data from the Microsoft SQL Server database by using AWS DMS. | DBA |
| Task | Description | Skills required |
|---|---|---|
| Follow the application migration strategy. | DBA, SysAdmin, App owner |
| Task | Description | Skills required |
|---|---|---|
| Switch the application clients over to the new infrastructure. | DBA, SysAdmin, App owner |
| Task | Description | Skills required |
|---|---|---|
| Shut down the temporary 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
Tutorials and videos
