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.
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/Multitonorch)
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).
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.
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.
I would avoid creating any sort of chain link between: XML Document, BizTalk and Website. Unless BizTalk is doing something magical with the XML file data and/or users are modifying the data that would get sent to the website database, I would keep BizTalk and the web site database completely separate. Send nothing from one to the other. Reason: why risk requiring a change to the WebSite export functionality because of a BizTalk change?
You may find that the schema you’re breaking the XML document into is beneficial to both systems, so create a separate service that manages the XML documents and puts it into some sort of schema that is easily consumed. This gives you a single source for the XML document and hopefully any changes made won’t break the interface. This also allows both systems to use the XML data as they need.
Although a lot depends on your database setup, since BizTalk shipped with some database adapters, I would say it’s designed to handle scenarios like that without external service to write to database.
There are advantages of using BizTalk,
- You easily can diagnose if there is any errors from BizTalk without digging through the logs
- Entire code base lives in one repository, so the build / deployment pipeline is simpler
- Since there can be Schema changes, changes happens in only one code base.
- You can easily map XML schema to database using BizTalk mapper.
- Don’t have to worry about transactions, and data consistency since BizTalk will handle these for you.