Currently, I am working on a BizTalk Server 2006 solution and I came across an interesting point about how a SQL receive adapter should be hosted.

Bytheway, I am not the only one with this scenario ; Richard Seroter posted the same issue on his blog, “BizTalk SQL Adapter Advice Requested” (http://blogs.msdn.com/richardbpi/archive/2005/11/22/496020.aspx) .

So, the scenario involves at least two physical servers, each one hosting the same biztalk host, e.g. “host1”. Host1 host many things, and in our case it is the one hosting a SQL receive adapter, e.g. SQL_R1.

The main function of SQL_R1 is to call a stored procedure on a production database, see whether or not there is any work to be picked up. If there is some, then it returns the TOP 10 records (or whatever), and set a flag. All of this is happening inside a distributed transaction (assuming that the production DB is on a different server than the BizTalk MessageBox DB):

– Our production table is enlisted in that transaction

– The BizTalk messagebox which will receive the message is also enlisted in that transaction

Under some circunstances (e.g. loads), a concurrency issue arises: one of the instance of SQL_R1 will have its transaction process terminated by the Resource manager on the production DB server (this is due to lock issues). Ok. this might raise some warnings on the BizTalk server hosting that SQL_R1 which had the error, but the real issue is that after a number of errors raised on the receive location for  SQL_R1, Biztalk will disable that receive location and both instance of SQL_R1 will stop running.

At the moment, the solution I implemented is as follow:

– Create a new host to host the SQL receive adpater.

– Create two host instance (in-process) – one per server (set “do not start” on one of the server).

– Allow the SQL adpter’s receive function to be hosted by the new host.

– Change the setting of the SQL_R1 in my Application to use the new “receive handler host”.

Pros and Cons:

+ we do not have anymore the SQL recieve adapter disabled by BizTalk (the transaction is not anymore terminated)

– We have lost the “automatic failover” facility  on the SQL receive adapter. Now, when the server hosting SQL_R1 is down, we need manually to  start the second host instance for SQL_R1 on the second BizTalk server.

I will carry on searching for other solution/implementation for this scenario.