Multiple SQL transaction transactions through different database connections

Question

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

For example: I need to perform an INSERT on a table for both Oracle databases and SQL Server, and then commit them if no exceptions were thrown. If an exception occurs, I would like to return to both servers if necessary.

I suspect that I will need to use System.Transactions and TransactionScope. To do this, you will need to configure Microsoft Distributed Transaction Coordinator (MSDTC) on the database servers, as well as on the application server.

I looked high and low and could not find articles describing the step-by-step setup of MSDTC with mutual authentication (including setting firewall settings and MSDTC settings.) I looked at Microsoft's MSDTC setup documentation, but it seems to be completely useless and not fully documented (unless you are find me a really good MSDN article on how to set it up).

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

If so, how did I configure it correctly?

EDIT:

• I am using Windows Server 2003 for all machines.
• I have two SQL Server. One of them is SQL Server 2000, and the other is 2005.
• I have one Oracle server and version 11g
• The application we use sometimes has to modify / create records for all three databases in a transactional way.
• This is not a problem between the keyboard and the chair. We read articles on MSDN on how to configure everything related to MSDTC, but we cannot get DTCPing and other test applications to work. We searched for a step-by-step article detailing this process. I have repeatedly come across MSDN documentation that I "missed" the steps to perform certain tasks.

Answer 1

Unfortunately, the official documentation of both vendors gladly mentions the interop provider or the other, but both do not want to acknowledge the existence of another database proposal.

You may need Oracle Services documentation for Microsoft Transaction Server .

• Oracle Documentation:
  • 10g
  • 9i
  • Old versions exist, but much has changed after 8

Starting with ODP.NET 10.2.0.3 and later (if you have properly configured MS DTC and OraMTS dll), you can simply use System.Transactions TransactionScope in the same way as if you coordinated between two sql database servers, but using sql server and oracle. Oracle 10 may be required for this to work quite simply out of the box.

The following is a guide to using DTC with .net 2.0 and Sql Server 2005 onwards . In particular, he notes OS requirements (which to a large extent should not be a problem, but worth noting). In addition, if both the database and the client are on the same computer, you must enable network DTC.

Answer 2

I use linked servers for all my tasks like this. This simplifies the management of connection information and credentials. Basically one stop for all our needs.

Edit: more details - We have one database used exclusively for reporting. We receive our data from a server throughout the corporation. We do not have one account to access these servers, some of them we use a functional identifier, others our AD credentials. Combining all of these connections into separate linked servers worked best for us. Our report server currently has 16 linked servers.

We also collapse our requests into views for easier access to our applications and crystal reports. Therefore, instead of creating several connection lines in our code, we use only one global line to connect to the reporting Db.

Answer 3

My answer may be a little strange, but I recommend that you (if technically possible) evaluate the use of two independent transactions for each database. My concern about distributed transactions / XA is the overall database performance / scalability / latency.

2 links that will try to prove my point:

• Martin Fowler notes: "You have to pay attention to the order of your commits, first get the more important ones. You have to check that it succeeded, and decide what to do if it doesn't work."
• Interview with eBay Architecture

Answer 4

This is a classic distributed transaction issue for MSDTC. Take a look at transaction monitors and the XA protocol for a deeper description of this class of problems.

Answer 5

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

Answer 6

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

So, I wrote about this experience on my blog, so next time I will not waste time anymore.

Check this:

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

Answer 7

Yes, you need to create a transaction scope, and the ADO.Net providers for SQL and Oracle will register each connection in one distributed transaction coordinated by MSDTC.

The MSDN documentation on how to configure MSDTC is quite complicated and covers the specific questions you ask: Enable firewall exceptions for MS DTC , Configure security for distributed transactions .

You omit the mention of the operating system your client is running on, the version of SQL Server, the version of Oracle, the back OS for SQL Server (s), and Oracle (s). You also ignore any problem you encounter or the error message that you saw. Right now, the problem seems to be somewhere between the keyboard and the chair.