Migrate an on-premises Microsoft SQL Server database to Microsoft SQL Server on Amazon EC2 running Linux
Created by Tirumala Dasari (AWS)
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.
Prerequisites and limitations
Prerequisites
An active AWS account
Amazon EC2 Linux AMI (Amazon Machine Image) with Microsoft SQL Server
AWS Direct Connect between 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
Database migration architecture
Tools
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
| Task | Description | Skills required |
|---|---|---|
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 |
| Task | Description | Skills required |
|---|---|---|
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 |
| Task | Description | Skills required |
|---|---|---|
Restore the database from the database backup file by using the sqlcmd utility. | DBA | |
Validate database objects and data. | Developer, Test engineer |
| Task | Description | Skills required |
|---|---|---|
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 |
Related resources
