In Automating daily transaction log reading, we’ve shown how to automate the process of pumping transaction log data into SQL Server tables with ApexSQL Log. The proposed solution revolved around the creation of a batch file which runs ApexSQL Log CLI commands. The batch file is then scheduled with a Windows scheduler to run on a daily basis. The result is a regular daily update of 2 tables, specifically created by ApexSQL Log to hold audited data, which are populated with fresh auditing results each night.
This concept has proved consistent and useful when transaction log auditing is performed on a regular basis, since it is fully automated and easy to setup. However, the process can sometimes be difficult to manage in environments that had fluctuations and inconsistent auditing schedules, since most commands have to be changed in order to change frequency and date/time settings in the command itself. Having to rearrange the automated process regularly, for all practical purposes, mitigates much of the value of automation, in this case.
Additionally, running consecutive auditing reports manually (by running premade batch files) could cause some headaches when it is performed by different users, since they would have to get a hold of information on the last process, or sometimes analyze, large CLI commands in order to find out when the last auditing process was performed, so they would know from where the previous process ended, and from where to initiate the following auditing process.
Fortunately, ApexSQL Log has a convenient feature that enables users to perform accurate continuous transaction log auditing much more easily and seamlessly. This new approach to continuous auditing of transaction log files makes it possible to automatically continue auditing at the exact point in time where the previous auditing job has finished with no runs, drips or errors ;)
Continuous transaction log auditing uses the LSN value to remember where the previous auditing ended. The last LSN value is stored in a tracking file (.axtr) and the value is read and the file updated when the next (continuous) transaction log auditing session is conducted. With this in mind, it is possible to use continuous auditing to setup database auditing not only from the last LSN, but to start from a specific LSN value – the LSN value inside the file can simply be changed to any other LSN value by editing the file with Notepad (or any other text editor) by changing the <LastLsn> value to any desired LSN.
<LastLsn>000001B9:00010703:0002</LastLsn>
The default location of the tracking file is C:\Users\UserRunningApexSQLLog\Documents\ApexSQL\ApexSQLLog%Version%\ (where %Version% = the current major version of ApexSQL Log; e.g. 2018) and it is named from the database name by default (e.g. the file name for the database AdventureWorks2014 would be AdventureWorks2014.axtr)
No operations will be skipped when auditing as different sessions are now made seamless. Continuous auditing in ApexSQL Log always picks up where the previous auditing session finished and provides mechanisms to ensure that full auditing chain is never broken. This can be particularly useful when ApexSQL Log is used to create a backup database of the original one, by replaying all changes that have occurred on the original database against another database, where continuing at the exact moment is of utmost importance
Start ApexSQL Log and select SQL Server, provide appropriate authentication credentials, and choose database for auditing transaction log files. Click the Next button to proceed
In the Select data sources step of the wizard, add transaction log files, transaction log backups or detached .ldf files for auditing and click on the Next button to proceed
In the Select output step of the wizard, select the desired output. Continuous transaction log auditing is supported in every output option
Regardless of the output choice above, the Filter setup step of the wizard allows users to choose continuous auditing by selecting the appropriate radio button and providing the tracking file location.
Note: if the tracking file does not exist, it will be automatically created at the end of the auditing process
Finish the remaining wizard steps, which will depend on the choice made in the step 3 (Select output) and auditing job will complete (again, the results will be different and appropriate for the selected output).
With this, continuous transaction log auditing has been performed, and the tracking file has been updated with the LSN value of last operation. When continuous auditing is initiated in the future, it will continue forward from that LSN file.
In addition to using continuous transaction log auditing from GUI, it is possible to set it up within the CLI with the following command:
/cont:arg [ /continuous ]: Continuous auditing tracking file location
Note: /continuous switch is not compatible with /first, /last, /from and /to switches
e.g.
"C:\Program Files\ApexSQL\ApexSQLLog2016\ApexSQLLog.com" /server:ZWERKA\SQL2014 /database:ApexSQLLogDEMO /user:Nebojsa /password:MyPassword /undo:C:\Users\FEAR\Documents\ApexSQL\ApexSQLLog2016\undo.sql /online:ApexSQLLogDEMO_log /backup:"C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\ApexSQLLogDEMOtlog2.bak" "C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\ApexSQLLogDEMOtlog1.bak" "C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\ApexSQLLogDEMOtlog.bak" /operations:DMLALL /transactions:COMMIT BEGIN UNKNOWN /continuous: C:\Users\FEAR\Documents\ApexSQL\ApexSQL Log\ApexSQLLogDEMO.axtr /verbose /force
Being able to use continuous transaction log auditing within the CLI further allows automation of the process. In order to setup automation of transaction log auditing, a simple solution to schedule (regular) execution of batch file containing CLI command with Windows scheduler can be found in this article.
