ApexSQL Log continuous auditing FAQs

Q: How is the continuous auditing repository initially established and configured?

A: up to 5 repository tables (2 for ‘regular’ auditing reports on DML and DDL operations, and 3 for before-after auditing reports) are automatically created within each exporting script/job in a database chosen by user for the role of the repository.

Q: Is there one repository per Server instance? One per entire SQL Server estate e.g. a particular SQL Server would be specified to hold the repository tables?

A: This is entirely up to user’s preference. While a central repository which will store data audited from all different SQL Server instances is a recommended option, it is entirely possible to set up different repositories for different auditing jobs.

Q: Are Server instance and Database names tracked in the repository?

A: Yes, they are – this allows the ‘central repository’ approach to be used in full.

Q: What is the structure of the repository e.g. tables, columns?

A: 5 different tables are used to store audited data. Full information on each table can be found in the ApexSQL Log continuous auditing repository topography article.

Q: What fields are stored?

A: The following information is gathered and stored.

DML/DDL auditing

  • Log sequence number (LSN)
  • Previous log sequence number
  • Operation type (insert, delete, create, drop…)
  • Server name
  • Database name
  • Object name
  • Object schema name
  • Table name
  • Column name
  • Column type
  • Before (change) value
  • After (change) value
  • User name
  • Time when user logged on
  • State of the transaction (committed, aborted…)
  • Time when the transaction has started
  • Time when the transaction has ended
  • Transaction duration
  • Transaction description
  • Row reconstruction state
  • Unique page id number
  • Unique slot id number
  • Unique id key
  • Unique transaction id number
  • Unique line number
  • Server process id number
  • Position (ordinal) of the specific record in the table
  • Parent object schema name
  • Parent object name

Q: Is row history data stored?

A: No, it is not.

Q: Can I include, exclude fields that I want to store in the repository?

A: Indeed, it is possible to customize which exact fields will be included/excluded in/from the auditing job. Only LSN, Server name, Database name and row reconstruction information fields are mandatory.

Q: What if I want to change, reconfigure the repository tables?

A: directly reconfiguring repository tables is not possible and can/will negatively impact auditing job. Instead of changing the actual tables, it is possible to first export into XML (instead of directly to repository) and then to process the XML file to extract data into any table configuration.

Q: What are some example queries I can use to extract useful information from the continuous auditing repository?

A: All repository tables can be directly and individually queried to acquire specific audited information using SQL Server Management Studio or any other similar tool. In the events when full auditing trail/information is required, connecting auditing tables the unique “LSN” column which is the same for both table sets is the easiest solutions by utilizing ‘INNER JOIN’, e.g.

SELECT *
FROM dbo.APEXSQL_LOG_OPERATION t
INNER JOIN dbo.APEXSQL_LOG_OPERATION_DETAIL r ON r.LSN = t.LSN;

Most frequently used reports, examples and reporting guides can be found in Continuous SQL Server database auditing and reporting using the transaction log article.

Q: Can I use the UI, filters to point to the repository and query that?

A: At the moment, querying repository via UI is not available in ApexSQL Log, yet we hope to have this feature implemented in the near future.

Q: Is there a filtering and reporting UI for the repository?

A: Not at the moment, no. This is another quality of life feature we hope to introduce in some of the new releases.

Q: Is exporting to a repository database mandatory?

A: No, it is not. Continuous auditing does not have mandatory ‘destination’ for the audited data. In addition to exporting to the repository database, users can opt to export directly to SQL script, SQL BULK files, XML, HTML or CSV reports.

Q: Can I set a custom checkpoint (start point) for continuous auditing, or skip specific time frames (e.g. maintenance periods)?

A: Indeed, you can. Simply navigate to the tracking file and manually update the ‘Last LSN value’ which will be used as a starting point for the next continuous auditing job. Default location of the tracking file is %Documents%\ApexSQL\ApexSQL Log\DatabaseName.axtr

Q: Will continuous auditing continue when failover occurs in audited server cluster?

A: Yes, as long as the server node is accessible, and ApexSQL Log ‘aims’ at it, auditing job will continue as per existing configuration

Q: Will continuous auditing job be fixable in case tracking file gets lost/corrupted?

A: Yes, it will. Simply navigate to the tracking file and update the checkpoint (starting point) for the next auditing job, and ApexSQL Log will pick up from there.

Q: What is expected performance impact of continuous auditing?

A: It is minimal. ApexSQL Log only reads the transaction logs to gather audit data and inserts the data into central repository. This allows auditing to be performed during low load times or even be offloaded to another server.

Q: Can the same continuous auditing job audit multiple databases at once?

A: No, a separate continuous auditing job is required for each SQL Server database that needs to be audited.

Q: Is continuous auditing limited to online transaction log file only?

A: No, it is not. All transaction log files can be used as source for continuous auditing, including transaction log backups, differential backups and even detached LDF files. In case when transaction log files other from online transaction log backups are used, data sources must be added using ‘add pattern’ option to ensure ApexSQL Log automatically considers all available/existing files to ensure auditing success, as can be seen in the Continuous auditing of SQL Server database using the transaction log article.

Q: Can continuous auditing be used with databases in a simple recovery model?

A: No, it cannot. While *some* information will be audited, most of the auditing information will be missing since SQL Server frequently overwrites online transaction log files when database is in the simple recovery model, leaving no sources of data for ApexSQL Log to perform full auditing.

Q: Is additional license needed for the destination SQL Server where repository will reside?

A: No, it is not. A separate license is required only for active SQL Servers or cluster nodes that will be the target of the auditing/recovery job.

Q: Can i run the ApexSQL Log session manually on demand/requirement (while I have the continuous auditing job running on the target database)?

A: Yes – unlimited number of manual auditing or recovery jobs can be performed over the databases which is actively being audited in an ApexSQL Log continuous auditing job. Please note that if/when you perform these manual tasks, if you opt to use a continuous auditing tracking file that it will be updated on the job finish, so it is a good idea to use a copy or another tracking file for the manual job in cases when you do not want to affect existing continuous auditing job.

Q: I want to perform manual continuous auditing but do not want to affect the already automated auditing – what steps should I perform?

A: If there is a need to continue auditing where the previous process ended without affecting the current (active) tracking file, simply create a copy of the tracking file and use it instead of the ‘original’ tracking file to perform those manual auditing jobs.

Q: Can i have different continuous auditing jobs set on the same database at once?

A: Yes, there is no limit here. Just make sure that each job uses its own separate tracking file and that the jobs are not scheduled at the same time (only one auditing job at a time can be run).

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: What happens if a continuous auditing job fails in some cases, like in cases when connection to the SQL Server is not possible, or database is offline?

A: Have no fear, your continuous auditing job will pick up where it ended the last time. As long as the data sources (transaction log files) are available, ApexSQL Log will simply use the last checkpoint and continuous performing the auditing job even in cases when multiple jobs were skipped or failed due to any reasons.