Migrate an on-premises Microsoft SQL Server database to Amazon Redshift using AWS SCT data extraction agents - AWS Prescriptive Guidance

Migrate an on-premises Microsoft SQL Server database to Amazon Redshift using AWS SCT data extraction agents

Created by Neha Thakur (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; AWS SCT

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

TaskDescriptionSkills 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
TaskDescriptionSkills required

Create a virtual private cloud (VPC) and subnets.

SysAdmin

Create security groups.

SysAdmin

Configure and start the Amazon Redshift cluster.

SysAdmin
TaskDescriptionSkills required

Migrate the data using the AWS SCT data extraction agents.

DBA
TaskDescriptionSkills required

Follow the chosen application migration strategy.

DBA, SysAdmin, App owner
TaskDescriptionSkills required

Switch application clients over to the new infrastructure.

DBA, SysAdmin, App owner
TaskDescriptionSkills 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