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

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

Architecture

Source technology stack

  • An on-premises Microsoft SQL Server database

Target technology stack

  • Amazon RDS for SQL Server DB instance

Data migration architecture

Architecture to migrate an on-premises SQL Server DB to an Amazon RDS for SQL Server DB instance.

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

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

Add the option group to Amazon RDS.

  1. Open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the navigation pane, choose Option groups, Create group.

  3. Complete the information for the option group, and then choose Create.

  4. 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.

  1. Connect to Amazon RDS for SQL Server through SSMS.

  2. Call the msdb.dbo.rds_restore_database stored procedure to restore the database.

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

Redirect application traffic.

After validation, redirect application traffic to the Amazon RDS for SQL Server DB instance.

App owner, DBA

Related resources