I don’t believe there is any way to limit the number of SQL Server connections via Send Ports through a generic BizTalk or adapter configuration, however there maybe two possible approaches:
1. The SQL Adapter actually uses a COM+ component (Microsoft.BizTalk.Adapters.SQL) to interact with SQL Server. If you look at the properties for that component under the Queuing tab, there is a property called Maximum Concurrent Players. The Microsoft website (see http://msdn2.microsoft.com/en-us/magazine/cc301497.aspx) details this property as:
The Queuing tab also allows you to control the maximum number of
concurrent players the application can contain. (A player is a
component that plays back the queued calls to your component.) Since
every player is created on a separate thread, there is nontrivial
overhead associated with creating and maintaining a player. In extreme
situations, your application may grind to a halt if the number of
concurrent players is too large (such as a few hundred). When you set a
limit to the number of players and that limit is reached, the listener
does not create new players. Rather, queued calls remain in the
application queue, allowing calls in progress to execute and complete.
The limit is also good for load balancing purposes, and can be used in
conjunction with application pooling.
This defaults to 0 (which I presume is ‘unlimited’) – it might be worth having a play with that value (say setting it to 50) to see whether the number of open connections reaches this upper limit and doesn’t go higher
2. It would be possible to implement some delay logic through async orchestrations to limit the number of active Send Ports, only creating further orchestrations when the number of active orchestrations drops below 50.
I will try out the first approach this afternoon as I have a similar application (albeit without the SQL connection issue!)
Regards, Nick.