Installing Ola Hallengren SQL Server Maintenance Solution on AWS RDS

I've used the Ola Hallengren Maintenance Solution across various SQL Server environments . I was recently asked by a colleague about how adaptable they are to the AWS RDS SQL Server environment. 

I checked the Ola Hallengren FAQ and there is a comment :

              “Is the SQL Server Maintenance Solution supported on Amazon RDS for SQL Server?The integrity check and the index and statistics maintenance parts of the SQL Server Maintenance Solution is supported on Amazon RDS for SQL Server. Backup is performed by the automated backup feature of Amazon RDS”

That was a good starting point to understand the limitations. The goal was to install the scripts with as little customisation as possible . One of the benefits of maintaining minimal customisation is 

By default the scripts are installed on the master database. On  RDS - by default there is an admin account - which doesn't have CREATE permissions on the master database. That was a straighforward change to the use master 

The fix was to create a maintenance database and install the script son this maintenance db.   

The following stored procedures were created 

CommandExecute
Commandlog
DatabaseintegrityCheck
IndexOptimise

The following SQL Server Agent Jobs were created. 

CommandLog Cleanup
DatabaseIntegrityCheck - USER_DATABASES
IndexOptimize - USER_DATABASES

If you execute the a job including all USER_DATABASES - you'll likely get an error  similar to "Cannot find the object "rdsadmin.dbo.log_backup_manifest" because it does not exist or you do not have permissions."

Avoid this error by excluding the rdsa database from being managed e.g use  "-rdsadmin"  , this is a supported syntax on the Ola scripts. The below values on @Databases is to apply the IndexOptimize databases on all user database except "rdsa". 

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES,-rdsadmin',
@LogToTable = 'Y'