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?
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
@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
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.
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.
