Migrate an on-premises Microsoft SQL Server database to Amazon RDS for SQL Server using Amazon S3 and SSMS - AWS Prescriptive Guidance

Migrate an on-premises Microsoft SQL Server database to Amazon RDS for SQL Server using Amazon S3 and SSMS

Created by Tirumala Rama Chandra Murty Dasari (AWS)

R Type: Replatform

Source: Databases: Relational

Target: Amazon RDS for SQL Server

Created by: AWS

Environment: PoC or pilot

Technologies: Databases; Migration

Workload: Microsoft

AWS services: Amazon RDS

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. The migration process includes making a backup and restoring the backup in an Amazon Simple Storage Service (Amazon S3) bucket, and using SQL Server Management Studio (SSMS).

Prerequisites and limitations

Prerequisites

  • An active AWS account

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

Product versions

Architecture

Source technology stack

  • An on-premises Microsoft SQL Server database

Target technology stack

  • An Amazon RDS for SQL Server DB instance

Data migration architecture

Tools

  • SSMS - 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.DBA, App owner
TaskDescriptionSkills required
Connect to the on-premises SQL Server database through SSMS.DBA
Create a backup of the database.DBA, App owner
TaskDescriptionSkills required
Create a bucket in Amazon S3.DBA
Upload the backup file to the S3 bucket using AWS Direct Connect.SysopsAdmin
TaskDescriptionSkills required
Open the Amazon RDS console at https://console.aws.amazon.com/rds/.SysopsAdmin
Choose Option groups in the navigation pane.SysopsAdmin
Choose the Create group button.SysopsAdmin
Add the SQLSERVER_BACKUP_RESTORE option to the option group.SysopsAdmin
Add the option group to Amazon RDS for SQL Server.SysopsAdmin
Connect to Amazon RDS for SQL Server through SSMS.DBA
Call the msdb.dbo.rds_restore_database stored procedure to restore the database.DBA
TaskDescriptionSkills required
Validate the objects and data between the source database and Amazon RDS for SQL Server.App owner, DBA
TaskDescriptionSkills required
After validation, redirect application traffic to the Amazon RDS for SQL Server DB instance.App owner, DBA