How to configure continuous auditing in ApexSQL Log

Continuous auditing is ApexSQL Log feature which allows seamless auditing of SQL Server database for data and structure changes which affect the audited database. This feature also ensures that no duplicate auditing results are audited, regardless of the fact if the auditing output is auditing report of any supported type or a replication (redo) script.

Continuous auditing is needed if you are ever going to run ApexSQL Log more than once, consecutively, and want to make sure you pick up exactly where you left off

Just like Johnny Bench used to say, ApexSQL Log ensures “No runs, no drips and no errors”

Use cases

Continuous auditing can be used for

  1. Imagine the ability to query your transaction log? You can do that with ApexSQL Log and using the continuous auditing feature allows you to do this nightly with no gaps or duplicates. In fact, we call this feature continuous auditing, because rather than reading the transaction log on an ad hoc basis, we set up ApexSQL Log to continually poll and pull the data into a repositiry
  2. Continuous auditing feature is critical for using ApexSQL Log for transactional replication. No matter if you poll the transaction log every night or every minute, this feature will be critical to maintain the integrity of your replicated data

How does it work?

Continuous auditing is achieved by utilizing LSN (log sequence number) which is a unique identifier in the SQL Server for every record written in the database transaction log. ApexSQL Log simply ‘remembers’ the LSN of the last record (operation) which was included in the transaction log auditing job and writes this LSN in the .axtr file commonly known as the ‘tracking file’.

Example of the tracking file content:

The first time the continuous auditing is performed, the tracking file is created and the information on the LSN value of the last included operation is saved within. Next time the auditing task is initiated using the continuous auditing feature, ApexSQL Log reads from the existing tracking file for the last LSN value and will use the first next value as the starting point for the continued auditing job, updating the last LSN value once the job is finished with the new ‘last’ value. This process will be repeated each time the continuous auditing feature (and the tracking file) is used ensuring that all information from transaction log files or backups is included in the auditing job while ensuring that no duplicate results are created.

Quick tip:

Tracking file can be edited manually (via notepad or similar tools) in order to change the LSN value of the last transaction included in the previous auditing task which means users have complete control over the continuous auditing job and can rollback (or even forward) or start from the specific record in the transaction log file if the need demands it.

How to use it

In order to configure continuous auditing in ApexSQL Log, the following steps should be taken

  1. Start ApexSQL Log and click on the New session in the main ribbon
  2. Connect to the SQL Server database that needs to be audited by choosing a SQL Server and authentication method and providing the appropriate credentials (user and password) and choosing the database from the drop menu
  3. Click Next to advance to the “Select data sources” step of the wizard where the transaction log files which will be audited should be added by clicking on the add files and choosing the specific transaction log backups or even detached LDF files.
  4. Note: the online transaction log file is checked by default

Quick tip:

In cases when auditing job will not only read online transaction log file but will read from database or transaction log backup files which can be created with different names, instead of choosing the specific files to be included in the task, the ‘Add pattern’ option will allow automatic adding of all files which match specified naming pattern.

e.g. adding all database files which start with “ApexSQL Log”

  1. Next step of the wizard requires an output choice to be made. Continuous auditing is supported in all output types, but for the sake of this guide we’ll opt to create a Before-after report
  2. The filter setup step of the auditing wizard is where we choose to perform continuous auditing, so it is necessary to check this option and to specify tracking file name and location
  3. Additionally, we can configure more filters, like choosing which exact operations will be included in the auditing task (on the Operations tab), which database tables will be included, and more to ensure our auditing output includes only those transaction log records which hold high value for the auditing task at hand

  4. In the final step of the auditing wizard, we can choose the output file type as well as on which location it will be created. The important part of this step is that here we will be presented by the Batch script which can be easily saved by clicking on the Save button in the bottom of the form. This script will be consisted of all the previous configuration settings including the continuous file (tracking file) reference which makes it and excellent asset for automating the continuous auditing job, which can be achieved via scheduling the mentioned batch file to be run via windows scheduler or any similar tool. Full guide on the automation topic can be found in How to automate daily reports with ApexSQL Log article.
  5. If we want to run the continuous job manually, click on the Finish button in the bottom right will complete the configuration and after short processing auditing job will be completed.

Instead of creating continuous before-after reports, users will often opt to audit (store) results directly into the auditing repository database which can later be queried for the forensic or reporting purposes. In How to work with the ApexSQL Log continuous auditing repository directly, including querying and reporting article, a detailed guide on how to directly work with the auditing repository via SQL queries describes the task in fine details.

Furthermore, ApexSQL Log continuous auditing repository topography article, which explains a complete repository topography in fine details can be very handy to those users which decide to perform continuous auditing directly to a SQL repository database and would like to know more about how the repository ticks from the inside.

FAQs

Q: Can I use continuous auditing to achieve database (transactional) replication?

A: Yes, ApexSQL Log can create regular replay (redo) scripts which will mimic the changes made to the primary replica and allow applying those same changes to the secondary copy. A detailed guide on database replication with ApexSQL Log can be found in article Hands free, no-coding SQL Server database replication of a reporting database

Q: Can more than one continuous auditing jobs be running on the same SQL database at once

A: Yes, just make sure you use separate tacking files (.axtr files) for each auditing task and these tasks will not interfere with one another in any way