Archiving captured SQL Server compliance data

ApexSQL Audit is a SQL Server auditing and compliance tool that provides a wide range of possibilities for auditing of access, changes, and security on SQL Server instances, databases, and objects. As the auditing process is constant by default (unless interrupted by an admin), the amount of captured SQL Server data may be significant. Therefore, archiving audited data, stored in a central repository database, is essential to:

• Preserve HDD storage space used by the central repository database, and
• Maintain low impact on the SQL Server instance hosting the central repository database while creating reports

To archive the central repository database, use the Archive database option in the ApexSQL Audit menu

Tech note: The background of the archiving process is renaming of the current central repository database (using the ArchiveCrd_yyyymmdd_hhmmss database name pattern), while keeping it on the same SQL Server instance with a new name. At the same time, a new central repository database (ApexSQLCrd database) is created for further repository storage use

This ensures that the archiving process is fast and the archive instantly becomes available for reporting purposes. Once the archiving process is initiated, user is informed on the important information an needs to confirm the archive request.

Once the archiving process is completed, the newly created archive appears in the Options form in the Reports tab, in the Data sources section, and can be selected as the auditing information source when generating reports:

Advanced archiving collected SQL Server auditing data

The described archiving process of captured SQL Server compliance data saves HDD space occupied by the central repository database, but what about the created archives? If the archive databases remain on the SQL Server instance, no HDD space is actually saved. Therefore, an additional action by a SQL Server admin is required – archiving is just the first step before the HDD space is actually freed up

To free up HDD space occupied by ApexSQL Audit archives, move them to another storage. This can be done by:

• Detaching an archive database, moving its MDF and LDF files to a different storage, and re-attaching them back to the same SQL Server instance
• Creating a backup of an archive database, moving it to another storage location and deleting a live archive database from the SQL Server instance

However, there are downsides in both cases. The first solution frees up a particular storage while occupying another. With the second one, the archive is no longer available for reporting purposes

Our recommendation, to mitigate both described potential issues, is to use ApexSQL Restore as an additional tool. ApexSQL Restore can attach native or natively compressed SQL database backups and backup sets created in SQL Server. Such virtual SQL databases will be attached as fully usable online databases, which are accessible via SQL Server Management Studio, Visual Studio, or any other third-party tool – in this case, via ApexSQL Audit

Here is an example of using the combination of an archive, ApexSQL Restore, and ApexSQL Audit internal reports:

1. Create a backup (e.g. “CRD_Backup.bak”) of the archive database (e.g. “ArchiveCrd_20180315_111417”)
2. Delete the online archive database – “ArchiveCrd_20180315_111417”, as we’ll replace it with its backup
3. Start ApexSQL Backup and attach “ArchiveCrd_Backup.bak” backup file to the SQL Server instance using, for example, “ArchiveCrd_Backup” as a new database name
   

4. The “ArchiveCrd_Backup” database will appear as a new database on the SQL Server instance
   

5. Start the ApexSQL Audit, select the Reports from the left pane add the additional data source to the list of the available data sources. In our example, we’ll use “ArchiveCrd_Backup” database the (archive restored using ApexSQL Backup) as the new data source
   

6. The “Restored archive using ApexSQL Backup” becomes available as a data source in application options

This way, by attaching an archive database backup to the SQL Server instance used by the reports:

• space is saved as ApexSQL Backup uses just a fraction of space used by a live SQL database
• virtually attached archive database backups have empty SQL transaction logs
• a database backup becomes available as an auditing information source