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

Metrics

Before

Bucket Size Bytes ( bytes / day )

 

Number of Objects ( count / day )

After

Bucket Size Bytes ( bytes / day )

Number of Objects ( count / day )

References

  1. Amazon
    • AWS
      • AWS Documentation » Amazon Simple Storage Service (S3) » Console User Guide » Storage Management » How Do I Create a Lifecycle Policy for an S3 Bucket?
        • How Do I Create a Lifecycle Policy for an S3 Bucket?
          Link
      • » Amazon Simple Storage Service (S3) » Developer Guide » Working with Amazon S3 Objects » Object Versioning
        Object Versioning

        • Object Versioning
          Link

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