How to automatically back up and perform recommended maintenance for Enterprise Vault SQL databases

Article:TECH74666  |  Created: 2009-01-19  |  Updated: 2014-07-28  |  Article URL http://www.symantec.com/docs/TECH74666
Article Type
Technical Solution


Issue



This article describes the suggested steps for how an administrator can automate backups and perform recommended maintenance procedures for Enterprise Vault (EV), Compliance Accelerator (CA), and Discovery Accelerator (DA) SQL databases using SQL Server maintenance plans.


Solution



This document is a compilation of several documents noted in the Related Documents section below.  It contains suggested steps that anyone authorized to manage SQL Servers can perform to create and administer a SQL Maintenance Plan to backup and maintain Enterprise Vault (EV), Compliance Accelerator (CA) and Discovery Accelerator (DA) databases.  SQL Database Administrators (DBAs) often have their own preferred method to perform backups and maintenance.  Those methods should take precedence over these suggested steps as long as the basic recommended functions are performed.  Those basic functions are, in order:
  • Backup each EV, CA and DA database.
  • Backup and truncate each database's transaction log.
  • Reorganize or rebuild the indexes on the most heavily used tables (i.e., tables with over 1,000 pages of data).
  • Update the database statistics.
Important Note - Shrink Database option discussion:
The suggested steps below also contain instructions to add an optional function of shrinking the database.  Shrinking the database should only be done to reorganize the pages within the database toward the beginning of the database file.  Such reorganization may allow for improved database performance as long as that function is followed by backing up and shrinking the transaction log, then rebuilding / reorganizing indexes and updating statistics.  Shrinking the database should not include the option to return file space back to the operating system unless there is a critical need for that space to be returned.  As noted on Page 37 of the "Symantec Enterprise Vault 10.0: SQL Best Practices Guide" (see DOC5356 in the Related Articles section below):
Note: The database maintenance plan should not include a data file shrink, to avoid unnecessary file growths.  However the database log files may need to be shrunk after backing up.
Shrinking the database file size can cause requests to grow the file sooner than would be needed for optimum performance.  When a request is submitted to grow the database file, processing in the database is paused until the new space is available for use.  This is why the optional step to shrink the database is included but without the option selected to return space to the operating system.
 
One of the best times to perform a shrink database operation is after an upgrade of EV, CA or DA as this might involve migrating and dropping one or more tables.  However, how regularly a database shrink operation is performed should be carefully reviewed by the SQL DBA responsible for the databases as SQL Server manages the white space (empty pages within the database file) quite well.
 
A shrink operation increases the fragmentation of table indexes; therefore, a shrink operation must be followed by the index maintenance operation of reorganizing or rebuilding the indexes.  For additional information on this topic, see the Microsoft MSDN article at http://msdn.microsoft.com/en-us/library/ms190488.aspx.
 
A shrink operation also causes the database statistics to become stale; therefore, an update of the statistics must also follow a shrink operation.  Updating the statistics and rebuilding / reorganizing the indexes can be done in any order after the shrink operation.
 
After a shrink operation with the option to return space to the operating system has completed, the database size will have been reduced to the minimum possible size.  Actions such as table index maintenance performed afterward will cause a growth in the database size.
 
 
Please refer to Technical Article TECH168905 in the Related Documents section below for a discussion of SQL index fragmentation and statistics.
 
Before performing any of the procedures below, ensure that the SQL Server Agent is running and the user executing maintenance plans has sysadmin rights on the SQL server.
 
The following instructions apply only to EV environments that:

•    Use Microsoft SQL Server 2005, 2008, or 2008 R2.
•    Do not have a third party backup software that utilizes a 'SQL Agent' to perform logical backups of SQL databases. This process allows SQL database backups, or dumps, to occur outside of any other schedule.

Symantec recommends regular backups of all EV, CA, and DA SQL databases. During backups, EV services must either be placed in read-only or backup mode or stopped, and the CA/DA Enterprise Vault Accelerator Manager Service (EVAMS) or each Customer Background Task must be stopped. For steps on placing services in read-only/backup mode specific to the implemented EV version, please see the Related Articles section at the end of this document.
 
  1. Create a backup location for EV, CA, and DA databases (for example, C:\EVDatabaseBackups).
     
  2. Open SQL Server Management Studio.
     
  3. Browse to Management | Maintenance Plans.
     
  4. Right click Maintenance Plans and select Maintenance Plan Wizard.
     
  5. A screen shot of the SQL Server Maintenance Plan Wizard Window initial page appears below -

    SQL Server Maintenance Plan Wizard
     
  6. Click Next and the Select Plan Properties window appears. Set the following properties:

    Select Plan Properties window

    •    Name: Enter a name for the maintenance plan (for example, BackupEVDatabases).
    •    Description: Enter a brief description for the Maintenance Plan.
    •    Select Single schedule for the entire plan or no schedule from the list of scheduling options,
     
  1. Click the Change button to set the schedule.
     
  2. In the Job Schedule Properties window, provide the following properties:

    Job Schedule Properties - set Name, Schedule type and Frequency

    •    Name: Enter a name for the Job Schedule or use the default.
    •    Schedule Type: Select Recurring from the drop-down list.
    •    Occurs: Choose a frequency equivalent to the frequency of EV Backups ((Daily, Weekly,     Monthly).
    •    Recurs every: Select the available options depending on the frequency of the backup. Note: While configuring this option, consider the status of EV services. During the SQL backup, the EV services should be in backup or read-only mode and CA/DA services should be stopped.
    •    Daily frequency: Choose the time the database backup should begin. Note: Consider the status of EV, CA and DA services while configuring this option. During SQL backup, EV services should be placed in backup/read-only mode and/or CA and/or DA services should be stopped OR all EV, CA, and DA services should be stopped.
  1. Click Ok, and then click Next.
     
  2. On the Select Maintenance Tasks page, select Back Up Database (Full), Back Up Database (Transaction Log), and Maintenance Cleanup Task

    Select Maintenance Tasks
     
  3. Click Next.
     
  4. The Select Maintenance Task Order window appears.

    Select Maintenance Task Order window
     
  5. Use the Move Up and Move Down buttons to order the Maintenance Tasks as follows:

    a. Backup Database (Transaction Log)
    b. Back Up Database (Full)
    c. Maintenance Cleanup Task
    Note: After a full backup, Symantec recommends to perform the following tasks in order to avoid DB fragmentation: Shrink database, Rebuild Indexes, Update Statistics.
  1. Click Next and set the following in the Define Backup Database (Full) Task window

    Define Backup Database (Full) Task window

    •    Database(s): Select the appropriate EV, CA, and DA databases to be backed up.
    •    Backup set will expire: Specify the required date when the backup set can be overwritten.
    •    Back up to: Select Disk.
    •    Select Create a backup file for every database.
    •    Create a sub-directory for each database: This option can be enabled to organize     database and transaction log backups.
    •    Folder: Browse to the backup location created in Step 1.
     
  2. Click Next.
     
  3. Set the following in the Define Backup Database (Transaction Log) Task window:

    •    Database(s): Select the appropriate EV, CA, and DA databases to be backed up.
    •    Backup set will expire: Specify the required date when the backup set can be overwritten.
    •    Back up to: Select Disk.
    •    Select Create a backup file for every database.
    •    Create a sub-directory for each database: This option can be enabled to organize the database and transaction log backups.
    •    Folder: Browse to the backup location created in Step 1.
     
  4. Click Next and then Next again.
     
  5. The Select Report Options window appears

    Select Report Options window

    •    By default, the Write a report to a text file is enabled with the default SQL Server.
    •    Additionally, the Email report option can be enabled.
    For information on configuring E-Mail servers in SQL Server, please see the SQL Documentation regarding Database Mail.
     
  6. Click Next.
     
  7. The Complete the Wizard window appears.
     
  8. Review the information displayed and click Finish.
     
  9. After the installation has finished, click Close.
     

Database and transaction log backup files

When the Maintenance plan is executed automatically or manually, backups of selected databases and transaction logs are added to the backup location created in Step 1.
Database Backup file names use the format <DatabaseName_backup_yyyymmddhhmm.bak>
Transaction log backup files use the format <DatabaseName_backup_yyyymmddhhmm.trn>
 
DatabaseName represents the name of the database; yyyy, mm, dd, hh, mm represent the year, month, day, hour, and minute respectively.
Examples:

•    Database Backup: EnterpriseVaultDirectory_backup_200908070030.bak.
In this example, the EnterpriseVaultDirectory database was backed up on August 07, 2009 at 12:30AM
 
•    Transaction Log  Backup:  EnterpriseVaultDirectory_backup_200907062330.trn.
In this example, the EnterpriseVaultDirectory transaction log was backed up on July 06, 2009 at 11:30pm

For additional assistance in configuring backups in SQL, please see the SQL Administration documentation. For information on EV environments using Microsoft SQL Server 2000, please refer to the Related Articles section at the end of this document.  
 

 
Verifying successful execution of the SQL backup maintenance plan
For Microsoft SQL Server 2005, 2008, or 2008 R2:
  1. Open SQL Server Management Studio.
     
  2. Expand Management | Maintenance Plans.
     
  3. Right click on the Maintenance Plan created to back up EV, CA, and DA databases and select View History.
     
  4. The Log File Viewer window appears with the maintenance plan selected.

    Log File Viewer window
     
  5. Successful jobs are shown with a green check mark; failed or unsuccessful items are highlighted with a white X inside a red circle.
     
  6. To determine where the job failed, expand each item listed in the Log File Viewer.

    For additional assistance in troubleshooting SQL Server Maintenance Plans, please see the SQL Administration documentation regarding Maintenance Plans or contact Microsoft Support.

Verifying successful completion of SQL backups

For Microsoft SQL Server 2005, 2008, or 2008 R2:
  1. Open SQL Server Management Studio.
     
  2. Expand Databases.
     
  3. Right click on the Database and select Properties.
     
  4. The Database Properties window appears.

    Database Properties
     
  5. Under Backup, dates and times of the last successful SQL Database and Transaction Log backups are listed.


Creating a database maintenance plan for EV, CA, and DA SQL databases

Symantec recommends to perform the following SQL maintenance procedures weekly for all EV, CA, and DA databases in the following order, with all appropriate EV services stopped to prevent normally running background tasks from holding any table open during the maintenance actions:

1. Backup Databases
2. OPTIONAL: Shrink Databases
3. Backup and Truncate Transaction Logs
4. Rebuild Indexes
5. Update Statistics
 
As noted in the discussion at the beginning of this section and in Step 2 above, the Shrink Database procedure is optional and inclusion in a maintenance plan is subject to the discretion of the SQL DBA responsible for the databases.  The Shrink Database procedure is highly recommended for CA and DA databases and suggested for Vault Store databases due to continued insertions and deletions in database tables that cause disorganized ‘white space’ to develop within the data files.  Such actions are, but not limited to:

  • Storage Expiry removing 1000's of items from the Vault Store database(s).
  • CA or DA searches being frequently run and rejected as part of testing search criteria.
  • CA Departments or DA Cases being deleted when their usefulness has ended.
  • DA Cases being enabled and disabled for Analytics processing.
  • Upgrades to the software have completed, as these upgrades often create temporary tables that are removed as part of the upgrade completion processing.

‘White space’ is used to describe table density or gaps between data. This ‘white space’ may result in more reads required to read through a table as well as making the Statistics and Indexes stale. Shrink database allows the freed space to remain with the files and moves the data to the front of the file space. It removes the ‘white space’ while allowing the remainder of unallocated space to be used for file growth. But once these files are moved to the front of available drive space, statistics and indexes are no longer viable and need to be updated. This is why Shrinking the database can cause SQL latency if not properly performed by being followed by the index rebuild / reorganize and update statistics procedures. However, if properly executed, the SQL performance will improve after shrinking the database.  This performance improvement is due to reduced needs to go through the database file to access needed data.

While running the shrink database process, users must adhere to the following three factors in order to improve SQL performance:

a. Retain freed space in database files - this allows the now unused space within the database to be used before requesting space for database growth, as this growth request pauses all processing in the database until the new space is made available.
b. Rebuild Indexes - as noted above, moving the data to the 'beginning' of the database file causes the referenced data in the indices to become invalid, which can cause table scans instead of index usage.
c. Update Statistics - also as noted above, moving the data to the 'beginning' of the database file causes the existing statistics to become stale, which can cause references to indexes to be unreliable and affect database performance badly.
 
For additional information regarding SQL maintenance for Indexes and Statistics, and how an EV, CA, and/or DA environment is affected, please refer to the Related Articles section at the end of this document.
 
Note: Performing these tasks on a regular basis with the appropriate EV service(s) stopped allows EV, CA, and DA to perform optimally.  Stopping the services prevents any background processing from locking resources needed to completely implement the actions for each selected operation (i.e., prevents background tasks from locking a table to prevent its indexes from being reorganized or rebuilt).

Note: The procedures in this topic include steps to perform backup of all selected EV, CA, and DA SQL databases. If the backups are performed by another job/task and confirmed successfully complete, skip those steps from this procedure. Symantec recommends to complete a FULL database backup and stop ALL EV, CA, and DA services before starting any maintenance.

NOTE: If EV services cannot be stopped for weekly maintenance then the maintenance can be run while the services are running. However, Symantec recommends to stop services once per month to perform maintenance. This allows for a more effective maintenance process. In some environments, it may also be necessary to run Update Statistics daily to maintain performance. The daily Update Statistics can be run while the EV services are running.
 

  1. Create a backup location for the EV, CA, and DA databases (For e.g. C:\EVDatabaseBackups)
     
  2. Open SQL Server Management Studio.
     
  3. Browse to Management | Maintenance Plans.
     
  4. Right click on Maintenance Plans and select Maintenance Plan Wizard.
     
  5. The SQL Server Maintenance Plan Wizard window appears.

    SQL Server Maintenance Plan Wizard
     
  6. Click Next.
     
  7. In the Select Plan Properties window, provide the following properties: 

    Select Plan Properties
    •    Name: Enter a name for the Maintenance Plan (for example: EVDatabaseMaintenance)
    •    Description: Enter a brief description for the Maintenance Plan.
    •    From the list of scheduling options, select Single schedule for the entire plan or no schedule.
     
  8. Click the Change button.
     
  9. In the Job Schedule Properties window, provide the following properties:

    Job Schedule Properties

    •    Name: Enter a name for the Job Schedule or use the default
    •    Schedule Type: Select Recurring from the drop-down list
    •    Occurs: Choose Weekly
    •    Every...week(s) on: Select 1 and check a day of the week.
    Note: While configuring this option, consider the status of EV services. The EV, CA and DA services should be stopped.
    •    Daily Frequency: Select the time the database maintenance should be performed. Note: While configuring this option, consider the status of EV services. All EV, CA and/or DA services should be stopped.
     
  10. Click Ok, then Next.
     
  11. The Select Maintenance Tasks Window is displayed.

    Select Maintenance Tasks window
     
  12. From the list of tasks, choose
    Shrink Database (this process is optional, but highly recommended - see the discussions at the beginning of the Solution section and this section for more information)
    Rebuild Index
    Update Statistics
    Back Up Database (Full)
    Back Up Database (Transaction Log)
    Maintenance Cleanup Task (note that the only information needed for this task is the specification of where the log files are to be written)
     
  13. Click Next.
     
  14. The Select Maintenance Task Order window is displayed.

    Select Maintenance Task Order
     
  15. Using the Move Up and Move Down buttons, order the Maintenance Tasks as follows:
    I. Back Up Database (Full)
    II. Shrink Database (optional process that must be in the order prior to the Rebuild Index and Update Statistics processes if selected for inclusion in the maintenance)
    III. Backup Database (Transaction Log)
    IV. Rebuild Index
    V. Update Statistics
     
  16. Click Next.
     
  17. In the Define Backup Database (Full) Task window provide the following:

    Define Backup Database (Full) Task

    •    Database(s): Select the appropriate EV, CA, and DA databases to be backed up.
    •    Backup set will expire: Specify the required date when the old backup set can be overwritten.
    •    Back up to: Select Disk.
    •    Select Create a backup file for every database.
    •    Create a sub-directory for each database option can be enabled to organize the database and transaction log backups.
    •    Folder: Browse to the backup location created in Step 1.
     
  18. Click Next.
     
  19. The Define Backup Database (Transaction Log) Task window appears.
    •    Database(s): Select the appropriate EV, CA, and DA databases to be backed up.
    •    Backup set will expire: Specify the required date when the old backup set can be overwritten.
    •    Back up to: Select Disk.
    •    Select Create a backup file for every database.
    •    Create a sub-directory for each database option can be enabled to organize the database and transaction log backups.
    •    Folder: Browse to the backup location created in Step 1.
     
  20. Click Next.
     
  21. Optional step if the Shrink Database process has been selected - In the Define Shrink Database Task window, set the following properties:

     Define Shrink Database Task window

    •    Databases: Select the appropriate EV, CA, and DA databases.
    •    Shrink database when it grows beyond: Use the default value of 50 MB.
    •    Amount of free space to remain after shrink: Use the default value of 10 %.
    •    Choose Retain freed space in database files.
     
  22. In the Define Rebuild Index Task window, set the following properties:

    Define Rebuild Index Task window

    •    Databases: Select the appropriate EV, CA, and DA databases.
    •    Choose the option Reorganize pages with the default amount of free space.
    •    If running SQL Server 2005, 2008 or 2008 R2 Enterprise Edition, the Keep index online while reindexing option will be available. It is recommended not to enable this option as any database indexes currently loaded in memory will be skipped.
     
  23. Click Next.
     
  24. The Define Update Statistics Task window is displayed. Set the following properties:

    Define Update Statistics Task

    •    Databases: Select the appropriate EV, CA, and DA databases.
    •    Update: Select All existing statistics.
    •    Scan type: Select Full scan
     
  25. Click Next.
     
  26. In the Select Report Options window, provide the following properties:
    •    Write a report to a text file: This option is enabled by default with the default SQL Server installation directory.
    •    Email report: This option can additionally be enabled. For additional assistance in configuring E-Mail servers in SQL Server, please see the SQL Documentation regarding Database Mail.
     
  27. Click Next.
     
  28. The Complete the Wizard window is displayed. Review the information displayed and click Finish.
     
  29. After the installation has finished, click Close.

When the Maintenance plan is executed automatically or manually, backups of selected databases and transaction logs are added to the backup location created in Step 1. The indexes are rebuilt, statistics are updated, and database as well as transaction log files are shrunk to the size specified in Step 21 if possible.
 

Verifying successful execution of the SQL database maintenance plan

  1. Open SQL Server Management Studio.
     
  2. Expand Management | Maintenance Plans.
     
  3. Right click the Maintenance Plan created to perform maintenance on the EV, CA, and DA databases and select View History.
     
  4. The Log File Viewer window appears with the Maintenance Plan selected.

    Log File Viewer
     
  5. Successfully completed jobs are shown with a green check mark; failed or unsuccessful items display a red circle with a white X.
     
  6. To determine where in the maintenance plan the job failed, expand each item listed in the Log File Viewer.

For additional assistance in troubleshooting SQL Server Maintenance Plans, please see the SQL Administration documentation regarding Maintenance Plans or contact Microsoft Support

 

Verifying successful completion of the SQL Server maintenance plan:

Notes:

  • The DBCC SHOWCONTIG command referenced below has been deprecated and will stop being available in a future version of Microsoft SQL Server.  This document will be updated with instructions to use and interpret the replacement command at a future date.
  • The actions below should be performed immediately after the maintenance has completed and while the SQL Server is not busy actively updating databases based on application activities as this action adds a processing load to the SQL Server while executing.
  1. Open SQL Server Management Studio.
     
  2. Select New Query.
     
  3. From the list of databases, select one of the databases that was selected in the Maintenance Plan.
  4. In the query window, type the following command and select Execute:

DBCC SHOWCONTIG WITH ALL_INDEXES

 

  1. The results are displayed below the query window. Review the results displayed in bold.

    DBCC SHOWCONTIG results
     
  2. For each table where 'Pages Scanned' is greater than 1000, the items in bold should not be higher than the following recommended settings:

    •    Extent Switches
    Value should be 1 less than Extent Pages scanned
    The higher this value is above the Extent Pages Scanned, the more fragmented the page order.

    •    Logical Scan Fragmentation
    Value should be under 1%  
    Latency occurs once logical scan fragmentation exceeds 10%.

    •    Extent Scan Fragmentation -
    Value should be under 50%
    Performance issues may occur for fragmentation over 50%
     
  3. If the values for Extent Switches, Logical Scan Fragmentation, and Extent Scan Fragmentation are not within recommended values after performing scheduled maintenance, you may need to modify the 'Rebuild Index' maintenance task settings or perform the task more frequently.
     
  4. To examine the amount of space being used by SQL database transaction logs, type the following command in the query window and select Execute:

    DBCC SQLPERF(LOGSPACE)
     
  5. The results are shown below the query window.

    DBCC SQLPERF(LOGSPACE) results
     
  6. If the Log Space Used % is 100% or there is insufficient room for the log to grow depending on the log's growth configuration settings (i.e., insufficient free space for the log to grow or the log is at its maximum size setting), no additional items are added to the SQL database transaction logs. This makes the database effectively Read-Only until such time as the transaction log is grown with additional space allocated or truncated.
  • If the amount of space being used by SQL database transaction log does not decrease after performing the scheduled maintenance, you should determine the reason why the log does not decrease.  To determine the reason, run the following SQL query:
  • The log_reuse_wait_desc should contain the reason why the transaction log's Log Space Used % did not decrease (i.e., awaiting backup, active transaction).
  • If the results of this query does not report the reason for the lack of Log Space Usage % reduction, then the onsite SQL Team should be consulted for assistance.

 

Additional Information

For additional assistance in configuring or troubleshooting SQL Server maintenance plans, please see the SQL Administration documentation regarding maintenance plans.

SQL Server 2005 Books Online

•    Backing Up and Restoring Databases -  http://technet.microsoft.com/en-us/library/ms187048(SQL.90).aspx
•    Maintaining Databases (Database Engine) -  msdn.microsoft.com/en-us/library/ms178081(SQL.90).aspx
•    Database Mail -  msdn.microsoft.com/en-us/library/ms175887(SQL.90).aspx

SQL Server 2008 Books Online

•    Backing Up and Restoring Databases -  technet.microsoft.com/en-us/library/ms187048.aspx
•    Maintaining Databases (Database Engine) -   msdn.microsoft.com/en-us/library/ms178081.aspx
•    Database Mail -  msdn.microsoft.com/en-us/library/ms175887.aspx

SQL Server 2008 R2 Books Online

•    Backing Up and Restoring Databases - technet.microsoft.com/en-us/library/ms189621.aspx




Legacy ID



332255


Article URL http://www.symantec.com/docs/TECH74666


Terms of use for this information are found in Legal Notices