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.
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)
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 ensure 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:
BizTalk - Receive Port reading twice from DB
Network Load Balancing Biztalk Instances