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

Short description

Use one of the following methods to migrate your database:

• Native SQL Server backup and restore
• AWS Database Migration Service (AWS DMS)
• Transactional replication
• Backup Package (.bacpac) file
• SQL Server Generate and Publish Script Wizard
• SQL Server Import and Export Wizard
• SQL Server bulk copy program (bcp) utility

Migration time and downtime depend on your migration method. For more information, see Best practices for migrating to Amazon RDS for SQL Server.

Resolution

Native SQL Server backup and restore

Native backup and restore is slower than methods that use replication, but it can have fewer limitations.

Complete the following steps:

1. Create an Amazon Simple Storage Service (Amazon S3) bucket to store the backup of the on-premises or EC2 instance. The Amazon S3 bucket must be in the same AWS Region as the RDS for SQL Server 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 of the on-premises or EC2 instance, and then move it to the S3 bucket.

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

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

   Note: Replace database_name with the name of your database, bucket_name with the name of your bucket, and file_name_and_extension with the name of your file.

6. Run the following script to track the status of your backup:

   exec msdb.dbo.rds_task_status;

If the task fails, then check the task_info column of the output for error messages. Look up the error messages in the Troubleshooting table for Amazon RDS issues.

Note: You can back up and restore differential backups. You can also restore logs to restore to a point in time.

AWS DMS

Complete the following steps:

1. To use SQL Server as a source or target for AWS DMS, complete the prerequisites and review the limitations.
2. Create an AWS DMS replication instance.
3. Use AWS DMS to create source and target endpoints.
4. Create a migration task.

Transactional replication

You can set up transactional replication from the on-premises or EC2 SQL Server instances to an RDS for SQL Server instance. However, you can only make the RDS for SQL Server instance a Subscriber. And, you can only make the on-premises or EC2 SQL Server instance the Publisher or Distributor with a push subscription.

Note: A transactional replication depends on the EC2 hostname that can change in RDS for SQL Server. Because of this limitation, it's a best practice to use a transactional replication only for a one-time migration of your data to your RDS for SQL Server instance.

Backup Package (.bacpac) file

The .bacpac file consists of copied metadata and the data compressed to a file. It's a best practice to use a .bacpack file for databases that are less than 200 GB. You can use Export/Import or the SQLPackage.exe (command line) utility to create a .bacpac file. For more information about the .bacpac file, see Migrate SQL Server databases from an Azure SQL database to Amazon RDS for SQL Server using bacpac method.

SQL Server Generate and Publish Script Wizard

If your database is smaller than 1 GB, then you can use the Generate and Publish Script Wizard. For larger databases, you can use the Import and Export Wizard or Bulk copy methods to script the schema of the database. For more information about 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.

SQL Server 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 instance to the RDS for SQL Server instance. You can filter the specific tables you want to copy to the RDS for SQL Server instance.

Before you run the Import and Export Wizard, verify that it can reach your network and configure permissions for your security group. See What permissions do I need? on the Microsoft Learn website.

For more information about the Import and Export Wizard, see How to: Run the SQL Server Import and Export Wizard on the Microsoft Learn website.

When you run the Import and Export Wizard, choose the following options for the destination RDS for SQL Server instance:

• For Server Name, enter the name of the endpoint for the Amazon RDS database instance.
• For Authentication mode, choose SQL Server Authentication.
• For the Username and Password, enter the main user that you created in the RDS for SQL Server instance.

SQL Server bcp utility

Use the command line utility to bulk import large sets of data between SQL Server instances or export to a file. 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

Run the following out 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 command 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 username of the AWS 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

Run the following in command to import all records from the dump file to the table.

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 on the Microsoft Learn website.