Architecture – Write data to SQL Server directly from BizTalk or use external service

Architecturebiztalksql server

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.

Best Answer

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).

Related Topic