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
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
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
| 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. | DBA, App owner |
| Task | Description | Skills required |
|---|---|---|
| Connect to the on-premises SQL Server database through SSMS. | DBA | |
| Create a backup of the database. | DBA, App owner |
| Task | Description | Skills required |
|---|---|---|
| Create a bucket in Amazon S3. | DBA | |
| Upload the backup file to the S3 bucket using AWS Direct Connect. | SysopsAdmin |
| Task | Description | Skills 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 |
| Task | Description | Skills required |
|---|---|---|
| Validate the objects and data between the source database and Amazon RDS for SQL Server. | App owner, DBA |
| Task | Description | Skills required |
|---|---|---|
| After validation, redirect application traffic to the Amazon RDS for SQL Server DB instance. | App owner, DBA |
Related resources
