Amazon RDS for SQL Server Maintenance Overview

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.

Supported SQL Server Maintenance on Amazon RDS

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))

Creating a Dedicated Maintenance Database

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”:

Installing Ola Hallengren’s Scripts in RDS

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

Configuring and Scheduling Maintenance Jobs

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.

Best Practices for Amazon RDS SQL Maintenance

House of Brick focuses on cloud adoption & secure management for enterprise applications and databases. Talk to us today!