As we did in a previous article, we’ll compare auditing features of ApexSQL Trigger and ApexSQL Audit auditing tools. ApexSQL Trigger is an auditing tool for SQL Server databases which audits and reports data changes that have affected a database. On the other hand, ApexSQL Audit is a tool for auditing SQL Server instances including data and objects access and changes, failed logins and security changes.
There are significant differences between auditing features of these two tools including:
- What is being audited/captured
- How ApexSQL Trigger and ApexSQL Audit are designed and operate
- Where and how the captured data is being saved
What is audited
ApexSQL Trigger uses triggers. These triggers capture data changes that have occurred on a database including the information on who made the change, which objects were affected, when it was made, as well as the information on the SQL login, application and host used to make the change
Audited operations are INSERT, UPDATE, and DELETE operations
Enhancements of captured data may be achieved by using Watches and Lookups to create more human-readable reports.
ApexSQL Audit captures a larger number of operations (more than 200) by taking a different approach, leveraging SQL Server traces. The primary difference is that it captures queries – what was exactly executed against the SQL Server instance or any of its objects. More details about audited operations by ApexSQL Audit are provided in the Capturing Who Saw What in SQL Server article.
Design and operation
By its design, ApexSQL Audit captures operations continuously in temporary files and then transfers them to permanent storage. Any changes on the structure of objects does not affect auditing.
ApexSQL Trigger is designed for capturing information on specified operations over selected database tables, via SQL database triggers that must be created.
If the structure of a database table being audited is changed, additional user action is required. Appropriate triggers must be re-created to match the new table definition.
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 Web console.
On the other hand, ApexSQL Trigger by default stores all captured information in each audited database itself, using two additional tables. However, a central repository database can be used for all audited data instead, as explained in the Using a centralized SQL Server auditing model article.
When to use one over the other
ApexSQL Trigger is designed primarily to make client applications self-auditing. In such a scenario, the auditing can be added to the database via triggers, and wherever it goes, the auditing functionality can go with it. This makes it a convenient tool for business application developers, and affords an opportunity to add a great feature to a completed application, self-auditing, that ships with the finished build.
ApexSQL Trigger isn’t a good solution for databases that change a lot, for cases where the risk of tampering is high (as triggers can just be disabled or deleted), or for companies with many databases and the need to manage audit data from a single repository
ApexSQL Audit is designed more for the enterprise, where it can more efficiently and effectively meet the compliance and regulatory needs or a large number of databases. It offers reports, out of the box, that meet specific compliance requirements. It addresses requirements for maintaining the integrity of auditing data, or at least mitigating efforts to tamper with or otherwise alter it. It also provides a strong foundation for many value added features like custom reporting, alerts, metrics, and other features either currently implemented or planned for the future.
August 12, 2013
