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

Resolution

Methods for migrating data

Native SQL Server backup and restore

You can use native backup and restore to migrate the SQL Server database from an on-premises or Amazon EC2 instance to an Amazon RDS for SQL Server instance. To do so, complete the following steps:

1. Create an Amazon Simple Storage Service (Amazon S3) bucket to store the backup from the source instance. The Amazon S3 bucket must be in the same AWS Region as the Amazon 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 Amazon RDS for SQL Server instance.

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

5. Run the following script to restore the backup to the Amazon 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';

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

   exec msdb.dbo.rds_backup_database  
   @source_db_name='database_name',  
   @s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',   
   @overwrite_S3_backup_file=1;

   Note: You can also backup and restore differential backups.

AWS Database Migration Service (AWS DMS)

Complete the following steps:

1. Verify the prerequisites and limitations of using SQL Server as a source or target for AWS DMS.
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 Amazon RDS for SQL Server instance. However, you can only make the Amazon 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 push subscription.

To set up transaction replication from an on-premises or Amazon EC2 SQL Server instance, see the following instructions:

• Migrating to Amazon RDS for SQL Server using transactional replication: Part 1
• Migrating to Amazon RDS for SQL Server using transactional replication: Part 2

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 about 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.

Methods for importing data

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.

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 Amazon EC2 SQL Server database to the Amazon RDS for SQL Server instance. You can filter the specific tables you want to copy to the Amazon 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 you run the Import and Export Wizard, make sure that you choose the following options for the Destination Amazon RDS for SQL Server instance:

• For Server Name, enter the name of the endpoint for the Amazon RDS DB instance.
• For Authentication mode, choose SQL Server Authentication.
• For the Username and Password, enter the main user that you created in the Amazon 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 previous 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 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: Use this command to import all records from the dump file to the existing table. You must create the table before you run 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.