How can I migrate databases from EC2 instances or on-premises VMs to RDS for SQL Server?

5 minute read

I want to migrate databases from an Amazon Elastic Compute Cloud (Amazon EC2) instance or on-premises Microsoft SQL Server instance to my Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server instance. What are the available options for migrating the data?


Methods for migrating data

Native SQL Server backup and restore

You can migrate the SQL Server database from an on-premises or EC2 instance to Amazon RDS for SQL Server instance using native backup and restore.

1.    Create an Amazon Simple Storage Service (Amazon S3) bucket to store the backup from the source instance. The S3 bucket must be in the same Region as the RDS instance.

2.    Create the AWS Identity and Access Management (IAM) role to access the bucket.

3.    Add the SQLSERVER_BACKUP_RESTORE option to the option group associated with the RDS for SQL Server instance.

4.    Create a backup from the source instance (on-premises or EC2), and then copy it to the S3 bucket you created in step 1.

5.    Run the following script to restore the backup to the RDS for SQL Server instance:

exec msdb.dbo.rds_restore_database
@s3_arn_to_restore_from='arn:aws:s3:::bucket_name file_name_and_extension';

6.    Run the following script to back up the RDS instance database to S3:

exec msdb.dbo.rds_backup_database 

Note: You can also backup and restore differential backups.

AWS Database Migration Service (AWS DMS)

1.    Verify the pre-requisites and limitations of using SQL Server as a source or target for AWS DMS:

2.    Create a DMS replication instance.

3.    Create source and target endpoints using DMS.

4.    Create a migration task.

Transactional replication

You can set up transactional replication from on-premises or EC2 SQL Server instances to RDS for SQL Server instance. RDS for SQL Server instance can only be made as a subscriber with push subscription from the On-premises or EC2 SQL Server instance as Publisher-Distributor.

For step-by-step instructions for setting up transaction replication from an on-premises or EC2 SQL Server instance, see the following:

Backup Package (.bacpac) file

The .bacpac file consists of copied metadata and the data compressed to a file. This approach is the best choice for databases that are around 200 GB.

You can create a .bacpac file using Export/Import or using the SQLPackage.exe (command line) utility.

For more information on the .bacpac file, see Migrate SQL Server database from an Azure SQL database to Amazon RDS for SQL Server using .bacpac method.

Methods for importing data

Generate and Publish Script Wizard

If your database is smaller than 1GB, you can use the Generate and Publish Script Wizard. For larger databases, you can script the schema of the database using the Import and Export Wizard or Bulk copy methods.

For more information on the Generate and Publish Script Wizard, see How to: Generate a script (SQL Server Management Studio) in the Microsoft SQL Server documentation.

Note: Make sure that you select Save scripts to specific location, Advanced on the Set Scripting Option page. The Advanced setting provides additional options for including or excluding object in the table during import and export.

Import and Export Wizard

The Import and Export Wizard creates an integration package. The integration package is used to copy data from your on-premises or EC2 SQL Server database to the RDS for SQL Server instance. You can filter the specific tables you want to copy to the RDS instance.

For more details on the Import and Export Wizard, see How to: Run the SQL Server Import and Export Wizard in the Microsoft SQL Server documentation.

Note: When running the Import and Export Wizard, make sure you choose the following options for the Destination RDS for SQL Server instance:

  • For Server Name, enter the name of the endpoint for the RDS DB instance.
  • For Authentication mode, select SQL Server Authentication.
  • For the User name and Password, enter the master user that you created in the RDS instance.

Bulk Copy Program utility

The Bulk Copy Program (bcp) is a command line utility that's used to bulk copy data between SQL Server instances. You can use the bcp utility to import large sets of data to a SQL Server instance or export to a file.

The following are examples of the IN and OUT commands:

OUT: Use this command to export or dump the records from a table into a file:

bcp dbname.schema_name.table_name out C:\table_name.txt -n -S localhost -U username -P     password -b 10000

The preceding code includes the following options:

  • -n: specifies that the bulk copy uses the native data types of the data to be copied.
  • -S: specifies the SQL Server instance that the bcp utility connects to.
  • -U: specifies the user name of the account to log in to the SQL Server instance.
  • -P: specifies the password for the user specified by -U.
  • -b: specifies the number of rows per batch of imported data.

IN: Use this command to import all records from the dump file to the existing table. The table must be created before running the bcp command.

bcp dbname.schema_name.table_name in C:\table_name.txt -n -S endpoint,port -U master_user_name -P master_user_password -b 10000

For more information, see bcp utility in the Microsoft SQL Server documentation.