Soup Recipes

I'm not much into BTS 2006 yet (hopefully i'll have chance to have more hands on on it). So, pardon me if you still see a lot of BTS 2004 features here.

I was often asked about the huge tracking database that BizTalk 2004 have over time. One my my colleague's project was having 80Gb of harddisk space after only 1 year of operation. My other colleague's development machine took about couple Gb of HD space. This is normal, if there are a lot of messages involved, as for each of the message that comes into and goes from BizTalk Server, it may spawn into multiple messages internally. And each of them will be recorded into default BizTalkDTADB database. Now the problem is, how to remove those records if we're not using it anymore?

Luckily, BTS 2004 SDK (found in product CD) has a stored procedure named "dtasp_PruneTrackingDatabase", that comes handy to delete those tracking records, along with their related information (which may spawned into several tables). Best thing is, we can specify how much of data we want to retain. For example, deleting all tracking records older than 2 months.

Cool! But, this may just add another headache! We have a 80Gb table to be deleted here, and if we take a look into the stored proc, it won't commit until all of them is "delete"-ed. And that means, it may take long hours to complete the delete, not to mention the database resource it will consume. (For a 20 Gb of data, it took abt 3 hours. Not bad though...) So, one of the trick we did was, we ran the sp incrementally, deleting few older months data one at a time, rather than 1 year data at 1 go, by simply specifying the date time. It took almost the same time to complete, but at least it didn't hog the server resources (especially if it's production server).

But what if time is not in our favor? What if we need to delete the records this right minute? Then it's time to just truncate the tracking tables. Get the script here.

Remember to shrink your BizTalkDTADB database after you're done, to reclaim back your property.

EDIT:

Warning [as highlighted by softwaremaker below]: The 2 scripts above doesn't archive your tracking record, and delete/truncate is irreversible.

Last month, a customer who was exploring BizTalk Server 2006 for their new system asked me whether BTS 2006 has out-of-the-box feature for a user to subscribe to a certain event in BTS2006. Imagine this scenario:

Officer A submits a report to his immediate supervisor for approval. Upon approval, the flow stops there. Now let's say the supervisor's immediate manager wants to be alerted on a certain report type, such as a report of monthly expense with total value > $10,000.

Naturally, my response was, sure, you can do it with BizTalk Server. Just add a new business rule policy, create a new send port exclusive for the manager, and forward a copy of the report to the manager's email when the policy was hit. But then, his next question is, can the manager subscribe to the event themselves (rather than subscribe to a message type only)? Perhaps from a web page, with something like UDDI where managers can look into the list and just add the event to their account?

Okay, i was stumbled... for a while. It makes sense for such request. Well, although this is still technically possible, but this is not out-of-the-box, and the complexity increased with allowing users to subscribe to BTS through the web. BTS provides BizTalk Explorer Object Model and WMI that we can use to create the send ports on the fly. RuleEngine namespaces to work with the business rules and policies. Wrap everything under a nice web page. That should work.

But, BTS is supposed to work behind the firewall (not to mention that it doesn't even have any web interface, other than the web services that we can publish), and there will be a lot of works and concerns to be tackled if we want to expose it entirely to the web. So, that's what i told him. Well, i can understand his disappointment.

Well, if he asked me that question now, i'll still be stumbled... for a while . Should I tell him to explore the "under development" BizTalk Services? Will BizTalk Services answer his requirements? As what I read, BizTalk Services is the BizTalk Server for the Internet (I don't read anywhere that says if BizTalk Service is an extension of the BizTalk Server. The documentations just mentioned the feature similarity). It works on the cloud, powered by .Net 3.0 entirely (Identity, Connectivity, and Workflow). I guess i'll just have to find out more, before I can answer him again...

Finally, my own technical blog, thanks to Kitkai. Nothing much yet, but i'll promise, this will be where i'll pour my technical findings, so, stay tuned.