Using a centralized SQL auditing model

By default, ApexSQL Trigger stores all captured information in the audited database itself. However, a central repository database can be used for all audited data instead. The advantages of using a centralized SQL auditing model can be summed up as:

  • Auditing maintenance advantages
  • Audit reporting advantages

Auditing maintenance advantages

Backing up a central repository and an audited database are 2 separate processes. Also, the central repository backup doesn’t contain the actual database being audited, which is the case when the captured data is stored in the audited database

Using the central repository database makes backup storage space management easier, as it is enough to keep database backups and only one central repository backup

If a central repository is used, maintenance of all captured data is carried out using one of the Manage data options. To access it, select the Advanced tab on the top ribbon, and click the Delete button in the Data section:

Manage audit data button location

This opens a dialog showing all SQL Server jobs, including manage audit data scheduled jobs and options for their maintenance

Manage audit data dialog

Also, captured data can be purged immediately using this dialog without creating additional jobs

Audit reporting advantages

In a scenario where audited databases are used as repositories as well, the auditing reports can be generated for one database at a time. On the other hand, when ApexSQL Trigger is connected to the central repository database, all of audited databases will be accessible in the reporting module

Reporting module database selection sample

So, in order to create audit reports for different audited databases, there is no need to switch the connection between them

This is an example of a standard report where reports for multiple databases can be created at the same time

Standard database audit report sample

The same applies to the aggregate reporting module

Aggregate database audit report sample

In turn, this means that the operations, applications and users which affected more than one database can be tracked much easier:

  1. To find all operations performed by a particular user on a specific database, set the Filter row like this:

    Report filter example

  2. To find all insert operations performed by a specific user, set the Filter row like this:

    Report filter example

The procedure for setting up the central repository is explained in the Installing audit tables on another database article.

Related posts:

  1. Auditing Features Comparison: ApexSQL Log vs. ApexSQL Audit
  2. SQL auditing – Why We Developed ApexSQL Audit – Part I
  3. ApexSQL Audit fault tolerant auditing – part I
  4. ApexSQL Trigger Viewer 2014 R2 – Product update alert
  5. Auditing features comparison: ApexSQL Trigger vs. ApexSQL Audit auditing
February 28, 2013