Migrate an on-premises Microsoft SQL Server database to Amazon RDS for SQL Server using native backup and restore methods

Created by Tirumala Dasari (AWS), David Queiroz (AWS), and Vishal Singh (AWS)

Environment: PoC or pilot	Source: On-premises SQL Server database	Target: Amazon RDS for SQL Server
R Type: Replatform	Workload: Microsoft	Technologies: Migration; Databases; Operating systems
AWS services: Amazon RDS; Amazon S3

Summary

This pattern describes how to migrate an on-premises Microsoft SQL Server database to an Amazon Relational Database Service (Amazon RDS) for SQL Server DB instance (homogeneous migration). The migration process is based on native SQL Server backup and restore methods. It uses SQL Server Management Studio (SSMS) to create a database backup file, and an Amazon Simple Storage Service (Amazon S3) bucket to store the backup file before restoring it in Amazon RDS for SQL Server.

Prerequisites and limitations

Prerequisites

• An active AWS account.

• AWS Identity and Access Management (IAM) role policies to access the S3 bucket and the Amazon RDS for SQL Server DB instance.

Limitations

• The process described in this pattern migrates only the database. SQL logins or database users, including any SQL Server Agent jobs, aren’t migrated because they require additional steps.

Product versions

• SQL Server 2012-2017. For the latest list of supported versions and features, see Microsoft SQL Server on Amazon RDS in the AWS documentation.

Architecture

Source technology stack

• An on-premises Microsoft SQL Server database

Target technology stack

• Amazon RDS for SQL Server DB instance

Data migration architecture

Tools

• Microsoft SQL Server Management Studio (SSMS) is an integrated environment for managing SQL Server infrastructure. It provides a user interface and a group of tools with rich script editors that interact with SQL Server.

Epics

Create an Amazon RDS for SQL Server DB instance

Task	Description	Skills required
Select SQL Server as the database engine in Amazon RDS for SQL Server.		DBA
Choose the SQL Server Express Edition.		DBA
Specify database details.	For more information about creating a DB instance, see the Amazon RDS documentation.	DBA, App owner

Create a backup file from the on-premises SQL Server database

Task	Description	Skills required
Connect to the on-premises SQL Server database through SSMS.		DBA
Create a backup of the database.	For instructions, see the SSMS documentation.	DBA, App owner

Upload the backup file to Amazon S3

Task	Description	Skills required
Create a bucket in Amazon S3.	For more information, see the Amazon S3 documentation.	DBA
Upload the backup file to the S3 bucket.	For more information, see the Amazon S3 documentation.	SysOps administrator

Restore the database in Amazon RDS for SQL Server

Task	Description	Skills required
Add the option group to Amazon RDS.	Open the Amazon RDS console at https://console.aws.amazon.com/rds/. In the navigation pane, choose Option groups, Create group. Complete the information for the option group, and then choose Create. Add the SQLSERVER_BACKUP_RESTORE option to the option group, and then choose Add option. For more information, see the Amazon RDS documentation.	SysOps administrator
Restore the database.	Connect to Amazon RDS for SQL Server through SSMS. Call the msdb.dbo.rds_restore_database stored procedure to restore the database.	DBA

Validate the target database

Task	Description	Skills required
Validate objects and data.	Validate the objects and data between the source database and Amazon RDS for SQL Server. Note: This task migrates the database only. Logins and jobs will not be migrated.	App owner, DBA

Cut over

Task	Description	Skills required
Redirect application traffic.	After validation, redirect application traffic to the Amazon RDS for SQL Server DB instance.	App owner, DBA

Related resources

• Amazon S3 documentation 

• Amazon RDS for SQL Server documentation  

• Options for the Microsoft SQL Server Database Engine