- This topic has 1 reply, 1 voice, and was last updated 9 years, 3 months ago by
.
-
Posts
-
-
Hi to all BizTalk Gurus here.
May I ask your help in this matter.
I am currently creating a POC on how to handle Concurrency with BizTalk when using BizTalk Adapters such as WCF-SQL and FILE adapter.
The scenario
1. BizTalk Group with two or more BizTalk Servers
2. Dedicated Receive, Send, Processing Host on each Host of BizTalk Server from the BizTalk Group.
3. Use WCF-SQL to poll a table and then generate a file
4. File Adapter will pickup the file generated by the WCF-SQL.
Initially my solution was
SQL
1. Use locking hints – ReadPast, UPDLOCK
2. Acquire unique identifier and then update the record as in-process
File Adapter
1. Enable the Receive Location advance settings “Rename files while reading”.
I’ve read some articles on how to implement this kind of strategy and I even did some testing which proves to be fine for me.
For SQL, the solution seems to be working and simple but my boss doesn’t want to rely on SQL Server locking for some reason. he’s suggestion was to use another table which has a unique key constraint, where every rows returned by the polling statement will have to generate a hashkey and be inserted on the table which will throw unique constraint violation error once another biztalk server from the same biztalk group already process that record.
May I also mention that BizTalk Host clustering is not an option as we want to use the host redundancy offered by using Multiple BizTalk Server in BizTalk Group.
Please I will appreciate if anyone can give me a highly recommended solution or at least some hint on creating a good solution.
Many Thanks
-
Firstly the WCF SQL adapter. While the hashkey table idea may provide some level of comfort it will kill any scalability. You are basically single threading the the database access. Only the first host instance will ever work, as all other queries will die with a concurrency violation until the first query has completed ( i.e. when the message is written to the message box). The UPDLOCK, READPAST locking hints are a proven technique for creating a re-entrant queue in SQL – here is one of many articles written on the subject http://www.mssqltips.com/tip.asp?tip=1257
Secondly, why do you output the SQL result to a file and then read it back in again. Why not bypass this step and have the subscriber of the file adapter, subscribe to the WCF SQL adapter.
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.