Microsoft SQL Server DBA Survival Guide --Chapter 13 Chapter 13 Distributed Transaction Coordinator Transaction Manager Resource Manager Two-Phase Commit Walking through a Distributed Transaction Process Developing an Application with Distributed Transactions Using Stored Procedures Using C/C++ Functions Installing the DTC Server Component Installing the DTC Client Component Starting the DTC Testing the DTC Administering the DTC MS DTC Configuration Dialog Box MS DTC Trace Window MS DTC Transaction Window MS DTC Statistics Window Between the Lines Summary 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. Transaction Manager 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. Resource Manager 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. Two-Phase Commit 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. Walking through a Distributed Transaction Process 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. Developing an Application with Distributed Transactions 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. Using Stored Procedures 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. Using C/C++ Functions 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. Installing the DTC Server Component 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: From the Enterprise Manager, select the server that will act as the DTC coordinator. Select the Remote Server option from the Server menu. The Manage Remote Servers dialog box opens. In the Manage Remote Servers dialog box, enter the remote server's name. Select the RPC option and enter Remote Login information (see Figure 13.2). Click the Add button to save the information. Figure 13.2. The Manage Remote Servers dialog box. Installing the DTC Client Component 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: Double-click the Network icon in the control panel. The Network dialog box appears. Click the Add button in the Network dialog box. The Select Network Component Type dialog box opens. Select Service and click the Add button. The Select Network Service dialog box opens. Click the Have Disk button. The Install From Disk dialog box opens. Enter the location of the REGSRV.INF file. This file can be found in the \i386\remotereg directory on the SQL Server v6.5 CD-ROM. Click the OK button to install the service. You return to the Select Network Service dialog box. At this point, you are ready to install the Microsoft Remote Registry Service. Click the OK button to continue the installation. The Microsoft Remote Registry Service begins to install. Depending on your Windows 95 configuration, you may be prompted for additional files that reside on the Windows 95 installation CD or disks. When the installation is complete, you will see Microsoft Remote Registry listed as an installed component (see Figure 13.3). Figure 13.3. The Microsoft Remote Registry service. In addition to installing the Microsoft Remote Registry service, you must also enable user-level access control. To enable user-level access control, select the Access Control tab in the Network dialog box. On this page, click the User-Level Access Control option and enter the source for user information (see Figure 13.4). 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: Find the installed location of DTCCFG.CPL and double-click the file. The MS DTC Client Configuration dialog box opens (see Figure 13.5). From the MS DTC Client Configuration dialog box, enter a default MS DTC server and a network protocol. Click the OK button to save the information. Figure 13.5. Configuring the MS DTC client. Starting the DTC Follow these steps to start the DTC from the Enterprise Manager: To manually start the DTC from the Enterprise Manager, select the server that will be running the DTC service and right-click the Distributed Transaction Coordinator icon. From the shortcut menu that appears, select the Start option (see Figure 13.6). Figure 13.6. Starting the Distributed Transaction Coordinator from the Enterprise Manager. Once the Distributed Transaction Coordinator has started, the DTC icon appears with a green dot. This indicates that the DTC is currently running. 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. Testing the DTC Follow these steps to verify that the DTC is properly configured: From the Enterprise Manager, right-click the Distributed Transaction Coordinator icon. From the shortcut menu that appears, choose the Statistics option. The MS DTC Statistics dialog box opens. Keep this dialog box open for the remainder of the test. From the Enterprise Manager, open a Query dialog box by clicking the SQL Query Tool button. In the Query dialog box, type begin distributed tran and click the Execute Query button to execute the query. Keep this dialog box open for the remainder of the test. Return to the MS DTC Statistics dialog box. If the DTC is properly configured, you should see one active transaction in the Current/Active counter section of the dialog box (see Figure 13.8). Figure 13.8. The MS DTC Statistics dialog box, showing one active transaction. Return to the Query dialog box. Type rollback tran and click the Execute Query button to execute the query. Return to the MS DTC Statistics dialog box. If the DTC is properly configured, you should see no active transactions in the Current/Active counter section of the dialog box. Administering the DTC 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: MS DTC Configuration dialog box. Allows you to set advanced MS DTC options. MS DTC Trace window. Allows you to monitor trace messages issued by the MS DTC. MS DTC Transaction window. Provides a graphical view of transaction states and enables you to manually resolve a transaction. MS DTC Statistics window. Provides a graphical view of transaction statistical information. MS DTC Configuration Dialog Box 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: From the Enterprise Manager, select the server you want to administer. Select the Distributed Transaction Coordinator icon (see Figure 13.9). Figure 13.9. The Enterprise Manager, with the Distributed Transaction Coordinator selected. Right-click the Distributed Transaction Coordinator to display the Distributed Transaction Manager shortcut menu (see Figure 13.10). Figure 13.10. The Distributed Trans-action Coordinator shortcut menu. Select the Configure option. The MS DTC Configuration dialog box appears (see Figure 13.11). Using the MS DTC Configuration dialog box, you can set various MS DTC options. Once you set the necessary options, click the Close button to save the changes and return to the Enterprise Manager. You can also stop or start the MS DTC by clicking the Start or Stop button. 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: Every 20 seconds (Infrequently) Every 10 seconds Every 5 seconds (Default value) Every 3 seconds Every 1 second (Frequently) 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: Transaction 5 minutes old (Very Old) Transactions 1 minute old Transactions 30 seconds old Transactions 10 seconds old Transactions 1 second old (New + Old) 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: Send no traces (Less; faster MS DTC) Send only error traces Send error and warning traces Send error, warning, and informational traces (default) Send all traces (More; slower MS DTC) 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: Make sure that the MS DTC has no unresolved transactions by viewing the MS DTC Transaction window (discussed later in this chapter). If no unresolved transactions exist, click the Stop button to stop the MS DTC. Once the MS DTC has stopped (that is, when the Status indicator at the bottom of the MS DTC Configuration dialog box shown in Figure 13.11 reads Stopped), you can change the size of the log by using the slider bar. You can also change the location of the file by using the Drive and Directory boxes. Once you have modified the MS DTC log (either the size or the location or both), click the Reset Log button. Click the Start button to restart the MS DTC. MS DTC Trace Window 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: From the Enterprise Manager, select the server you want to trace. Select the Distributed Transaction Coordinator icon. Right-click the Distributed Transaction Coordinator icon to display the shortcut menu. Select the Trace option. The MS DTC Trace window shown in Figure 13.12 appears. Figure 13.12. The MS DTC Trace window. MS DTC Transaction 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: From the Enterprise Manager, select the server for which you want to view current transactions. Select the Distributed Transaction Coordinator icon. Right-click the Distributed Transaction Coordinator icon to display the shortcut menu. Select the Transactions menu option. The MS DTC Transaction window appears (see Figure 13.13). 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: Active. The transaction has been started and work has been done on it by the Resource Manager. Aborting. The transaction is aborting. Aborted. Subordinate Transaction Coordinators and Resource Managers have been notified or are currently unavailable. Preparing. The application has issued a commit request. Prepared. All enlisted Resource Managers and MS DTC have agreed to prepare. In-doubt. The transaction is prepared and initiated by a different server; the MS DTC coordinating the transaction is unavailable. Forced Commit. An in-doubt transaction has been manually committed. Forced Abort. An in-doubt transaction has been manually aborted. Notifying Committed. The transaction has prepared and MS DTC is notifying all enlisted Resource Managers that the transaction has com-mitted. Only Fail Remain to Notify. All connected Resource Managers and subordinates have been notified of the transaction commit; the only ones left to notify are not connected. Committed. The transaction has been successfully committed. 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: From the MS DTC Transaction window, select the transaction you have to resolve. Right-click the transaction; the shortcut menu shown in Figure 13.14 opens. Figure 13.14. The MS DTC Transaction window shortcut menu. Select the Resolve option. The menu shown in Figure 13.15 appears. Figure 13.15. The MS DTC Transaction window Manual Transaction Resolution menu. Select the proper command from the menu. MS DTC Statistics Window 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: From the Enterprise Manager, select the server for which you want to view statistics. Select the Distributed Transaction Coordinator icon. Right-click the Distributed Transaction Coordinator icon to display the shortcut menu. Select the Statistics option. The MS DTC Statistics window appears (see Figure 13.16). 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: Active. The current number of transactions yet to complete in the two-phase commit protocol. Max Active. The peak number of transactions reached at any time while the MS DTC is running. In-Doubt. The current number of transactions that are unable to commit because of communication problems between the commit coordinator and the local SQL Server. The Aggregate frame displays the following information: Committed. The cumulative total of committed transactions (excluding those committed manually). Aborted. The cumulative total of aborted transactions (excluding those aborted manually). Forced Commit. The cumulative total of manually committed trans-actions. Forced Abort. The cumulative total of manually aborted transactions. Total. The cumulative total of all transactions. 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. Between the Lines Here is a list of the important points to review when using the Microsoft Distributed Transaction Coordinator: The Transaction Manager is responsible for the coordination and management of a transaction. The Resource Manager is responsible for performing the request of the transaction. As this book went to press, only SQL Server 6.5 can serve as a Resource Manager. The 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 (that is, the state it was in before the transaction was initiated). The Enterprise Manager provides several tools you can use to monitor and tune the MS DTC. Summary 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. DISCLAIMER 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.