Monthly Archives: August 2012

BizTalk WCF-SQL Transactions Hanging

I’ve had a lot of trouble recently where MS SQL database transactions never seem to finish when useAmbientTransaction is set to true in the WCF-SQL adapter. What made the problem more infuriating is that it didn’t always happen and was much more frequent in my development environment. I was trying to follow the method detailed in Stuart Brierley’s excellent blog article which describes how to execute multiple procedures/inserts/updates in one message and transaction. I’ve also experienced the same problem when transactions are declared within stored procedures BizTalk is trying to execute.

Whilst all this was going on I had noticed but discounted this error appearing in the windows event viewer.

The following stored procedure call failed: ” { call [dbo].[bts_UpdateMsgbox_SendHost]( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}”. SQL Server returned error string: “Warning: The join order has been enforced because a local join hint is used.;Warning: The join order has been enforced because a local join hint is used.;Duplicate key was ignored.”.

I had originally ignored the error mistakingly thinking it wasn’t really relevant. Why would an error on the BizTalk database have any impact on the transaction taking place on the application database?

I eventually found that this error was causing the entire transaction to hang, or at least never actually commit to the database even though SQL Agent trace proved that the queries were running all the way through to completion. The connection would just sit there, idle and open forever and there would be a lock on the database table I was trying to update/insert into. The only way of releasing the transaction would be to sp_who and then kill <PID>.

Reading more into my problems this error is releated to performance issues in the environment which is entirely plausible based on the modest hardware specs of my development VM. The solution for me was to turn off tracking on my send/receive ports. I have also read that running this SP can also help.

DECLARE @
RC int
DECLARE @fLeaveActSubs int

SET @fLeaveActSubs = 1

EXECUTE @RC = [BizTalkMsgBoxDb].[dbo].[bts_CleanupMsgbox]
@fLeaveActSubs
GO

ESB with a Publish / Subscribe model in BizTalk 2010

I’ve done a lot of work implementing both ESB and Publish / Subscibe patterns and I have come to the conclusion that they are not really compatible with each other, at least not in BizTalk 2010.

  • The Publish / Subscribe model works best when you have one master data source (the publisher) and you need to keep slave datastores in sync with the master.
  • ESB is more suited to a model which is opposite to publish subscribe, where there are multiple publishers passing messages to one subscriber. For example a chain of shops taking orders for products from various store outlets into one central order processing system. It can also be use for exposing functionality and data via WCF services using one universal end point.

The ESB is not really worth the effort unless you have a lot of clients calling your services which are not within your control, e.g. external suppliers or resellers. In this case the ESB give you the flexibility to add new services and schemas without having to ask all of your 3rd party clients to update their code. If you are developing services that will only ever be consumed from within your own organisation then I’d recommend giving it a miss as it just adds complexity for little or no benefit.

I started a discussion on MSDN a few months ago and it seems others have come to the same conclusion.