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:

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:

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.