Search code examples
biztalkbiztalk-2013r2

BizTalk 2013 R2 WCF-SQL adapter having random issues

Ahoy,

We have two BizTalk applcations in BizTalk 2013 R2 that seem to be having random issues. Both applications follow the same process.

  • Pull data from a WCF endpoint.
  • Delete data from a database via a stored procedure.
  • Insert the new data that was pulled via WCF-SQL call.

Both applications worked great during our testing for quite a while. But, over time, we've had a few issues crop up with the insert via the WCF-SQL call.

  1. A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 64, output error: 0).

This error showed up in the Sql Server logs. We had this one for about a day and then it just went away. Everything else continued to work fine on that target sql server. It was only BizTalk that had issues.

  1. Our latest error is where the request to the WCF-SQL insert happens ( the data is actually inserted ), but there never is a response. So, the Send Port continues to try and send for it's retries and the Orchestration just dehydrates.

We tinkered with every setting throughout the application to try and solve this, but only a delete of the application and a redeploy fixed this ( for now at least ).

So, I guess my question is whether or not anyone else has had these sorts of issues with BizTalk having "random" errors like this where it'll work great and then go downhill like we've seen?

I'd really prefer to have something stable that is minimal maintenance. This is an enterprise product after all.

Solution

  • I've issues similar to this happen when moving between environments where there were data differences, e.g. a column full of NULLs in QA and a column full of actual data in PROD. There are a few things you can try.

    1. Use SQL Sever Profiler to capture the RPC call coming from BizTalk, and try running it directly on the SQL Server BizTalk is calling remotely (wrap it in a transaction you roll back at the end if this is production). Does it take longer than expected to run? Debug the procedure to find the pain points and optimize if possible. I've written a blog about how to do this here: http://blog.tallan.com/2015/01/09/capturing-and-debugging-a-sql-stored-procedure-call-from-biztalk/
    2. Up the timeout settings in the binding configuration for the send port to ensure that it is not timing out before SQL can finish doing its work.
    3. Up the System.Transactions timeout in Machine.config to ensure that MSDTC isn't causing issues: http://blogs.msdn.com/b/madhuponduru/archive/2005/12/16/how-to-change-system-transactions-timeout.aspx and http://blog.brandt-lassen.dk/2012/11/overriding-default-10-minutes.html
    4. If possible, do a data compare between the TEST/QA and PROD databases. Look for significant differences, especially in columns that you are using in JOIN conditions and WHERE clauses.