ePolicy Orchestrator 5.x database size warning during upgrade

ePolicy Orchestrator 5.x database size warning during upgrade

Environment
McAfee ePolicy Orchestrator (ePO) 5.x

Problem

A pre-upgrade check during the ePO 5.x upgrade displays a warning message similar to the following:
 
The SQL database needs more free space or setup will fail.

It needs two times the current database size to continue.

The ePO 5.x installation makes a schema change on these tables. This change could take up significant additional disk space on the SQL Server, and migrating this data could take a long time to complete.

The time and space required are directly proportional to the number of rows in the table.

System Change

Upgraded any supported ePO version to ePO 5.x.

Cause

The ePO 5.x installer checks the size of unbounded tables in the ePO database.

When you upgrade from the 5.x version, it checks only a subset of the following tables. If you upgrade from earlier 4.x versions, it includes checks for all the following tables:
  • EPOEvents
  • EPOProductEvents
  • EPORollup_Events
  • EPORollup_ProductEvents
  • OrionSchedulerTaskLogDetail
  • OrionAuditLog

If the ePO installer changes any of the tables listed, and they have over one million rows, a warning message displays before the installation starts. The SQL Server needs significant reserve disk space to apply the schema changes to these tables. These changes take significant time to complete, which causes the ePO upgrade to potentially run for many hours or fail. It is important to create a full backup of the ePO server and database as directed in the Release Notes.

Solution

Perform the following steps to ensure the upgrade of ePO 5.x is successful.

Remove unnecessary data:
You can reduce the size of the unbounded tables in the ePO SQL database by using purge server tasks in the ePO console. ePO has a default server task for purging data from the EPOEvents and EPOProductEvents tables that is older than 90 days. Locate the Purge Threat and Client events Older than 90 days task in Server Tasks and click Run.
 
For the other tables, ePO does not have a default server task preconfigured to purge this data. Do the following to build one:
  1. Log on to the ePO console.
  2. Click Menu, Automation, Server Tasks.
  3. Click New Task, type an appropriate title in the Name field, and click Next.
  4. Select Purge Rolled-up Data from the Actions drop-down list.
  5. Select Threat Events for the Data Type drop-down list, select Purge rolled up items older than, and determine the age of events you want to purge: 
    • Small environments - 90 days recommended. 
    • Large environments - Select a smaller count to reduce the table size below the threshold. 
       
  6. Click the + symbol on the far-right side of the Actions drop-down list to add another action.
  7. Select Purge Rolled-up Data from the Actions drop-down list.
  8. Select Client Events for the Data Type drop-down list, and select Purge rolled up items older than and the age of events you want to purge: 
    • Small environments - 90 days recommended. 
    • Large environments - Select a smaller count to reduce the table size below the threshold.
       
  9. Click the + symbol again, add additional Actions for Purge audit log and Purge Server Task Log, and click Next.
  10. Set a schedule for the task to run and click Next.
  11. Click Save.
  12. Run the new server task.
  13. Verify that the server task has completed successfully.
     
    NOTE: It might take a long time to purge the data.

Prepare the SQL Server: 
If your company has a database administrator (DBA) for managing the SQL Server, DBA involvement is likely required for the following steps:
  1. If you have changed the Database to Full Recovery Mode (default is Simple), perform a backup and set it to Simple Recovery Mode. This cleans the transaction log and limits the growth of the transaction log size.
  2. Ensure the disk volume that contains the transaction log has additional free space, greater than the size of the ePO database MDF file to be safe. If that is not possible, ensure it has enough space to hold the largest of the tables listed above.
  3. Ensure the transaction log is set to auto-grow.
  4. Ensure the disk volume that contains the ePO database MDF file has significant free space for duplicating your largest tables from the list above.
  5. Ensure that you have set aside significant time for the upgrade to complete. It could take hours or potentially days, depending on the size of the tables.


Supporting data:
The following data from comes our internal testing of the ePO 5.x table schema upgrade.

SQL Server specifications:

  • Single SATA 2 spindle
  • 4 Core 2.8 GHz Xeon
  • LDF and MDF on same disk
Test
Table
Row count
Time
MDF growth
LDF growth
Large Client Event count. Modify Identity column while preserving time stamps.
EPOProductEvents
60 million
2 hours 15 minutes
15 GB
36 GB
Large Audit Log count.  Addition of int column with default constraint.
OrionAuditLog
14.5 million
23 minutes
5 GB
5 GB

When you have performed the steps above and have adequate disk space on the SQL Server for the upgrade to occur, let the installation proceed after the warning.