With SQL Server 6.5, Microsoft began supporting distributed transactions, which are transactions that span more than one server. There are many times when you need to update more than one source of information and you need to apply transaction technologies to the update to ensure that all or none of the changes are made against the database tables. The DTC is the tool that will provide this functionality, as well as utility functions that help you manage the process.
As with standard transactions, distributed transactions must follow the ACID rules. For more information on this, be sure to read Chapter 12, "Understanding Transactions and Locking." In short, the transaction must be self-contained and must be autonomous, or able to be reversed without affecting other processes. As you might imagine, distributed transactions add a whole new layer of variables to the development and deployment process.
With a typical system, you don't have to work with server-to-server issues, network problems, or other issues that can become a part of the transaction process. You may remember that transactions must be completely self-contained and able to be completed without intervention. With a distributed transaction, you may face issues with hardware failures, server availability, or different loading and performance patterns between the participating servers. As you configure DTC, you'll need to keep these in mind and make sure you set appropriate time-out and latency options.
You can think of the DTC as an extension of standard transactions. The DTC simply allows you to build transactions across servers, rather than contain them within a given server.
DTC is broken up into two different components. The first, the Transaction Manager, is responsible for overall coordination of the transactions. The Transaction Manager is responsible for enforcing the ACID rules and for making sure the transaction objects are complete and that they are addressed as needed to complete the transaction.
The other component, the Resource Manager, has the role of setting up the transaction and making it happen. The Resource Manager's role is to carry out the statements that make up the transaction and make sure the updates happen as requested. More on these components is covered in the next two sections.
The Resource Manager has the task of carrying out the distributed transaction's mission. This means that the commands requested are performed by the Resource Manager against the different tables as needed.
The application doesn't work directly with the Resource Manager, but the transaction objects are submitted to the Transaction Manager. The Transaction Manager sets up the transaction and works with the Resource Manager to make it happen. All of this is transparent to the application, which must only issue the appropriate begin and end transaction statements to ensure a complete distributed transaction.
The Resource Manager is also responsible for making sure that the transaction can be recovered, even in cases of power failure during the resolution of the transaction.
The Transaction Manager responds when a new distributed transaction is begun. With the transaction begin statement, the Transaction Manager sets up the transaction object and works through two distinct phases of the transaction. These are the PREPARE phase, and the COMMIT phase. This commit is referred to as a two-phase commit, which is something SQL Server could not do before the implementation of the Transaction Manager and the DTC overall.
In the PREPARE phase, the Transaction Manager works with the Resource Manager to complete the operations, but not commit them to the databases involved. You'll recall that with a standard transaction, you open a transaction, perform the actions you need, then commit the transaction, saving the changes to the tables. With distributed transactions, you do basically the same thing. First, you begin the distributed transaction, perform the actions you need to perform, and then commit the changes to the respective database tables.
The Transaction Manager is responsible for making sure that, each step along the way, the proper logging is done, and that the Resource Manager is able to obtain the information and responses it needs to be able to ultimately fulfill the request. The Transaction Manager logs all activity to the msdtc.log file, which is used to roll back transactions if needed.
The Transaction Manager steps in when a BEGIN DISTRIBUTED TRANSACTION request is issued. This statement tells the Transaction Manager to set up a new transaction object and get ready to work through the distributed transaction as needed. The Transaction Manager enlists the proper Resource Managers on the different servers that will be needed to complete the transaction.
When completed, the application issues a COMMIT TRANSACTION or a ROLLBACK TRANSACTION, and the Transaction Manager works with the Resource Manager to complete or abort the task.
As with a typical transaction, you bracket distributed transactions between BEGIN and COMMIT or ROLLBACK transaction statements. The SQL statements between the two are carried out, then committed or rolled back as a whole depending on the completion of the series of statements.
When you start a distributed transaction, you indicate the name of the remote server in the transaction's execute statements where you call the stored procedures on the remote system. You need to define those server names before you can reference them in the execute statements. If you don't, SQL Server won't be able to resolve where to look for the stored procedure you're executing.
You set up the server in the Manage Remote Servers dialog box. You access this dialog from the Server menu by selecting the Remote Server option. See Figure 18.1 for an example.
FIG. 18.1
You must define the servers that will be accessed by the DTC by providing the server
name and selecting the RPC check box.
Provide the name of the server and be sure to select the RPC check box. Remote Procedure Calls, RPC, is the method that is used by the DTC to communicate with the remote servers. If you don't check this option, the DTC will not be able to work with the other servers.
You'll also need to provide the login information, though the easiest and most direct route is to select the Translate All Remote Logins Names To check box and leave <Same Name> selected. This will use the current user's name as the login on the remote system. Keep in mind that the user must be a valid user on the remote system as well as the local system.
Once these are defined, select the Add button to save the update. Be sure to complete this information for each server that will be working with DTC.
NOTE: If you have any trouble connecting over RPCs, check the network protocols on the server and make sure the RPC service is loaded and available. If it's not, you'll need to add it to the server and will likely have to reboot your system to have the changes take effect.
There are two pieces to setting up the DTC component of SQL Server. First, you need to set up the server-side components. Those components make the DTC available to your applications.
The second piece of software you need to set up is the client interface to the server, allowing you to work with the server-based DTC and specifying the protocol that is used to work with the server.
In the next sections, you'll see how to set up these components on your network.
When you install SQL Server, it automatically installs the DTC. It's not an automatically started option, though, so you won't see it in the Enterprise Manager until you start it. To start the service, from the Control Panel, select Services and then find the MSDTC service. (See Figure 18.2).
FIG. 18.2
By default, the service does not start auto-matically when the server is started.
If you'll be using the service regularly, you'll want to set it up to start automatically when your server is started. To set this up, click the Startup button. The service dialog box, shown in Figure 18.3, lets you provide information about several of the options of the DTC server component startup. The two key items are as follows:
FIG. 18.3
You should select automatic start and log on as a system account for the most trouble-free
installation.
Once you've set up the service, be sure you start it manually the first time. If you do not, the DTC won't start until the next time you reboot your server.
From this point on, the DTC will show up in the Enterprise Manager as a new component for the server on which it's running. The link in the tree-view list lets you set additional options for the component, including trace options, review statistical summaries on usage, and so on.
NOTE: If you're using the Enterprise Manager from Windows 95, you won't be able to administer the DTC, and you won't see the option listed under the server. You must be using Windows NT Workstation or Windows NT Server in order to remotely administer the DTC components using Enterprise Manager.
To modify these options for the DTC, right-click, the component and then select the option you need from the menu. In addition, you have the option of manually starting or stopping the service. See Figure 18.4 for an example of the functions you have available for the DTC.
FIG. 18.4
You can work with many of the options associated with the DTC from within Enterprise
Manager.
These options are covered in the next five sections.
Start/Stop the Service As the name indicates, this option allows you to start and stop the service from the Enterprise Manager. You cannot, however, set up the service for automatic startup when NT Server starts. To set this up, you'll need to see the earlier section "Setting Up the Server Components of the DTC."
Transactions The Transactions option brings up a dialog box that shows any open trans-actions at the time you request the option. Each transaction is listed in a table, and the DTC shows trace information for each transaction so you can track it down should the need arise.
Trace As with SQL Trace, you can set up filters on DTC transactions. These traces can help you determine where a transaction has gone wrong and how to track it down to correct the situation. The Trace dialog box shows a table of the transactions that you're monitoring and provides the information you'll need to work with them.
TIP: When you're running the DTC, it logs its messages to the Windows NT Server's event logs, as well as any trace facilities you may have implemented. You should always review the event logs as well as the trace facilities if you're experiencing trouble with a distributed transaction.
Tracing produces messages with four different severity levels:
In addition, the Trace display shows one of several different status states for each transaction:
Three different subsystems generate messages to the logs and the trace facility. These are CM for the Connection Manager and its related activities, LOG for the logging mechanisms, and SVC for the overall DTC service.
Statistics The Statistics dialog box will show you the volume and types of transactions you're processing through the DTC. The dialog box shows the current and overall total, or aggregate, values for your distributed transactions. See Figure 18.5 for an example.
FIG. 18.5
If you think distributed transaction performance may not be up to par, be sure to
review the statistics on each of the servers involved in the distributed trans-actions
on your system.
A key value to watch is the Response Times frame. These values represent a good view on how your DTC is doing in working through transactions. If you start to see these values jump, you'll need to review activity on the servers involved in the transactions, and you'll want to make sure the transactions aren't reliant on some factor that creates a bottleneck for the transactions to complete.
Remember, the response times listed are overall response times. To find out about specific transactions, you'll need to use the trace facility.
Configure There are several different options you can set up for the DTC on the server to help in the monitoring of the server. If you are tracing transactions, though, and you find that performance has been significantly affected, be sure you've set the Trace settings to the Faster MS DTC side of the equation. You set this value by selecting the Configure option from the menu. See Figure 18.6 for an example of the Configuration dialog box.
FIG. 18.6
Be careful not to make the trace frequency too small, as it can affect overall
DTC performance on the server.
The display refresh values range from one to 20 seconds. This is another item that can affect the server performance, but it's likely you'll want to set this rather low to see transactions more readily if you're debugging a known issue.
Transaction aging runs from one second to five minutes. This controls the age of transactions that are shown. Transactions falling outside this range are still processed, just not displayed. In the normal course of business, it's unlikely you'll need to see transactions that are only a few seconds old. On the other hand, transactions that approach even one minute are surely suspect and need to be reviewed and modified if possible. Remember that the user of your application won't want to sit and wait for a transaction to commit, regardless of the technology happening behind the scenes.
The Log options let you dictate how large the log file will be allowed to get and where it should be located. You can select the directory, drive, and size for the file. To make changes to the log file settings, you'll need to first stop the DTC services. After you've completed your changes, you can restart the services, and the new settings will be in effect.
The Reset Log option will clear the log file and let you start anew. As with the other log file options, you'll need to have the DTC stopped prior to using this option.
As mentioned in the opening section of this chapter, distributed transactions are extensions to the standard transactions that you found out about in Chapter 12, "Understanding Trans-actions and Locking." Distributed transactions add the DISTRIBUTED keyword to the BEGIN TRANSACTION statement and allow you to indicate different server names on the execute instructions, but otherwise are identical to more typical transactions.
For example, if you have two servers, SQL1 and SQL2, you can create a distributed transaction with the following code snippet:
BEGIN DISTRIBUTED TRANSACTION insert into localtable values ("These", "are", "inserted", "locally") exec sql2.testdb.dbo.sp_addvalues "these", "are", "inserted", "remotely" COMMIT TRANSACTION
The two SQL statements doing the value insertions are nothing that you can't accomplish without the use of the DTC. The catch, of course, is what context they are executed within and the amount of control you have should a problem occur.
Without the DTC, these statements would be executed as two separate and distinct requests. If one failed, it would not necessarily affect the second. This would even be true if you had enclosed the two statements in the standard BEGIN TRANSACTION and COMMIT statements. Because they are running on different servers, the only thing that would be seen by the transaction and considered a part of it is the actual execution of the INSERT statement and the execution, but not successful completion, of the stored procedure call. Regardless of success or failure programmatically of the stored procedure, the transaction would succeed.
With the DTC, the problem of the remote operation is handled, and you'll know for sure the operation succeeded. The DTC effectively extends the reach of the transaction to the second system. With the DTC in effect by using the DISTRIBUTED keyword when you declare the transaction, you'll be able to ensure that all aspects of the transaction succeed, not just the local portions of it.
If you find that you have transactions remaining on your SQL Server in a pending state, you have a few options available to you that will help to track down the problem area. In general, you can review the statistics that are gathered by the DTC and see how long transactions are taking overall, and you can start to see trends between your servers.
If transactions are slow to complete one of the first areas to look at is the connectivity between your local server and the remote system. If the connection speed is slow, or if either of the servers is being heavily used, it will help explain the execution time of the transactions.
You can also force a transaction to commit by pulling it up in the trace facility, right-clicking the transaction, and selecting the Resolve option. You'll be able to Commit, Abort, or Forget the transaction. See Figure 18.7 for an example.
FIG. 18.7
Once you manually force an outcome for a given transaction, be sure to track down
what happened and what caused the transaction to fail to complete on its own.
If you have a transaction that is runaway, don't forget that you can run the different components of the logic through ISQL and watch a little more carefully, considering each value and logic step along the way. In the worst-case scenario, consider adding logic to your stored procedures that call the DTC and have them drop values for your variables into a working table along the way, along with checkpoint notations so you will know where in your code you are when the value is written. In psuedo-code:
Start procedure Write initial value to work table, along with "start of routine" Statement 1 Write state values to work table, along with "before DTC call" ...call the DTC participants and start your transaction
This admittedly goes back to the debugging days of using msgbox's to show information to the developer during the execution of code. The difference, of course, is that you don't have the luxury of the msgbox when working in this environment.
TIP: If you use this approach, be sure you don't put your debugging code inside the transaction. If you do, when the transaction rolls back, you'll also lose your debugging information in the work table as it is reversed out of the system.
In the systems that have been completed by using the DTC, the biggest stumbling block is the lack of ability to really see what's going on, and how to determine what you need to do to fix any problems that arise. This is where the debugging "droppings" mentioned in the last section come into play and can save you many hours of test time. Keep in mind that you can have each of your participating servers log information to work tables, providing you with an overall picture of what's happening on your system.
The other thing that you'll want to keep in mind with DTC-based applications is that you'll need to be sure you test what happens in your application if a distributed transaction either takes too long to complete, or is rolled back for whatever reason. Be sure your application is able to recover gracefully from the situations that are sure to arise as you add more variables to your development picture.
The DTC capabilities of SQL Server can be an excellent addition to your system, especially in cases where you're involving several servers with different roles in the processing picture. As with most development issues, there is more than one way to approach moving information around the system and protecting it during that process. Consider the following additional areas of information as you design your system:
© Copyright, Macmillan Computer Publishing. All rights reserved.