Multiple BizTalk Servers in a BizTalk Group - How to handle concurrency?

Question

Hi,

I'm currently responsible for putting up a multi environment BizTalk Deployment and we're planning on deploying two or more BizTalk Server in a BizTalk Group to provide High Availability and Scalability.

Our concern now is how to handle concurrency for adapters like WCF SQL and File Adapters because it is likely possible for example for SQL Adapter that two BizTalk Receive host will keep on polling on the same DB table.

Initially my idea for SQL is employ locking hints but i'm not really sure if there's any other concern on this or if there's any other proven solution.

For File Adapter What I know is it's possible to set the receive adapter to change the filename of the file during processing so that other BizTalk host will avoid picking up the file yet I'm not very sure if this solution is enough already to handle concurrency.

I would appreciate any help / suggestions.

Many Thanks

Answer 1

In general most BizTalk adapters will avoid race conditions for you out of the box.

For example, the BizTalk file adapter already implements file locking so multiple instances of the file adapter on different hosts will not read the same file.

The usage of the file rename feature is explained below (From the MSDN documentation)

You can also configure the file receive adapter to rename files when processing them. You should rename files to ensure that the receive adapter does not generate duplicate messages if the receive location is shut down and restarted

There are several adapters that are not safe when run on multiple instances. These include (but there may be others) the POP3, FTP, MSMQ/MSMQT and Database adapters in polling scenarios.

For those adapters you can use the BizTalk feature of clustered hosts. There is a post here explaining the usage and configuration of a clustered host. This is the easiest way to exsure your SQL polling will not produce duplicates. You can also achieve this at the SQL level if you need to.

Beyond that you are really getting into the specifics of business processes and you will need to design for each case. For example - what happens if the same file is provided twice by a source system?

See the two following SO posts for some more information around this subject:

http://stackoverflow.com/questions/2165668/biztalk-receive-port-reading-twice-from-db/2166434#2166434

http://stackoverflow.com/questions/1951469/network-load-balancing-biztalk-instances/1955678#1955678

Answer 2

Hi, this section of MSDN can also help you out:

http://msdn.microsoft.com/en-us/library/aa558765(BTS.20).aspx

Answer 3

Hi David,

Thank you for the answer and the links you have provided. Actually I’ve read already some of them and I was also able to do some testing regarding those solutions. For SQL adapter, BizTalk Host clustering proves to be a good solution but will cause you not to maximize the advantage of BizTalk Group (Host redundancy) for scalability purpose. So for that reason we've decided we're going to avoid the clustering solution but for MessageBox which really need the clustering for High Availability. What we’re trying to accomplish is having an Active / Active or shall I say Load Balancing by means of BizTalk Server Group which means we are really going to have several SQL receive adapter polling simultaneously. Initially my solution was in query level handling it thru locking hints, below is sample of my SP.

MERGE INTO EmployeeComp
USING (SELECT EmployeeID
FROM
[AdventureWorks].[dbo].[EmployeeComp] 
WITH (READPAST, UPDLOCK) 
WHERE
[AdventureWorks].[dbo].[EmployeeComp].[Status] = 0) e(EmployeeID)
 ON EmployeeComp.EmployeeID = e.EmployeeID
WHEN MATCHED THEN
 UPDATE SET
 STATUS = 2

OUTPUT Inserted.EmployeeID, Inserted.Name, Inserted.Status;

Can you give some feedback with the SP I’ve created? I did several testing with around 500,000 data being read by BizTalk WCF-SQL adapter and then at the same time another thousands of data being write and it prove to be working as what I expected.

Although solution seems to be fine but I’m not yet sure what are impacts on this kind of solution, maybe you can shed me some light with this also?

Also I was thinking if it’s possible to use SQL Server Service Broker from this article. http://blogs.msdn.com/adapters/archive/2008/06/30/using-the-wcf-sql-adapter-to-read-messages-from-ssb-queues-and-submit-them-to-biztalk.aspx

Right now I’m still checking with it and not yet sure if how possible to work this out.

Many Thanks