AWS Prescriptive Guidance
Patterns

Migrate an on-premises Microsoft SQL Server database to Microsoft SQL Server on Amazon EC2 running Linux

R Type :RePlatform

source :Databases: Relational

target :Amazon EC2 Linux with Microsoft SQL Server

tags :on-premises windows sqlserver, amazon linux ec2, sqlserver

categories :Enterprise Applications, Software Infrastructure

Summary

This pattern describes how to migrate from an on-premises Microsoft SQL Server database running on Microsoft Windows, to Microsoft SQL Server on an Amazon Elastic Compute Cloud (Amazon EC2) Linux instance by using backup and restore utilities.

Assumptions and Prerequisites

Prerequisites

  • An active AWS account

  • Amazon EC2 Linux AMI (Amazon Machine Image) with Microsoft SQL Server

  • AWS Direct Connect between the on-premises Windows and Microsoft SQL Server on the Linux EC2 instance

Architecture

Source technology stack

  • On-premises Microsoft SQL Server database

Target technology stack

  • Linux EC2 instance with a Microsoft SQL Server database

Target architecture

Database migration architecture

Tools Used

WinSCP - This tool enables Windows users to easily share files with Linux users.

Sqlcmd - This command-line utility lets you submit T-SQL statements or batches to local and remote instances of SQL Server. The utility is extremely useful for repetitive database tasks such as batch processing or unit testing.

Epics

Prepare the EC2 Linux instance with SQL Server

Tasks

Title Description Skills Predecessor
Select an AMI that provides the Linux operating system and includes Microsoft SQL Server. Sysadmin
Configure the AMI to create an EC2 instance. Sysadmin
Create inbound and outbound rules for security groups. Sysadmin
Configure the Linux EC2 instance for a Microsoft SQL Server database. DBA
Create users and provide permissions as in the source database. Appowner, DBA
Install SQL Server tools and the sqlcmd utility on the Linux EC2 instance. DBA

Back up the database and move backup file to Linux EC2 instance

Tasks

Title Description Skills Predecessor
Back up the on-premises SQL Server database. DBA
Install WinSCP on Microsoft SQL Server. DBA
Move the backup file to the Linux EC2 instance running Microsoft SQL Server. DBA

Restore the database on Linux EC2 instance running SQL Server

Tasks

Title Description Skills Predecessor
Restore the database from the database backup file by using the sqlcmd utility. DBA
Validate database objects and data. Developer, Test engineer

Cut over from Windows SQL Server to Windows SQL Server on Linux EC2 instance

Tasks

Title Description Skills Predecessor
Validate database objects and data. Developer, Test engineer
Cut over from the on-premises Microsoft SQL Server database to the Linux EC2 instance running Microsoft SQL Server. DBA

References and Help

References

Contact and help

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