by Mark Spenik and Orryn Sledge
SQL Server 6.5 includes a distributed transaction coordinator, the Microsoft Distributed Transaction Coordinator ( DTC). DTC provides an easy-to-use distributed transaction capability for the Windows NT and Windows 95 environments. The DTC uses OLE transaction objects to provide complete transaction management in a distributed environment. This technology allows applications to modify data in multiple SQL Server databases through a single transaction object. By using the distributed transactions through the MS DTC, you can guarantee that each data modification will complete in its entirety; in the event of an aborted transaction, the modified data will retain its original state (before the transaction was initiated).
NOTE: The DTC was introduced in SQL Server 6.5. Previous versions of SQL Server must be upgraded to version 6.5 to take advantage of the DTC service.
The MS DTC is integrated into the SQL Server Enterprise Manager. You can easily start, stop, trace, view statistics, and configure the MS DTC all from the Enterprise Manager. Before you look at the Microsoft DTC, you should understand the different components involved in distributed transactions. The following sections explain the key components involved in a distributed transaction and the role played by SQL Server and the Distributed Transaction Coordinator.
The Transaction Manager is responsible for the coordination and management of a transaction. The MS DTC serves as the Transaction Manager. The MS DTC creates transaction objects on behalf of the calling application. Resource Managers participating in the transaction enlist with the Transaction Manager. The Transaction Manager is then responsible for initiating and coordinating the two-phase commit protocol for the participants. The Transaction Manager is also responsible for keeping a log of transaction events. For the MS DTC, this log is the sequential file MSDTC.LOG. The log is used in case Transaction Manager should fail, so that the Transaction Manager can reconstruct the transaction by reading the log.
The Resource Manager is responsible for performing the request of the transaction. In the case of a SQL Server acting as a Resource Manager, this request could be an INSERT, UPDATE, or DELETE statement. Resource Managers are responsible for keeping enough information so that they can commit or roll back the transaction. Currently, the DTC supports only the SQL Server 6.5 Resource Manager.
The MS DTC uses a two-phase commit algorithm to guarantee that a distributed data modification will run in its entirety or that the modified data will return to its original state (the state it was in before the transaction was initiated). The two-phase commit algorithm is based on the following logic.
When a commit statement is issued, the Transaction Manager (in the case of SQL Server, the MS DTC) asks the resources involved in the transaction if they are ready to commit the transaction. This step is known as preparing to commit. If every resource is ready to commit the transaction, the Transaction Manager broadcasts a message to commit the transaction. Each resource sends back a message stating that the transaction is committed. If each resource successfully commits the transaction, the Transaction Manager marks the transaction as successfully committed. If a resource fails to commit a transaction, the Transaction Manager continues to hold the transaction in a pending state. This state must be resolved before the transaction is considered complete; otherwise the transaction is rolled back.
Refer to Figure 13.1 as you walk through a simple example of a distributed transaction. The transaction begins with the application, which issues a BEGIN DISTRIBUTED TRANSACTION command, causing the Transaction Manager (MS DTC) to create a transaction object for the transaction. When the application begins to perform a SQL statement as part of the transaction (for example, an INSERT statement), the Resource Manager (in this case, SQL Server) calls the Transaction Manager to enlist in the transaction. Keeping track of enlisted Transaction Managers is part of the responsibility of the Transaction Manager.
During the life of the transaction, the Transaction Manager (MS DTC) records in the MSDTC.LOG file events such as transaction starts, enlistments, and commits or aborts. By keeping the log file up to date, the Transaction Manager ensures that it can reconstruct a transaction in case the Transaction Manager should go down. When the application commits or aborts the transaction, the Transaction Manager begins the two-phase commit with all the enlisted Resource Managers. (The example in Figure 13.1 involves only a single computer with a single Transaction Manager and Resource Manager.)
Figure 13.1.
Walking through a distributed transaction process.
The real power behind the DTC is that you can use it in a distributed environment
for transactions that span multiple computers and Transaction Managers. In a distributed
environment, each system has a local Transaction Manager. The Transaction Manager
for each system works with the other Transaction Managers in the distributed environment
to manage transactions that span multiple systems. The Transaction Manager that initiates
a distributed transaction is referred to as the global commit coordinator
(or the root Transaction Manager). When a transaction crosses to other systems,
the Transaction Managers from each system establish relationships. The system making
the request is said to have an outgoing relationship with the Transaction
Managers on the other systems. Transaction Managers receiving the request establish
an incoming relationship with the root Transaction Manager. The relationships
between the different Transaction Managers are called a commit tree. The general
idea is that, when a distributed transaction is committed or aborted, the request
flows outward. Any Transaction Manager in the commit tree can abort the transaction
before it agrees to prepare to commit or abort the transaction.
By using Transact SQL, stored procedures, or C/C++ functions, you can develop an application that incorporates distributed transactions. The following sections explain each method.
By using SQL Server's stored procedures, you can implement distributed transaction logic. To implement distributed transaction logic, use the following syntax: begin distributed tran, commit tran, rollback tran. You can also use stored procedures to make remote procedure calls (RPC) to stored procedures located on servers. These RPC calls allow you to modify data located on another server.
You can also implement distributed transaction logic by using C or C++ functions with DB-Library or ODBC. If you use C or C++ functions, you can directly initiate a DTC transaction within an application through an OLE transaction-compliant Resource Manager.
Preparing to use the DTC service on the server where SQL Server resides is relatively straightforward. By default, the DTC service is automatically installed when you install SQL Server 6.5. When you install SQL Server, the MSDTC service is automatically added to the NT operating system.
When you use remote procedure calls (RPC) to modify data on another server, the remote server must be added to the list of available remote servers. Follow these steps to add a remote server:
Figure 13.2.
The Manage Remote Servers dialog box.
If you want a client to initiate a DTC transaction, the MS DTC client utility must be installed on a client machine.
NOTE: The DTC client utility is required only when a client initiates a DTC transaction. It is not required when a client calls a stored procedure in SQL Server and the stored procedure initiates the DTC transaction. Therefore, if you code the begin distributed tran, commit tran, and rollback tran logic using stored procedures, you do not have to install the DTC client utility. The DTC client must be installed on a 32-bit client. It does not work with Windows for Workgroups or Windows 3.1.
Before you can install the Microsoft DTC client component, you must first install the Microsoft Remote Registry Service. To determine whether the Microsoft Remote Registry Service is installed on your computer, double-click the Network icon in the control panel. If you do not see Microsoft Remote Registry listed as an installed component (see Figure 13.3), you must install it by performing the following steps:
Figure 13.3.
The Microsoft Remote Registry service.
Figure 13.4.
Enabling user-level access control.
After you successfully complete the installation of the Microsoft Remote Registry
Service, you must run the DTCCFG.CPL control panel extension to configure
the default commit coordinator. Follow these steps to configure the DTCCFG.CPL
control panel extension:
Figure 13.5.
Configuring the MS DTC client.
Follow these steps to start the DTC from the Enterprise Manager:
Figure 13.6.
Starting the Distributed Transaction Coordinator from the Enterprise Manager.
NOTE: You can also start the DTC from the Services icon in the control panel or by typing net start msdtc.
TIP: If you plan to use the DTC on a regular basis, you should configure it to start automatically whenever the NT Server starts. To automatically start the DTC service, double-click the Services icon in the Windows NT control panel. The Services dialog box opens. In the Services dialog box, double-click the MSDTC service. A dialog box containing information about the MSDTC service opens. From this dialog box, select Automatic as the Startup Type (see Figure 13.7). Click the OK button to save the changes.
Figure 13.7.
Configuring the DTC service to start automatically.
Follow these steps to verify that the DTC is properly configured:
Figure 13.8.
The MS DTC Statistics dialog box, showing one active transaction.
Although DTC sounds complex, Microsoft supplies several graphical tools built into the Enterprise Manager to simplify the administration of DTC. The following Enterprise Manager tools are used to manage DTC:
You can use the MS DTC Configuration dialog box to set advanced parameters. For example, you can set MS DTC display parameters or reset the MS DTC log and timers.
To view the MS DTC Configuration dialog box, perform the following steps:
Figure 13.9.
The Enterprise Manager, with the Distributed Transaction Coordinator selected.
Figure 13.10.
The Distributed Trans-action Coordinator shortcut menu.
Figure 13.11.
The MS DTC Configuration dialog box.
The following sections examine the MS DTC Configuration dialog box in more detail.
View Frame The View frame shown in Figure 13.11 is used to control how often the
MS DTC updates information. To adjust any values in the View frame, move the slider
bars.
The Display Refresh slider bar determines how often the statistical, transaction list, and trace information is sent to the various graphical interfaces. The more frequently the information is updated, the more accurate the information. However, frequent updating increases the administrative overhead required. The Display Refresh slider bar starts at Infrequently and goes to Frequently. Following are the display update intervals for each value:
The Transactions Shown slider bar determines how long a transaction must be active before it appears on the graphical interfaces. The Transactions Shown slider bar goes (from left to right) from Very Old to New + Old; the associated values are as follows:
The Trace slider bar controls the amount of trace information sent to the graphical interface. The Trace slider bar goes from Less (faster MS DTC) to More (slower MS DTC). The more trace statements you send back to the graphical interface, the slower MS DTC performs. The values for the Trace slider bar are as follows:
TIP: You can change the parameters in the View frame dynamically while the MS DTC is running.
Log Frame The Log frame allows you to adjust the size of the MS DTC log. The MS
DTC log file is called MSDTC.LOG.
CAUTION: Do not modify the size of the MS DTC log while MS DTC has unresolved transactions.
To modify the size of the MS DTC log, follow these steps:
The MS DTC Trace window allows you to view the trace messages issued by MS DTC. Use this information to track or debug problems or potential problems. The type of information displayed in the Trace window depends on the message trace level set with the MS DTC Configuration dialog box. You can view errors, warnings, or informational messages. To view the MS DTC Trace window, follow these steps:
Figure 13.12.
The MS DTC Trace window.
The MS DTC Transaction window allows you to quickly view current transactions and resolve the transaction manually if required. The following sections explain how to accomplish both these tasks. Viewing Current Transactions The MS DTC Transaction window allows you to quickly view current transactions that may require a DBA's intervention or attention. Because a single MS DTC can have many concurrent transactions, the MS DTC Transaction window displays only those transactions that have remained in the same state for an extended period of time or whose status is in doubt. You can configure the time interval for which transactions must remain in the same state before they appear in the MS DTC Transaction window by using the MS DTC Configuration dialog box discussed earlier in this chapter. To view the MS DTC Transaction window, follow these steps:
Figure 13.13.
The MS DTC Transaction window (Large Icons).
You can change the view of the Transaction window by right-clicking anywhere in the
MS DTC Transaction window and selecting View from the shortcut menu. Figure 13.13
shows the Large Icon view. You can also display transactions in Small Icon view,
List view, or Detail List view. The transactions displayed in the MS DTC window will
be in one of the following states:
Manually Resolving Transactions From time to time, you may be required to resolve a distributed transaction manually because of a break in the commit tree (for example, one caused by a break in the communications link). Transactions that remain in the in-doubt state can prove to be a problem because transactions that remain in the in-doubt state may cause the Resource Manager to hold all the locks on the various resources, making them unavailable to others.
To determine the proper action to take when manually resolving an in-doubt transaction, use the MS DTC Transaction window to locate the transaction's immediate parent. Examine the parent using the MS DTC Transaction window to determine the fate of the transaction. If the transaction does not appear in the MS DTC Transaction window, the transaction has been aborted. If the transaction shows up with the Only Failed To Notify state, the transaction has been committed and you can manually commit the transaction. If the status reads In-Doubt, you must look at the next parent node in the chain. Continue to search the nodes until you can determine whether the transaction has been aborted or committed. Once you know the status of the transaction, commit or abort the transaction on the child node and forget the transaction with the Failed To Notify status on the parent node.
To manually resolve a transaction, follow these steps:
Figure 13.14.
The MS DTC Transaction window shortcut menu.
Figure 13.15.
The MS DTC Transaction window Manual Transaction Resolution menu.
The MS DTC Statistics window provides cumulative and current information about the transaction in which a server has participated. The DBA can use this information to monitor the performance of MS DTC and make adjustments if required. If possible, you should leave the MS DTC up and running at all times to get the most out of the cumulative statistics. The cumulative statistics displayed in the MS DTC Statistics window are reset to zero when the MS DTC is stopped and restarted. To view the MS DTC Statistics window, follow these steps:
Figure 13.16.
The MS DTC Statistics window.
The MS DTC Statistics window has several frames, each of which is described following.
The Current frame displays the following information:
The Aggregate frame displays the following information:
The Response Time frame displays the average, minimum, and maximum response times in milliseconds. The response time is the time between when the transaction was started and when it was committed by the commit coordinator.
The MS DTC Started frame displays the date and time on which the current MS DTC was started.
Here is a list of the important points to review when using the Microsoft Distributed Transaction Coordinator:
With the Microsoft Distributed Transaction Coordinator, the process of distributed
transactions and two-phase commits has been greatly simplified. Read through this
chapter--as well as the Microsoft documentation--and experiment with setting up and
administering a Distributed Transaction Coordinator.
To order books from QUE, call us at 800-716-0044
or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster
Company.