As DBAs we are constantly adapting to recent technologies. We are seeing an increase in organizations migrating to the cloud. However, there are some fundamentals that remain and one of those is proper index maintenance for your databases.

We utilize Ola Hallengren’s Maintenance scripts for all our clients. This is a widely used solution and often considered the “gold standard” for proper maintenance on SQL Server. We have been asked if this is supported in Amazon RDS? And the answer is “yes!” A quick look at Ola’s FAQ and you will find this:

“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.” (SQL Server Maintenance Frequently Asked Questions (hallengren.com))

In the case of Amazon RDS SQL, we can leverage the SQL Server Agent just as we would with a regular virtual machine. There are just a few considerations that differ from a traditional SQL Server engine.

First, we must create a separate database to host the maintenance scripts. (Some of you may already do this but make a note that we cannot make changes to the “master” database on an AWS RDS instance).

For the purposes of this demo, I went ahead and created a databased called “dbmaint”:

Next, go to Ola’s Download section and download and install the following scripts in the newly created “dbmaint” database:

CommandExecute.sql
CommandLog.sql
DatabaseintegrityCheck.sql
IndexOptmize.sql

Once that is setup you can simply create SQL Server Agent jobs to run whatever commands you would like. Just one final consideration is that you must exclude the “rdsadmin” database from any of your maintenance tasks. Here is an example of how you would want to run the index optimize procedure:

Execute dbo.IndexOptmize @Databases = ‘USER_DATABASES,-rdsadmin,’ @LogToTable = ‘Y’

Then simply schedule the jobs that best suit your environment and that should be it.