Repository maintenance options

Problem Description

Since the central repository database is the tamper-evident environment, what options are there to perform maintenance of the previously collected data without affecting the data integrity checks.

Cause

The central repository can grow at a rapid pace in some environments which can also lead to the creation of many SQL repository archives with much data that should not be kept for prolonged time periods in most cases in which case removing any excess data is needed.

Resolution

SOLUTION 1

The first solution for this task is to delete all excess data from the SQL repository itself. This can be performed using the ‘Purge data’ option from within the application UI which offers a large variety of filters to ensure only specific events that hold no value for the auditing job (anymore) are removed from the archives.

Step 1: Creating an archive

Since data purge operation cannot be performed on the live central repository database, first ensure that the current repository is archived if it currently holds the audited data that needs to be deleted – a new SQL repository will be created to collect freshly audited data while the newly created archive can be purged.

In the Archiving central repository database article, we describe the archiving process in full details:

Step 2: Removing the database archive from active data sources

Another requirement for performing purge operation on the SQL repository archive is to not have it appended to the data sources list in ApexSQL Audit.

To remove any archives, do the following:

1. Go to application Options
2. Navigate to the Reporting tab
3. Select the archive to remove and click on the red X

Step 3: Purging data

1. With the preparations complete, data purge can be initiated by clicking on the Maintenance button in the main ribbon

2. Next, choose the Purge data option

   

3. In the next step of the wizard, choose the database to purge and provide a valid time period

   

4. Next, choose event source, operations, and general filters to pinpoint which exact operations will be deleted and click Next

   

5. Finally, on the summary screen click the Purge button and wait for the processing to complete

The purging process can be performed multiple times on multiple occasions, so clearing only ‘chunks’ of data at a time is a viable strategy.

SOLUTION 2

Instead of deleting the data as described in solution #1, this maintenance approach has the goal of keeping the audited data instead of deleting it by splitting SQL repository archives into two or more archives. The split process is found in the same maintenance menu as the Purge data option.

This time, select the Split data option, and in the maintenance, wizard do the following:

1. Choose the SQL repository to split
2. Set the file location for the split database (optional)
3. Choose the split condition between:
   1. Split by size
   2. Split by date/time

   And click Next

   

4. In the maintenance summary, click on the Split button and wait for the process to finish

Once completed, the repository will be split into a few/several databases which can then be brought offline, taken to storage, even deleted, or more.