Applies to
ApexSQL Log, ApexSQL Trigger
Summary
ApexSQL has two auditing tools – ApexSQL Log and ApexSQL Trigger. There are certain advantages to using one over the other. This article is an overview on how to choose between the two
Description
ApexSQL Trigger is the trigger-based auditing tool. It helps users create audit triggers that are fired by DML operations (INSERT, UPDATE and DELETE) and DDL operations (CREATE, ALTER and DROP), and then stores details of the operation into AUDIT_LOG_DATA, AUDIT_LOG_TRANSACTIONS and AUDIT_LOG_DDL auditing tables
Pros
- Total control over what tables and operations to audit
- Audit data is stored in SQL Server tables and can therefore be manipulated like any other SQL Server table. Audit history is immediately available
- Creation of the custom reporting system based on the audit data stored in SQL Server tables
- Auditing of system databases is prevented to avoid database crashes if something goes wrong (e.g. system tables locking)
- As database administrators have full access to the data and they can change said data in the audit tables or even truncate them
- Audit DDL (schema) changes
Cons
- It can cause performance degradation, as is the nature of all triggers
- It can’t undo transactions
ApexSQL Log is the transaction log-based tool – it reads transaction logs to get the data
Pros
- If the database is already in full recovery model, then there is no downside on the space/performance
- Move transaction logs to another server and audit them there
- UNDO-ing of actions/recovery
- Schedule ApexSQL Log reading the transaction logs, so the auditing is done during offline hours
- Audit DDL (schema) changes
Cons
- The database has to be in full recovery model. If the maintenance is not planned right, the hard drives can get filled with transaction logs and this can have an impact on performance. The best way to manage the online transaction log size is to perform transaction log backups on regular basis, preferably daily
Both tools allow generating reports in various formats
About ApexSQL Log
ApexSQL Log is a SQL Server Transaction Log reader that allows viewing transaction log data in read-friendly format. Audit and undo SQL database changes of your choosing. Determine who changed the data and when the change occurred. Read the transaction log to find out who created, changed or dropped a database object
About ApexSQL Trigger
ApexSQL Trigger is a SQL Server auditing tool, which tracks data changes in SQL Server databases using triggers. ApexSQL Trigger allows you to audit database access by login, host and application name
