SQL Server database migration methods - AWS Prescriptive Guidance

SQL Server database migration methods

There are various methods to migrate your SQL Server databases to AWS. You can choose these methods based on your assessment and requirements. This section describes some of the most common methods, which are summarized in the following table. Detailed discussions of some of these methods are included in the sections on Amazon EC2 and Amazon RDS later in this guide.

Migration method Target Features and limitations More information

Native backup and restore

Amazon EC2

Amazon RDS

  • Can be applied to one or many databases at one time

  • Requires downtime

  • Supports all database sizes

Native SQL Server backup/restore section

Log shipping

Amazon EC2

Amazon RDS

  • Applied per database

  • Can be delayed

Log shipping section

Database mirroring

Amazon EC2

  • Applied per database

  • Can be synchronous or asynchronous, based on the SQL Server edition

  • Secondary database isn’t readable; it acts as a standby

  • Supports both automatic and manual failover

Database mirroring section

Always On availability groups

Amazon EC2

  • Applied to a set of user databases

  • Can be synchronous or asynchronous

  • Secondary database is readable (SQL Server Enterprise edition only)

  • Supports both automatic and manual failover

  • Failover can be initiated for multiple databases at a time, at the database group level

Always On availability groups section

Basic Always On availability groups

Amazon EC2

  • Supported in SQL Server Standard edition

  • Applied to a single user database per availability group

  • Can be synchronous or asynchronous

  • Supports both automatic and manual failover

  • Failover can be initiated at the availability group level

  • Can be used as a hybrid environment between on premises and AWS

Not covered in this guide (see Basic Always On availability groups for a single database in the Microsoft documentation)

Distributed availability groups

Amazon EC2

  • Can be used for multi-Region SQL Server deployments

  • Can fail over to a later version of SQL Server

  • Doesn’t require Windows Server Failover Clustering (WSFC) to be extended to the target AWS environment

  • Can be used between Windows-based (source) and Linux-based (target) SQL Server databases

  • Can be used as a hybrid SQL Server deployment between on premises and AWS

Distributed availability groups section

Transactional replication

Amazon EC2

Amazon RDS

  • Supports migration of a set of objects (tables, view, stored procedures)

  • Supports asynchronous replication with near real-time data

  • Subscriber database is readable

  • Requires close monitoring of SQL Server replication jobs that perform the replication

Transactional replication section

AWS Snowball Edge

Amazon EC2

Amazon RDS

  • Supports very large databases (up to 80 TB)

  • Uses Amazon Simple Storage Service (Amazon S3) for storing and restoring data

Snowball Edge section

CloudEndure Migration

Amazon EC2

  • Highly automated lift-and-shift solution

  • Agent-based, block-level replication

CloudEndure Migration section

AWS DMS

Amazon EC2

Amazon RDS

Amazon Aurora

  • Supports full load and CDC

  • Supports all database sizes

AWS DMS section

Bulk copy program (bcp)

Amazon EC2

  • Supports small databases

  • Requires downtime

  • Schema is pre-created at the destination

  • Used for moving data, but not metadata

Not covered in this guide (see Importing and exporting SQL Server data using other methods, Bulk copy section in the Amazon RDS documentation)

Detach and attach

Amazon EC2

  • No backup needed

  • Requires downtime

  • Involves stopping, detaching, copying files, and attaching to Amazon EC2

Not covered in this guide (see Database Detach and Attach in the Microsoft documentation)

Import/export

Amazon EC2

  • Supports small databases

  • Requires downtime

  • Schema is pre-created at the destination

  • Used for moving data, but not metadata

Not covered in this guide (see Importing and exporting SQL Server data using other methods in the Amazon RDS documentation)