Migrating SQL Server to Amazon RDS Part 1 – Backup/Restore from S3
You can migrate your Microsoft SQL Server databases into Amazon RDS in multiple ways. With multiple ways, comes various pros and cons along with them. In this three- part series, I’ll describe each of the primary methods that you can leverage for migrating your SQL Server databases to Amazon RDS.
In this blog post, we are going to start with the simplest path to migration, using a practice that a majority of production DBAs perform each day – backup and restore.
Since Amazon RDS supports native backup and restore for Microsoft SQL Server databases using full backups, you can now access backup files stored in Amazon S3 just as you would using the local file system on your database server. This leads to many advantages using the native backup and restore. A few of these advantages include the ability to migrate databases to and/or from Amazon RDS, migrate databases between Amazon RDS SQL Server instances, and create copies of databases for development or testing purposes.
There are also a number of limitations to using the native backup and restore. A few of these disadvantages include only being able perform a backup to, or restore from, an S3 bucket within the same AWS Region as your RDS instance, backups on databases larger than 1 TB in size not being supported, and restores on databases larger than 4 TB in size not being supported.
There are three main components that you will need on the Amazon side for the native backup and restore migration method.
- An Amazon S3 bucket to store your backup files. This can be an existing bucket, a newly created bucket, or you can choose to have a new bucket created when adding the SQLSERVER_BACKUP_RESTORE option. In this blog post, I am using an existing bucket.
- An AWS IAM role to access the bucket. This can be an existing role, a manually created role, or choose to have a new IAM role created when adding the SQLSERVER_BACKUP_RESTORE option group. In this blog post, I am allowing a new IAM role to be created while adding the option group.
- The SQLSERVER_BACKUP_RESTORE option added to an option group on your Amazon RDS instance.
For this post, we are assuming that you have already performed a full backup of your database and have copied the .BAK file to your S3 bucket. If you are new to copying files to Amazon S3, stay tuned for my next blog post about how to use AWS CLI to copy files to S3!
Before we can restore from S3, we have to enable the backup and restore option for our Amazon RDS instance; otherwise, you will encounter the below error:
We enable this from the AWS Management Console within the RDS service category and under Option groups to list existing option groups that you have. There are default option groups utilized when creating your Amazon RDS instance, unless otherwise specified, so we will show creating a new group.
When creating a new group, we need to specify a name, description, the database engine and major engine version as shown below.
Once created, we then can add options to this new group.
Now that we created our new option group and added the SQLSERVER_BACKUP_RESTORE option to it, we need to apply this option group to our Amazon RDS instance. In order to do this, we stay within the RDS services on the AWS Management Console, select our RDS instance, and then select Modify.
Scroll until you see Database options, and select our newly created option group within the Option group dropdown.
After making our modifications, a summary page confirms the changes to our RDS instance.
After selecting Modify DB Instance, you will notice the status of your instance will now show as “Modifying.”
This is only required to be done once for our RDS instance. From this point forward, we will be able to natively backup and restore. Additionally, now that we have an option group created with the SQLSERVER_BACKUP_RESTORE option, we can apply this option group when creating new RDS database instances.
Now we are ready to connect to our RDS database instance and start restoring our database from S3!
To restore the database backup file copied to S3, we will need to call the rds_restore_database stored procedure, as shown below, rather than our typical RESTORE DATABASE or GUI operation.
The required parameters are:
- @restore_db_name – name of the database to restore
- @s3_arn_to_restore_from – Amazon S3 bucket that contains the backup file, file name, and extension
Upon executing this stored procedure, we will immediately get results similar to those below. This is only telling us that the task has been issued and provides various details on that task specifically.
Once we see success for the task life cycle, we know our database restore was successful and our database is now available on our Amazon RDS instance!
While this method is the simplest, it typically requires the most downtime for your migration, as your downtime window would be the duration of the backup file being created, copied to S3, and then restored. If this downtime window is not be acceptable for your large, highly available databases, tune in to parts 2 and 3 of this series to find the alternative solutions for your migration!
Thanks for reading!