none
WCF-SQL and Turning Off DTC? RRS feed

  • Question

  • The first commentor on this blog: http://connectedthoughts.wordpress.com/2011/01/18/wcf-sql-versus-the-old-sql-adapter/

    indicates you can turn off DTC when calling a Stored Proc?  Is that true, and if so, how and where do you do it?

    Thanks,

    Neal Walters
    http://BizTalk-Training.com

    Tuesday, October 25, 2011 1:47 PM

Answers

  • If your SP is doing updates/inserts/deletes and you want to be 100% sure that no messages will ever get lost, you need transactions set to true on your SQL WCF Adapter. Other protocols such as SOAP ensures transactions with BizTalk out of the box, because BizTalk will wait for a HTTP response before commiting to the MsgBox even when using a one-way port.

    This also means that the SP itself should not initialize nor commit any transactions, it should be BizTalk doing that.

    The level of transaction used by the SQL adapter can be manipulated by adding extensions to the adapter.

     

    Morten la Cour

    Thursday, November 3, 2011 12:25 PM

All replies

  • Hi

    DTC is important when you have transactions (insert/update/delete) where you call a SQL Server on another server (in most cases). If you only do select then you can turn off DTC.


    Torben Christiansen http://www.snatchedmoments.com/
    Tuesday, October 25, 2011 5:00 PM
  • Hi Neal,

    I think for non transactional operations the setting UseAmbientTransaction = false is sufficient.

    HTH


    Steef-Jan Wiggers

    Ordina ICT B.V. | MVP & MCTS BizTalk Server 2010

    http://soa-thoughts.blogspot.com/ | @SteefJan

    If this answers your question please mark it accordingly


    BizTalk
    • Proposed as answer by Walter Michel Tuesday, October 25, 2011 11:38 PM
    Tuesday, October 25, 2011 7:10 PM
    Moderator
  • I'm familiar with DTC in the respect of two-phase commits. And after years of BizTalk, I've always just told people that BizTalk requires DTC.  But now that I'm using the WCF/SQL adapter instead of the built-in SQL Adapter, I'm wondering if that is no longer true. 

    So when BizTalk does call a StoredProc on a different SQL server machine, why does it need DTC?  Is it because of the persistent points, so they will be commited in sync with the user database on the other machine? 

    I've never had problems with the Stored Procs I've written, but I'm working at a client where they are doing several challenging things in stored procs (stuff I would rarely do), such as Bulk Loads, Truncates, heavy use of multiple temp-tables, commits, rollback, and various error-catching (basically the kitchen sink).  It's even an odd-design, but it was something we agreed to for supposed expediency.   We've been getting DTC errors left and right, mostly due to nested transactions.

    Thanks,
    Neal

     

     

     

    Tuesday, October 25, 2011 7:46 PM
  • Neal,

    I have not come across this kind of scenario, but in this post I read following, which makes sense to me why MSDTC needs to be running:

    "Performing operations on SQL Server using BizTalk Server always involves two resources—the adapter connecting to SQL Server and the BizTalk Message Box residing on SQL Server. Hence, all operations performed using BizTalk Server are performed within the scope of an MSDTC transaction. So, to use the SQL adapter with BizTalk Server, you must always enable MSDTC."

    HTH


    Steef-Jan Wiggers

    Ordina ICT B.V. | MVP & MCTS BizTalk Server 2010

    http://soa-thoughts.blogspot.com/ | @SteefJan

    If this answers your question please mark it accordingly


    BizTalk
    Tuesday, October 25, 2011 7:52 PM
    Moderator
  • But does that apply to the WCF/SQL adapter as well? If I call a WCF web service, that doesn't have to be in a transaction.

    Thanks,

    Neal

     

    Thursday, October 27, 2011 12:17 PM
  • Hi Neal,

    But does that apply to the WCF/SQL adapter as well? >> According to the post/article it does.

    HTH


    Steef-Jan Wiggers

    Ordina ICT B.V. | MVP & MCTS BizTalk Server 2010

    http://soa-thoughts.blogspot.com/ | @SteefJan

    If this answers your question please mark it accordingly


    BizTalk
    Saturday, October 29, 2011 9:37 AM
    Moderator
  • Excerpt from this article on msdn.

    For operations where the adapter client does not write any data to the SQL Server database, such as a Select operation, you might not want the additional overhead of performing the operations inside a transaction. In such cases, you can configure the SQL adapter to perform operations without a transactional context by setting the UseAmbientTransaction binding property to  false. For more information about the binding property, see Working with BizTalk Adapter for SQL Server Binding Properties In such cases, you do not need to configure MSDTC as well.


    Regards,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    My Blog:dpsbali-biztalkweblog
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Saturday, October 29, 2011 10:49 AM
  • But our Stored Proc is doing updates.  In the link you just sent "Working with BizTalk Adapter for SQL..." it says:

    "NOTE: Not performing operations in a transactional context is advisable only for operations that do not make changes to the database. For operations that update data in the database, we recommend setting the binding property to true; otherwise you might either experience message loss or duplicate messages, depending on whether you are performing inbound or outbound operations."
    NealWalters


     

     

    Thursday, November 3, 2011 12:16 PM
  • If your SP is doing updates/inserts/deletes and you want to be 100% sure that no messages will ever get lost, you need transactions set to true on your SQL WCF Adapter. Other protocols such as SOAP ensures transactions with BizTalk out of the box, because BizTalk will wait for a HTTP response before commiting to the MsgBox even when using a one-way port.

    This also means that the SP itself should not initialize nor commit any transactions, it should be BizTalk doing that.

    The level of transaction used by the SQL adapter can be manipulated by adding extensions to the adapter.

     

    Morten la Cour

    Thursday, November 3, 2011 12:25 PM