Multiple SQL Transactional Commands Across Different Database Connections

Question

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.

Answer 1

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.

Answer 2

My answer could be a bit strange, but I'll recommend you (if it's technically possible) evaluate using of 2 independent transactions for each database. My concern regarding distributed transactions/XA is overall database performance/scalability/latency.

2 links that will try to prove my point of view:

1. Martin Fowler notes: "You have to pay attention to the order of your commits, getting the more important ones in first. At each commit you have to check that it succeeded and decide what to do if it fails."
2. Interview regarding eBay architecture

Answer 3

I use linked servers for all my tasks like this. It makes it easier for us to manage connection info and credentials. Basically one stop shopping for all our needs.

Edit: more details - We have one database used strictly for reporting. We get our data from server all over the corporation. We don't have one single account to access these servers, some of them we use a functional id, other our AD credentials. Wrapping all these connections up into separate linked servers has worked best for us. On our reporting server we currently have 16 linked servers.

We also wrap up our queries into views for easier access into our applications and crystal reports. So instead of having to create multiple connection strings in our code we use just one single global string to connect to the reporting Db.

Answer 4

This is a classic distributed transaction problem and is what MSDTC is for. Look up transaction monitors and the XA protocol for a deeper description of this class of problem.

Answer 5

When using multiple connections, you will need to use the DTC. A single connection can manage a single transaction, but if you are having multiple connections, you will need a transaction coordinator to handle the two-phase commit protocol.

Answer 6

I had the same problem. Needed to make distributed transactions between 2 SQL and 1 Oracle database server, all this from an ASP.Net application.

So I wrote about that experience in my blog, so next time I would not waste time again.

Check it at:

MS DTC Distributed transactions: Oracle 10gR2, SQL Server 2005 and Windows Server 2008 R2

Answer 7

Yes, you'll need to create a transaction scope and the ADO.Net providers for SQL and Oracle will enroll each connection into a single distributed transaction, coordinated by MSDTC.

The MSDN documentation on how to set up MSDTC is quite elaborate and covers specifically the questions you ask: Enable Firewall Exceptions for MS DTC, Configuring Security for Distributed Transactions.

You omit to mention the OS your client is running, the SQL Server version, the Oracle version, the back end OS for SQL Server(s) and Oracle(s). You also neglect to tell any actual problem you encountered or error message you've seen. Right now, the problem seem to be located somewhere between keyboard and chair.