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.
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:
||Windows NT 4.0 SP3+
||SQL Server 7.0
SQL Server 2000
||MDAC 2.6 or higher
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
|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:
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
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 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.
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.
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.
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.
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:
|Ease of Use
||Like the other ApexSQL products, this one has a well-done
user interface and logical placement of buttons and menu items.
||The product's features work as advertised. I'm looking
forward to some of the forth coming features.
|Lack of Bugs
||No real bugs that I saw in testing.
||I see definite use for this product, especially in the
||No contact with technical support was necessary.
||The documentation was light, but covered everything
needed to use the product.
||Overall this product performs well.
||A smooth install. A description of the components during
the install would have been an added bonus.
||This product is very intuitive and except for the initial
setup, you probably won't need the docs.
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.