Is a maintenance plan for your SQL Server essential? Absolutely. Most people appreciate the need to back up data, but the importance extends to ensuring optimal database performance. Here are some important points to consider when creating a plan.
Maintenance Plan Basics for Back-ups
When deciding which back-up plans to put in place, do not take this process lightly. This is one of those rare times we advise you to think of the worst possible scenario. Could your business survive a loss of files or software? Could you start from scratch without any of your customers’ private data?
- Include all necessary databases in the back-up plan.
- Consider business needs for recovery when selecting the level of detail and frequency of back-up needed for each database.
- Save each back-up copy in a safe destination. (We can also discuss cloud options with you.) Choose a place that will be accessible if the primary instance of the database goes down and the back-up copy needs to be restored.
- Set back-ups to run at the least busy times of day when they can complete successfully without slowing down other jobs and negatively impacting overall system performance.
- Include a clean-up element to ensure back-ups are kept only as long as necessary to avoid wasting valuable storage space.
- TEST! Perform tests to ensure your maintenance plans run without error.
SQL Server Maintenance for Optimal Database Performance
Include these additional steps in your nightly process, and you can ensure your databases won’t begin to slow down as data is added on a regular basis.
These functions can be found on the SQL Maintenance Plan task selection screen (Image 1)
- Check Database Integrity
Performs internal consistency checks of the data and index pages within the database
- Rebuild Index
Improves performance of index scans and seeks, and optimizes the distribution of data and free space on the index pages, allowing faster future growth
- Update Statistics
Ensures the query optimizer has up-to-date information about the distribution of data values in the tables
- Clean Up History
Deletes historical data about Backup and Restore, SQL Server Agent, and Maintenance Plan operations; Note: During initial set-up, you may use the Maintenance Plan Wizard to specify the type and age of data to delete
- Maintenance Clean-up Task
Removes residual files from maintenance plan execution
MicroAccounting suggests setting up a maintenance plan that includes three jobs–one for system databases, one for user databases, and one for transaction logs. This will provide optimum flexibility for including the necessary tasks for each database type and customize the time schedule for each one.
If you need assistance assessing your current SQL Server Maintenance plans, or in setting up new ones, we’d love to help. Contact MicroAccounting today!