Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Review of ApexSQL Log

By Brian Kelley,

Introduction

ApexSQL Log is a new product from ApexSQL, formerly LockwoodTech Software. ApexSQL Log provides the capability to passively review the contents of database transaction logs and transaction log backups. For databases prepared beforehand (more on this under installation), ApexSQL Log gives the DBA the capability to audit what activity has occurred without the use of triggers and other mechanisms which require overhead. The current version is ApexSQL Log 1.1. ApexSQL Log was formerly known as Log Navigator.

Environment

ApexSQL Log has two pieces, a GUI interface for the client and a set of extended stored procedures and accompanying .DLL file for installation on SQL Server. ApexSQL Log is designed to work on both SQL Server 7.0 and SQL Server 2000. ApexSQL Log also works with both clustered and named instances of SQL Server. Minimum requirements:

Operating System: Windows NT 4.0 SP3+
Windows 2000
Windows XP
Windows 2003
SQL Server: SQL Server 7.0
SQL Server 2000
MDAC: MDAC 2.6 or higher

Installation

Installation is pretty simple, but there is one thing to watch for. During the installation process the setup prompts for one of three choices: (1) Client side, (2) Server side, and (3) Both client and server side. If Server side components are not selected (I selected Both client and server side since I have a test SQL Server on my system), the setup program will not install MSSQL Live Log Setup (run on the SQL Server). This is the piece for ApexSQL Log that installs/uninstalls the extended stored procedures on the server. If you're just installing to a workstation, you only need the client-side component, the ApexSQL Log program itself. If you are setting up a server, install the Server side components. The difference in component installs is immediately obvious: 

Figure 1: Client Side Only Figure 2: Client & Server Side

Using ApexSQL Log

In order to use ApexSQL Log, there are a couple of tasks I had to perform on the SQL Server itself. First, I ran MSSQL Live Log Setup to install the server-side components within SQL Server. This tool is used to both install and uninstall the extended stored procedures needed by ApexSQL Log (Figure 3) if I want to monitor a live transaction log. It is not required to examine transaction log backups.

Figure 3: MSSQL Live Log Setup

I'm very conscious to what changes occur in my databases and servers. Naturally I was interested to see what ApexSQL Log installed on the server itself. The following extended stored procedures are added:

  • ln_close
  • ln_is_open
  • ln_is_valid
  • ln_open
  • ln_read
  • ln_seekga
  • ln_seekgr
  • ln_tellg
  • xp_InitLogNav
  • xp_UnInitLogNav

One .DLL is added: lognavxp.dll.

Finally, I also had to toggle the database I wanted to audit to Full Recovery mode (SQL Server 2000). This is required in order to ensure the transaction log doesn't get truncated unexpectedly, losing the very information I desire to see. In SQL Server 7.0, which does not well define the concept of recovery modes, the database needs to be set where Truncate Log on Checkpoint is turned off. Instructions on setting up the databases are included in the ApexSQL Log documentation.

Picking a Log:

ApexSQL Log can read on-line logs, which I have selected in Figure 4. I also tested ApexSQL Log against a transaction log backup and was able to read the backup without issue.

Figure 4: Selecting a Log

Viewing the Log:

ApexSQL Log will retrieve and display up to 1000 log entries when the connection is made. To see more than 1000 rows, there is the option to Show More Rows (F6). This option works well, but in the documentation ApexSQL recommends that if displaying a large number of rows, filter if possible. I pushed up the count fairly high (16,000 rows) and while retrieval took a bit, the program itself didn't really suffer from any slowdowns.  

Seeing the contents of a single row works very well on inserts. Figure 5 shows the transaction record for just one such INSERT.

Figure 5: Viewing an INSERT Command

Viewing a DELETE command also shows the information for that database record. An UPDATE command, however, does not show the contents of the record in Log view. In order to see the contents of an UPDATE command, I have to switch to History view.

Viewing History:

History view will let me see all the operations recorded in the transaction log that were performed on a particular database record. This view also happens to be the only places where I can view the UPDATE operation within the program. In Figure 6 I have switched to the History view for a particular row and have both the original INSERT as well as an UPDATE statement displayed. The Changed in red is the value MyData was set to by my UPDATE statement.

Figure 6: History View

One piece I read about being desired was auditing the contents of text columns. ApexSQL Log cannot display the contents of an operation involving a text field as of yet. Figure 7 shows the information ApexSQL Log does provide. While I'd love to see the contents of the MyText column, ApexSQL Log still permits auditing who performed a given operation and when the action was taken. A lot of time this is the core information I'm interested in as I want to see because it'll give me an idea where a breakdown was if data has been modified unexpectedly. 

Figure 7: Text Fields Not Fully Displayed

Auditing After the Fact:

Along those lines, every so often someone posts a message on SQLServerCentral.com about auditing after the fact. The case is nearly always the same: there weren't triggers in place to record changes and no traces were running. So long as the database is in Full Recovery mode, ApexSQL Log can retrieve the information stored in the transaction log and provide some, if not all, the auditing information needed. It means that I can install the server-side components after an incident and retrieve who performed the action and when it happened. 

Exporting:

ApexSQL Log also is capable of exporting the information in two formats: XML and as a SQL script. For folks like me, this is great. I can take the export and use a tool or script to process the contents, and reveal the information I'm looking for. The XML file is very straight-forward while the SQL script takes care of creating two tables where the information will be stored.

First Impressions

The initial look and feel of ApexSQL Log is different than other products I have looked at from this company. However, the folks at ApexSQL have kept the user interface clean,  logical, and easy on the eye. Setup was painless and I was up and running in a few minutes. I was able to connect to an online log and pull information back without any issue. My first impression of this product was a very positive one.

Conclusions

After putting this application through its paces, I felt this was a very good product that has definite uses. From an auditing perspective, being able to go back and look at the transaction log can be invaluable, especially if there were no other auditing mechanisms in place when a given action occurred. There were a few features I would liked to have seen, the auditing of the content of text fields being the biggest, but overall I didn't feel their exclusion detracted from the product in any way. The biggest factor that stood out to me was the product's ease of use and I have reflected such in my ratings.

Ratings

Overall I am very satisfied with ApexSQL Log. Dealing mainly on the security side, I can see where I'd use this product. Here is how I rated the product based on the SQLServerCentral.com criteria:

Category Rating

Comments

Ease of Use 5 Like the other ApexSQL products, this one has a well-done user interface and logical placement of buttons and menu items.
Feature Set 4 The product's features work as advertised. I'm looking forward to some of the forth coming features.
Lack of Bugs 5 No real bugs that I saw in testing.
Value 4 I see definite use for this product, especially in the security realm.
Technical Support NA No contact with technical support was necessary. 
Documentation 3 The documentation was light, but covered everything needed to use the product.
Performance 4 Overall this product performs well.
Installation 4 A smooth install. A description of the components during the install would have been an added bonus. 
Learning Curve 4 This product is very intuitive and except for the initial setup, you probably won't need the docs.

Product Information

ApexSQL Log is a product from ApexSQL. It can be downloaded from their web site for a two-week full-feature trial period. After that, a registration key is required to use the product. You can find more information and download this product at the vendor's page.

Product: ApexSQL Log
Company: ApexSQL
Version: 1.1
Single License Price: US$599
Vendor's Product Page: http://www.apexsql.com/index_lognavigator.htm

 

 © 2004 by K. Brian Kelley. http://www.truthsolutions.com/
 Author of Start to Finish Guide to SQL Server Performance Monitoring.

Total article views: 10381 | Views in the last 30 days: 6
 
Related Articles
BLOG

ApexSQL SQL Server VIP/MVP Offer

ApexSQL has an offer to anyone who is a SQL Server MVP or VIP for a gratuity of their ApexSQL...

BLOG

New Tool: ApexSQL Audit Viewer

ApexSQL has announced a new viewer tool for ApexSQL Audit. ApexSQL Audit does a lot of the dirty wor...

ARTICLE

A Look at ApexSQL Log

Reading the transaction log is challenging and one of the tools that enables you go get insight is A...

BLOG

[Video] ApexSQL Log – Software Review

Today I will be reviewing the product ApexSQL Log which is a tool designed for Transaction Log disco...

ARTICLE

Review of ApexSQL Report v4.3

We had Mike take a look at this product recently and he found a lot to like. He also had some sugges...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones