Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

An external source will be sending us XML data that BizTalk will pick up and transform into an internal schema. We need this data to be loaded into a SQL Server database as we're going to expose some of the data to our web front-end via a custom WCF service.

The question is: what is the recommended approach for doing something like this? Options we're considering are having BizTalk write to the database directly or having BizTalk call a custom WCF service which would handle the save operation. Another briefly considered idea was having BizTalk write to an MSMQ and have a custom service pull from there and store it in the database.

What are some of the guidelines or questions that should be asked in assessing these options? There are concerns related to overhead from calling the extra service, duplication of efforts if the schema is modified in the future (which it will be to some extent), and simply the best way to design within a service-oriented architecture that we're struggling with.

share|improve this question

3 Answers 3

I would adopt a pragmatic approach. If the current requirement is primarily just to get the xml data into a Sql database ETL style, there's no point over-engineering the solution at present.

Although it is tempting to make the loading process asynchronous via MSMQ:

  • BizTalk can be viewed as a kind of (Sql based) queueing mechanism in itself, by buffering messages in the messagebox (although I wouldn't recommend storing too much backlog in BizTalk, as performance degrades with #messages in the messagebox)
  • If the only reasons to use MSMQ during the load are to throttle the throughput and / or handle downtime of the Sql Database, there are a bunch of knobs in the WCF-Sql adapter, and the standard retries mechanism in the send port should cater for downstream Sql unavailability (and if all else fails, you can orchestrate load and reliability requirements, e.g. with a Singleton / Multiton orch)

IMO the option to move to a SOA service would be driven by questions like:

  • Are there additional systems (other than your BizTalk server) which need to push the same data to the Sql server?
  • If there are additional rules which need to be enforced with the data, which may require information from additional data / services.
  • If data enrichment or cleansing steps are required
  • Are there other SOA services needed elsewhere in your enterprise which may tip the scales toward a WCF loading layer?

If you do foresee change to the direct coupling of BizTalk to Destination Sql database in the future, you can use a canonical schema in Biztalk for your xml data, which is then mapped to the WCF-SQL send port as late as possible (i.e. so you are decoupled as far as possible from the generated Sql-WCF schema, which may be discarded).

share|improve this answer

A lot depends on your environment and requirements. If BizTalk can keep up with the traffic, then I'd just have it write the data to the database. Simple, easy to understand and implement.

If it has problems keeping up, though, then I'd go with the MSMQ solution. This will provide you some buffering, as well as a convenient point to add things like load balancing or sharding, if those become important. It also permits a clean architecture if you need to introduce a filter chain between the source and the database. In fact, if BizTalk becomes the bottleneck, you could simply write a simple service to take the XML and dump it onto the queue, then have BizTalk read it from there. That way you won't risk losing messages because your BizTalk service can't respond quickly enough, and would also permit you to add additional instances of the service to handle heavy data loads.

share|improve this answer

If the database is local, you can use the WCF-SQL adapter to directly use stored procedures in send ports and receive locations. A benefit of this is that you can coordinate the database transaction with the BizTalk message transactions as well.

If the database is remote, i would advice using services or a messaging / queueing approach.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.