Answered by:

Adapter for SQL Server

Question

Ive been told by some that Biztalk talking to your main Transactional system is not a good idea due to performanace reasons. Can someone point me to some case studies where BizTalk 2010 was used to load data directly into a OLTP system?
Concerns being raised include performance and contention with the main system that uses the OLTP system. The volume we are talking about is anywhere between 100 and 5000 new entries at a time.

Answers

There are many patterns you can implement on BizTalk depending on the circumstances. Unless the 5000 are somehow transactionally related, many applications implement patterns to flatten the load specifically to avoid big hits.

There is not a 1-to-1 relationship between SQL Operations and Orchestrations and Ports. Orchestrations are not even required unless some type of application level process is required.

Yes, Orchestrations can be shared depending on what role they serve in the app.

Yes, one SQL Send Port can process any number of operations. The natural relationship is with the SQL Instance, but that can be overridden.

Not at all. Realistically, 3 or 4 operations is relatively few. To clarify, the Adapter is the code that interfaces with an external system, like SQL Server, and is a one-time setup (actually the SQL Adapters come with BizTalk).

You would have to create a Port for each external system, but you'd have to do that on any platform.

All replies

First off, there is nothing inherently wrong with using BizTalk to update any system, main or otherwise. Some system has to do it and BizTalk Server is very well suited to that task.

Also consider that it's easy to develop a poor update application no matter what platform you choose.

You can pick any case study at
Microsoft BizTalk Server Case Studies and it will likely involve a BizTalk application directly interfacing with another LOB system. That's the roll BizTalk serves.

5000 updates is not really that many unless you mean 5000/min. Either way, the most important factor in the performance of such an interface is the actual SQL update process, preferable a Stored Procedure. That is where these efforts fail or have otherwise
undesirable results.

5000 in a batch for example, which could then be inserted one at a time into the OLTP database. The concern is however, how long that batch to complete, but as you mentioned, that's more of a sql performance question.

Now, from the example in the link above, it seems that just to call one stored procedure, you have to define a distinct and separate orchestration for this, correct?

Forgive the newbie question, but can an orchestration be reused from another, as mentioned
here ?

Can the same Adapter be reused for different procedures?

It would seem to be a real paid to have to set up adapters AND orchestrations for each stored procedure you want to call (say 3 or 4).

There are many patterns you can implement on BizTalk depending on the circumstances. Unless the 5000 are somehow transactionally related, many applications implement patterns to flatten the load specifically to avoid big hits.

There is not a 1-to-1 relationship between SQL Operations and Orchestrations and Ports. Orchestrations are not even required unless some type of application level process is required.

Yes, Orchestrations can be shared depending on what role they serve in the app.

Yes, one SQL Send Port can process any number of operations. The natural relationship is with the SQL Instance, but that can be overridden.

Not at all. Realistically, 3 or 4 operations is relatively few. To clarify, the Adapter is the code that interfaces with an external system, like SQL Server, and is a one-time setup (actually the SQL Adapters come with BizTalk).

You would have to create a Port for each external system, but you'd have to do that on any platform.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.