This article explores the options Snow Commander administrators have available in AWS RDS when creating maintenance plans, used to ensure the database is functioning optimally. When looking at RDS performance, it's important to understand each of these decisions, as a setting appropriate for one environment may not be ideal for another.

It's possible that your organization already has some maintenance in place. If so, Snow still recommends having your database admins review this article to make sure our best practices are followed. If you're uncertain of what makes the most sense for you, contact Snow Technical Support to discuss. 

Important! All of the queries and examples in this article are prepared for a database named vcommander.

Creating the Index Defragmentation Task

Over time, database indices will become fragmented and lead to reduced performance on all read/write operations. This is especially true in very active environments where there is a lot of inventory churn. To combat this performance degradation over time, run the queries included in the zip file attached to this article against the Commander database. These create tables and a stored procedure used to schedule a defragment task in the maintenance plan, ensuring optimal database performance.

The queries must be run in the following sequence:

1. CommandLog.sql
2. CommandExecute.sql
3. IndexOptimize.sql

Next, create a SQL Server Agent Job:

1. In the Object Explorer, expand SQL Server Agent.
2. Right-click Jobs and choose New Job...
3. On the General page, provide the Name Defrag Commander DB.

4. Switch to the Steps page and click New...
5. Enter the Step Name Execute Command.
6. Set Type to Transact-SQL script (T-SQL)
7. Set the Database to the commander database.
8. Enter the following as the Command, replacing commander with the name of your commander database, then click OK.

EXECUTE [dbo].[IndexOptimize] @Databases = 'commander', @LogToTable = 'Y', @TimeLimit = '3600', @UpdateStatistics = 'ALL'

9. Switch to the Schedules page and configure a recurring schedule.  Snow Software recommends the Index Defragmentation be run on a weekly basis during a period of low activity.
10. Optionally, switch to the Notifications page. Check Write to the Windows Application event log: When the job fails if you are able to monitor the log for failures and wish to do so. Otherwise, configure any other notification options as suitable. 
11. Click OK.

Creating the Database Corruption Detection

Adding a database integrity check protects you from continuing to operate with a database that's been corrupted, but because the operation is resource-intensive, Snow Software recommends that this only occur during maintenance windows or periods of very low activity.

Next, create a SQL Server Agent Job:

1. In the Object Explorer, expand SQL Server Agent.
2. Right-click Jobs and choose New Job...
3. On the General page, provide the Name Commander DB Corruption Check.
4. Switch to the Steps page and click New...
5. Enter the Step Name Execute Command.
6. Set Type to Transact-SQL script (T-SQL)
7. Set the Database to the commander database.
8. Enter the following as the Command, replacing commander with the name of your commander database, then click OK.

DBCC CHECKDB ('commander') WITH NO_INFOMSGS, ALL_ERRORMSGS;

9. Switch to the Schedules page and configure a recurring schedule.  Snow Software recommends the Corruption Detection be run in a maintenance window because the operation is resource-intensive.
10. Switch to the Notifications page. Check Write to the Windows Application event log: When the job fails if you are able to monitor the log for failures and wish to do so. Otherwise, configure any other notification options as suitable. 
11. Click OK.

See Also

• Best practices for Amazon RDS
• Backing up and restoring an Amazon RDS DB instance