AWS-RDS :- Backing up SQL Database

Background

Backing up and restoring databases is one of the common tasks performed by DBAs.

Because of the regularity of the tasks, the DBA often schedules them to periodically occur and walks away.

Outside of occasionally alerts due to storage issues, life is good and fancy free.

 

AWS/RDS

Snapshots

In the Amazon RDS World, snapshots can be configured to auto-occur and life remains drama free.

 

S3 Storage

To facilitate husbandry of databases in-house and general safe-keeping, AWS now supports actual database backups.

Backup

Outline

The steps are:

  1. S3
    • Provision S3
  2. AWS Identity and Access Management IAM Role
    • Setup IAM Role
      • Setup Trust Policy for Native Backup and Restore
      • Setup Permissions Policy for Native Backup and Restore
  3. RDS Instance
    • RDS Instance Option Group
      • Option Groups
        • SQLSERVER_BACKUP_RESTORE

 

Steps

Step – S3

For the sake of brevity we will not touch upon provisioning a new S3.

We will instead focus on switching roles to a predefined role that has access to the S3 bucket.

Need to know
  1. S3 bucket
    • It is customal to have various S3 buckets in an organization
    • Determine the one pre-assigned to the group
    • Understand the folder structure
Permissions

In our original setup, my personal account did not have permission to the S3 bucket.

Permissions was granted to specific roles, and not to individual user’s personal accounts.

And, so if at first you fail to see S3 folders or the ones that you are looking for, please do not be too dissuaded,  as permissions might have been granted to specific roles instead.

 

Step – Identity & Access Management ( IAM )

Just as for S3, Identity and Access Management ( IAM ), is another big area and so we will skip the details here.

There are two areas that are important and have to be satisfied to allow RDS to access S3 buckets.  Those two areas are:

  1. Trust Policy
  2. Permission Policy
Trust Policy
Syntax

{
    "Version": "2012-10-17",
    "Statement":
    [{
        "Effect": "Allow",
        "Principal": {"Service":  "rds.amazonaws.com"},
        "Action": "sts:AssumeRole"
    }]
}

Explanation
  1. Designate that principal rds.amazonaws.com is allowed access to the S3 Service

 

Permission Policy
Syntax
{
    "Version": "2012-10-17",
    "Statement":
    [
        {
        "Effect": "Allow",
        "Action":
            [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
        "Resource": "arn:aws:s3:::bucket_name"
        },
        {
        "Effect": "Allow",
        "Action":
            [
                "s3:GetObjectMetaData",
                "s3:GetObject",
                "s3:PutObject",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload"
            ],
        "Resource": "arn:aws:s3:::bucket_name/*"
        }
    ]
}


Explanation
  1. Bucket
    • s3:ListBucket
      • List Bucket
    • s3:GetBucketLocation
      • Get S3 Bucket Location
  2. Bucket and sub-folders
    • s3:GetObjectMetaData
      • Get Object Metadata
    • s3:GetObject
      • Get Object
    • s3:PutObject
      • Put Object
    • s3:ListMultipartUploadParts
      • List Multipart upload parts
    • s3:AbortMultipartUpload
      • Abort Multipart Upload

Step – RDS Instance Option Group – Option Group – SQLSERVER_BACKUP_RESTORE

As said earlier, the option to perform database backup and restore is new to RDS.

The specific option’s name is SQLSERVER_BACKUP_RESTORE.

Need to know
  1. Review the SQL Server Instance
    • Review the Option group
      • Console
        • Sample
          • Region :- US-East-2
            • https://us-east-2.console.aws.amazon.com/rds/home?region=us-east-2#option-groups:

 

Step – Backup

After all that, we are finally at the point where we can backup the database

Metadata

The Stored Procedure that we will using is dbo.rds_backup_database.

Let us review how it should be invoked.

To do so we will use the sp_help command.

Syntax

exec msdb.dbo.sp_help 'dbo.rds_backup_database'

Output

Actual
Syntax

exec msdb.dbo.rds_backup_database 
          @source_db_name= @dbName
        , @s3_arn_to_backup_to=@s3arn
        , @overwrite_S3_backup_file=@overwrite

Sample

declare @dbName sysname
declare @s3arn  sysname
declare @overwriteS3BackupFile int

set @dbName = 'datahub';
set @s3arn = 'arn:aws:s3:::sqlServer/backup/datahub/datahub_20171207_0448PM.bak'
set @overwriteS3BackupFile = 1

-- exec  msdb.dbo.sp_help 'rds_backup_database'
exec msdb.dbo.rds_backup_database 
          @source_db_name= @dbName
        , @s3_arn_to_backup_to=@s3arn
	, @overwrite_S3_backup_file = @overwriteS3BackupFile



Output

Review
Syntax

declare @dbName sysname

exec msdb.[dbo].[rds_task_status]
	@db_name = @dbName

Sample

declare @dbName sysname

set @dbName = 'datahub';

exec msdb.[dbo].[rds_task_status]
	@db_name = @dbName


 

Output

 

References

  1. Amazon / AWS /RDS
    • AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Microsoft SQL Server on Amazon RDS » Importing and Exporting SQL Server Databases
      • Importing and Exporting SQL Server Databases
        Link
    • AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Microsoft SQL Server on Amazon RDS » Options for the Microsoft SQL Server Database Engine
      • Options for the Microsoft SQL Server Database Engine
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s