Improvements in ApexSQL Log 2013

Local server auditing without server-side components

In the past, server-side components were needed for two processes:

1. Online transaction log file reading – As the online transaction log is locked by SQL Server, we used Windows “duplicate handles” mechanism to gain safe read-only access to it. Server-side components provided this functionality
2. Server-side auditing – The bulk of data reading operation is performed by server-side components and only final results are sent to a client. This was the case only when a remote server was being audited

One of the new features in ApexSQL Log 2013 is auditing of local servers without the need to install server-side components. In order to do this, we had to provide online transaction log access without installing server-side components. We achieved this by utilizing a new mechanism for handle duplication using direct access to the local server. As accessing the server requires administrator privileges on the local machine, we also had to implement process elevation and make sure it is unobtrusive and easy to use. We achieved this by implementing a simple mechanism to automatically restart the application after a user confirms the elevation. We simply restart the application and it starts auditing right away utilizing all the options the user has previously selected. This is completely transparent to the user and doesn’t require any additional steps

Grid view performance improvements

The grid view got major under the hood improvements. We have rewritten the data virtualization layer, which enables the grid to show results over 100 million records instantly. We employed new technics and advanced algorithms to optimize metadata processing. Metadata is collected whenever new operation is found during auditing and we collect basic operation information in order to easily identify it later when we need to display it to the client. Some basic operation data is also collected at this moment in order to improve the filtering speed. The new algorithm optimizes this data collection process – we pack data into compact structures and use additional algorithms to combine different operation/transaction information. This provides quick access to any operation and also gives substantial performance boost to grid filtering. This was mainly achieved by reducing the number of data structures and replacing sorted data containers with simple lists. This required complex modification of the data sorting algorithm so that the list order is always enforced. The data-mapping algorithm for filtered data also had to be updated to become compatible with the new list approach

The following tables show actual performance improvements:

Exporting performance improvements

We also improved exporting performance by optimizing algorithms to better handle large databases. This was achieved by improving internal caching and data conversion algorithms

User interface improvements

The major changes on the user interface are five wizard steps for creating an audit session

1. The connection page allows to easily connect to an existing SQL Server instance. You can also browse through the recently saved sessions stored on the file system

   

2. The data source wizard page allows selecting all sources type quickly

   

3. The filtering page is now better organized and offers powerful pre-filtering, as well as table mapping and column selection. New time filters provide flexibility for users that doesn’t need auditing of the entire log

   

4. After setting up the filters, the user can choose the result type. The types available are the grid view which provides additional data filtering and refining, exporting, and a batch file for automating processes

   

5. Choosing the Grid view option will open the results in the table-like grid. The Export option allows choosing between various file formats

   

The Export results to file progress dialog has been completely redesigned:

Also many other dialogs are improved and modernized in order to provide a better user experience. Default values are set to match the most common usage scenarios of ApexSQL Log