none
Adapter for SQL Server

    Question

  • http://msdn.microsoft.com/en-us/library/dd787968.aspx

    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.

    Tuesday, October 08, 2013 9:05 PM

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.

    • Marked as answer by shiftbit Wednesday, October 09, 2013 12:16 AM
    Tuesday, October 08, 2013 11:48 PM

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.

    Tuesday, October 08, 2013 9:28 PM
  • 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).

    Tuesday, October 08, 2013 11:11 PM
  • 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.

    • Marked as answer by shiftbit Wednesday, October 09, 2013 12:16 AM
    Tuesday, October 08, 2013 11:48 PM