SQL Server Distributed Database Problems and Solutions MSDTC Configuration

Microsoft Distributed Transaction Coordinator

I. symptom
If the client and server (maybe N) of the distributed transaction are not on the same server, for example, the application server and the database server are respectively, the following error occurs frequently:

① An error occurred while establishing a connection with the server. When you connect to SQL Server 2005, the default setting does not allow remote connection to SQL Server may cause this failure. (Provider: named pipeline provider, error: 40-unable to open the connection to SQL Server ).

② When SQL is used to process data: the transaction has been implicitly or explicitly committed or terminated. (My problem today !)

③ The partner Transaction Manager has disabled its support for remote/network transactions. (The exception is from hresult: 0x8004d025 ). (Transactionscope exception)

④ [Comexception (0x8004d00e): the transaction has been explicitly or implicitly confirmed or terminated (the exception comes from hresult: 0x8004d00e)]. (MSDTC Distributed Transaction error)

⑤ Import of MSDTC Transaction Failed: result code = 0x8004d023. (MSDTC security configuration problem)

Ii. Solution
If you encounter the above problems or the SQL Server Distributed problem, follow these steps to solve the problem. Some steps may be unnecessary for you, but they are not missing.

1. Start the MSDTC Service.
MSDTC Description: MSDTC is short for Microsoft Distributed Transaction Coordinator, that is, Microsoft Distributed Transaction Coordinator. Description: Coordinates transactions across multiple databases, message queues, file systems, and other resource managers. If the service is stopped, these transactions will not occur. If this service is disabled, other services explicitly dependent on this service cannot be started.

MSDTC start method:

① "START" | "run", and enter "services. MSC, or control panel | Administrative Tools | service, open the service window, find Distributed Transaction Coordinator in the name, and start it.

② "START" | "All Programs" | "Microsoft SQL Server" | "Service Manager", Open the "SQL Server Service Manager" window, and select the "Distributed Transaction Coordinator" service, start it.

 

2. Set the MSDTC component.
① Check whether the DTC component is installed in the operating system. XP is installed by default. win2003 is not installed by default. The installation procedure is as follows:

A. "Start" | "Control Panel" | "Add/delete programs" | "Add/delete Windows components", select "Application Server", and click "details ", select enable Network DTC Access and click OK | next | finish ".

B. Stop and restart the MSDTC Service (command: net stop MSDTC and Net start MSDTC ).

C. Stop any resource manager services (such as Microsoft SQL Server or Microsoft Message Queue Server) involved in distributed transactions and restart them.

 

② "START" | "run", enter "dcomcnfg", or "Control Panel" | "Management Tools" | "component service" to open the "component service" window, "Component Service" | "computer" | "my computer", right-click "properties" | "MSDTC", select "use local coordinator", and click "Security Configuration ", the "Security Configuration" window is displayed, select "Network DTC Access", "allow remote client", "Allow Remote Management", "Allow inbound", "Allow outbound", "do not require verification", and "enable "transaction Inernet protocol (TIP) transaction and XA transaction enabled (Allow inbound and allow outbound are set according to specific conditions, the client machine must "Allow outbound ", the server-side machine must be "Allow inbound"), and the DTC login account must be "NT
Authority \ NetworkService ". For Vista, you only need to check "use local coordinator.

 

③ Configure a firewall to allow network communication with the msdtc.exe service. [All firewalls and anti-virus software can be removed during testing]

 

3. Check whether two machines can ping each other through hostname.
Note that the machine name is not the IP address. If not, perform the following settings:

① Write the corresponding IP address and Hostname Information in the hosts file under "% WINDIR % \ system32 \ drivers \ etc", for example, "10.25.11.185 gsmajk ". In addition, you can also use the dtcping tool to test whether MSDTC can be used between two machines, and help you analyze the cause, you can go to the Microsoft Site.

 

4. Start the distributed remote connection of SQL Server.
(1) SQL Server 2000:

① "START" | "All Programs" | "Microsoft SQL Server" | "Enterprise Manager", right-click the SQL server instance, for example, gsmajk (Windows NT), and right-click "properties ", the "SQL Server properties (configuration)" window is displayed, "connection" | "remote server connection ", select "allow other SQL servers to remotely connect to this SQL Server using RPC" and "Force Distributed Transaction Processing (MTS)". SQL Server 2000 is selected by default.

② Configure the firewall to allow network communication related to the SQL Server service. [All firewalls and anti-virus software can be removed during testing]

(2) SQL Server 2005: see how to configure SQL Server 2005 to allow remote connection

① Enable remote connection on the SQL server instance that you want to connect to from a remote computer.

"Start" | "All Programs" | "Microsoft SQL Server 2005" | "configuration tool" | "SQL Server peripheral application configurator ", click "service and connected peripheral application configurator" | "Database Engine", and click "remote connection ", select "local connection and remote connection" and "colleagues use TCP/IP and named pipes", and then click "application ". After receiving the message "the changes made to the connection settings will take effect only after the Database Engine service is restarted", click "OK ". Click service to check whether the MSSQLServer service is enabled.

② Open the SQL Server Browser Service.

"Start" | "All Programs" | "Microsoft SQL Server 2005" | "configuration tool" | "SQL Server peripheral application configurator ", click "service and connected peripheral application configurator" | "SQL Server Browser", click "automatic" in "Startup Type", and then click "application ". Click Start, and then click OK ".

③ Configure the firewall to allow network communication related to the SQL Server and SQL Server Browser services. [All firewalls and anti-virus software can be removed during testing]

 

5. Check whether necessary patches are installed on SQL Server.
For example, SQL Server 2000 SP4.

 

Once. in the development of SQL Server 2000 SP3, there were all issues encountered during issue ① ②, which had not been solved for several days. Later, check the database version, one is SQL Server 2000 RTM. I installed SQL Server 2000 SP4 on the basis of the latter. The problem is solved.