Should Microsoft provide a transaction log reader tool?

Now this one’s sure to spark some controversy…

I was checking my RSS feeds of the SQL blogs that I follow and noticed an interesting post on Kalen’s blog (the post is here). She’d been lambasted for suggesting in the weekly SQL Server Magazine newsletter that *Microsoft* shouldn’t provide a log reader tool and asked for comments. It’s worth reading her original newsletter too, as it provides some interesting history of the development of log readers.

Donning my flame-proof suit, I wholeheartedly agree. I don’t think Microsoft should spend engineering resources on a log reader tool that tries to show the SQL that generated the log records. So why shouldn’t Microsoft build one? Well, IMHO, in a perfect world with no mistakes and perfect applications, there really isn’t a need for a log reader tool. In an imperfect world, there is a need – but should Microsoft be the one to provide it? I think there are way more important tools and features that Microsoft should spend engineering resources on.

So why do people want a log reader tool, apart from curiosity? The three uses I see are change tracking, auditing, and the ability to rollback mistakes.

The first use, change tracking, is viable, and in fact the change data capture feature in 2008 is built on top of the transactional replication log reader Agent job (I’ll post more on this, and I’ve just written an article on tracking changes in 2008 for the November issue of TechNet Magazine).

For auditing, how can a log reader tell whether the SQL statement was being run under a different security context, such as after an EXECUTE AS statement? How can it tell the difference between a single statement UPDATE with a multi-part WHERE clause, and multiple UPDATE statements of single rows? And on top of that, it needs to read through all the transaction log, causing contention on the log drive. In 2008 there’s an in-built, synchronous auditing solution (SQL Server Audit), although it has issues with parameterized queries. In 2005, you could roll your own auditing by having all DML done through stored-procs that log what they did, for instance, or using DML triggers.

For the ability to rollback mistakes… don’t get me started! Recovering from user mistakes is not a situation you want to be in – you can setup your system to avoid mistakes altogether. DDL triggers, DML triggers, schema separation, no direct access to tables for DML, and so on. Any of these are better to do than having to figure out what someone did so you can undo a mistake. Ok – so people make mistakes and you’d like to rollback one statement. How about using your backups? Oh, you don’t have a good backup strategy. Well, that’s where a log reader can help, if it works. But should Microsoft have to provide it?

Yes, I know the alternatives I mentioned above are sometimes easier said than done, especially with 3rd-party applications, but that’s for the application writers to fix. Microsoft shouldn’t have to provide a tool because of broken applications, or 3rd-party license agreements that preclude adding triggers, or DBAs that haven’t implemented safe-guards. It already provides features that can stop mistakes happening, and allow auditing to happen. Why provide a log reader tool that allows other companies to then produce tools on top of that which do what SQL Server already provides out-of-the-box? And why provide a log reader at all when other companies do it already?

If anything, I’d like to see the existing tools be made to work in all circumstances. AFAIK there isn’t a log reader tool on the market today that copes with absolutely everything 2005 can put into the log. Although the log internals are supposedly proprietary, there’s an internals document that Microsoft licenses for free (at least when I was on the team until last summer) to companies wishing to build such tools, and they’re not *secret*. There’s plenty of info about the log internals on the web (some provided by me) and you can poke about to your heart’s content using the undocumented tools (that’s what DBCC LOG and fn_dblog are for).

However, what I’d *really* like to see is the need for a log reader tool to slowly die away as more devs and DBAs are educated and implement techniques for preventing the problems that log reader tools help to rectify (sometimes). One thing I haven’t mentioned above is to have a log-shipping secondary with a load-delay – that way you have a redundant copy of the data that’s always several hours behind your primary system. Or even using regular database snapshots.

Ok – that was a bit of a rant, and this is the same view when I worked for Microsoft too. To summarize, I don’t think Microsoft should provide a log-reader tool. I see the need for them, when a system isn’t setup to prevent mistakes happening, and there’s no good backup or redundancy strategy, but I think that need can be filled by 3rd-party vendors.

Happy to hear well-thought-out arguments on this either way, either privately or as comments.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.