I had to migrate a SQL 2014 database from our on-prem environment to AWS. The requirement was to use RDS. So, these are the steps that I’ve taken in order to successfully migrate the database. You can also check the official AWS link here.
First, take a backup of the existing database in BAK format. In my case for the purpose of this blog, I’ve backed up my vCenter database called VCDB in a file called VCDB.BAK. Once you have the file, create a S3 bucket in AWS with the default settings and upload the BAK file there. In my case the S3 bucket was named restoremybackup.
Next, make sure you have access to a Windows server with SQL Management Studio installed, either in AWS or on-prem, but this server will need to talk to the RDS database over port 1433 TCP. I built a server in AWS and installed the SQL Management Studio, which is a free download. Make sure that you have a security group in place that allows port 1433. We will use this security group to assign it to the RDS instance. I’ve created a new security group called sgAllowSQLAccess. For the source, type the IP of the server with SQL Management Studio or the security group itself.
Then, go to RDS and create an Option Group. Name the Option Group, in my case it’s ogSQL2014Restore, choose the engine and the version. I am using sqlserver-se which is SQL Standard Edition and the version is 12.00 which is MS SQL 2014.
Once created, select your Option Group and click on Add Option.
Choose SQLSERVER_BACKUP_RESTORE, then click on Create a New Role. For the role, use a name, in my case it’s iamRestoreSQLBackup. Then type the name of the S3 bucket that you just created and make sure that you select Apply Immediately – yes.
Then click on Add Option.
Now, it’s time to create the RDS database. Launch a new RDS instance and use the parameters that suit your needs.
For the next set of options, choose your preferred settings, but make sure that you assign the Security Group that we just created and the Option Group that we also created. In my case it looks like this.
Once the RDS it’s in available state, try to telnet to port 1433 to the RDS endpoint from the Windows server where you have the SQL Management Studio installed. If everything is OK, you are ready to restore the database. In SQL Management Studio, select a New Query and type the following.
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';
Replace ‘database_name’ and ‘bucket_name/file_name_and_extenstion’ with your values. In my case, they were VCDB and restoremybackup/VCDB.BAK.
