This article explores the options Embotics® vCommander® administrators have available within 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 that's the case, Embotics® 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 Embotics® Technical Support to discuss. 

 

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

 

Follow best practices https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_BestPractices.html


    Monitor CPU/Memory/Storage usage


Follow AWS guide for backing up and restoring backups.  Embotics requires frequent backups to be performed.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_CommonTasks.BackupRestore.html


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 this zip file (files also attached to this article below) against the vCommander 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.

 



  • Switch to the Steps page and click New...
  • Enter the Step Name Execute Command.
  • Set Type to Transact-SQL script (T-SQL)
  • Set the Database to the commander database.
  • 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'
  • Switch to the Schedules page and configure a recurring schedule.  Embotics recommends the Index Defragmentation be run on a weekly basis during a period of low activity.
  • 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. 
  • 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, Embotics 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;

 

  • Switch to the Schedules page and configure a recurring schedule.  Embotics recommends the Corruption Detection be run in a maintenance window because the operation is resource-intensive.
  • 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. 
  • Click OK.