viernes, 15 de abril de 2011

SQL Server Distributed Transactions Problems

Hi everybody.
Recently i´ve been working in an app with Microsoft SQL Server 2008.
One of the things the app must do is:
From one instance of SQL Server begin a distributed transaction and commit the transaction in another instance of SQL Server in other server.
According to microsoft, in both servers you must have the MSDTC service configured and started. I´m not going to deep in detail right now (but then i will).
I was happy beacause i had all in its place and from the source server i executed the next query:
image

but after executed the query i got an awful error:
image

OLE DB provider "SQLNCLI10" for linked server "" returned message "No transaction is active.".Msg 7391, Level 16, State 2, Line 5
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server " " was unable to begin a distributed transaction.

And that was the beginning of my headache. I was googling for some days and found nothing that could help me.
Before this happen all was good and worked well. The scenario before the error is next:
Server 1
  • Windows Server 2003 R2 (x86) Standard Edition
  • SQL Server 2008 (x86) Standard Edition SP1
Server 2
  • Windows Server 2003 (x86) Enterprise Edition
  • SQL Server 2000 Estandard Edition SP3
The MSDTC service was configured and started. There was no problem in executing the distributed transaction.
But IT team decided that the company needed new hardware and software(OS) for the servers.
So the scenario changed as follows:
Server 1
  • Windows Server 2008 R2 Enterprise Edition (x64)  SP1image

  • SQL Server 2008 Standard Edition (x64) SP2 10.0.4000image
Server 2
  • The same as before
I thought everything was good, but after testing the distributed transaction i got the error.
I spent several hours trying to find a solution by googling and posting in forums, but nothing seems to take me to the solution.
Looks like in Windows Server 2008, the MSDTC has been improved and you have to be careful with the configuration.
So the problem was that i could not start a distributed transaction from a x64 bit Windows 2008 OS to a x86 bit Windows 2003 OS.
After waiting for replays in posts and trying several combinations of configurations nothing worked.
But finally i found a solution, so here it is:
Network Connectivity
First of all:
  • Make sure both servers can ping each other by the machine name, cause MSDTC uses netBIOS to establish a connection.
  • Open the command line on both servers and run this command
    • On Server 1:   ping Server2
    • On Server 2: ping Server1
  • If you got response from both servers you are good, else you need to check your network dns.
  • Figure out if a firewall is active and blocking the MSDTC communication.
This was one of my problems cause Server 1 is in one lan and Server 2 in other and there was not mx records in the DNS for the servers but was easy to solve asking the network administrator to register the names in the DNS.
DTC Configuration
The second step is configure the MSDTC on both Servers and Here is how:
On Server 1 (Windows Server 2008)
1.- Open Start->Administrative Tools->Component Services.
image
2.- Expand the Component Services Node untill you find Local DTC right click and select properties option
image
3.- In the Local DTC properties clic in the security section and set the configuration as in the image
image
Two parts of this image are the most important in configuration of DTC in WS2008:
  • No Authentication Required.
  • Enable SNA LU 6.2 Transactions.
Note that every time you make a change in the DTC Security configuration, the DTC service is restarted automatically.
The final step is make shure the DTC service is started and configured to start when windows start.
1. Open Start->Administrative Tools->Services
image
2. Look for the Distributed Transactions Coordinator Service
image
Make sure the status is “Started” and the Startup Type is “Automatic”.

On Server 2 (Windows Server 2003)
To configure the DTC follow these steps
1. Open Start->Adminitrative Tools->Component Services
image
2.- In the Components Services window Expand Component Services->Computers->My Computer and right clic on this node and select properties option
image
3. In this new window clic on the MSDTC section and find the Security Configuration button
image
4. In the Security Configuration window apply the changes as in the next image and clic OK
image
And that is all the configuration of the DTC.
Now you need the verify that the service is started, i´m not going to detail this step as is the same in WS2008.
Just remember that all this configurations mus be done with an account that has the permissions to do it.
Linked Server Configuration on Server 1
Now the DTC is configured on both servers we must create and configure a linked server on Server 1 pointing Server 2.
Im not going to give much detail in how this is done, but here is a quick shot:
image
Here is my Linked Server in the SQL Server 2008 instance pointing to my SQL Server 2000 instance.
And remeber to apply this settings to the linked server:
Data Access: True
RPC: True
Enable Promotion of Distributed Transactions: True
You can do this when creating the linked server or when it already exists just right clic over it and select properties, then clic on the Server Options option in the left panel and change the options.



Testing Distributed Transactions
We’ve come so far so the final step is run the distributed transaction query of the beginning:
image
As you can see no error is raised.
If none of this stuff helps i recommend you to visit the next links for a deep troubleshooting of DTC:
Troubleshooting MSDTC Part 1
http://www.mssqltips.com/tip.asp?tip=2083
Troubleshooting MSDTC Part 2
http://www.mssqltips.com/tip.asp?tip=2113
MSDN posts
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/523116d4-4c8e-43a8-abf4-e705ffaa68c8
Thanks for reading and hope this is very informative and useful for you.

6 comentarios:

  1. This change removed the error that I came up with which was:"The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server " " was unable to begin a distributed transaction.". But Now I am getting a different error. The error is:

    "The local MS DTC detected that the MS DTC on SQLVM has the same unique identity as the local MS DTC. This means that the two MS DTC will not be able to communicate with each other. This problem typically occurs if one of the systems were cloned using unsupported cloning tools. MS DTC requires that the systems be cloned using supported cloning tools such as SYSPREP. Running 'msdtc -uninstall' and then 'msdtc -install' from the command prompt will fix the problem. Note: Running 'msdtc -uninstall' will result in the system losing all MS DTC configuration information."

    Per the log uninstalling and reintalling would reset the settings and if I do this I am going to get the first error back.

    Please suggest

    ResponderEliminar
  2. We also have the same problem.

    Our environment is a mix of SQL 2003 and 2008R2 and the problem occurs only when a 2008R2 server uses linked servers to write to the 20003 server. Selects work, updates don't. The only solution we found was to set the Transactions Manager Communication to "No Authentication Required". We are still trying to find a better solution.

    ResponderEliminar
  3. Hi dear
    its good, and worked well
    but when, i want to update records in server "B" give
    the The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server " " was unable to begin a distributed transaction. error again
    pleas help me.
    hamedpalik@gmail.com

    ResponderEliminar
    Respuestas
    1. Hi there. Can you describe the configuration you have?

      Eliminar
  4. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  5. This problem has haunted my system for a long long time,..
    I've done everything I thought relevant but the problem still exists,..

    I've googled a lot of solution, but so far this is the only solution that point out to check Window Firewall,.. which is the only thing I didn't check because all other connectivity between my server seems fine.

    It appears that when the admin installed window for the first time, they set firewall to on with block as default inbound rule. And somehow, someone set the rule for DTC to be disabled, which obviously will render DTC connection to default...

    Thanks a lot for pointing me to the right direction :D

    ResponderEliminar