BizTalk Database Dumps

The other day we had one of our backup drives fill up. Turns out that it was our BizTalk development server, which had been running for a few months now. We have been evaluating BizTalk for a while and are close to going to production. One of the items discussed prior to a production deployment was how to configure the database dumps. It was agreed upon to allow for the BizTalk database dump job inside of SQL Agent to handle the dumps, as opposed to our custom jobs.

Now, this made absolute, complete sense to me. The BizTalk job will issue a transaction (think ‘stop at’) that serves as a marker in order to allow for easy recovery. So, the BizTalk job issues the command, waits for a response, then does a dump of the databases. Hey, sounds great to me. And they are fairly small databases right now, so I am not worried about LItespeeed or the need for native command substitution. So, what happened?

Well, it turns out that the BizTalk job does not clean up the old database dumps after any period of time.

Let me write that again. It turns out that the BizTalk job does not clean up the old database dumps after any period of time.

Typing it twice did not make the pain go away. So I decided to look up some info on BizTalk on MSDN and I found two interesting nuggets of information:

Important  Do not store the backup files on the same storage system as the database, because in the event of a failure, both the backups and the databases would be lost.

Wonderful, it looks like someone is on the ball here, they actually understand this very basic concept. Believe me, I have seen plenty of vendor products that insist on using one drive for everything. The next nugget was:

Important  Backup BizTalk Server does not delete old backup files. You must manage those files to conserve disk space by backing them up and deleting them after new full backups have been created.

Okay then, here is my WTF moment for the day. Seriously? Who the hell is in charge of BizTalk over at Microsoft? How could they possibly go out of their way to write code to do a dump, but NOT think about cleanup afterwards? Worse yet, they recommend doing dumps every fifteen minutes! How big are the drives over at Redmond such that they never ran out of disk space? My guess is that they did, and the end result was to include this last nugget of information as a work around. As opposed to, say, writing six lines of code to remove old backups after a configurable amount of time.

My next WTF moment is the fact that we had a Microsoft consultant on the phone and not once did he mention this fact. Oh, but he made it quite clear that we wanted to be using the BizTalk dump job. He also recommended log shipping…hmmmm…maybe he recommends log shipping because that would clean up files automatically?

And there is my final WTF for all of this. Log shipping is your recommendation? Really? You think log shipping is a better option than our SAN replication? Or even database mirroring? Honestly, did you consider bringing your product into the 21st century yet? 

Sorry that this has become such a rant, but it astounds me that vendor products such as these are allowed into the wild. Then again, without such screw ups, there would be less need for people like me to fix things when they go wrong.

Hmmmm…

Forget I said anything.