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
![Architecture to migrate an on-premises SQL Server DB to an Amazon RDS for SQL Server DB instance.](/images/prescriptive-guidance/latest/patterns/images/pattern-img/c2dcd6ab-deb1-4d5e-b3c5-3bf48c02ca4e/images/29f90473-6dd4-4574-bfbd-5c6a0481c40e.png)
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
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 |
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 |
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 |
Task | Description | Skills required |
---|---|---|
Add the option group to Amazon RDS. |
For more information, see the Amazon RDS documentation. | SysOps administrator |
Restore the database. |
| DBA |
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 |
Task | Description | Skills required |
---|---|---|
Redirect application traffic. | After validation, redirect application traffic to the Amazon RDS for SQL Server DB instance. | App owner, DBA |