Archiving the Central repository database – ApexSQL Audit

Applies to

ApexSQL Audit

Summary

This article describes how to manually archive ApexSQL Audit’s Central repository database explains the background of the process, how to manage archived databases, and understand the implications of archived databases when creating reports.

Description

Over time, auditing can collect and store immense amount of data. Archiving option in ApexSQL Audit is a handy tool to manage and organize data that is being collected over time, meaning:

  • Better performances when creating reports
  • Manage database files on the file system
  • Easily put data to cold storage that is out of compliance retention requirements

To archive the central repository database, locate to the Archive tab in the main application ribbon, and choose Archive now option from the header controls  

Before the process starts, the user is prompted with configuration dialog allowing to set custom location to store database data files, or send a status e-mail notifications. The archiving process is initialized when opting for the Archive button in the very same dialog

The background of the archiving process

Archiving in ApexSQL Audit is accomplished by renaming the current repository (using the ArchiveCRD_yyyymmdd_hhmmss database name pattern) while keeping it on the same SQL Server instance. At the same time, a new repository (ApexSQLCrd database) is created to be used by the ApexSQL Audit service.

There is no actual copying of captured data from one database to another at the moment of archive creation, except for some basic internal system data (such as auditing configurations). This makes creating an archive quite fast, regardless of the size of the current repository.

When an archive process is finished, the history of events is available in the Archive tab, providing information on time and status of the process, as follows

Using archived repository database for creating auditing reports

When new archive databases is created through the process, it is automatically considered as a reporting data source and is available in the Data Source list inside the Reporting tab in Options

The archived databases can be easily added (1) and/or removed (2) from the data sources list. The data sources is used to tell the system what archive databases are considered for querying when creating audit data reports.

ApexSQL Audit product design also provides the ability to put archiving on a schedule, a condition-based trigger mechanism to automatically create archive databases on custom condition definitions, to learn more visit the Schedule archiving in ApexSQL Audit article.