Follow

Database Maintenance and Sample Plans

Created by: Justin Johns
Created date:
Last Updated date:

DATABASE MAINTENANCE

Databases require ongoing maintenance to prevent poor application performance, system downtime, and data loss. There is no one-size-fits-all solution to database maintenance and regular attention must be given to ensure the continued successful operation of any maintenance plan, but the implementation of a basic maintenance plan is a relatively simple undertaking. The following guidelines can be used to assist in the development of a basic Microsoft SQL Server maintenance plan.

(Summary of best-practice maintenance recommendations - http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx#id0230078)

For additional information on SQL database maintenance, please see the footnotes included within this section or contact your AccessData technical support representative. Additional assistance with database maintenance, including the development of customized plans and professionally-staffed on-going maintenance, is available through AccessData’s Support Services department. Please contact your AccessData salesperson for additional information and pricing.

DATABASE RECOVERY MODEL

The selection of a database recovery model is the first decision that must be made when developing a SQL maintenance plan. The recovery models provided by Microsoft SQL Server are meant to address varying levels of resource availability and acceptable data loss.

(Detailed information on Microsoft SQL recovery models - http://technet.microsoft.com/en-us/library/ms189275(v=sql.105).aspx)

AccessData recommends the use of the Full Recovery model with user databases, but supports the use of either the Full Recovery model or the Simple Recovery model.

DATABASE BACKUP STRATEGY

A database backup strategy is generally the focus of any maintenance plan. While primarily meant to protect against data loss, database backups may also be necessary to address other significant maintenance requirements. Microsoft SQL Server supports three primary database backup methods: Full, Differential, and Log:

  • The Full backup method creates a complete record of a database. A Full backup record provides the ability to restore a database to a single point-in-time.
  • The Differential backup method requires the existence of a Full backup and creates a record of any extents that have been modified since the Full backup was created. A Differential backup record in combination with its associated Full backup record provides the ability to restore a database to a single point-in-time.
  • The Log backup method creates a record of all transactions made in a database since the last Log backup. A Log backup record in combination with its associated Full and Differential backup records provides the ability to restore to any point from the time of the Full backup record to, contingent on the success of a tail log backup, the most recent transaction in the database.

(Detailed information on Microsoft SQL backup methods and strategies - http://technet.microsoft.com/en-us/library/ms187048(v=sql.105).aspx)

The Full and Differential backup methods are available to both the Simple and Full recovery models. The Log backup method is unavailable under the Simple recovery model. The output of any backup method should be directed to a location that is not being used to store active SQL database files (e.g., MDF, NDF, or LDF files).

NOTE: LOG BACKUPS MUST BE TAKEN FOR ANY DATABASE USING THE FULL RECOVERY MODEL; THE FILE CONTAINING THE DATABASE’S TRANSACTION LOG WILL OTHERWISE CONTINUE TO GROW INDEFINITELY.

AccessData strongly recommends that, at minimum, full backups of both system and user databases are made regularly. Additional complexity and scheduling will be dictated by criteria such as acceptable work-loss exposure, the speed and volume of storage available for both the data files themselves and the backup records, and the maintenance’s impact on the overall performance of the application.

DATABASE INDEX OPTIMIZATION

Microsoft SQL server uses indexes to quickly query commonly used data and improve operation. Rebuilding and reorganizing these indexes is important to the overall performance of the application.

(Detailed information on Microsoft SQL index optimization - http://technet.microsoft.com/en-us/library/ms190910(v=sql.105).aspx)

As modifications are made to the tables within a database, the associated indexes will be subject to internal (i.e., excessive, unused memory allocated to the indexes) and external fragmentation (i.e., indexes that are stored non-sequentially) which can degrade performance. Regular reorganization (i.e., reordering an index within its existing allocated memory) and rebuilding (i.e., reordering the index into freshly-allocated contiguous memory) of fragmented indexes is necessary to counteract the results of this activity.

The AccessData Summation Pro solution performs database index optimization following certain activities, but AccessData recommends performing scheduled index optimization regularly in concert with Full backups.

(Note: Scheduled index optimization should be performed prior to the Full backup; scheduled index optimization performed after a Full backup can dramatically increase the size of the subsequent differential and log backup records.)

DATABASE INTEGRITY CHECKS

Database integrity checks are a method by which any logical or physical issues in a database can be identified. Depending on the severity of an identified issue, a database can either be repaired or restored to a point prior to the genesis of the damage.

(Detailed information on Microsoft SQL database integrity checks - http://msdn.microsoft.com/en-us/library/ms176064(v=sql.105).aspx)

AccessData recommends that integrity checks be run prior to any Full backup to help ensure the integrity of the database contained within the backup.

MAINTENANCE CLEANUP

Maintenance cleanup is a necessary piece of any maintenance plan which must be explicitly run to remove old backup files and other unnecessary maintenance records.

(Detailed information on Microsoft SQL maintenance cleanup - http://msdn.microsoft.com/en-us/library/ms345177(v=sql.105).aspx)

AccessData recommends establishing a regular maintenance cleanup schedule based on the Full backup schedule and organizational backup retention policies.

 

SAMPLE SQL MAINTENANCE PLANS

The section below contains a pair of hypothetical SQL maintenance plans. Please note that the maintenance tasks outlined below are for demonstrative purposes only and may not be appropriate for your environment. AccessData Global Support Services offers paid solutions tailored to help with the design, implementation, and continued administration of SQL database maintenance plans. Please contact Global Support Services or your sales representative for additional information.

SAMPLE MAINTENANCE PLAN ONE: SIMPLE RECOVERY MODEL

Job #1: Full Backup (System Databases)

Description: Performs an integrity check and full backup on all system databases.

Schedule: Occurs every day at 12:00:00 AM.

Step 1. Check the integrity of the system databases.

Step 2. Perform a Full backup of the system databases.

Job #2: Full Backup (User Databases)

Description: Performs an index optimization, integrity check, and full backup on all user databases.

Schedule: Occurs every day at 12:00:00 AM.

Step 1. Defragment the indexes and update the statistics of the user databases.

Step 2. Check the integrity of the user databases.

Step 3. Perform a Full backup of the user databases.

Job #3: Differential Backup (User Databases)

Description: Performs a differential backup on all user databases.

Schedule: Occurs every day every 6 hours between 6:00:00 AM and 11:59:59 PM.

Step 1. Perform a Differential backup of the user databases.

Job #4: Cleanup

Description: Deletes all backup and job history records that are older than 30 days.

Schedule: Occurs every week on Sunday at 12:00:00 AM.

Step 1. Execute sp_delete_backuphistory.

Step 2. Execute sp_purge_jobhistory.

FULL RECOVERY MODEL MAINTENANCE PLAN

Job #1: Full Backup (System Databases)

Description: Performs an integrity check and full backup on all system databases.

Schedule: Occurs every day at 1:00:00 AM.

Step 1. Check the integrity of the system databases.

Step 2. Perform a Full backup of the system databases.

Job #2: Full Backup (User Databases)

Description: Performs an index optimization, integrity check, and full backup on all user databases.

Schedule: Occurs every week on Saturday at 1:00:00 AM. 

Step 1. Defragment the indexes and update the statistics of the user databases.

Step 2. Check the integrity of the user databases.

Step 3. Perform a Full backup of the user databases.

Job #3: Differential Backup (User Databases)

Description: Performs a differential backup on all user databases.

Schedule: Occurs every week on Monday, Tuesday, Wednesday, Thursday, Friday, and Sunday at 1:00:00 AM.

Step 1. Perform a Differential backup of the user databases.

Job #4: Transaction Log Backup (User Databases)

Description: Performs a transaction log backup on all user databases.

Schedule: Occurs every day every 4 hours between 12:00:00 AM and 11:59:59 PM.

Step 1. Perform a Log backup of the user databases.

Job #5: Cleanup

Description: Deletes all backup and job history records that are older than 30 days.

Schedule: Occurs every week on Sunday at 12:00:00 AM.

Step 3. Execute sp_delete_backuphistory.

Step 4. Execute sp_purge_jobhistory.

Was this article helpful?
2 out of 2 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk