Tuesday, 25 May 2010

Database audit trails

I have a technology problem, and I can see that it's probably not going to be fixed any time soon. It goes like this. We have some databases that we develop at work, and of course they have to be deployed to client sites to be used. On those client sites, the client database administrators fix minor issues themselves (because it's quicker, easier and cheaper that way) while forwarding on more difficult or intensive tasks to us. Gradually, the scripts we used to create and update the database and the client's version drift apart. Eventually the day comes that we need to do a major update, but before we can do that, we need to get our scripts back in sync with the client's version.

All that is background. This is where the problem comes in. As we compare the databases, we have no audit trail, which means we don't know which version is the source of which difference and which particular version we want to keep. So my job has been to look over all the changes and decide which ones came from the client side or our side and update both sides appropriately. It's long, tedious, error-prone work that will just have to be repeated later.

Now, there is often a similar problem in regular old programming where two developers work on the same thing. We have tools that help track who made what changes, and they allow us to "merge" the two developers' work when we want to make one authoritative version. The tools know who made what changes to which files, which is more recent and what's in conflict (where both changed the same thing in different ways). If databases had the same kind of audit trails and merging tools, bringing two database schemas up to date with each other would be much easier, and would probably be done in an afternoon rather than a week and a half.

While I am a programmer and might be able to do something like this on my own, or add it to an existing open-source database engine, we work with Microsoft SQL Server which, to the best of my knowledge, doesn't provide any way to extend it in quite the manner I require. Besides that, most database systems are written with the general understanding that changes to production systems should be rare, and should be done through proper channels.

Mokalus of Borg

PS - I did find one article that might be of use.
PPS - But the real solution, I believe, involves some new standards at work.


Anonymous said...

I hope you remembered to take a towel with you today (it is Towel Day afterall)

John said...

No, I forgot about that. I used to always have a towel with me in my work bag, just because they're so useful, but it's been a while since I stopped carrying even a small one.