What have you been working on?
We have been working on integrating trigger based before/after reporting
By implementing integration with ApexSQL Trigger, in the latest release of ApexSQL Audit, we managed to very quickly provide an option so very often requested by our users, to monitor before and after values of DML statement executions.
Users not yet familiar with our trigger based flagship product, might want to check ApexSQL Trigger extensive documentation that will quickly introduce them to the basics of project creation, architecture installation, object selection and trigger deployment which are basic steps needed to enable before after auditing for a single database.
Basically, the before-after functionality can be accessed using the new ApexSQL Audit toolbar button:
This will open a new child window with all the advanced trigger management features of ApexSQL Trigger. The basic purpose of this feature is to capture DML statement execution and record it into the local database on monitored server.
This before-after solution will enable users to easily and quickly create DML triggers for any SQL Server database, to deploy and manage them and to access collected data any time, by using intuitive and readable reports that can be exported to common formats like .docx and .csv
Basically users would get this ApexSQL Trigger functionality for free when buying ApexSQL Audit
This will not, however, be integrated into ApexSQL auditing, reporting or alerting functionality at this time, and will function as a separate tool.
What are you working on next?
Working on native Before/After solution – although we have added Before/After solution which is based on ApexSQL Trigger, we are working hard to improve further this feature in future by creating native support for Before/After via CLR triggers. With this approach we’ll integrate Before/After with alerting, reporting and our current filter configuration which will greatly improve usability. This will be integrated in ApexSQL Audit R4 release.
What is the ETA to production?
ApexSQL Audit 2015 R4 is planned for release in the mid-December
What have been some design and technical challenges you have encountered, overcome?
The main design challenge was designing new Before/After functionality that will be fully integrated with current ApexSQL Audit Workflow. In order to have fully integrated Before/After feature, we analyzed several existing technologies available on SQL Server. These technologies are:
- SQL Server native triggers
- SQL Server auditing feature
- SQL Server Extended Events feature
- SQL Server Trace feature
- SQL Server CLR Integration with CLR Triggers
Each technology has its own pros and cons but we decided to go with CLR Integration and CLR Triggers. This technology has practically only one disadvantage and that is – CLR Integration is not turned on by default on SQL Server and must be turned manually, but in the context of ApexSQL Audit this will be automated and will be presented to user as an option to turn it on with simple button click. Besides this disadvantage, there are numerous advantages:
- CLR Triggers are imported via dll which is signed by ApexSQL certificate which in turn means a much better security model than native SQL Triggers, which can be changed easily and additional infrastructure is needed to detect these changes
- CLR Triggers implementation is hidden from everyone so there almost none possibilities to infer the logic and tamper with triggers
- CLR Triggers are implemented as a C# library which will allow easy integration and communication with ours existing libraries
With all this in mind it was easy to pick a right technology.
What are some features planned in the next release(s) and what are their ETAs?
Big features planned in next releases are:
ApexSQL Audit R4 – ETA December 2015:
- Multiple simultaneous data sources in reports – the application will automatically choose which repository archives to read from based on the selected time span
- Advanced system status – showing detailed per instance information and information about the central instance which collects the events into the repository
- CLI for exporting report – allowing scheduling the export process through external task scheduling applications
- Improved archiving – choose to archive from specific time: last week, last month, last year or just by specific date. The current repository database will always contain the newest data and old data will be automatically moved to a repository archive
- Improved before and after auditing
ApexSQL Audit R1 – ETA March 2016:
- SQL Server 2016 support
- Choose table columns (sensitive columns) – giving the ability to specify which columns are audited
- Reporting support for column level filtering
- Include/Exclude query text – when SQL statement text is not necessary it can be excluded, which will help to make repository smaller and reports generate faster
- Configuration templates – allowing the same auditing filter to be applied to multiple audited servers, making the configuration process faster
- Aggregate and graphical views on collected data per instance
- Purge data from specific time range – old events will automatically be purged, freeing up disk space
- Improved alerts – alerts related to sensitive columns, custom alert threshold to minimize the number of alert emails, date filter, making alerts more powerful
- Health information – diagnostic window, allows detecting system wide issues and suggest possible solutions
August 20, 2015
