How to Migrate SQL Server Database running on EC2 instance to Amazon RDS Instance

Recently i worked on migrating sql server databases running on amazon EC2 instances to Amazon RDS instances. Starting from 27th Jul 2016 Amazon announced you can ship your databases from ec2 to rds using native backup and restore method. Let’s see how it works.

Steps:

  • Assume you have RDS Instance ready with default setting
  • Create a custom option group “NativeBackup” and enable S3 bucket full access. You may use the below IAM rule
RDSAccessToS3
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "s3:*",
"Resource": "*"
}
]
}
  • Right click on Custom option group and then click on Add option –> Select SQLSERVER_BACKUP_RESTORE option — > Apply Immediately

rds_sql_server_add_backup_option_1

  • Now you have custom group setup with native backup and restore feature and correct permissions to S3 bucket. Now, Add this group to RDS instance. Right click on RDS instance –> Click on Modify –> Navigate to Option group —> Assign the NativeBackup group which was created in the step 2 –> Click Modify instance

1

  • Now, Copy the backup of EC2 instances to S3 bucket. You may use AWS CLI command line tool to copy from one EC2 instance to S3 bucket
Aws s3 cp \\ec2backuplocations <a href="//Infra_Bucket" target="_blank" rel="noreferrer">s3://</a>Buket_Name &nbsp;--profile “your profilename"
  • Once you are done with copying the backup to S3 location, then you may use the below script to restore the database from backup
    
    use master
    
    go
    
    exec msdb.dbo.rds_restore_database
    
    @restore_db_name='TestDB',
    
    @s3_arn_to_restore_from='arn:aws:s3:::Bucket_Name/testdb.bak';
  • Restore will begin, you may use the below stored procedure to get the status of the restore process
    exec msdb.dbo.rds_task_status
  • That’s all about the restore! Below are the possible errors you may encounter while migrating to RDS instance

Error1:

Aborted the task because of a task failure or a concurrent RESTORE_DB request.
Database TestDB cannot be restored because there is already an existing database wih the same family_guid on the instance.

Reason: If you have same file guid for data or log file exists then you cannot restore to rds instance. The file guid’s are unique to each database. There is a possibility to get the same fileguid based on the name.

You may use sys.master_files view to get the details of file guid

Another reason for this error is you have already restored the same database and trying to restore, hence the error. RDS has the limitation that you cannot restore the same database file names on the same instance.

Resolution: Do not restore the same database names on the same RDS instance

Error2:

Msg 229, Level 14, State 5, Procedure rds_restore_database, Line 1 [Batch Start Line 45]
The EXECUTE permission was denied on the object ‘rds_restore_database’, database ‘msdb’, schema ‘dbo’.

Reason: You do not have permission to perform the restore

Resolution:

Please make sure the login you used have execute permission for the stored procedure rds_restore_database. If you are using master credentials and you still getting the same error then you have not granted full permission to S3 bucket or missing IAM roles for the S3 bucket. Please grant the access and re-try

Error3:

Msg 50000, Level 16, State 0, Procedure rds_restore_database, Line 73 [Batch Start Line 22]
A task has already been issued for database:TestDB  with task Id: 3, please try again later.

Reason: You have issued the restore command multiple times and hence the error.

Resolution: Do not issue the restore command twice for the same database. Kill the multiple restore processes and re-issue the command

References:

https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/

https://www.brentozar.com/archive/2016/07/holy-cow-amazon-rds-sql-server-just-changed-everything/

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

Published by Ramasankar

Hi. I’m Ramasankar Molleti. I’m a passionate IT professional with over 14 years of experience on providing solutions for customers who are looking on cloud computing, Database Migration, Development, and Big Data. I love learning new technologies and share my knowledge to community. I am currently working as Sr Cloud Architect with focus on Cloud Infrastructure, Big Data. I work with developers to architect, build, and manage cloud infrastructure, and services. I have deeep knowledge and experience on working with various database platforms such as MS SQL Server, PostgeSQL, Oracle, MongoDB, Redshift, Dyanamodb, Amazon Aurora. I worked as Database Engineer, Database Administrator, BI Developer and successfully transit myself into Cloud Architect with focus on Cloud infranstructure and Big Data. I live in USA and put my thoughts down on this blog. If you want to get in touch with me, contact me on my Linkedin here: https://www.linkedin.com/in/ramasankar-molleti-23b13218/ My Certifications: Amazon: AWS Certified Solutions Architect – Professional AWS Certified DevOps Engineer – Professional certificate AWS Certified Big Data – Specialty AWS Certified Security – Specialty certificate AWS Certified Advanced Networking – Specialty certificate AWS Certified Solutions Architect – Associate Microsoft: Microsoft® Certified Solutions Associate: SQL Server 2012/2014 Microsoft Certified Professional Microsoft® Certified IT Professional: Database Administrator 2008 Microsoft® Certified Technology Specialist: SQL Server 2008, Implementation and Maintenance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: