How to Automatically Backup and Perform Recommended Maintenance for Enterprise Vault SQL databases when Using Microsoft SQL Server 2000

Article:TECH142729  |  Created: 2010-10-26  |  Updated: 2011-10-13  |  Article URL http://www.symantec.com/docs/TECH142729
Article Type
Technical Solution

Product(s)

Environment

Issue



The following document is to assist Administrators in automating backup and recommended maintenance procedures for Enterprise Vault (EV), Compliance Accelerator (CA), and Discovery Accelerator (DA) SQL databases using SQL Server Maintenance Plans. For all procedures, it is assumed that the SQL Server Agent is started and operational and the user executing the maintenance plan is assigned the sysadmin SQL Server role.

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

Notes:  - The examples listed below are for SQL 2000.  The concept is the same for SQL 2005 and 2008, but the screen displays will be different.
             -  When performing maintenance that includes Shrink Database, always make Shrink Databases the first action performed (after backup if included).

 


Solution



Configuring a Maintenance Plan to Backup EV, CA, and DA databases
The following instructions are for EV environments where there is not a 3rd party backup software that will utilize a 'SQL Agent' to perform a logical backup of the SQL databases. This process will allow SQL backups, or dumps, to occur of the databases outside of any other schedules.  

It is recommended that regular backups of all EV, CA, and DA SQL databases is performed.  When performing backups, EV services should be placed in Read-Only/Backup Mode and CA and DA services should be stopped.   

For additional assistance in configuring backups in SQL, please see the SQL Administration documentation regarding backing up SQL Databases

 
1. Create a backup location for the EV, CA, and DA databases (Ex. C:\EVDatabaseBackups)

2. Open SQL Enterprise Manager

3. Browse to Management | Database Management

4. Right click on Database Management and choose New Maintenance Plan Wizard

5. The Database Maintenance Wizard Window is displayed
 

6. Click Next

7. The Select Databases Window is displayed
 

8. Choose These databases:.

9. From the list of databases, choose the appropriate EV, CA, and DA databases to be backed up

10. Click Next

11. The Update Data Optimization Information window is displayed
 

12. Ensure no options are enabled and click Next

13. The Database Integrity Check window is displayed
 

14. Ensure no options are enabled and click Next

15. The Specify the Database Backup Plan window is displayed
 

16. Enable the Back up the database as part of the maintenance plan, choose Disk, and click Change

17. The Edit Recurring Job Schedule window is displayed
 

18. For Occurs:, choose a frequency (Daily, Weekly, Monthly) equivalent to the frequency of EV Backups

19. For Recurs every:, choose the available options depending on the frequency of the backup.  The options selected should be in conjunction with EV services placed in backup-mode/Read-Only mode and/or CA and/or DA services stopped OR all EV, CA, and DA services stopped


20. For Daily frequency:, choose the time the database backup should begin.  The time selected should be in conjunction with EV services placed in backup-mode/Read-Only mode and/or CA and/or DA services stopped OR all EV, CA, and DA services stopped.

21. Click Ok, then Next

22. The Specify Backup Disk Directory window is displayed
 

23. Choose Use this directory: and browse to the location created in Step 1

24. To assist with the organization of database and transaction log backups, the Create a sub-directory for each database option can be enabled

25. To assist with the removal of older backups, the Remove files older than: with the required date parameters can be enabled

26. Click Next

27. The Specify the Transaction Log Backup Plan window is displayed
 

28. Enable the Back up the database as part of the maintenance plan, choose Disk, and click Change

29. The Edit Recurring Job Schedule window is displayed
 

30. For Occurs:, choose a frequency (Daily, Weekly, Monthly) equivalent to the frequency of EV Backups

31. For Recurs every:, choose the available options depending on the frequency of the backup.  The options selected should be in conjunction with EV services placed in backup-mode/Read-Only mode and/or CA and/or DA services stopped OR all EV, CA, and DA services stopped.

32. For Daily frequency:, choose the time the database backup should begin.  The time selected should be in conjunction with EV services placed in backup-mode/Read-Only mode and/or CA and/or DA services stopped OR all EV, CA, and DA services stopped

33. Click Ok, then Next

34. The Specify Transaction Log Backup Disk Directory window is displayed
 

35. Choose Use this directory: and browse to the location created in Step 1

36. To assist with the organization of database and transaction log backups, the Create a sub-directory for each database option can be enabled

37. To assist with the removal of older backups, the Remove files older than: with the required date parameters can be enabled

38. Click Next

39. The Reports to Generate window is displayed
 

40. By default, the Write  a report to a text file is enabled with the default SQL Server installation directory

41. Additionally, the Email report: option can be enabled.  For additional assistance in configuring E-Mail servers in SQL Server, please see the SQL Documentation regarding SQL Mail

42. Click Next

43. The Maintenance Plan History window is displayed
 

44. Click Next

45. The Completing the Database Maintenance Wizard is displayed
 

46. In the Plan name: field, enter a name for the maintenance plan (Ex. BackupEVDatabases)

47. Click Finish

48. When the Maintenance plan is executed automatically or manually, backups of selected databases and transaction logs will be created to the backup location created in Step 1.  

49. Backup files are named using DatabaseName_backup_yyyymmddhhmm.bak for database files and  DatabaseName_backup_yyyymmddhhmm.trn for transaction logs.  DatabaseName represents the name of the database; yyyy, mm, dd, hh, mm represent the year, month, day, hour, and minute respectively
 
Example:
 
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
 
 
Verifying SQL Backup Maintenance Plan Executed Successfully
 
1. Open SQL Enterprise Manager

2. Expand Management

3. Right click Database Maintenance Plans and choose Maintenance Plan History

4. The Database Maintenance Plan History window will be displayed
 

5. For Plan Name:, choose the Maintenance Plan created to backup EV, CA, and DA databases from the drop down list

6. To view jobs that have successfully completed, for the Status: field, choose Succeeded; for jobs that have failed, choose Failed from the drop down list

7. To determine where the job failed, highlight the job failed and choose Properties.  

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

Verifying SQL Backups Have Successfully Completed

1. Open SQL Enterprise Manager for SQL Server 2000

2. Expand Databases

3. Right click on the Database to check and choose Properties

4. The database properties window is displayed
 

5. Under Backup, dates and times will be listed when the last successful backup was performed on the SQL Database and Transaction Log

Configuring a Maintenance Plan to Perform Maintenance of EV, CA, and DA databases
It is recommended that the following SQL maintenance procedures be performed weekly for all EV, CA, and DA databases in the order listed:
-Shrink Databases
-Rebuild Indexes
-Update Statistics
 
Note: The reasoning behind a Shrink Database is continued insertions and deletions in a database table will cause ‘white space’ to develop within the data files.  ‘White space’ is used to describe table density or gaps between data.  This ‘white space’ will result in more reads required to read through a table as well as making the Statistics and Indexes stale. Shrinking the database, while allowing the freed space to remain with the files, will essentially move the data to the front of the file space, removing the ‘white space’ while allowing the remainder of unallocated space to be used for file growth. But once these files have been moved to the front of available drive space, Statistics and Indexes are no longer viable and require to be updated.  This is why Shrinking the database can cause SQL latency if not properly performed, but if properly executed the SQL performance will improve after the database has been shrunk So, When Shrinking a database, three factors must be adhered to, in order to improve SQL performance:
 
1. Retain freed space in database files
2. Rebuild Indexes
3. Update Statistics

Performing these tasks on a regular basis will allow EV, CA, and DA to perform optimally.  

Each maintenance plan created using these instructions, also performs a SQL backup of all EV, CA, and DA databases selected.  If this step is performed by another job/task and confirmed complete, these steps can be omitted from this procedure.  It is recommended that a FULL database backup is completed and ALL EV, CA, and DA services are STOPPED before any maintenance is started.  
 
For additional information regarding SQL maintenance for Indexes and Statistics and how an EV, CA, and/or DA environment is affected, please referer to the Related Articles at the end of this document (SQL Index Fragmentation and Statistics)

For Microsoft SQL Server 2000, separate Maintenance Plans are needed to perform the recommended database maintenance.  SQL Server 2000 does not allow Rebuilding Indexes and Updating Statistics in the same maintenance plan.

Maintenance Plan to Rebuild Indexes
1. Create a backup location for the EV, CA, and DA databases (Ex. C:\EVDatabaseBackups)

2. Open SQL Enterprise Manager

3. Browse to Management | Database Management

4. Right click on Database Management and choose New Maintenance Plan Wizard

5. The Database Maintenance Wizard Window is displayed
 

6. Click Next

7. The Select Databases Window is displayed
 

8. Choose These databases:.

9. From the list of databases, choose the appropriate EV, CA, and DA databases

10. Click Next

11. The Update Data Optimization Information window is displayed
 

12. Enable Reorganize data and index pages

13. Click Change

14. The Edit Recurring Job Schedule window is displayed
 

15. For Occurs:, choose Weekly

16. For Every...week(s) on:, choose 1 and check a day of the week.  The options selected should be in conjunction with all EV, CA, and/or DA services being stopped AND after a successful backup of all EV, CA, and/or DA databases has been performed.

17. For Daily Frequency, choose the time the database maintenance should be performed.  The time selected should be in conjunction with all EV, CA, and/or DA services being stopped AND after a successful backup of all EV, CA, and/or DA databases has been performed.

18. Click Ok, then Next

19. The Database Integrity Check window is displayed
 

20. Ensure no options are enabled and click Next

21. The Specify the Database Backup Plan window is displayed
 

22. Enable the Back up the database as part of the maintenance plan, choose Disk, and click Change

23. The Edit Recurring Job Schedule window is displayed
 

24. For Occurs:, choose Weekly

25. For Every...week(s) on:, choose 1 and check a day of the week.  The options selected should be in conjunction with all EV, CA, and/or DA services being stopped.

26. For Daily Frequency, choose the time the database maintenance should be performed.  The time selected should be in conjunction with all EV, CA, and/or DA services being stopped.

27. Click Ok, then Next

28. The Specify Backup Disk Directory window is displayed
 

29. Choose Use this directory: and browse to the backup location created in Step 1

30. To assist with the organization of database and transaction log backups, the Create a sub-directory for each database option can be enabled

31. To assist with the removal of older backups, the Remove files older than: with the required date parameters can be enabled

32. Click Next

33. The Specify the Transaction Log Backup Plan window is displayed
 

34. Enable the Back up the database as part of the maintenance plan, choose Disk, and click Change

35. The Edit Recurring Job Schedule window is displayed
 

36. For Occurs:, choose Weekly

37. For Every...week(s) on:, choose 1 and check a day of the week.  The options selected should be in conjunction with all EV, CA, and/or DA services being stopped AND after a successful backup of all EV, CA, and/or DA databases has been performed.

38. For Daily Frequency, choose the time the database maintenance should be performed.  The time selected should be in conjunction with all EV, CA, and/or DA services being stopped AND after a successful backup of all EV, CA, and/or DA databases has been performed.

39. Click Ok, then Next

40. The Specify Transaction Log Backup Disk Directory window is displayed
 

41. Choose Use this directory: and browse to the location created in Step 1

42. To assist with the organization of database and transaction log backups, the Create a sub-directory for each database option can be enabled

43. To assist with the removal of older backups, the Remove files older than: with the required date parameters can be enabled

44. Click Next

45. The Reports to Generate window is displayed
 

46. By default, the Write a report to a text file is enabled with the default SQL Server installation directory

47. Additionally, the Email report: option can be enabled.  For additional assistance in configuring E-Mail servers in SQL Server, please see the SQL Documentation regarding SQL Mail

48. Click Next

49. The Maintenance Plan History window is displayed
 

50. Click Next

51. The Completing the Database Maintenance Wizard is displayed

52. In the Plan name: field, enter a name for the maintenance plan (Ex. EVDatabaseRebuildIndexes)

53. Click Finish

54. When the Maintenance plan is executed automatically or manually, backups of selected databases and transaction logs will be created to the backup location created in Step 1, and indexes will be rebuilt.


Maintenance Plan to Shrink database and Update Statistics

1. Browse to Management | Database Management

2. Right click on Database Management and choose New Maintenance Plan Wizard

3. The Database Maintenance Wizard Window is displayed
 

4. Click Next

5. The Select Databases Window is displayed
 

6. Choose These databases:.

7. From the list of databases, choose the appropriate EV, CA, and DA databases

8. Click Next

9. The Update Data Optimization Information window is displayed
 

10. Enable Update statistics used by query optimizer.  

11. For Sample:, use the default value of 10 % of the database.

12. Enable Remove unused space from database files.

13. For When it grows beyond:, use the default value of 50 MB

14. For Amount of free space to remain after shrink:, use the default value of 10 % of the data space

15. Click Change

16. The Edit Recurring Job Schedule window is displayed
 

17. For Occurs:, choose Weekly

18. For Every...week(s) on:, choose 1 and check a day of the week.  The options selected should be in conjunction with all EV, CA, and/or DA services being stopped.

19. For Daily Frequency, choose the time the database maintenance should be performed.  The time selected should be in conjunction with all EV, CA, and/or DA services being stopped.

20. Click Ok, then Next 4 times, not enabling any options on the Database Integrity Check, Database Backup Plan, and Transaction Log Backup Plan windows

21. The Reports to Generate window is displayed
 

22. By default, the Write a report to a text file is enabled with the default SQL Server installation directory

23. Additionally, the Email report: option can be enabled.  For additional assistance in configuring E-Mail servers in SQL Server, please see the SQL Documentation regarding SQL Mail

24. Click Next

25. The Maintenance Plan History window is displayed
 

26. Click Next

27. The Completing the Database Maintenance Wizard is displayed
 

28. In the Plan name: field, enter a name for the maintenance plan (Ex. EVDatabaseShrinkdatabasesUpdateStatistics)

29. Click Finish

30. When the Maintenance plan is executed automatically or manually, database statistics will be updated and database and transaction log files will be shrunk to the size specified if possible.

Verifying the SQL Maintenance Plan to Perform Database Maintenance Executed Successfully

1. Open SQL Enterprise Manager

2. Expand Management

3. Right click Database Maintenance Plans and choose Maintenance Plan History

4. The Database Maintenance Plan History window will be displayed
 

5. For Plan Name:, choose the Maintenance Plan created to perform maintenance on the EV, CA, and DA databases from the drop down list

6. To view jobs that have successfully completed, for the Status: field, choose Succeeded; for jobs that have failed, choose Failed from the drop down list

7. To determine where the job failed, highlight the job failed and choose Properties.  

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


Verifying SQL Maintenance Has Successfully Completed

1. Open SQL Enterprise Manager

2. Choose Tools | Query Analyzer

3. From the list of databases, choose one of the databases selected in the Maintenance Plan

4. In the query window, type the following command and choose Execute:

DBCC SHOWCONTIG

5. The results will be displayed below the query window.  Review the results displayed in bold, for tables that have a 'Pages Scanned' value greater than 1000.
 

6.   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 then 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%
Once fragmentation exceeds 50%, indexes are no longer used

Extent Scan Fragmentation -
Value should be under 50%
Performance issues may occur for fragmentation over 70%

7. If the values for Extent Switches, Logical Scan Fragmentation, and Extent Scan Fragmentation are not within recommended values after performing scheduled maintenance, the 'Rebuild Index' maintenance task settings may need to be modified or the task may need to be performed more frequently.  

8.   To examine the amount of space being used by SQL database transaction logs, in the query window, type the following command and choose Execute:

DBCC SQLPERF(LOGSPACE)

9. The results will be displayed below the query window.
 

10. If the Log Space Used (%) is 90% or above, no additional items will be added to the SQL database transaction logs.  The transaction logs must have additional space allocated or the transaction logs must be truncated.  If the amount of space being used by SQL database transaction logs does not decrease after performing scheduled maintenance, the 'Shrink Database' maintenance task settings may need to be modified or the task may need to be performed more frequently.  


Additional Information
Administering SQL Server (SQL Server 2000) -
Backing Up and Restoring Databases - http://technet.microsoft.com/en-us/library/aa196685(SQL.80).aspx
Database Maintenance Plan Wizard -  http://msdn.microsoft.com/en-us/library/aa933075(SQL.80).aspx
SQL Mail -  http://msdn.microsoft.com/en-us/library/aa213788(SQL.80).aspx

 





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


Terms of use for this information are found in Legal Notices