Managing Your SQL Servers

Starting, Stopping, and Configuring MSDTC

Microsoft Distributed Transaction Coordinator (MSDTC) is a transaction manager that makes it possible for client applications to work with multiple sources of data in one transaction.

When a distributed transaction spans two or more servers, the servers coordinate the management of the transaction by using MSDTC. When a distributed transaction spans multiple databases on a single server, SQL Server manages the transaction internally.

SQL Server applications can call MSDTC directly to start an explicit distributed transaction. Distributed transactions can also be started implicitly by using one of the following methods:

• Calling stored procedures on remote servers running SQL Server

• Updating data on multiple OLE DB data sources

• Enlisting remote servers in a transaction

If you work with transactions under any of these scenarios, you should have MSDTC running on the server, and you should set MSDTC to start automatically when the server starts. As with SQL Server itself, MSDTC runs as a service. Unlike the SQL Server service, only one instance of the MSDTC service runs on a computer, regardless of how many database server instances are available. This means that all instances of SQL Server running on a computer use the same transaction coordinator.

You can view the current state of MSDTC in SQL Server Management Studio by connecting to the server’s Database Engine. In Object Explorer, expand the server and Management nodes. If the service is running, you see a green circle with a right-facing triangle in it (similar to a Play button). If the service is stopped, you see a red circle with a square in it (similar to a Stop button). You can control the MSDTC service with Computer Management. Follow these steps:

1. Start Computer Management by clicking Start, pointing to All Programs, Administrative Tools, and then selecting Computer Management.

2. By default, you are connected to the local computer. To connect to a remote computer, right-click the Computer Management node, and then select Connect To Another Computer. In the Select Computer dialog box, choose Another Computer, and then type the name of the computer. The name can be specified as a host name, such as EngDBSrv12, or as an FQDN, such as EngDBSrv12.cpandl.com.

3. Expand Services And Applications, and then select Services. Right-click Distributed Transaction Coordinator, and then choose Properties. You can now manage MSDTC.