Santosh Benjamin's Weblog

Adventures with AppFabric, BizTalk & Clouds

To DAL or not to DAL

with 4 comments

Do BizTalk consultants need to care about Data Access Layers? Does a BizTalk solution really need a DAL?  These are the questions that I’ve been mulling over in the past few weeks. Let me explain.

There are a couple of places where a BizTalk solution encounters a DAL. The first is where the DAL acts as an integration enabler. Here the endpoint of the LOB application we are integrating with happens to be a database. The second is where the DAL acts as a process enabler. Here the DAL provides the underpinning of the business process (that is, as part of the business process, it is frequently necessary to update a database with the state of the business document being operated on).

In my current gig, we are using both BizTalk and SSIS. SSIS is great for the ETL and various data related actions. BizTalk then takes over and passes the data to an LOB application doing various business processes as part of that communication. The nature of the processes is such that there is a significant DAL. Early on in the project we went through the usual debate on whether a custom DAL was necessary or if we should just use the requisite database adapters. Isnt the database adapter an obvious choice?  Maybe, or maybe not. In an earlier post , i talked about just such a situation a few years ago where we had choose whether to link directly to the DB or wrap the system in a web-service first and as i explained, things didn’t turn out the way they were expected to.

So, what are the considerations?

  1. Firstly, (as I explained in the post and the follow up posts) one of the key issues is the level of abstraction you are given. Especially when dealing with the scenario of integration enablers, a database endpoint is very rarely coarse grained enough to support a service oriented approach. Its more likely that you will be provided with CRUD level interfaces. Even if you decide to direct all communication via an orchestration that wraps all this, how does the orchestration actually call the backend system? Go via the adapter or use a DAL?
  2. For the scenario of process enablers, abstraction comes into play again. You don’t want to be cluttering up your orchestrations with bits and pieces of database schema related stuff. You could choose to wrap the database calls in a coarser stored proc but this leads to the next key point which is
  3. Performance. If you have a number of send ports (for all these stored procs) in the middle of your orchestrations, there is a cost associated with all those persistence points. If your transaction handling requirements permit, you could think about wrapping some of those calls in atomic scopes, but you have to be  very careful with this. If you do encounter an issue and everything gets rolled back, are your processes really designed to start at the right place all over again without compromising data integrity?
  4. If your DAL is designed well, your orchestrations will benefit from having to call methods on business level entities and, just from a persistence point consideration, will, in my opinion, be better off.
  5. Transaction Bridging : There were a few situations where we had to bridge the transaction across the database and the segment of the business process. Fortunately, the DAL being of extremely high quality (courtesy of an expert colleague) made this very easy to do.

But, having said all this, a DAL doesn’t come free. You have to write code. Sometimes lots of it. The more code you write, the higher the probable bug density. If the functionality can be satisfied with a code-generator then that will reduce the code you have to write, but it DOES NOT reduce the amount of code you have to MAINTAIN. I think many developers forget about this last point. I’m all in favour of code-gen, but don’t forget the maintenance cost.  (Further, if the functionality in the middle of your processes can be satisfied with boiler plate code, perhaps it’s an opportunity to question what it’s doing there in the first place. Can it be pushed to a later stage and componentized? )

I must confess, at one point, when wading through a sea of DAL code early on in the project, I was quite tempted to throw it all away and go for the adapters, but the considerations above outweighed the pain at that point. Now much later, with everything having stabilized, we know just where to go to make any changes and the productivity is quite high.

But I’ve seen cases where BizTalk developers didn’t care about the SQL they wrote and they ended up in a mess with locking and poor performance. And it takes a really good developer to write a first class DAL and having interviewed and worked with a number of devs I can say that its hard to find good skills in this area. Pop quiz: Do you know how to use System.Transactions yet ?  🙂

There is always the option of using something like NHibernate. If you use some coarse grained stored procs and some business entities, you could kill all the “goo” in the middle by letting NH take care of the persistence. That, i wager would reduce the bug count in that area. But watch out for the maintenance times and the bug fixing. When there’s a component in the middle that you don’t know the internals of, it can make life very hard when trying to track down bugs.

That leads me on to the point of making choices based on knowledge and not ignorance. If you want to adopt “persistence ignorance”, don’t do it because you cant write proper DAL code yourself. Do it for the right reasons.

So I hope the points above have given some food for thought. Custom code is not always bad as long as it is approached and implemented correctly. Whether you choose to use a DAL or not, do it with careful thought on issues like the ones above. As always, your feedback is welcome.

Technorati Tags: ,,

Written by santoshbenjamin

November 6, 2009 at 9:17 PM

Posted in Architecture, BizTalk, Coding

Tagged with , ,

4 Responses

Subscribe to comments with RSS.

  1. […] This post was mentioned on Twitter by FastFood Bob and FastFood Bob, Solidsoft. Solidsoft said: GA: To DAL or not to DAL « Santosh Benjamin's Weblog: BizTalk then takes over and passes the da.. #BizTalk […]

  2. hi santosh

    nice article and interesting discussion point. ive come across this design decision a lot myself too.

    its always one of those where there isnt really a right or wrong answer but like you say its easy to end up in a mess.

    I think normally I would say use adapters unless you can justify to yourself why not to, by this i mean when someone comes along and asks why you didnt use the normal expected pattern you should be able to backup the design decision with well thought out arguments and not just some lame excuse like spending an hour on trying to get it to work and not being able to.

    I think when you do use a DAL I would prefer to initially wrapper this behing a WCF facade. Mainly for the abstraction benefits and being able to test and deploy this bit in isolation which can be very useful.

    If you do have a DAL you call direct from an expression shape its probably a good practice to implement the facade pattern so you hide all of this DAL code from any orchestration, maybe even using a WCF style interface which you just happen to execute inline rather than with a remote call. This will make it easy to distribute later if you want to.

    On the NHibernate stuff, ive actually been using Linq to NHibernate myself recently and must say its very nice. I havent been using it with BizTalk though. I can see where you might want to do this but i would still recommend the above approach to people about using a facade pattern. Im not a huge fan of having orchestrations work with some kind of domain model set of classes as I think you then start thinking about the problems with a C# mind set rather than a BizTalk one. I dont mind having a domain model underneath the facade “if required” but keep the interactions between BizTalk orchestrations and C# classes simple, easy to use and easy to test

    Mike Stephenson

    November 9, 2009 at 12:40 PM

    • Hi Mike,
      Thanks for the feedback. Very useful point about the WCF facade and the potential of distributing later. I’ll keep that in mind. I also agree that a big domain model would skew the perspective from a business process view to an application centric view. We are collectively, in the team , conscious that the DAL is primarily supporting the process and in that way it is “dumb” and doesnt have a particular object model apart from the basic OM needed to abstract the database specifics. We’ve made sure to keep any process logic in the orchestrations alone. The db is tuned for various SQL related things to keep the perf really high but a simple OM on top of it makes it easy to interact with and allows for any change in the SQL artefacts that are needed.



      November 9, 2009 at 5:03 PM

  3. You find quite a lot of times that you end up needing to have a custom database to support various biztalk processes. particularly if they re batch driven processes.

    It could be a good future feature to have something little like BAM where it could create tables which you could interact with for some of the common patterns. Fair enough if its an external databse you would need to write code, but if its really internal to the process it would be nice to get rid of any coding and just have a designer to create a table that ends up in a biztalk database and then a designer to map what data you wish to retrieve. I think one of the concerns about doing this previously would always have been performance of someone creating a poor database, but with velocity that poor performance isnt so much of an issue??

    I know it could be beneficial to some projects not to have to create custom databases and to have to worry about keeping them in sync with the biztalk backups.

    Mike Stephenson

    November 9, 2009 at 5:40 PM

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: