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
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
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”
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
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