Every DBA has received the call before. It’s on a Friday and you’re getting ready to go home and enjoy your weekend. All of a sudden your phone rings. You know you shouldn’t answer and you should turn the other way. Probably due to a lack of oxygen in your cube habitat, you decide to answer the phone.
"Uhhh Brian, I made a terrible mistake," mutters the developer. Luckily you have your trusty Tums bottle nearby and you decide that preventative action is needed before the developer continues. You pop a few Tums and grunt at him to continue.
"The last upgrade we ran at in production wiped out all the accounting records because I forgot my WHERE clause," the developer moans. You quickly realize that you’ve been eating the Tums tablets like popcorn as the developer speaks in slow motion. You do this because he didn’t call you 3 hours ago when he first did this. You know that your system receives 5,000 transactions an hour and 3 hours of data loss is impossible to explain to management if you do have to restore from you last transaction log at 3:15 PM.
You stare up at the florescent lights and realize that your backup/recovery plan has a hole. A huge hole that this developer just found with such ease. It’s going to be a long night.
Luckily, Lumigent Log Explorer has improved on an already brilliant product to help combat these types of scenarios. Log Explorer 3.0 came out last month to improve on their already strong monopoly in looking at the SQL Server transaction log to find rogue transactions and remove them among other things. The product has the following functions (although not limited to these):
- Viewing the transaction log (only product on the market that can do that)
- Viewing transactions in a backup file
- Viewing transactions in real-time
- Seeing who did what transaction when and rolling back that transaction
- See database statistics like how many inserts, updates and deletes occurred on the server
- Look at the history of a record and how it changes over time
- Auditing schema and security changes
- Recover transactions or even a dropped table
The most important item to mention here is that this is all done without triggers or performance hit to the SQL Server. I did see a slight performance hit for viewing transactions in real-time, but no more than one would see if you were monitoring Profiler. I did not see any performance hit when looking at the transaction log items that had already occurred. With auditing products that do use triggers, I have seen larger (although reasonable) performance hits. Because Log Explorer 3.0 uses the transaction log, it provides a gold mine of information about who did what when.
The install of the product went without a hitch. I reviewed the Log Explorer 3.0 Enterprise Edition, which has some added investigative features, which I will discuss. The install has a client component and a server component. The server component installed extended stored procedures used to peer into the transaction log and the client itself looks almost like 2.0 of Log Explorer. I did encounter typical domain problems when trying to run the program to install the server component on a SQL Server that was on a remote domain. This was fixed by loading it locally on those servers.
After that, I ran through the tutorial, which was one of the most interactive tutorials I’ve ever seen to walk you through the program. This tutorial made opening the program for the first time and actually using all of its features a snap. Whatever money Lumigent spent on this was well worth it in this reviewer’s eyes and is well worth the 10 minutes for a new user.
Once you open the application, you connect to the database logs and then you’re ready to roll. Figure 1 shows you what the application looks like. One of the nicest features that would solve the developers problem where he forgot a WHERE clause would be the Undo Transaction option. The option produces a SQL script to run against the server to undo the transaction.
Lumigent Log Explorer 3.0 can also recover lost tables, whether they were dropped or truncated. This feature will generate a script that is necessary to create the table and all of its data (as insert scripts). You may have to temporarily disable foreign key constraints as you’re inserting data if you receive table constraint errors.
In this reviewer’s opinion, Lumigent has done it again with their newest release of Log Explorer 3.0. The ability to restore data that is truncated and dropped tables was a gold mine and will save DBAs tons of time and headaches. In this time of tight budgets, if I had to have one tool in my DBA toolbox, Log Explorer 3.0 would be it. Beware Enron, because Log Explorer can find and recover your data even if you’ve deleted it and the backups!
Review in a Nutshell
Return on Investment
5.0 - A steal when you compare the price of downtime
4.5 - I wish the drop-down boxes were better organized but very easy-to-use application.
5.0 - They’re the only tool on the market that can view the transaction log
4.5 - The interactive tutorial made learning this product a 10 minute process
5.0 - Hands on tutorial and documentation were excellent.
5.0 - Saves tons of time in auditing and rolling back single transactions
Lack of Bugs
5.0 - None found during this review
5.0 - Support provided phone number to call back in less than 10 mins when anonymously e-mailed
5.0 – Lumigent has done it again!
Phone: (866) LUMIGENT
E-mail : email@example.com
Website : http://www.lumigent.com
Price : $995 / Server
Log Explorer Professional Edition
Price: $395 / Server
30 day full demos are available of all Lumigent Products