Loading...

Knowledge Center


Recommended maintenance plan for ePolicy Orchestrator databases using SQL Server Management Studio
Technical Articles ID:  KB67184
Last Modified:  2/19/2015
Rated:


Environment

McAfee ePolicy Orchestrator (ePO) 5.x, 4.6

Microsoft SQL Server 2012
Microsoft SQL Server 2008 R2
Microsoft SQL Server 2008
Microsoft SQL Server 2005

Summary

This article describes the recommended maintenance plan for ePO databases using SQL Server Management Studio.

Solution 1

IMPORTANT: These routine tasks include SQL Server maintenance jobs that will not only keep the data and the engine performing at satisfactory levels, but will also keep the data backed up to aid recovery in the event of a disaster. This information is intended for use by database administrators (DBA) and ePO administrators only. Use the following procedure at your own risk. Intel Security does not assume responsibility for any damages as a result of following these instructions.

Background:
SQL Server uses the concept of Write Ahead Logging, where each data modification operation (Insert, Update, Delete, and other operations such as index rebuild and reorganize) is first written to the Transaction Log (.LDF) from memory (buffer pool) and periodically flushed to the disk data file (.MDF) as part of the CheckPoint process. One of the main reasons to use a Transaction log is to ensure that in the event of a disaster (such as hardware failure or human error), you can restore the database to an earlier state without much data loss.

Full Recovery Model:

In the Full Recovery Model, after a backup of the transaction log is performed using the Full Recovery model, SQL Server will mark those records that are backed up as Inactive (also known as truncating the log). In this way, any new operations that are logged to the Transaction log can reuse that space by overwriting the inactive entries, which helps to prevent the log size from growing.

If no periodic backup of the Transaction log is done, the size of the Transaction log will continue to grow until it consumes all available disk space. Therefore, if your ePO database is configured to use the Full Recovery model, it is important to perform regular transaction log backups to keep its size in check.

Simple Recovery Model:
In the Simple Recovery Model, after the CheckPoint occurs and the records are flushed to disk, SQL Server will truncate the Transaction log. This frees up the space internally in the Transaction log file. The Transaction log does not grow in size as long as there is enough space available for the current open transactions.

This means that in the Simple Recovery Model, the concept of backing up the Transaction Log is not used, because you only take a regular full backup of the ePO database. In the event of a disaster, you can only recover to the last full backup. All changes that occurred after the last full backup are lost.

For most enterprise customers, using the Simple Recovery Model is an acceptable solution (because what is lost in case of a disaster is mostly event information as of the last full backup), rather than using the Full Recovery Model, which involves the administrative overhead of backing up the Transaction log for your ePO database periodically.

For this reason, Intel Security recommends using the Simple Recovery Model for the ePO database. However, if you choose to use the Full Recovery Model, ensure that you have a good backup plan for both your ePO database and Transaction log. A discussion of the backup plan for SQL Server databases is beyond the scope of this article. For more details, refer to SQL Server Books Online at: http://msdn.microsoft.com/en-us/library/ms130214.aspx.

NOTE: If you have multiple databases with different recovery models, you can create separate database maintenance plans for each recovery model. In this way you can include a step to back up your transaction logs only on the databases that do not use the Simple Recovery Model.


Set the ePO database Recovery Model to Simple

To ensure that the recovery model is set to Simple, open SQL Server Management Studio and perform the following steps: 
  1. Click All ProgramsMicrosoft SQL Server <version>, SQL Server Management Studio.
  2. Select the Authentication (Windows or SQL Server) and click Connect to log on to the SQL Server instance hosting the ePO database.
  3. In the Object Explorer window, expand the Databases node.
  4. Right-click the ePO_<server name> (ePO 5.x) or ePO4_<server name> (ePO 4.x) entry.
  5. Select Properties to bring up the Database Properties window.
  6. Click Options in the Select a Page area on the left pane.
  7. Click the drop-down arrow on the right of the Recovery model and select Simple.
  8. Click OK.

Shrink Database and why it is NOT recommended:
Shrinking the ePO database should be avoided as much as possible. Shrinking a production SQL Server database would introduce logical fragmentation (physical order of the pages in the leaf level of an Index is not the same as the logical order of the pages). Effectively, the disk head has to go back and forth in reading the pages, thus performing more I/O operations and degrading performance.

When you shrink the data file, pages at the end of the data file are moved to the beginning of the file, disregarding any potential fragmentation that is introduced in this process.

If you find that after deleting events and shrinking the database, the ePO database is growing in size, this means that the space is needed for events that are being sent by the Agent. So shrinking the data file after deleting the events would only cause the file to grow back, in addition to causing fragmentation. If space usage is a concern, consider filtering the non-essential events using ePO Event Filtering.

NOTE: You can consider shrinking the data file after performing a large number of delete operations (such as purging old events) if you are sure that you will not need that space again for storing new events. Otherwise, it is recommended to simply rebuild the indexes periodically and filter the unnecessary events using ePO Event Filtering to avoid capturing unwanted data in the first place.

IMPORTANT: Filtering events will have a direct impact on what reports can be generated that make use of those events. Ensure that you filter out only those events that you know are not necessary for day-to-day reports. It is recommended that you back up the ePO database before purging the older events. For future reference, you could always restore this ePO database backup to a new name to generate reports for that period.

As long as proper database maintenance is performed (such as rebuilding and reorganizing indexes), the size of the ePO database should not negatively affect query performance. If you regularly purge old events (for example, all events older than three months) using the ePO Purge Events Server Task, the database size should more or less stabilize. This assumes that your database growth rate is proportional to the older events that are deleted.

You must have a proper database maintenance plan configured to ensure  that the ePO database performance is healthy. 

Create a maintenance plan for the ePO database in SQL Server 2012 / 2008 R2 / 2008 / 2005
  1. Click All ProgramsMicrosoft SQL Server <version>, SQL Server Management Studio.
  2. Select the Authentication (Windows or SQL Server) and click Connect to log on to the SQL Server instance hosting the ePO database.
  3. Using SQL Server Management Studio, expand Management in the server object explorer window.
  4. Right-click Maintenance Plans and select Maintenance Plan Wizard
  5. Enter a name for the Maintenance Plan (for example, ePO Database Maintenance Plans).
  6. Change the schedule by clicking Change and click Next.
     
    NOTE: Schedule the task to execute during off-peak times (for example, a recurring task every Saturday at 11:00pm).
     
  7. Select the following options under Maintenance tasks and click Next:
    • Check Database Integrity
    • Rebuild Index
    • Back Up Database (Full)
  8. Define the order for the tasks to execute as follows and click Next:
    • Check Database Integrity
    • Back Up Database (Full)
    • Rebuild Index
     
    NOTE: These tasks can be interchangeable in terms of the order in which they execute. Intel Security recommends a database backup before the index rebuild process. This is to ensure there is a working backup copy of the database in case of an issue during the rebuild process.
     
  9. Define a Check Database Integrity task:
    1. Select the ePO database ePO_<servername> (ePO 5.x) or ePO4_<servername> (ePO 4.x).
    2. Select Include indexes.
    3. Click Next
  10. Define a Back Up Database (Full) task:
    1. Select the ePO database ePO_<servername> (ePO 5.x) or ePO4_<servername> (ePO 4.x).
    2. Type the backup path location.
    3. In the Set backup compression drop-down, select Compress backup.
    4. Click Next.
  11. Define a Rebuild Index task:
    1. Select the ePO database ePO_<servername> (ePO 5.x) or ePO4_<servername> (ePO 4.x).
    2. Select Object: Tables and Views.
    3. Select Change free space per page percentage to: 10 %.
    4. Under Advanced options, select Keep index online while reindexing.
       
      NOTE: Online Index rebuild is not supported by all editions of SQL Server. For more details on which editions support the Online Index rebuild, refer to SQL Server Books Online documentation.
       
    5. For index types that do not support online index rebuilds, select the option Rebuild Indexes offline.
    6. Click Next.

      NOTE: An Index Rebuild task would cause the statistics to be updated as part of the rebuild (effectively with full scan) so an Update Statistics task is not needed after a Rebuild Index.
       
  12. Define Select Report Options:
    1. Select Write a report to a text file and enter the desired folder location.
    2. Click Next.
    3. Click Finish.
NOTE: For a large ePO database, the Database Administrator should monitor the maintenance task and avoid running the task during production hours.

Solution 2

If you have a large production database, Intel Security recommends that you use a custom index rebuild / reorganize script instead of the Index Reorganize and Rebuild Maintenance plan tasks.

This allows more flexibility about which objects need to be reorganized and which need to be rebuilt, instead of rebuilding every object regardless of the fragmentation level.

According to SQL Server books online:  
  • If fragmentation is < 30%, you should reorganize.
  • If fragmentation is > 30%, you should rebuild.

You can determine the fragmentation level of an index by querying the sys.dm_db_index_physical_stats dynamic management view (DMV) entry. SQL Server Books Online provides a sample SQL Script that provides a fragmentation ratio as listed above. Refer to the topic on sys.dm_db_index_physical_stats in the SQL Server Books Online documentation link below:

http://technet.microsoft.com/en-us/library/ms188917.aspx

NOTE:
Example D in the online documentation provides the sample code.


It is important that you update the statistics after an Index Reorganize command. Unlike Index Rebuild, statistics are not automatically updated as part of an Index Reorganize. An updated SQL script located in RebuildReorganizeIndexes-V3_Includes46.zip, based on the above SQL Server Books Online example, is located in the Attachments section of this article. This adds the step to update the statistics after an Index Rebuild Operation.

You can further customize the script to include the option to perform an Online Rebuild of Indexes. Online Rebuild provides more concurrency during the Index Rebuild and is resource intensive. However, this feature is not available in all editions of SQL Server. Refer to the Books Online documentation on which editions support the Online Rebuild of Indexes feature.

Attachment

RebuildReorganizeIndexes-V3_Includes46.zip
2K • < 1 minute @ 56k, < 1 minute @ broadband


Rate this document

Did this article resolve your issue?

Please provide any comments below

Beta Translate with

Select a desired language below to translate this page.

Glossary of Technical Terms


Highlight Glossary Terms

Please take a moment to browse our Glossary of Technical Terms.