AWS / S3 – Management LifeCycle

Background

As one gets more comfortable with AWS RDS and S3,  discussions of backup files retention might arise.

That is how long are backup files kept.

 

Use Case

If you find yourself backing up files and using timestamps as part of your naming convention, you likely want to look into pruning files based on date.

Here is what our S3 bucket looks like:

 

Processing

Script

We can write scripts to prune backup files older than specific days.

Policy

Or we can manage retention via policies.

 

Overview

  1. We define life cycle rules at the bucket level
    • Keep in mind we do not go down to each folder
  2. Life-cycle Rule
    • Tab :- Name and Scope
      • Name
        • Offer a meaningful name
      • Scope
        • In our case we offer folder
          • full/
            • Full is the name of the folder
            • /
    • Tab :- Transitions
    • Tab :- Expiration
      • Permanently clean up after 3 days
    • Tab :- Review

 

 

Screen Shot

Amazon S3 – S3 Bucket [dbabackupmssql]

Tab – Overview

 

 

Tab – Management

 

 

Tab – Life-cycle rule – Name and scope

 

Tab – Life-cycle rule – Transitions

 

Tab – Life-cycle rule – Expiration

 

 

Tab – Life-cycle rule – Review

 

 

AWS/RDS – Backup/Restore – Error Messages

Background

Here are are some errors you might run into when you try to backup/restore a SQL Server database running on Amazon AWS RDS Instance.

Error Messages

Error Message Remediation
Msg 50000, Level 16, State 0, Procedure rds_backup_database, Line 350
Database backups can only be performed by members of db_owner or db_backupoperator roles in the source database
If you try to backup system databases ( master, msdb) or RDS Databases ( rdsadmin), please skip them
Msg 50000, Level 16, State 0, Procedure rds_backup_database, Line 287
A task has already been issued for database: AdminDB with task Id: 26, please try again later.
If you attempt to issue a new backup (rds_backup_database ) request while one is another running.
Msg 50000, Level 16, State 1, Procedure rds_backup_database, Line 58
Error executing procedure. Please provide appropriate backup type. Currently FULL and DIFFERENTIAL backup types are supported.
We can not log backup.  Only Full & Differential backups are supported.

 

AWS – RDS – SQL Server – Scheduling Database Backups

Background

Preparing for a meeting where we will be discussing our experience with Amazon AWS /RDS Deployment.

And, so it is time to document and share a bit more.

 

Code

  1. We will have a set of Stored Procedures
    •  dbo.usp_RDSDBBackupCore
      • The core SP
    • dbo.usp_RDSDBBackupFullAutomated
      • Manager
        • passes in targeted S3 folder and backup type

Stored Procedure

dbo.usp_RDSDBBackupCore



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


if object_id('[dbo].[usp_RDSDBBackupCore]') is null
begin

    exec('create procedure [dbo].[usp_RDSDBBackupCore] as ')

end
go

ALTER PROCEDURE [dbo].[usp_RDSDBBackupCore]
(
      @s3Bucket		varchar(100) 
    , @s3Folder		varchar(100) = null
    , @type			varchar(30) = 'FULL'
	, @addTS		bit         = 0
	, @dbName		sysname		= null
	, @scriptOnly	bit = 0

)
AS
BEGIN

	/*
		-- =============================================
		-- Author:		Daniel Adeniji
		-- =============================================
	*/
    SET NOCOUNT ON;
	SET XACT_ABORT ON;


	declare @tblDatabase TABLE
	(
		  [id] smallint not null
			identity(1,1)

		, [name] sysname not null
	)

	declare @tblDatabaseSkip TABLE
	(
		  [id] smallint not null
			identity(1,1)

		, [name] sysname not null

		, UNIQUE NONCLUSTERED 
			(
				[name]
			)
	)

	declare @id smallint
	declare @idMax smallint
	declare @db    sysname

	declare @s3arnBase			   varchar(30)
	declare @s3arn				   sysname
	declare @overwriteS3BackupFile int

	declare @CHAR_BACKSLASH        char(1)
	declare @CHAR_DASH			   char(1)
	declare @CHAR_COLON			   char(1)
	declare @CHAR_PERIOD		   char(1)
	
	declare @CHAR_EXT_BAK		   varchar(10)
	declare @CHAR_EXT_LOG		   varchar(10)
	declare @charExt               varchar(10)

	declare @backupTypeFULL		   varchar(15)
	declare @backupTypeLOG         varchar(15)

	declare @dtNow				   datetime
	declare @dateFormat			   smallint
	declare @TSAsString			   varchar(30)

	declare @log				   varchar(300)

	set    @s3arnBase = 'arn:aws:s3:::'


	set    @CHAR_EXT_BAK = '.bak'
	set    @CHAR_EXT_LOG = '.log'

	set    @backupTypeFULL = 'full';
	set    @backupTypeLOG = 'log';

	set @CHAR_DASH = '-'
	set @CHAR_BACKSLASH = '/'
	set @CHAR_COLON = ':'
	set @CHAR_PERIOD = '.'


	set @overwriteS3BackupFile = 1

	set @dateFormat = 126

	if (@type = @backupTypeLOG)
	begin

		set @charExt = @CHAR_EXT_LOG 

	end
	else if (@type = @backupTypeFULL)
	begin

		set @charExt = @CHAR_EXT_BAK 

	end
	else
	begin

		raiserror('Unsupported @type', 16,1)

		return
	end

	/*
		List databases that we will be skipping
			System Databases
				a) master
				b) tempdb
				c) model
				d) msdb
	*/
	 insert @tblDatabaseSkip
	 (
		[name]
	 )
	 select 'tempdb'
	 union 
	 select 'msdb'
	 union 
	 select 'master'
	 union 
	 select 'model'
	 union 
	 select 'rdsadmin'


	if (@dbName is not null)
	begin

		insert into  @tblDatabase 
		(
			[name]
		)
		select
			@dbname

	end
	else
	begin

		/*

			Skip Databases that are offline

				a) databasepropertyex(name, 'collation')	
						is null when a db is offline

		*/
		insert into  @tblDatabase 
		(
			[name]
		)
		select   tblSD.name

		from   sys.databases tblSD

		where  not exists
				(
					select [name]
					from   @tblDatabaseSkip tblDS
					where  tblSD.[name] = tblDS.[name]
				)

		and   databasepropertyex
				(
					   tblSD.[name]
					, 'collation'
				) 
					is not null

	end

	/*
		Get Number of databases
	*/
	set @idMax
			=
				(
					select max(id)
					from    @tblDatabase 
				)

	/*
		reset pointer
	*/
	set @id = 1

	while (@id <= @idMax)
	begin

		/*
			Get contextual data
		*/
		select 
				@db = tblD.name
		from   @tblDatabase tblD
		where  tbLD.id = @id

		if (@addTS = 1)
		begin

			set @dtNow = getdate()

			set @TSAsString = convert(varchar(30), @dtNow, @dateFormat)
			set @TSAsString = replace(@TSAsString, @CHAR_DASH, '')
			set @TSAsString = replace(@TSAsString, @CHAR_COLON, '')
			set @TSAsString = replace(@TSAsString, @CHAR_PERIOD, '')

		end

		/*
			Get computed data
		*/
		set  @s3arn =  @s3arnBase 
						+ @s3Bucket 
						+ case
							when @s3folder is null then ''
							when @s3folder = '' then ''
							else @CHAR_BACKSLASH + + @s3folder 
							end
							
						+ @CHAR_BACKSLASH
						+ @db
						+ case
							when (@addTS = 1) 
								then '__' + @TSAsString
							else ''
						  end	
						+ @charExt

		/*
			issue backup command
				note that backup is not completed, only issued
		*/
		set @log = 's3arn :-' + @s3arn

		print @log

		if (
				   ( @scriptOnly = 0)
				or ( @scriptOnly is null)
		   )
		begin

			exec msdb.dbo.rds_backup_database 
				  @source_db_name= @db
				, @s3_arn_to_backup_to=@s3arn
				, @overwrite_S3_backup_file = @overwriteS3BackupFile
				, @type = @type

		end

		/*
			prepare for next record
		*/
		set @id = @id + 1

	end


END
GO



dbo.usp_RDSDBBackupFullAutomated



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[usp_RDSDBBackupFullAutomated]') is null
begin

    exec('create procedure [dbo].[usp_RDSDBBackupFullAutomated] as ')

end
go

ALTER PROCEDURE [dbo].[usp_RDSDBBackupFullAutomated]
AS
BEGIN


    SET NOCOUNT ON;

    declare  @s3Bucket  varchar(100) 
    declare  @s3Folder  varchar(100) 
    declare  @type	    varchar(30)
	declare  @addTS		bit

    set @s3Bucket = 'dbabackupmssql'
    set @s3Folder = 'full'
    set @type = 'FULL'
	set @addTS = 1

    exec [dbo].[usp_RDSDBBackupCore]
              @s3Bucket = @s3Bucket
            , @s3Folder = @s3Folder
            , @type = @type
	    , @addTS = @addTS


END
GO





Scheduler

Invoke Scheduler and scheduled job that calls the  dbo.usp_RDSDBBackupFullAutomated Stored Procedure.

Job Properties -Tab – General

 

Job Properties -Tab – Steps

 

Review

SQL Server

Stored Procedure

Stored Procedure – msdb.dbo.rds_task_status

Script

declare @dbName sysname

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

Output

 

S3

Access S3 Service and the specific folder and you should see the files.

 

Source Control

GitHub

DanielAdeniji/SQLServerBackup.AWS
Link

AWS/RDS – SQL Server – DB Restore – Error – “Aborted the task because of a task failure or a concurrent RESTORE_DB request”

Background

Issue “msdb.dbo.rds_restore_database“.  But, when database restore progress using “msdb.dbo.rds_task_status” received an  error message.

Error Message

Error Text

Aborted the task because of a task failure or a concurrent RESTORE_DB request

Error Image

 

Remediation

Review SQL Instance and ensure that Option Group is pointing at the right S3 bucket.

Screen Shots

Options Groups

Accessed “Option Groups“…

Selected Option by placing a check mark to the left of it, and clicking the Modify group

 

Options Group – …vs2017Express

Edit

  • Installed Options :- SQLServer_Backup_Option
  • S3 Bucket :- dba-bucket

 

 

Review Assignment

Once our OptionGroup has been assigned SQLSERVER_BACKUP_RESTORE and it has been granted access to specific S3 bucket.

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

Amazon AWS/SQL Server – Task Status

Background

Back in 2012, Amazon started offering support for MS SQL Server.

Recently they added support for whole database backup and restore.

 

Review

To review the status of current operation and past ones, one can issue a query against the msdb.dbo.rds_task_status Stored Procedure.

 

Metadata

msdb.dbo.rds_task_status

Syntax


declare @dbname  sysname
declare @taskID  int

exec msdb.dbo.rds_task_status 
		  @db_name = @dbname
		, @task_id = @taskID

Sample

Sample  – Review all tasks

SQL


declare @dbname  sysname
declare @taskID  int

exec msdb.dbo.rds_task_status 

Output

Grid

Sample  – Review tasks targeting specific database

Sample  – Review tasks targeting specific database ( datahub )


declare @dbname  sysname
declare @taskID  int

set @dbname = 'datahub'

exec msdb.dbo.rds_task_status 
		  @db_name = @dbname
		--, @task_id = @taskID

Output

Sample  – Review tasks targeting specific task

Sample  – Review tasks targeting specific database ( task )

If you initiated a task and recorded the task Number, you can go back and inquire about that task, as well.

Sample  – Review tasks targeting specific database ( task – Not Indicated)

If you indicate that you will be passing a task ID, but pass in a null one, you will get an error.


declare @dbname  sysname
declare @taskID  int

--set @taskID = 

exec msdb.dbo.rds_task_status 
     @task_id = @taskID

Output

Grid

Textual

Msg 50000, Level 16, State 1, Procedure rds_task_status, Line 36 [Batch Start Line 2]
Could not find the specified task. Execute without any parameters to show all tasks.