Auditing Features Comparison: ApexSQL Log vs. ApexSQL Audit

ApexSQL Log is an auditing and recovery tool for SQL Server databases which audits, reverts or replays data and object changes that have affected a database. ApexSQL Audit is a tool for auditing SQL Server, but without the ability to revert or replay changes. Let us focus on their auditing features and compare them

There are significant differences between auditing features of these two tools in many aspects:

1. What is being audited/captured
2. How ApexSQL Log and ApexSQL Audit are automated
3. Where and how the captured data is being saved
4. When to use one over the other

What is audited

ApexSQL Log is focused on server and database operations (both DML and DDL, 45 in total – see the picture below) and what was changed by execution of these operations. All operations written to a transaction log file by a SQL Server are supported and can be audited along with details on what was changed (both object and data). The audited information may be provided even for the operations that were executed prior to installation of ApexSQL Log since the tool reads database transaction logs which already contain records describing changes made to a database.

ApexSQL Log provides some additional information like the transaction time, operation type, schema, object, user, transaction ID , LSN….. The following picture shows an example of information related to a specific operation.

On the other hand, ApexSQL Audit captures larger number of operations (almost 200) by taking a different approach, leveraging SQL Server traces. The primary difference and advantage is that it captures queries – what was exactly executed against the SQL Server instance or any of its objects

Automation

ApexSQL Audit is automated by its design to capture and store all operations over selected SQL Server instances and their objects selected by the user – it captures information constantly in temporary files and then transfers them to the permanent storage – ApexSQL Audit central repository.

ApexSQL Log is not designed for automation by default, but can be automated to perform auditing and store captured information via CLI and SQL Server Agent jobs or PowerShell scripts. Further details on automating ApexSQL Log auditing are explained in the Automating daily transaction log reading article.

Centralized captured data

ApexSQL Audit stores all audited information in a Central Repository Database no matter whether it originates from a remote or central instance. This provides comprehensive usability when it comes to reviewing and reporting using ApexSQL Audit reporting application.

ApexSQL Log, by default, doesn’t store audited information in centralized database, but fully supports automated export of audited information in various formats or as a set of SQL scripts as well as direct export into a SQL Server database.

When to use one over the other

Since the concept of these two tools differs in many aspects, their usage and purpose diverge, too. When to use one over the other, in terms of auditing, depends primarily on:

• The time period which will be audited – whether it is prior to the installation of the tool or afterwards
• The range of operations which need to be audited
• Leveraging different mechanisms – transaction log files (online, backup or detached transaction logs) userd by ApexSQL Auditvs. SQL traces leveraged by ApexSQL Audit
• The automation, how audited information is stored and how it can be queried afterwards

These two tools differ in many aspects. It’s up to you to decide which one suits your needs best