Migrating Microsoft SQL Server Enterprise Workloads to Amazon RDS

October 2023: This post was reviewed and updated for accuracy.

If you’re thinking about moving your on-premises Microsoft SQL Server databases to Amazon RDS, one option to migrate your databases is to use native SQL Server backups. You can also use Amazon Database Migration Services to migrate your data to RDS. In this blog post, we will focus on SQL native backup, restore approach. Native backups can be uploaded to AWS using Amazon Simple Storage Service (Amazon S3). These backups can also be encrypted using the AWS Key Management Service (AWS KMS). Once the backup has been uploaded to Amazon S3 from source, you can then restore your databases onto an RDS for SQL Server instance. You can also use this feature to:

• Move databases between Amazon RDS SQL Server DB instances.
• Backup and restore individual databases, as opposed to entire RDS instances.
• Store and transfer backups in and out of RDS while preserving complete compatibility with native SQL Server installations.
• Move database backups to RDS to use as a disaster recovery solution for on-site SQL Server installations.
• Move databases from Amazon EC2 to Amazon RDS for SQL Server

Setting Up

RDS offers option groups to enable certain features on an RDS instance. You can associate an option group with multiple options. Each option typically represents one feature. Depending on the feature, the option might have additional settings to be configured.

To enable native backups on your RDS instances, you create an option group, add the SQLSERVER_BACKUP_RESTORE option, and assign it to an RDS instance. The steps following show how.

1. Choose Option Groups on the navigation pane in the RDS dashboard in the AWS Management Console.
2. Choose Create Group to create a new option group.Note: You can also select one of your existing groups and add the option. However, the default option groups cannot be modified. The default groups start with the word “default” and can be easily identified. So, unless you already have an option group that you have manually created in the past, you will have to create a new one. Manually created option groups are referred to as Customer Option Groups.
3. Type the option group name, description, engine type and engine version and choose Create.Note: Option groups are specific to particular RDS engines, engine editions, and major engine versions. If you have multiple RDS instances running on different editions and versions of SQL Server, you’ll have to create an option group for each edition and version combination.
4. Select the option group that you created in the previous step and choose Add Option.
5. In the Add Option section:
   1. Select the option name SQLSERVER_BACKUP_RESTORE from the drop down.
      
   2. Choose an IAM role of your choice. Typically in an on-premises setting, DBAs back up databases to disk and move the backup to external storage by using a file transfer mechanism. However, backups made by using this feature in RDS are directly uploaded to S3. So, RDS needs permissions to access your S3 bucket to upload and download database backups. To provide this access to RDS, you can provide your own AWS Identity and Access Management (IAM) role. This approach comes in handy when you already have an IAM role that contains a bucket where you want RDS to place backups or retrieve backups from. Or you can let RDS set up an IAM role for you in the background by providing the necessary information. If you let RDS set up the IAM role:
      • You can specify an existing S3 bucket or create a new one.
      • An S3 prefix is optional. We recommend it only in cases where you want to restrict access to a shared bucket to the files in folders with that specified prefix.
      • You can enable encryption, to encrypt and decrypt the backups using KMS. If you enable encryption, you’ll need to specify a KMS key to encrypt or decrypt backups.
   3. Click on Apply immediately for the addition of the option to be done immediately or you can also schedule it to be applied in your next maintenance window.

Once the option group creation succeeds, you can see the IAM role that was created in the option groups’ screen. You can also edit it by going to the IAM service dashboard.

6. You can assign the new option group to an existing or a new RDS instance. To assign the option group to an existing RDS instance, navigate to the RDS dashboard, choose Instances, and then select the instance that you want to associate the option group with. For Instance Actions, choose Modify. You can modify the option group associated with the instance under Database Options:

Note: After you have associated the instance with the option group, it will take a few minutes for the modifications to complete. When the instance returns to available status, you can start using the feature related to that option group with your RDS instance.

Please follow the steps mentioned in the post “Backup SQL Server databases to Amazon S3” to upload the files to s3. You can also refer to the limitations with the native backup restore approach in RDS in this documentation.

Troubleshooting Errors during Setup

For possible errors during setup, some recommendations follow.

This error occurs when RDS can’t generate temporary credentials to access an S3 bucket using the IAM role specified. In this case, it’s likely that the IAM role provided doesn’t give RDS access to the S3 bucket. To fix this issue, you can modify the role and provide RDS access (rds.amazonaws.com) under the trust relationship tab of the IAM role.

This error occurs when the AWS console is unable to retrieve existing IAM roles, which happens if IAM service is unavailable temporarily. To address this issue, try again later.

Backing Up or Restoring a Database
To perform backups and restores, RDS provides four stored procedures in the MSDB system database. The master user can run or grant access to others to run these stored procedures. Following are the stored procedures and their details.

Backup Task Stored Procedure

Restore Task Stored Procedure

Task Status Stored Procedure

Task Cancellation Stored Procedure

After you issue a backup or restore task by using one of these stored procedures, RDS outputs task information as shown following. You can also retrieve this information later by running the rds_task_status stored procedure.

The following table gives some details about these columns.

Troubleshooting Backup and Restore Errors
When you run the stored procedures, you might encounter some validation errors. These errors are displayed immediately, and a task will not be created if you encounter these errors. You can find common validation errors and suggested fixes following—here, assume your database is named AdventureWorks.

Sometimes, the backup or restore task is created successfully but you encounter errors afterward and the task errors out. You can find the related error information in the task_info column that is displayed by running the rds_task_status stored procedure. Following, you can find some common errors and how you can correct them.

Conclusion

In this blog post, we demonstrated how to create a new option group, add the SQLSERVER_BACKUP_RESTORE option to it and attach it to the RDS SQL Server instance. This enables the RDS SQL Server instance to allow native backups and restores from s3 which can be very helpful in migration scenarios.

Try out the solution and please feel free to leave comments and ask any questions below.

About the authors

Prudhvi Janga is a software development engineer in Amazon Web Services.

Nirupam Datta is a Cloud Support DBA at AWS. With over 11 years of experience in database engineering and infra-architecture, Nirupam is a Subject Matter Expert in in Amazon RDS core systems and Amazon RDS for SQL Server. He provides guidance and technical assistance to customers, assisting them to navigate their journey in the AWS Cloud.

Audit History

Last reviewed in October 2023 by Nirupam Datta | Cloud Support DBA