Migrating Microsoft SQL Server Enterprise Workloads to Amazon RDS: Part 1
Prudhvi Janga is a software development engineer in Amazon Web Services
SQL Server Native Backups and Restores Using RDS and Amazon S3
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. 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, you can then restore your databases onto an RDS SQL Server instance. We recommend this option for bulk migration if having only a minimum downtime is not a critical requirement for you. You can also use this feature to:
- Move databases between Amazon RDS SQL Server DB instances.
- Back up 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.
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.
- Choose Option Groups on the navigation pane in the RDS dashboard in the AWS Management Console.
- 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.
- Type the option group name, description, and other details, 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.
- Select the option group that you created in the previous step and choose Add Option.
- In the Add Option section, fill in the details.
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 do one of two things. 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. We will cover this process in a separate blog post.
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.
- 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 anywhere from 10 to 15 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.
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.
|Cannot find database AdventureWorks.||This error shows up if you try to back up a database that doesn’t exist on the RDS instance.|
|Database AdventureWorks is not online.||If the database you are trying to back up is not online, you’ll see this error. Bring the database online and try again.|
|Parameter @source_db_name cannot be empty, please provide database name to backup.||If the source database name supplied to backup is empty (only contains spaces), you will see this error. Correct the name and try again.|
|Database backups can only be performed by members of db_owner or db_backupoperator roles in source database.||If you are not the database owner or don’t have a backup operator role for the source database you’re trying to back up, you will see this error. Get yourself access as db_owner or db_backupoperator in the database and try again.|
|Database backup/restore option is not enabled or is in the process of being enabled. Please try again later.||If the initial option group setup encountered an error or is incomplete, you will see this error. To correct the issue and try again, check instructions in Setting Up earlier in this blog post.|
|S3 object ARN cannot be empty.||Because the storage used by RDS for backups is S3, you will need to specify a valid S3 Amazon Resource Name (ARN). Find more about resource ARNs in Amazon Resource Names (ARNs) and AWS Service Namespaces in the AWS General Reference.
The S3 ARN should include the bucket name, an optional prefix, and either the name of the backup file you want created or the name of a file to restore from:
|Parameter @overwrite_S3_backup_file must be either 0 or 1.||If you have an existing backup file in S3 and you don’t specify the @overwrite_S3_backup_file parameter, you will get an error. This parameter is used to specify whether you want an existing backup file to be overridden with the latest backup or not. Here, 0 indicates that you don’t want to overwrite the file present in S3 if one already exists, and 1 indicates otherwise.|
|Cannot backup system databases or rdsadmin.||Only customer databases can be backed up. Correct the source database name and try again.|
|A task has already been issued for database: AdventureWorks with task Id: 1, please try again later.||If a backup task is already in progress on the source database specified, you will see this error. You can try again after the current task is complete.|
|Database restores can only be performed by database logins with [CREATE ANY DATABASE] permissions.||Only logins with CREATE ANY DATABASE privileges on the RDS instance can perform restores. Correct the permissions and try again.|
|No database name specified to restore.||To restore a backup, you have to specify a non-empty database name. Correct the target database name and try again.|
|Database name cannot start or end with a space.||Remove the starting or trailing spaces from the database name and try again.|
|Database AdventureWorks already exists. Cannot restore database with the same name.||You cannot restore a backup to an existing database or restore differential backups or transaction logs using this feature.|
|Cannot find a CREATED or an IN_PROGRESS task: 1 to cancel.||Only tasks that are in CREATED or IN_PROGRESS lifecycles can be canceled. The same error will also show up if the task ID you are looking for doesn’t exist.|
|Could not find the specified task. Execute without any parameters to show all tasks.||This error appears when you run rds_task_status with an invalid database name or task ID. You can run the stored procedure without any parameters if you don’t have the task ID handy.|
|Error executing procedure. Please reconnect to the RDS endpoint and retry.||You’ll usually see this error on mirrored instances if you are connected to a mirror after a failover. Reconnect to the endpoint and try again.|
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.
|Access Denied.||This error comes up when the IAM role setup in step 5 of Setting Up doesn’t provide sufficient access to RDS to access the bucket list or the bucket itself. Make sure that the IAM role is still associated with the option group and RDS instance and has sufficient privileges.|
|Task was initiated on server name: EC2AMAZ-ABCDEFG, current server name: EC2AMAZ-MNOPQRS. Cannot complete the task, please try again.||Usually you’ll see this error when a task is in progress on a mirrored instance and a failover happens. The new principal or primary does not know about the task in progress. This error can also appear when the task is in progress and the underlying EC2 machine has been replaced with a different one. In either case, reissue the task to solve the error.|
|Could not parse the amazon resource name string provided:
||This error means that the S3 ARN you have provided isn’t in the correct format. Correct the ARN and reissue the task. Find more about resource ARN format in Amazon Resource Names (ARNs) and AWS Service Namespaces in the AWS General Reference.|
|Unable to retrieve temporary credentials for: SQLSERVER_BACKUP_RESTORE||This error means that RDS couldn’t generate temporary credentials to access S3. Make sure the IAM role involved is still associated with the option group and the RDS instance and has sufficient privileges.|
|The S3 bucket location: US-EAST-1 does not match the RDS instance region: US-WEST-2. Please specify a bucket that is in the same region as RDS instance.||Currently, RDS only supports backups and restores to and from S3 in the same AWS Region as the RDS instance. Cross-region backup and restore is not available. However, you can use S3 cross-region replication to copy the backup from a bucket in one region to a bucket in another region. Find more on cross-region replication Cross-Region Replication in the Amazon S3 Developer Guide.|
|RESTORE_DB task is unable to locate the backup file in S3. Try again and specify an existing backup file.||A restore database task requires that a backup file exists in S3. If the task cannot find the backup file, this error is thrown. Correct the issue and try again.|
|BACKUP_DB task found the backup file already present in S3. Rename the existing file, or use the overwrite parameter to overwrite the existing file.||If a backup file already exists on S3 but the overwrite option was not specified for rds_backup_database, you will see this error. To overwrite the file on S3, set @overwrite_S3_backup_file to 1.|
|The KMS master key region: US-EAST-1 does not match the RDS instance region: US-WEST-2. Please specify a key that is in the same region as RDS instance.||If you see this error, the KMS key supplied to encrypt or decrypt the backup file is not in the same region as the RDS instance. Supply a KMS key that is present in the same region.|
|KMS master key is not enabled. Please correct the issue and try again.||If you see this error, the specified KMS key is not enabled to encrypt or decrypt the backup file. Enable the KMS key or use a different KMS key.|
|Please provide KMS ARN with a region name.||A KMS key requires a region name to be specified. Correct the key’s ARN and reissue the task. Find more about resource ARNs in Amazon Resource Names (ARNs) and AWS Service Namespaces in the AWS General Reference.|
|No encrypted envelope key present. Unable to generate S3 object metadata.||If the backup file does not contain the encryption metadata (the envelope key) required for decryption, you will get this error. Make sure you are using an encrypted backup to restore from.|
|No initialization vector present. Unable to generate S3 object metadata.||If the backup file does not contain the encryption metadata (the initialization vector) required for decryption, you will get this error. Make sure you are using an encrypted backup to restore from.|
|Object metadata (x-amz-key) incomplete, cannot decrypt the given S3 object.||If the backup file does not contain the encryption metadata (the envelope key) required for decryption, you will get this error. Make sure you are using an encrypted backup to restore from.|
|Object metadata (x-amz-iv) incomplete, cannot decrypt the given S3 object.||If the backup file does not contain the encryption metadata (the initialization vector) required for decryption, you will get this error. Make sure you are using an encrypted backup to restore from.|
|Cannot generate S3 chunks that are greater than 524288000 bytes to upload to S3.||If the task errors out with this message, the size of the backup for the specified database is greater than the currently supported maximum size of 1 TB.|
|There is not enough space on the disk to perform restore database operation.||Before a restore starts, RDS makes an estimate about how much disk space will be required by the database that is going to be restored. If that estimate exceeds the amount of free space left on the disk, you will see this error. You can restore the database to a different RDS DB instance that has enough disk space.|
|Database AdventureWorks cannot be restored because there is already an existing database with the same file_guids on the instance.
Database AdventureWorks cannot be restored because there is already an existing database with the same family_guid on the instance.
|RDS does not allow restoring a copy of the same database onto the same DB instance. A copy of the same database is defined as one for which the family_guid or file_guids values are the same.
For example, if there are two full database backups in S3 for AdventureWorks called AdventureWorks_2015 and AdventureWorks_2016, you can only restore one of them to one RDS DB instance. If you run into this problem, you can fix it two ways:
|THE IN-PROGRESS TASK COULD NOT BE RETRIEVED. RE-ISSUING THE TASK.||Sometimes, if the backup or restore task encounters an error it can’t recover from, the task is reissued on customer’s behalf by RDS automagically.|
|Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automated backup.||This generic error message appears whenever a backup task encounters any of the error messages preceding. If this is the only error message in task_info, the task has been aborted because of a conflict with RDS automated backups. To avoid this conflict, you can temporarily turn off automated backups and either reissue your backup or issue your backup outside the backup window.|
|Unable to retrieve family guid for database name: AdventureWorks||You might see this error when the database you have restored contains a large log file and SQL Server is performing crash recovery. Shrink the log file on premises before creating a backup, uploading it to S3, and issuing the restore task.|
|Unable to retrieve reserved database size.||If RDS can’t retrieve the database size from the database on the RDS DB instance to estimate the backup size, you will see this error. This error can happen if the database is not online. Bring the database online and try again.|
|Empty restore file list result retrieved.||If a restore task cannot retrieve the file list information from the database backup, you will see this message. Make sure the backup file in S3 isn’t corrupt.|
|The specified bucket does not exist||This error means that the bucket name in the S3 ARN does not exist. Make sure the bucket exists and is associated with the IAM role, and try again.|
|Cannot find server certificate with thumbprint ‘0xE82F1BCC5F20DMNO24334331’.||Backups encrypted with Transparent Data Encryption (TDE) aren’t supported by backup and restore. As an alternative to TDE, you can encrypt the backup using a KMS key on-premises and restore the encrypted backup onto an RDS DB instance. Find more info about encrypting a file using KMS in Protecting Data Using Client-Side Encryption in the Amazon S3 Developer Guide. Another option is to just use server-side encryption. However, client-side encryption is the preferred approach.|
In addition to these errors, you might receive errors from SQL Server if there are problems performing a backup or restore. For example, you might receive errors when restoring from a backup file that is not backward compatible, restoring a database greater than 10 GB on a SQL Server express instance, or restoring when the backup is not correctly formed, to name a few.
Stay tuned for our next blog post on migrating enterprise workloads using AWS Data Migration Service (AWS DMS).