SQL Server Distributed Database Problems and Solutions

I. symptom

Assume that the client and server (maybe N) of the distributed transaction are not on the same server, for example, the applicationProgramThe following errors often occur on servers and database servers:

①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 ).

②The transaction has been implicitly or explicitly committed or terminated.

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

④[Comexception (0x8004d00e ):This transaction has been explicitly or implicitly confirmed or terminated (exception 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.StartMSDTCService.

 

MSDTC Introduction: MSDTC isMicrosoftDistributed Transaction Coordinator is short for MicrosoftDistributed 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.SetMSDTCComponent.

① 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 MSDTCAnd Net start MSDTC).

C.Stop any resource manager services (such as Microsoft SQL Server or Microsoft Message Queue Server) that participate 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 ", in the "Security Configuration" window, select "Network DTC Access", "allow remote clients ","Allow Remote Management ","Allow inbound", "Allow outbound", "do not require verification ","Enable transaction Inernet protocol (TIP) Transactions"And "enable XA transaction" ("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 the 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 interact with each otherPingConnectHostname.

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 ". You can also useDtcping 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.StartSQL ServerDistributed Remote connection.

(1) SQL Server 2000:

① "START" | "All Programs" | "Microsoft SQL Server" | "Enterprise Manager", with the mouse selectedSQL ServerInstanceFor example, gsmajk (Windows NT), right-click "properties", and the "SQL Server properties (configuration)" window pops up. "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. SQL ServerInstall required patches.

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.