0

Very new to SQL on AWS RDS so.

Need to create maintenance jobs on an AWS RDS SQL2016 instance. I know parts of Ola Hallengren's life saver maintenance script works, Index, stats and integrity.

Picking through Ola's maint script it looks like there is logic that checks for RDS.

For anyone who has done this, how do I install it, do I run the scripts individually and against which DB?

3

The stored procedure DatabaseBackup is not supported on Amazon RDS.

As you note, there is logic that checks for the RDS platform.

If these checks return that your server is an RDS instance the following code block is encountered.

  IF @AmazonRDS = 1
  BEGIN
    SET @ErrorMessage = 'The stored procedure DatabaseBackup is not supported on Amazon RDS.'
    RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT
    SET @Error = @@ERROR
    RAISERROR(@EmptyLine,10,1) WITH NOWAIT
  END

Pretty self explanatory, eh? Sorry about that 😕

You're probably best served starting by browsing AWS docs for SQL Server backups on RDS

  • Hi Peter, thanks for that I know it isn't supported for the backup side I am resigned to letting AWS do that side, so far that's fine. I just want to get the DBCC checks, indexing implemented as I am seeing some performance issues .. I suppose my last stupid question is do I just run the maintenance script and let the logic do it's stuff and against which db? – Stockburn Sep 10 at 21:40
  • @Stockburn the @AmazonRDS is set and checked in the dbo.DatabaseIntegrityCheck source but it doesn't appear to be restricted on that platform. Looks like you may have already posted a question more specifically relevant to that topic though. Can't say I know the answer but I'll signal boost a bit for you. When in doubt though, get yourself a dev instance and just start pulling levers to see what happens ¯\_(ツ)_/¯ – Peter Vandivier Sep 11 at 7:03
  • Thanks for the update mate, good suggestion will be my next step. – Stockburn Sep 12 at 3:51
3

Adding this in the hope it helps others trying to test Ola's script in AWS RDS.

I have created a 'DBA' database on a test RDS instance and installed the following parts of Ola Hallengren's maintenance scripts to this new DB.

  1. CommandExecute
  2. Commandlog
  3. DatabaseintegrityCheck
  4. IndexOptimise

I did test running the full maintenance script but you get the following errors, but the script still created the agents jobs I needed for integrity / index.

Msg 262, Level 14, State 1, Line 69 CREATE TABLE permission denied in database 'master'. Msg 262, Level 14, State 18, Procedure CommandExecute, Line 1 [Batch Start Line 96] CREATE PROCEDURE permission denied in database 'master'.

I still hit errors when running the update statistics job because it was attempting to update them against the rdsadmin database, commenting this database out in the agent job means my job now works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.