Migrate an on-premises Microsoft SQL Server database to Amazon Redshift using AWS DMS
Created by Marcelo Fernandes (AWS)
Environment: PoC or pilot | Source: Microsoft SQL Server | Target: Amazon Redshift |
R Type: Re-architect | Workload: Microsoft | Technologies: Migration; Databases |
AWS services: Amazon Redshift |
Summary
This pattern provides guidance for migrating an on-premises Microsoft SQL Server database to Amazon Redshift by 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
Completed prerequisites for using an Amazon Redshift database as a target for AWS DMS, as discussed in the AWS DMS documentation
Product versions
SQL Server 2005-2019, Enterprise, Standard, Workgroup, Developer, and Web 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 is a data migration service that 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, Systems administrator | |
Identify the storage requirements (storage type and capacity). | DBA, Systems administrator | |
Choose the proper instance type based on capacity, storage features, and network features. | DBA, Systems administrator | |
Identify the network access security requirements for the source and target databases. | DBA, Systems administrator | |
Identify the application migration strategy. | DBA, App owner, Systems administrator |
| Task | Description | Skills required |
|---|---|---|
Create a virtual private cloud (VPC). | For more information, see Working with a DB instance in a VPC in the AWS documentation. | Systems administrator |
Create security groups. | Systems administrator | |
Configure and start an Amazon Redshift cluster. | For more information, see Create a sample Amazon Redshift cluster in the Amazon Redshift documentation. | DBA, Systems administrator |
| 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, App owner, Systems administrator |
| Task | Description | Skills required |
|---|---|---|
Switch the application clients over to the new infrastructure. | DBA, App owner, Systems administrator |
| Task | Description | Skills required |
|---|---|---|
Shut down the temporary resources. | DBA, Systems administrator | |
Review and validate the project documents. | DBA, App owner, Systems administrator | |
Gather metrics such as time to migrate, percentage of manual versus automated tasks, and cost savings. | DBA, App owner, Systems administrator | |
Close out the project and provide feedback. | DBA, App owner, Systems administrator |
Related resources
References
Tutorials and videos
