How to execute a command to rebuild indexes after upgrading to ePolicy Orchestrator

How to execute a command to rebuild indexes after upgrading to ePolicy Orchestrator

Environment
McAfee ePolicy Orchestrator (ePO) 5.9

Summary

This article provides a procedure to rebuild indexes after upgrading to ePO 5.9 if:
  • Index fragmentation has gone higher than 30%.
  • The task EPOCore.RebuildIndexesCommand to rebuild indexes fails (as shown in the Server Task Log).

Problem

After upgrading to ePolicy Orchestrator 5.9, you might need to rebuild indexes if index fragmentation has gone higher than 30% or if the task EPOCore.RebuildIndexesCommand fails. If tables have more than 30% index fragmentation, a background task named EPOCore.RebuildIndexesCommand is triggered to rebuild the indexes for those tables. Rebuilding the indexes is beneficial for better ePO performance.

After the ePO 5.9 upgrade, you can view the status of the task EPOCore.RebuildIndexesCommand in the Server Task Log under Rebuilding Indexes as shown in the following example.

Example showing Status: Completed for Rebuilding Indexes in the Server Task Log:



Example showing the Rebuilding Indexes details page with Status: Completed in the Server Task Log


 

Solution

Run the task EPOCore.RebuildIndexesCommand using one of the following options:
  • Remote command for a predefined list of tables: Schedule it to run via a browser using the following remote command:
     
    https://<ePO_system_IP_address>:8443/remote/EPOCore.RebuildIndexesCommand?mode=1&table=Few
     
    This remote command rebuilds indexes where index fragmentation has gone higher than 30%. The remote command works only for the following set of ePO-related tables, which are included in the EPOCore beans.xml. The remote command does not work for all other tables in ePO or other point product-related tables. To rebuild the index for other tables, use the other options in this Solution.
    • OrionAuditLogMT
    • OrionSchedulerTaskLogMT
    • OrionSchedulerTaskLogDetailMT
    • OrionTaskQueueMT
    • EPOEventsMT
    • EPORollup_Events
    • EPOEventsArchiveQueueMT
    • EPOTaskObjectSettingsMT
    • EPOCoreLogMessage
    • EPORepositoryLogMessage
    • EPOLeafNodeMT
    • EPOComputerPropertiesMT
    • EPOPolicyObjectsMT
    • EPOPolicySettingsMT
    • EPOPolicyObjectToSettingsMT
    • EPOPolicySettingValuesMT
    • EPOTaskObjectsMT
     
  • Remote command for all tables: Schedule it to run via a browser using the following remote command:
     
    https://<ePO_system_IP_address>:8443/remote/EPOCore.RebuildIndexesCommand?mode=1&table=All
     
    This remote command rebuilds indexes for all ePO-related tables where index fragmentation has gone higher than 30%.
     
  • Remote command for a single table: Schedule it to run via a browser using the following remote command:
     
    https://<ePO_system_IP_address>:8443/remote/EPOCore.RebuildIndexesCommand?mode=1&table=<table_name>
     
    This remote command rebuilds indexes for the specified table where index fragmentation has gone higher than 30%.
     
  • Procedure: Rerun the procedure directly in SQL Server Management Studio.
    1. Open Microsoft SQL Server Management Studio.
    2. Type the user name and password information for the account specified and click Connect.
    3. Expand Databases.
    4. Right-click the ePO database (where the default name is ePO_<epo_server_name>) and select New Query.
    5. Paste the following SQL statement into the query window:
       
      EXEC EPOUtil_FetchIndexesToBeRebuildAndRebuild <mode>, <table_name>, <online_option>, <table_name_prefix>
       
      Where EXEC EPOUtil_FetchIndexesToBeRebuildAndRebuild takes the following parameters:
      •  Mode - Which mode to use:
        • 0 - Fetch indexes where index fragmentation has gone higher than 30%.
        • 1 - Fetch and rebuild the indexes where index fragmentation has gone higher than 30%.
      • Table Name - Name of the table for which index fragmentation is required (set to all ePO tables by default).
      • Try Online Option - Whether to use Online or Offline mode:
        • 0 - The default. Check whether it is SQL Server 2008 or earlier and then decide on Online or Offline mode based on the edition: SQL Enterprise, Developer, or Evaluation.
        • 1 - Ignore whether it is SQL Server 2008 or earlier and try Online mode first and then Offline mode based on the edition: SQL Enterprise, Developer, or Evaluation as First or Offline.
         
        NOTE: Online index rebuilding is supported only in Enterprise Edition of SQL Server. There is built-in logic in the procedure to cross-check the deployed SQL Server Edition and, if Enterprise Edition is found deployed, internally execute it in Online or Offline mode.
         
      • Table Name Prefix - The prefix of the table (only applies when the Table Name is not sent, and is kept empty by default). By default, the prefix is set to %EPO%, meaning it checks ePO-related tables. For other point product-related tables, set the prefix accordingly.
         
      Examples:

      EPOUtil_FetchIndexesToBeRebuildAndRebuild 0

      Runs the procedure for all ePO-related tables, and fetches index details where index fragmentation has gone higher than 30%.

      EPOUtil_FetchIndexesToBeRebuildAndRebuild 1

      Runs the procedure for all ePO-related tables, and fetches and rebuilds indexes where index fragmentation has gone higher than 30%.

      EPOUtil_FetchIndexesToBeRebuildAndRebuild 0, 'EPOEventsMT'

      Runs the procedure for the table EPOEventsMT, and fetches index details where index fragmentation has gone higher than 30% in the table EPOEventsMT.

      EPOUtil_FetchIndexesToBeRebuildAndRebuild 1, 'EPOEventsMT'

      Runs the procedure for the table EPOEventsMT, and fetches and rebuilds indexes where index fragmentation has gone higher than 30% in the table EPOEventsMT.

      EPOUtil_FetchIndexesToBeRebuildAndRebuild 1, ",  0,  'FW'

      Runs the procedure for all FW-related tables where the Table Name starts with the prefix FW, and fetches and rebuilds indexes only for FW-related tables where index fragmentation has gone higher than 30%.
       
    6. Click Execute.