Maintenance Plan Recommendations Guide

Database Maintenance Plans are a key piece to an effective Disaster Recovery Plan. Microsoft SQL Server Management Studio allows operators to set up email notifications on success and failure actions of steps in a maintenance plan. This is an efficient way of finding out when issues are occurring without having to log in to the server and monitor the plans. In total, New World ERP should at least have an Optimization Plan, a plan that includes a backup of the database (could be part of the Optimization Plan) and a plan that backs up the New World ERP transaction log files.

Step-by-Step

Setting Up Operators for Database Mail

  1. First we need to create an operator in SQL.
  2. Open up Microsoft SQL Server Management Studio on the database server.
  3. Expand the SQL Server Agent.
  4. Right click on Operators.

  5. Click New Operator.

  6. Assign an email address and name to the Operator. Click OK.

Viewing the Optimization Plan

  1. Maintenance Plans are set up in a Graphical User Interface (GUI) in Microsoft SQL Server Management Studio. Typically one or more will already be set up.
  2. Open Microsoft SQL Server Management Studio on the database server.
  3. Expand Management, then expand Maintenance Plans.
  4. Look for a plan called something like Nightly Maintenance, New World ERP Optimization or Nightly Optimization. This example is called Conference Optimization.

  5. Double clicking on the plan brings it up in the right hand side of the GUI.

  6. The key components to any New World ERP Optimization Plan are the Check Database Integrity Task, a Rebuild Index Task, and Update Statistics.
  7. A Back Up Database Task is optionally included in the Optimization Plan, but it needs to occur nightly. This step is often in its own plan.
  8. The Maintenance Cleanup Task removes old database backup files that are older than the configured age on the step in the plan. In this case, backups older than 4 days would be removed by the plan on a successful run.

Configuring the Optimization Plan (non-existing)

  1. Open Microsoft SQL Server Management Studio on the database server.
  2. Expand Management and right click on Maintenance Plans.

  3. Click New Maintenance Plans.

  4. Drag the components into the plan one by one from the left hand pane. Order the tasks like the image above in step 5 of Viewing the Optimization Plan.
    1. Check Database Integrity Task
    2. Rebuild Index Task
    3. Update Statistics Task
    4. Back Up Database Task
    5. Notify Operator Task (3)
    6. Maintenance Cleanup Task

  5. Continue when you see something like the image below:

  6. When the above picture looks similar to what is on your environment, right click on Check Database Integrity Task and click Edit.

  7. Choose your live and/or test database and continue.
  8. Right click on Rebuild Index Task in the Maintenance Plan Graphical User Interface and click Edit.
  9. Choose your database as in Step 7 and continue.
  10. Right click on Update Statistics Task in the Maintenance Plan Graphical User Interface and click Edit.
  11. Choose your database as in Step 7 and make sure to choose Column statistics only on the initial Edit screen.
  12. After setting the Update Statistics Task, right click on Back Up Database Task and click Edit.

  13. Ensure the Backup type is Full. Choose your database and then provide a path to back up the database to the server or NAS. Keep in mind that several days of backups will be going to this directory for as many databases as are chosen in this step.

  14. Right click on Maintenance Cleanup Task and click Edit.

  15. Configure the task to delete files from the same directory from above (with a life of 3 to 7 days). Ensure the sub folders are being scanned for stale files.

  16. After clicking OK on the Maintenance Cleanup Task, it is time to draw the arrows to link the plan together.
  17. Draw an arrow from Check Database Integrity Task to Rebuild Index Task. You can activate the arrow by clicking on the Check Database Integrity box.

  18. The arrow should be green for Success.

  19. Draw another green arrow from Rebuild Index Task to Update Statistics Task.

  20. The next green arrow would go from Update Statistics Task to Back Up Database Task.
  21. Draw another arrow from Back Up Database Task to Maintenance Cleanup Task.

  22. At this point the plan should resemble the following image:

  23. It is time to link in Database Main for notification of Operators on failure and success.
  24. Draw an arrow from Check Database Integrity Task to the first Notify Operator Task.

  25. Right click on this arrow and choose Failure. If Check Database Integrity fails, there is a chance of data corruption in the database. The arrow will change to a red color after choosing failure.

  26. Right click on the Notify Operator Task that an arrow was just connected to. Choose Edit.

  27. Choose an operator to send the email to. Enter a subject and note to the Operator on what to look for.

  28. Draw a similar Failure arrow from Back Up Database Task to the second Notify Operator Task. Perform the same setup from Step 27 to configure the email. This notification is helpful in the event of the database server running out of hard drive space.
  29. Draw another arrow from the Maintenance Cleanup Task ot the final Notify Operator Task. This one should be a success arrow. Configure the email as in Step 27, this time notifying the operator that everything has finished successfully and the database is running optimally.

  30. In the end, the plan should look similar to the image below.

  31. Finally, a schedule needs to be applied to the plan to ensure it runs daily.

  32. At the top of the Maintenance Plan GUI, you will see a scheduling area. Click on the Calendar icon on the right side of the screen.

  33. Configure the process to run nightly at a time when other processes are not running on the server. Maintenance Plans can severely hinder performance while the plan is running.

  34. The final product should look like the image below, with all steps configured and a schedule running on the server.

Configuring/Viewing a Transaction Log Maintenance Plan

  1. Transaction Logs allow for hourly rollback points during the middle of the day in case of database corruption.
  2. A Transaction Log backup should occur every hour during the working day in the Maintenance Plan as part of the Disaster Recovery Plan.
  3. The Transaction Log Backup plan is much simpler to complete than the Optimization Plan. This example is the Conference TRN Log plan.

  4. The same principles applied above would be used, with the difference of the schedule being run hourly during the workday and the Backup being done on a Transaction Log backup type as well as a trn File Extension.

  5. The Maintenance Cleanup Plan should clean up .trn extensions instead of .bak extensions like above.

  6. The final plan should resemble the following: