Infrastructure at your Service

Introduction

RDS provide automatic backup feature. This feature will backup the entire RDS instance. As a DBA sometimes you need to backup individual database, they are many reason for that (legal, migration, replication,…). Unfortunately individual database backups are not available within the RDS instance.

This post explains how you can enable the native database backup on RDS , as you are used to with an on-premises SQL Server instance.
To summarize we will create a S3 bucket on AWS to store the backups, create IAM role having the mandatory permission on the S3 bucket, create an RDS Option Group associated with the role and having the SQLSERVER_BACKUP_RESTORE option.

Of course you need to have a existing RDS instance running. I have one with a SQL Server 2017 EE.

Create a S3 bucket

If you do not already have one, create first a S3 bucket that will be your repository for the saving your database backups.
Open your s3 management console and click [Create bucket]

Enter a S3 bucket name and select the region where your RDS instance is located.
When done click [Create]

Create a IAM role

Open the AWS IAM management console and select [Policies} in the navigation pane and click [Create policy]

Select the Json tab and copy the following script to replace the existing one

{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Sid": "VisualEditor0",
			"Effect": "Allow",
			"Action": [
			"s3:ListBucket",
			"s3:GetBucketLocation"
			],
			"Resource": "arn:aws:s3:::dbi-sql-backup"
		},
		{
			"Sid": "VisualEditor1",
			"Effect": "Allow",
			"Action": [
			"s3:PutObject",
			"s3:GetObject",
			"s3:AbortMultipartUpload",
			"s3:ListMultipartUploadParts"
			],
			"Resource": "arn:aws:s3:::dbi-sql-backup/*"
		}
	]
}

In the script do not forget to replace the S3 bucket name with the one you created previously and click [Review policy]

Set a name for your policy and finish the creation with [Create policy]

Now, select [Roles] in the navigation pane. Click the option [Create role]

Select the [AWS service] option, then the [EC2] and finally again [EC2] in the use case list and click [Next:Permissions]

Search the policy you create and select it. Then click the [Next:Tags]. I recommend to capture some tags, to later be able to identify it easier. Click then [Next:Review]

Key in your role name and finally press [Create Role]

When created edit again the role by selecting it in the role list

Select the [Trusted relationship] tab and edit it.

Replace the line “Service”: “ec2.amazonaws.com” with “Service”: “rds.amazonaws.com” as we want this role to be active in RDS.
Click [Update Trust Policy]

Create an Option Group

Next step is to create an option group.
To do so, open the RDS management console and select [Option groups] in the navigation pane and click on [Create group]

Set the name of your option group and select the engine version and Major Engine Version of your RDS instance and create your option group.

Now select your option group in the list and add an option.

In the option detail section select SQLSERVER_BACKUP_RESTORE and in the IAM section select the role you created previously. In the scheduling option choose the option you want. In my case I want it to be applied immediately. Then click [Add option].

Link your RDS instance with your option group

The last configuration step is to couple your RDS instance with the create option group

In the RDS management console select [Databases] in the navigation pane and select your RDS instance for which you need native backup to be activated. Make sure a well that the version of the RDS instance match the one set in the option group you created previously. Check if you instance is available and then click [Modify]

Scroll down until the Database options section. In the option group combo box select the option group your created previously and then click [Continue] at the bottom of the page.

Choose when you want to apply the modification. Be aware that if you select “Apply immediately” your RDS instance will restart and you will have an interruption of the service.

Test the Backup

Connect to you RDS SQL Server instance using for exemple Microsoft SQL Sever Management Studio.
There is a stored procedure in the [msdb] named [dbo].[rds_backup_database] that you must use to start your native database backup

USE [msdb]

EXECUTE [dbo].[rds_backup_database]
@source_db_name = 'ProductDB'
,@s3_arn_to_backup_to = 'arn:aws:s3:::dbi-sql-backup/awsvdata_ProductDB.bak'
--,@kms_master_key_arn
--,@overwrite_s3_backup_file
--,@type
--,@number_of_files
GO

 

Adapt the script with your database name and the path of your S3 bucket with the backupfile name. Note that I did not used all parameters of the stored procedure in this post.
The result of the stored procedure execution will give you a task_id associated with your command.

With the task_if, you can follow up the status of the process with the following stored procedure:

Use [msdb]
execute rds_task_status @task_id = 4

Conclusion

Enabling Native database backup is indeed very practical. Unfortunately there are some limitations.

For instance there is no differential, transaction log or filegroup backup or restore possibility that could be very useful in many scenario.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Christophe Cosme
Christophe Cosme

Consultant