SQL Server Distributed Database Problems and Solutions 1

Unable to start Distributed Transaction

I. a. Do not use transactions. Use select statements. Can I perform distributed queries?

B. linkserver cannot operate on the local machine when performing distributed update transactions (that is, it cannot return distributed transactions)

C. DBCC traceon (3604,730 0) -- use tracking to view more detailed error information.

D.download the dtcping.exe package of msbench on two machines and run it according to readme instructions. See error information.
Http://download.microsoft.com/download/complus/msdtc/1.7/nt45/en-us/DTCPing.exe

2. Whether MSDTC is enabled for both machines.
1. Click Start, point to control panel, and click Add/delete programs ".
2. Click "Add/delete Windows Components ".
3. Select "Application Server" and click "details ".
4. Select enable Network DTC Access and click OK ".
5. Click "Next ".
6. Click Finish ".
7. Stop the Distributed Transaction Coordinator service and restart it.
8. Stop any resource manager services (such as Microsoft SQL Server or Microsoft Message Queue Server) involved in distributed transactions and restart them.
 

Iii. Whether MSDTC is set correctly.
1. Open the command prompt, Run "net stop MSDTC", and then run "Net start MSDTC ".
2. Go to "Component Service Management Tools ".
3. Browse to "start management tool ".
4. Select "component service ".
A. Expand the "component service" tree and expand the computer.
B. Right-click "my computer" and select "properties ".
C. On the MSDTC tab, make sure the following options are selected: Network DTC Access
Network Management
Network transactions
XA transaction
D. In addition, "DTC Logon account" must be set to "nt authority/NetworkService ".
5. Click OK ". This will prompt you that "Ms DTC will stop and restart.
All dependent services will be stopped. Press "yes" to continue ". Click "yes" to continue.
6. Click "OK" to close the "my computer" Properties window.
4. MSDTC depends on rpc. RPC uses port 135 to test whether port 135 is enabled. Is there a firewall? If the firewall is disabled first.
Telnet IP 135
If it is disabled, open it.

5. due to various reasons, sqloledb cannot use distributed transactions and is changed to "msdasql" ODBC connection.
Use RRAS instead of RAS. (control panel -- Management Tools -- remote service manager)
Check whether you are using remote access server (RAS) to access remote servers. if so, make sure that you have implemented routing RAS (RRAS ). linked server does not work on Ras because Ras allows only one way communication.


6. Check whether your two servers are in the same domain.
Whether to establish a trusted connection if it is not in the same domain.

7. Upgrade to SP4.

8. Upgrade MDAC to 2.6 or above, preferably 2.8.

 

 

Other materials:

The configuration of distributed transactions in Windows 2003 is quite troublesome. I have encountered a situation where the configuration cannot be started. Recently, I have finished it under the guidance of others. By the way, I will write a configuration step, hope to be useful to everyone
1. Add/delete programs, select Add Windows Components, select Application Server, and enable Distributed Transaction Network Access
2. Enable NetBIOS on all servers involved in DTC to ensure that all servers can ping each other. Specifically, modify the C: /Windows/system32/Drivers/etc/hosts file (For details, refer to the internal help of the hosts file ). [Note] You must modify the hosts files on all servers.
3. Disable SQL Server's DTC RPC security. Specifically, create a DWORD key under HKEY_LOCAL_MACHINE/software/Microsoft/MSDTC named turnoffrpcsecurity and set the value to 1.
4. Restart MSDTC.
5. Disable the network firewall (or open the corresponding port)
6. Reference:
Http://www.support.microsoft.com /? Kbid = 555017 & SD = tech
Http://support.microsoft.com/default.aspx? SCID = KB; ZH-tw; 306843
Http://support.microsoft.com/kb/250367/

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/lanman/archive/2009/07/03/4319288.aspx