Should I connect directly to 3rd-party databases with BizTalk or build a separate layer between them?

Question

I work for a retail company, and we want to build a unified order entry system that integrates with depots' order management applications. At the very high level, I propose that we have a BizTalk server that exposes unified web services to the order entry application (or other applications as well) and uses canonical message formats when translating from SOAP, before passing on the requests. Canonical messages would allow for reuse of the orchestrations.

Can you help me understand which of these two options is best:

1. Should we use the BizTalk SQL adapter to connect to the databases hosting the order applications (which don't provide any API at all, since they're 3rd party) from head office to depots?

2. Should we build simple web services at the depots that BizTalk can consume?

I see #2 as being redundant work, since we'd still need to transform between database records and incoming SOAP messages (and vice versa). However, something in my gut is telling that it wouldn't be a bad idea.

Answer 1

Schellack's point about coupling systems reliably and asynchronously (e.g. via queues) would always be first prize IMO.

However, if you really can't sell the queued decoupling strategy within your company, and they are still hell-bent on direct coupling to the 3rd party systems via SQL then you should consider the following:

• Use the WCF SQL Adapter
• Ensure that you aren't voiding any warranty or support agreements for the vendor's product by directly accessing the database (although even if you wrote web or windows services these would ultimately need to hit the vendor database, if there is no API).
• Encapsulate any access you make to the vendor's database via SQL Stored Procs and Views, and ensure any changes you make to the database are in your own database schema - this makes it easier to identify custom modifications to a 3rd party system, e.g. during an upgrade of the 3rd party system.
• Be aware that during periods of high volume, that BizTalk can place extreme load on downstream systems. You will need mechanisms for throttling the load placed on the downstream systems, e.g. by tweaking the adapter settings or implementing singleton / multitons in BizTalk.

The same is true when pulling data from a database :

• encapsulate the pull in a proc, limit the number of records at a time (e.g. SELECT TOP *) and adjust the polling frequency.
• When pulling data, you will also need a transparent mechanism to mark records as read without interfering with the 3rd party database.
• For pulling data, remember to factor concurrency concerns into your design, that more than one instance of the SQL Adapter may be triggered concurrently (e.g. Poll While Data Found)

There is a good write up here on writing a good 'pull data' proc.

Answer 2

When you write directly to a third-party database—particularly one that you do not control—you tightly couple your integration solution to that external system. A third party partner or vendor does not design its database with you in mind and will not care when it changes its database and breaks your integration.

If you can find absolutely no other way to store/retrieve data in/from the third party application, other than by directly interacting with its database, then you have to consider how you can do so in a way that most minimizes the coupling. Making your own order entry system talk directly to a third party application's database would, of course, be the worst choice.

BizTalk makes it fairly easy to translate between various message formats. If you will only ever map between your own order entry system and a single order management system, then BizTalk is probably too heavy of a solution for you. Just write your own receiving application to site atop the third-party application's database.

If, however, you need to convert from your order entry system to myriad order management systems, each with its own unique schema, then BizTalk might work well for you, given its mature mapping tool for data transformation.

Exposing web services on top of BizTalk, just for the purpose of giving your order entry system a means to communicate with BizTalk, also may not be the best choice. BizTalk supports many communication protocols with its adapters, and SOAP or WCF web services can be fairly chatty and requires the receiving system to be online at the time the sender tries to send a message. For systems on the same network, MSMQ often makes more sense, as it supports asynchronous, transactional messaging and comes for free with Windows.

As this white paper says:

the key requirement to place an order from Customer Managment to Order Management is for guaranteed delivery, so we might use some queuing technology (such as IBM MQSeries or MSMQ) to deliver the message where performance is traded for a higher level of reliability.

It is important to have a good grasp of how MSMQ works, as well as some of its pitfalls:

• storage considerations
• never read from remote message queues
• cluster MSMQ to make it truly fault tolerant

...so take the time to understand how everything in your solution will work together, even if you find it fairly trivial to make a .NET application send a message via MSMQ.

If you do still want to expose a web service from BizTalk, be careful not to inadvertently couple everything together by hard-coding your sending and receiving systems into a single orchestration. Here are some important

• Try to only use MessageBox Direct Bound Ports. They "yield orchestrations that are more self-contained, and therefore more reusable and easier to redeploy independently."
• Follow your own advice to use canonical schemas. It's a good idea.
• Resist the temptation to directly expose your orchestration as a web service. When you do that, you tightly couple external callers of your web service to your internal business logic.
• Keep your business logic (orchestration) separate from your data (schemas). Embedding a BizTalk map inside an orchestration marries your logic and data together. Configure your maps on send/receive ports instead.
• Think about whether your actually need to use orchestrations or not. If all you do is receive a message, transform it, and send it out, then you probably do not need an orchestration. The first rule of optimizing orchestration performance is to eliminate orchestrations.

If you follow the above advice, then using the WCF SQL adapter to write to a third-party SQL database is not too bad, since you will have de-coupled the inbound message and its processing from the outbound SQL operation. When the partner/vendor does update its database schema to break your integration, then simply deploy a new schema, update your BizTalk map, and redeploy. Isolating the impact of such a change means that you only have to re-test the transformation from your canonical schema to the external database schema (perhaps with an integration test of saving the data into the database).

Performing CRUD operations with the WCF SQL adapter is very simple. And if the vendor does ever provide an API, then swap out a different BizTalk adapter to handle the communication protocol, update your schemas/maps, and your order origination system need never know the difference.