AWS Prescriptive Guidance
Patterns

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

R Type :RePlatform

source :Databases: Relational

target :Amazon RDS for SQL Server

tags :on prem windows sql server 2008, amazon rds postgresql server

categories :Enterprise Applications, Software Infrastructure

Summary

This pattern describes how to migrate an on-premises Microsoft SQL Server 2008 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).

Assumptions and Prerequisites

Prerequisites

  • An active AWS account

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

Architecture

Source technology stack

  • An on-premises Microsoft SQL Server 2008 database

Target technology stack

  • An Amazon RDS for SQL Server DB instance (version 2016)

Source and target architecture

Tools Used

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

Create an Amazon RDS for Microsoft SQL Server DB instance

Tasks

Title Description Skills Predecessor
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, AppOwner

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

Tasks

Title Description Skills Predecessor
Connect to the on-premises SQL Server 2008 database through SSMS. DBA
Create a backup of the database. DBA, AppOwner

Upload the backup file to Amazon S3

Tasks

Title Description Skills Predecessor
Create a bucket in Amazon S3. DBA
Upload the backup file to the S3 bucket using AWS Direct Connect. SysopsAdmin

Restore the database in Amazon RDS for SQL Server

Tasks

Title Description Skills Predecessor
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

Validate the target database

Tasks

Title Description Skills Predecessor
Validate the objects and data between the source database and Amazon RDS for SQL Server. AppOwner, DBA

Cut over

Tasks

Title Description Skills Predecessor
After validation, redirect application traffic to the Amazon RDS for SQL Server DB instance. AppOwner, DBA

References and Help

References

Contact and help

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