Problem
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
- 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 - Offline vs. Online Maintenance:
Whilst SQL Server 2005 and higher allow for online index operations, i.e., running index rebuilds and maintenance without stopping any EV, CA or DA services, this is not the recommended method as there are certain restrictions to running online operations. First, online index operations are typically only available in the Enterprise version of SQL Server. Second and most important, the following SQL Server versions do not allow online index operations when the following large object (LOB) data types are present in a table:
- 2005, 2008, 2008 R2: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), xml.
- 2012, 2014: image, ntext, text (these are depricated in SQL Server 2012).
The LOB data types do not have to be included in an index; it is enough to have a column defined with the LOB data type present in the table for SQL to disallow online index operations.
As EV, CA and DA make use of these LOB data types in the current versions of the product, it is therefore recommended all SQL maintenance be run offline and all steps below be run with the applicable EV, CA or DA services stopped.
Here are some supporting Microsoft references:
- Quickstart: Backup and restore a SQL Server database on-premises: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/quickstart-backup-restore-database?view=sql-server-ver15
- Back Up and Restore of SQL Server Databases: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases?view=sql-server-ver15
- Performing Index Operations Online: https://msdn.microsoft.com/en-us/library/ms177442(v=sql.90).aspx
- Guidelines for Performing Online Index Operations SQL Server 2005: https://msdn.microsoft.com/en-us/library/ms190981%28v=sql.90%29.aspx
- Guidelines for Performing Online Index Operations SQL Server 2008: https://msdn.microsoft.com/en-us/library/ms190981%28v=sql.100%29.aspx
- Guidelines for Performing Online Index Operations SQL Server 2008 R2: https://msdn.microsoft.com/en-us/library/ms190981%28v=sql.105%29.aspx
- Guidelines for Performing Online Index Operations SQL Server 2012: https://msdn.microsoft.com/en-us/library/ms190981%28v=sql.110%29.aspx
- Guidelines for Performing Online Index Operations SQL Server 2014: https://msdn.microsoft.com/en-us/library/ms190981%28v=sql.120%29.aspx
- Guidelines for Performing Online Index Operations SQL Server 2019: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations?view=sql-server-ver15&redirectedfrom=MSDN&viewFallbackFrom=sql-server-2019
- Scalability and Performance Enhancements (Database Engine): https://msdn.microsoft.com/en-us/library/cc645580%28v=SQL.110%29.aspx
- Deprecated Database Engine Features in SQL Server 2012: https://technet.microsoft.com/en-us/library/ms143729%28v=sql.110%29.aspx
Important Note - Shrink Database option discussion:
Shrinking the database is an optional step that could be done to reorganize the pages within the database toward the beginning of the database file which MAY allow for improved database performance. Note this is NOT a guaranteed result. One example of when a shrink database operation could (but not necessarily should) be performed is after an upgrade of EV, CA or DA, as this might involve migrating and dropping one or more tables. However, the need for running a shrink operation should be carefully reviewed by the SQL DBA responsible for the databases.
Running a shrink on a regular basis is not recommended and there are numerous online references stating as such, the following examples being from a trusted SQL resource:
- https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
- https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/
Shrinking a 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 in the SQL Best Practices Guide (see 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 and will cause requests to autogrow the file more frequently than before the shrink operation. When an autogrowth request is submitted, data processing for the database is paused until the new space is available for use.
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. Note if an index maintenance operation does not reduce fragmentation levels, even after multiple runs with the EV/CA/DA services stopped, and there is a large amount of free space in the database, a shrink operation with the option to return file space back to the operating system may need to be run, followed by the index maintenance operation to rebuild the indexes. Additionally, a shrink operation can also cause 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.
For additional information on this topic, see Microsoft articles https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database?view=sql-server-ver16.
- BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.
- Differential backups are not supported on secondary replicas.
- BACKUP LOG supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas). A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).
- To back up a secondary database, a secondary replica must be able to communicate with the primary replica and must be SYNCHRONIZED or SYNCHRONIZING.
• Configuring a maintenance plan to back up EV, CA and DA databases
• Creating a database maintenance plan for EV, CA, and DA SQL databases
Veritas 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.
The screenshots below reference SQL 2005 to 2008 R2. Some windows in SQL 2012 and higher may be different from the listed screenshots. Please be sure to view all sections/tabs of any windows that appear different from the screenshots to configure and verify the settings.
- Create a backup location for EV, CA, and DA databases (for example, C:\EVDatabaseBackups).
- Open SQL Server Management Studio.
- Browse to Management | Maintenance Plans.
- Right click Maintenance Plans and select Maintenance Plan Wizard.
- A screen shot of the SQL Server Maintenance Plan Wizard Window initial page appears below -
- Click Next and the Select Plan Properties window appears. Set the following properties:
• 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,
- Click the Change button to set the schedule.
- In the Job Schedule Properties window, provide the following properties:
• 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.
- Click Ok, and then click Next.
- On the Select Maintenance Tasks page, select Back Up Database (Full), Back Up Database (Transaction Log), and Maintenance Cleanup Task.
- Click Next.
- The Select Maintenance Task Order window appears.
- 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, Veritas recommends to perform the following tasks in order to avoid DB fragmentation: Shrink database, Rebuild Indexes, Update Statistics.
- Click Next and set the following in the 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.
- Click Next.
- 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.
- Click Next and then Next again.
- The Select Report Options window appears
• 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.
- Click Next.
- The Complete the Wizard window appears.
- Review the information displayed and click Finish.
- After the installation has finished, click Close.
Database and transaction log backup files
Database Backup file names use the format <DatabaseName_backup_yyyymmddhhmm.bak>
• 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.
- Open SQL Server Management Studio.
- Expand Management | Maintenance Plans.
- Right click on the Maintenance Plan created to back up EV, CA, and DA databases and select View History.
- The Log File Viewer window appears with the maintenance plan selected.
- Successful jobs are shown with a green check mark; failed or unsuccessful items are highlighted with a white X inside a red circle.
- 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
- Open SQL Server Management Studio.
- Expand Databases.
- Right click on the Database and select Properties.
- The Database Properties window appears.
- 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
Veritas 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
NOTE:
SQL Server 2016 introduces the ability to perform conditional rebuilding of table indexes. SQL Server 2016 also performs an automatic update of the statistics if an index is actually rebuilt. If an index is not rebuilt due to a low fragmentation level or page count, statistics will not be updated for the table in which the index resides. Please ensure that statistics are updated for all tables on a regular basis by performing an unconditional index rebuild periodically. Unconditional Index rebuilds are performed by unchecking the Fragmentation and Page count boxes in the Rebuild Index Task window.
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. Veritas 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, Veritas 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.
The screenshots below reference SQL 2005 to 2008 R2. Some windows in SQL 2012 and higher may be different from the listed screenshots. Please be sure to view all sections/tabs of any windows that appear different from the screenshots to configure and verify the settings.
- Create a backup location for the EV, CA, and DA databases (For e.g. C:\EVDatabaseBackups)
- Open SQL Server Management Studio.
- Browse to Management | Maintenance Plans.
- Right click on Maintenance Plans and select Maintenance Plan Wizard.
- The SQL Server Maintenance Plan Wizard window appears.
- Click Next.
- In the Select Plan Properties window, provide the following 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.
- Click the Change button.
- In the Job Schedule Properties window, provide the following 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.
- Click Ok, then Next.
- The Select Maintenance Tasks Window is displayed.
- 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)
- Click Next.
- The Select Maintenance Task Order window is displayed.
- If running Maintenance with services stopped (i.e. offline maintenance), order the Maintenance Tasks using the Move Up and Move Down buttons as follows:
I. Back Up Database (Full)
II. Backup Database (Transaction Log)
III. 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)
IV. Rebuild Index
V. Update Statistics - If running Maintenance with services running (i.e. online maintenance - see Important Note - Offline vs. Online Maintenance above), order the Maintenance Tasks using the Move Up and Move Down buttons as follows:
I. Back Up Database (Full)
II. Backup Database (Transaction Log)
III. 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)
IV. Update Statistics
V. Rebuild Index - Click Next.
- In the Define Backup Database (Full) Task window provide the following:
• 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.
- Click Next.
- 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.
- Click Next.
- Optional step if the Shrink Database process has been selected - In the Define Shrink Database Task window, set the following properties:
• 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.
- In the Define Rebuild Index Task window, set the following properties:
• Databases: Select the appropriate EV, CA, and DA databases.
• Choose the option Reorganize pages with the default amount of free space.
• It is not recommended to enable the Keep index online while reindexing option as any indexes currently loaded in memory will be skipped.
- Click Next.
- The Define Update Statistics Task window is displayed. Set the following properties:
• Databases: Select the appropriate EV, CA, and DA databases.
• Update: Select All existing statistics.
• Scan type: Select Full scan
- Click Next.
- 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.
- Click Next.
- The Complete the Wizard window is displayed. Review the information displayed and click Finish.
- 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
- Open SQL Server Management Studio.
- Expand Management | Maintenance Plans.
- Right click the Maintenance Plan created to perform maintenance on the EV, CA, and DA databases and select View History.
- The Log File Viewer window appears with the Maintenance Plan selected.
- Successfully completed jobs are shown with a green check mark; failed or unsuccessful items display a red circle with a white X.
- 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.
- Open SQL Server Management Studio.
- Select New Query.
- From the list of databases, select one of the databases that was selected in the Maintenance Plan.
In the query window, type the following command and select Execute:
DBCC SHOWCONTIG WITH ALL_INDEXES
- The results are displayed below the query window. Review the results displayed in bold.
- 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%
- 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.
- 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)
- The results are shown below the query window.
- 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:
SELECT Name, log_reuse_wait_desc, recovery_model_desc FROM sys.databases;
- 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.