Search code examples
.netsqlt-sqltransactionsmsdtc

Multiple SQL Transactional Commands Across Different Database Connections

I am using the .NET 2.0/3.5 framework for my application. I need to run several SQL commands across multiple connections and each connection is on a different server (Oracle, SQL Server). I need to make sure these commands are transactional.

For example: I need to perform an INSERT in a table on both Oracle and SQL Server databases, then commit them if no exceptions were thrown. If there was an exception, I would like to roll-back on both servers if needed.

I suspect I will need to use System.Transactions and TransactionScope. This will require that I setup the Microsoft Distributed Transaction Coordinator (MSDTC) on the database servers and also the application server.

I have looked high and low and could not find any articles describing step by step setting up MSDTC with mutual authentication (including configuring firewall settings and MSDTC settings.) I looked at the Microsoft documentation on setting up MSDTC, but it seems completely worthless and not fully documented (unless you can find me a really good MSDN article on how to set it up.)

Is using MSDTC the only way to get my job done?

If so, how the heck do I configure it properly?

EDIT:

  • I am using Windows Server 2003 for all machines.
  • I have two SQL Server's. One is SQL Server 2000 and the other is 2005.
  • I have one Oracle server and it is version 11g
  • The application we are developing sometimes must alter/create records across all three database in a transactional manner.
  • It's not a problem between the keyboard and the chair. We read the articles on MSDN on how to set up everything regarding MSDTC, but we cannot get DTCPing and other testing applications to work. We were looking for a step by step article detailing the process. I have come across MSDN documentation on more than one occasion that 'left out' steps to do certain things.
Solution

  • Sadly both official documentation from both vendors seem happy to mention the interop provider or the other but both seem loath to acknowledge the existence of the other's database offering.

    You may prefer need the documentation on Oracle Services for Microsoft Transaction Server.

    • Oracle Documentation:
      • 10g
      • 9i
      • Older versions exist but much appears to have changed after 8

    From ODP.NET 10.2.0.3 onwards you should (if you have appropriately configured MS DTC and the OraMTS dll is present) be able to simply use the System.Transactions TransactionScope just as you would if co-ordinating between two sql server databases but using a sql server and oracle connection. Oracle 10 onwards may be required for this to work pretty simply out of the box.

    Here is a guide to using DTC from .net 2.0 and Sql Server 2005 onwards. In particular it notes the OS requirements (which should largely no longer be an issue but are worth noting). In addition, unless both databases and the client are on the same machine, then network DTC must be enabled.