Introduction

One of the best known third-party products in the SQL Server arena is Log Explorer from Lumigent

Technologies. I am not saying every DBA has it in his toolbox, but most of us have heard about it. It is the only tool (that I know of at least) that connects directly to the transaction log of a database and can show you what was done, when it was done and who did it. You can view any item in the log, including viewing it in real-time. You can also view items in log backups. However, it is not limited to viewing; you can rollback any transaction in the log, including recovering entire tables that were truncated or even dropped. Because Log Explorer works directly with the log no error prone or performance degrading triggers are needed on any of your tables. This also means that it is not limited to data changes; you can view and undo changes to schemas and permissions as well. All of these tasks are done when the database is online and in use. Newer transactions, entered after some are undone/redone in the log are not affected by the operation. Compare this to the standard way of recovering from unwanted transactions, such as DELETEs without WHERE clauses, which required taking the database offline, restoring an old backup, applying transaction logs to a point-in-time and finally bringing it online again.

Since I had never tried Log Explorer, and the latest review of the software at SQL Server Central was for version 3.0, I decided to give it a test run. The new version (4.0.1, released November 4, 2003) that I tested has the following new features:

• Instant notification functionality for key events such as create/alter/drop objects, backup/restore, execution of DBCC commands and deadlock & rollbacks.
• Restore any dropped table by simply pointing Log Explorer to a backup that contains the table.
• Undo/redo for new commands that were not possible in earlier versions, including stored procedures, triggers, and functions.

Because of the other great things I had heard about it, I was eager to test Log Explorer.

Environment

I downloaded the free trial version from Lumigent's web site (www.lumigent.com) and installed it on my laptop. The trial version can only be used to attach to the logs of the databases Northwind, pubs, or LumigentDemoDB (created by the included tutorial), so naturally the testing is mostly functionality testing as opposed to reviewing any performance capabilities. Log Explorer is made up of two parts, a server component and a client component. The server component is installed on the machine that SQL Server is running on and requires no other machine configuration or resources than SQL Server. The client component can be run either on the same machine as the server component or any other machine running Windows with a network connection to the server. I used my laptop (running SQL Server 2000 Developer Edition on Windows XP) as both server and client.

Installation

The installation process was simple: click Next a couple of times and enter a little configuration information. If you are installing the client component on a different computer than the server you might need to change the way that the components communicate. By default, they use named pipes, so if you are behind a firewall or somehow cannot use named pipes you will have to change this to use TCP/IP instead, which is a simple configuration option once you have installed the client. Also note that Log Explorer

supports SQL clustering but is not a cluster-aware application, so it must be installed on each server if installed in a cluster environment. I installed the free trial version that does not require a license key and only works for 30 days, but if I decide to 'upgrade' to the full version I just pay for a license and enter the license information in the product. No further installation is required. In addition, since the Northwind

and pubs databases by default use Simple recovery mode, I also had to switch them to Full recovery mode to be able to use them.

Using Log Explorer

The installation of Log Explorer comes with a very easy to use hands-on tutorial that explains much, but not quite all, of the functionality in the product. However, the first thing I did after I installed the product was to start it without going through the tutorial or reading any manual. This way I have a chance to get a feel for the intuitiveness of the product. And besides, let’s face it, who reads a manual if they don't have to? After I specified which server I wanted to connect to I got to choose which database to attach a log file for. I could either choose the online log or a log backup. I didn't know it at that moment, but Log Explorer is actually capable of attaching more than one log file, either several backup files or a mix of backup files and the online log. You can then apply all the functionality of Log Explorer to the combined single virtual log. When you have attached Log Explorer to a log file you are ready to start exploring. The GUI is clear, it is mainly grids and menus except for some unnecessarily big purple borders. As you can see in the image to the right the menu items are very self-explanatory. Still, I would probably have felt more at home in a standard Windows GUI, maybe even an MMC snap-in. Nevertheless, I can't really complain, as it took me only a second to start viewing what was happening right then in my online log. Just a single click on 'Real-time Monitor', and then an INSERT statement in Query Analyzer, and I could immediately see the transaction in Log Explorer: Viewing the log in realtime (click for larger image)

The main menu of Log Explorer

As you can see in the screenshot I can easily see all info regarding the transaction, who executed it, at which time, and from which client. I also tried undoing this transaction. A right click with the mouse on the context menu gives me the option I am looking for, although I once again found that a standard Windows application interface was missing - Ctrl-Z for undoing. OK, maybe Ctrl-Z might be misleading, but there is actually no keyboard shortcut for undoing a transaction. Navigating the application using only the keyboard was not possible, and pressing Ctrl-Tab gave me the rather strange view seen in this screenshot.

It's not really important as long as the application works well, but I always have trouble understanding why applications don't work more in a standard way. OK, back to my testing. As I said, I tried the Undo transaction functionality found in the context menu when right-clicking my INSERT_ROWS operation. A great thing about Log Explorer is that it never actually does any changes directly by itself. When I chose Undo it created a script that contained the following SQL statement:

DELETE [dbo].[Region]
WHERE [RegionID] = 42
AND [RegionDescription] = N'Somewhere and nowhere                             '

The script was immediately opened in Notepad so I could copy-paste it into QA (why not open in QA directly?), but I chose to try the menu item Run SQL Script in Log Explorer instead. As I guessed, I could run my newly created SQL script from there (though not edit it unless I opened it in Notepad again) and it ran smoothly. Going back to View Log I could then refresh the view with the latest transactions from the online log and there was, of course, my DELETE statement.

Steps included in the tutorial

I then went on to work through the tutorial. The steps of the tutorial are shown in the image to the left. As you can see, Log Explorer really has many useful features besides just looking through the log. I particularly liked the ability to restore entire dropped tables by simply pointing to a backup that contained them, reviewing the revision history of any row in the database (and being able to restore it to any of these revisions), and the very extensive filtering available. Using the filters you can view the online log in real-time without being completely cluttered, quickly find specific transactions in older logs, and undo/redo several transactions at a time. I tried my best to confuse the script generator by using image and text data and other things difficult to create automatic scripts for, but I couldn't find any errors in any of the SQL scripts generated. The only time I could not run the SQL directly was when I tried to restore data from a backup for a table that did not exist anymore. Since Log Explorer didn't generate a CREATE TABLE statement, I got an 'Invalid object name' error, but Log Explorer captured the error very nicely and gave me the option to either ignore the error and move on, modify the line with the error and continue running, or cancel. I finally tested the Server Manager application included, which is where you set up notification for specific events in the databases you specify. Notification uses email, so all you need is an SMTP server (make sure relaying is allowed from the SQL Server computer). Again, just a few simple clicks and I had configured an alert for every time a DBCC command was executed in Northwind.

Technical Support

I did not really test the Technical Support of Lumigent Technologies as I was only doing a quick review of the free trial version of Log Explorer. The tutorial and documentation of the product were very satisfactory and I had no trouble understanding and using all of the features. I do however get the feeling that the support available at Lumigent is very good. On their Web site, Lumigent has good descriptions of how they work with problem reports and questions, including how issues are escalated. In addition to providing release notes, documentation, and technical FAQs on the Web site, Lumigent offers both online and telephone support. Support pricing is a percentage of the software’s purchase price, and differs depending on which support option you select (24x7 or normal business hours). When purchasing Log Explorer you are also required to purchase one year of support for it.

Conclusions

Having tried more or less all the functionality of Log Explorer, I am starting to wonder why no one else has offered a product like this. Log Explorer has many great features that can really help a DBA, especially when a problem occurs. Using most other data recovery solutions (including homemade ones) you are required to be auditing while the problem occurs, or you will not get a record of it. Reviewing the transaction log to find a problem is so much easier and more efficient. There is virtually no performance penalty since all work is done on the client. I started out with high expectations, having heard a lot of good stuff about Log Explorer, and I definitely like it a lot. Log Explorer is definitely a part of the coming budget talks!

Regarding pricing of Log Explorer there was no price included on the Web site. You can obtain price information by calling them by phone or emailing them (see product information at the end of the review for details), but since I was only doing a review of the free trial version I decided not to obtain a price and include it here. Since no two systems are alike I feel the price I would get for my setup could very well differ from other setups. Therefore I think it is best for each interested reader to contact Lumigent themselves regarding pricing for their specific setup.

Ratings

Product Information

Web: http://www.lumigent.com/products/le_sql/le_sql.htm

Developer: Lumigent Technologies
Pricing: Pricing for Log Explorer is not posted on the Web site, but can be obtained by calling Lumigent toll free at 866-586-4436 or emailing sales@lumigent.com or info@lumigent.com. Discounts are available for five or more copies. You are required to have a server license for each named instance and a client license for each user using the client component.