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.

11 thoughts on “Should Microsoft provide a transaction log reader tool?

  1. I agree – I’d like to see a more comprehensive Log Explorer, regardless who builds it as unfortunately it’s a necessary evil.

    I’m not sure education is the only solution though. Microsoft markets SQL Server as a low/self-maintnenace platform so many customers think they don’t need to employ DBAs & hence don’t employ them. Even if a DBA is employed who implements a solid backup plan, this doesn’t safeguard against hardware corruption of log backups. If a customer has a corrupt .lbak & needs to restore to a point in time past that .lbak, he’s toast without a log explorer.

  2. Yes, we should have flawless applications and perfect end user queries that never require us to do a post-mortem and figure out what went wrong.

    We should also have world peace.

    While you’re holding Middle East negotiations and convincing developers to write perfect code, I’ll be using my log reader. Let me know how that works out for you. :-D

  3. Brent – did you actually read the blog post all the way through? I *agree* there’s a need for log readers, as the world isn’t perfect, and I’d like to see that need slowly go away. And I’d like to see people stop using them as a crutch. And I’d love to see some *well-thought-out* arguments :-D

  4. Excellent Post by Paul and good comments. I agree with Paul and his candid statements. Way to go Paul. If a developer commits a stupid mistake and expects MS to help him, thats his problem. I dealt with a situation where in inspite of me repeatedly telling a DEV not to run delete statements on production without using caution, the dev did not heed to my suggestion and ran delete statements on production without using caution, and then he deleted 5 miliion rows by miatake and came to me for help. so why should i pay for some one else’s deliberate mistake?

    There is no need for MS to build this tool.People need to own to their actions.

    Thanks

    Meher

  5. I’m going to comment with basically the same response that I gave on Kalen’s blog page. I whole-heartedly agree. While there may be a small niche for a log reader tool they are not a necessity. Ask the folks at the different companies that sell them now and see how much money they are making off them. The one company that I worked pretty closely with was trying to figure out how they could license them for emergency use only as no one really wanted to buy them as a necessary tool for company success.

    So, with that being said, why would MS want to write one. There are other ways to safeguard yourself against the need of one as Paul has written and IF you do need one, buy one from one of the current vendors that sells them.

    If MS ever gets bored and wants a pet project for an application to write to help SQL Server DBA’s please let them know to give me a call as I have a couple that I would really like. :)

    Great post and no flame coming from this direction.

  6. As expected with this topic and discussion, people focus on the technical and not the business side of things.

    User mistakes are normal and expected in any business. I think it is extremely naive to say that you can "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." What rubbish!

    And using backups as a means to restore from user mistakes? What an extremely poor (and really in most cases an umimplementable) solution to a common problem. Backups should be used as a means from recovering from a "disaster", and not a means from rolling back data changes, but we have no choice as Microsoft has no other solution. It’s a hack!

    Sure, we can take database snapshots every second or use log-shipping with an 8 hour delay or buy SQL2008EE and use CDC or use save points or rely on transaction log based backups but ultimately they are all poor solutions / hacks for a legitimate business requirement.

  7. By that argument, I can say analogously that Microsoft shouldn’t have built an interactive debugger on Visual Studio, code should be written correctly in the first place and that would encourage better designs and more thought out code. Resources should have gone to, say, improving refactoring support.

  8. Paul
    I may agree with you that we do not need a log reader as we already have one: [b]fn_dblog[/b].
    I may also agree that Microsoft should not spend engineering resources on it.
    I however do not agree and cannot understand why this function is totally undocumented?
    Why DBA should beg for information when it may take a couple of hours to document this function?
    Still cannot understand what sould be documented and what should not
    May be SQL Server is now more a political than a technical product.
    Who should get the information and who should not?

  9. Paul,
    I see most all are agree with but I am not. Here are my arguments (sorry for the late).
    Human error must always be considered as something you must always have to count. Human errors are present in all phases of software:
    • Development (Transact SQL code)
    • Implementation (configuration)
    • Data Migration
    • End user use.
    SQL must face this problem in order to round the security issue.
    I am going to give you a analogy, imaging that in a large company some one erase accidentally a very importance folder of his own but doesn’t give the alert after a few days when he realize what he had made. Does the administrator of the file system need to take the decision of erases everything after accident or live that person without those documents? Even knowing the information is in a backup.
    Do you imagine that scenario in a Database? The Administrator is going to restore de Database “just before that point” in other sever and star queering, how many possibilities have him to make it completely correct?

  10. Yes, as I said in the article – it can be argued that there is a need for one. I just don’t think MS should spend time providing one when there are ways to avoid needing one, and other companies provide one already.

Leave a Reply

Your email address will not be published. Required fields are marked *

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.