BizTalk 2006 / 2009 SQL Adapter

When working with SQL Server data in BizTalk Server, there are several differences between using the BizTalk SQL Adapter vs. using .Net assemblies for executing SQL stored procedures and manipulating data using CRUD (Create, Read, Update, Delete –see more on http://en.wikipedia.org/wiki/Create,_read,_update_and_delete
One of there is the behavior of persistency points when orchestrations are involved.
Using the SQL Adapter, by adding one or more receive and send ports to the scenario, the orchestration will stop and a persistency point will be created by BizTalk, and followed by sending (or receiving) the message to (or from) the port.
Contrary, when using .Net assemblies, the SQL command performed is part of the transaction, and not causing any persistency points.
Since working with SQL adapter cause persistency points and therefore increase overall required time needed to complete the operation, comparing to execute a custom stored procedure via Expression share.
But when working with long running SQL Stored procedures, invoking them via Expression shape will keep the orchestration in active state wasting BizTalk resources. Executing the same SP via SQL Adapter will cause the orchestration to be dehydrated when sending the message to port, and resume when SP will end.
Although using SQL Adapter is a bit complicated (see: http://msdn.microsoft.com/en-us/library/cc507804.aspx) there are benefits to use it.
Note that the current BizTalk 2006 SQL Adapter is obsolete in BizTalk 2009 (meaning it will be discontinue to the next version) and BizTalk 2009 introduce new WCF based SQL Adapter.
Shimshon.
(Posted also on: http://www.g-s.co.il/193.html)

BizTalk max receive locations using FTP Adapter and File Adapter

When considering developing of a business process which receive the incoming messages in mass multiple receive location using FILE or FTP adapters, one should know that a limitation exists depending on hardware performance which runs BizTalk servers.

In a particular project, we encounter this limit where 200 receive locations activated, both File and FTP adapters.

When large amount of messages arrives to those receive locations, BizTalk stops to collect these messages, and large amount of waiting threads can be found using performance monitor.

Our solution was to develop a specific .NET service which locate the messages in the locations and move the messages to one location, defined as the only one receive location.