Migrate an on-premises Microsoft SQL Server database to Amazon Redshift using AWS SCT data extraction agents
Created by Neha Thakur (AWS)
Summary
This pattern outlines steps for migrating an on-premises Microsoft SQL Server source database to an Amazon Redshift target database by using AWS Schema Conversion Tool (AWS SCT) data extraction agents. An agent is an external program that is integrated with AWS SCT but performs data transformation elsewhere and interacts with other AWS services on your behalf.
Prerequisites and limitations
Prerequisites
A Microsoft SQL Server source database used for the data warehouse workload in an on-premises data center
An active AWS account
Product versions
Microsoft SQL Server version 2008 or later. For the latest list of supported versions, see AWS SCT documentation.
Architecture
technology stackSource
An on-premises Microsoft SQL Server database
technology stackTarget
Amazon Redshift
Data migration architecture

Tools
AWS Schema Conversion Tool (AWS SCT) handles 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. When the source and target databases are very different, you can use an AWS SCT agent to perform additional data transformation. For more information, see Migrating Data from an On-Premises Data Warehouse to Amazon Redshift in the AWS documentation.
Best practices
Epics
Task | Description | Skills required |
---|---|---|
Validate the source and target database versions and engines. | DBA | |
Identify hardware requirements for the target server instance. | DBA, SysAdmin | |
Identify storage requirements (storage type and capacity). | DBA, SysAdmin | |
Choose the proper instance type (capacity, storage features, network features). | DBA, SysAdmin | |
Identify network access security requirements for the source and target databases. | DBA, SysAdmin | |
Choose an application migration strategy. | DBA, SysAdmin, App owner |
Task | Description | Skills required |
---|---|---|
Create a virtual private cloud (VPC) and subnets. | SysAdmin | |
Create security groups. | SysAdmin | |
Configure and start the Amazon Redshift cluster. | SysAdmin |
Task | Description | Skills required |
---|---|---|
Migrate the data using the AWS SCT data extraction agents. | DBA |
Task | Description | Skills required |
---|---|---|
Follow the chosen application migration strategy. | DBA, SysAdmin, App owner |
Task | Description | Skills required |
---|---|---|
Switch application clients over to the new infrastructure. | DBA, SysAdmin, App owner |
Task | Description | Skills required |
---|---|---|
Shut down temporary AWS resources. | DBA, SysAdmin | |
Review and validate the project documents. | DBA, SysAdmin, App owner | |
Gather metrics such as time to migrate, percentage of manual versus automated tasks, and cost savings. | DBA, SysAdmin, App owner | |
Close the project and provide any feedback. | DBA, SysAdmin, App owner |
Related resources
References
Tutorials and videos