Ms SQL Server 2000 administrator manual series-25. Component Services and Microsoft Distributed Transaction coordinators

25. Component Services and Microsoft Distributed Transaction coordinators
Component Service Overview
Microsoft Distributed Transaction coordinators
Summary
This chapter will learn about Microsoft Distributed Transaction coordinators (Microsoft Distributed Transaction Coordinator, ms dtc), which allows a single database transaction to access multiple data sources and ensure data integrity. You will see in this chapter that ms dtc has many purposes and many types of applications must use the services provided by ms dtc.
Ms dtc is a part of the component service. The component service is derived from the Microsoft Windows NT Service and is a collection of products and technologies built in Windows 2000. Component services include Component Object Model (COM), Distributed COM (DCOM), Microsoft Transaction Server, Microsoft Internet Information Server, and Microsoft Message Queue Server. In Windows 2000 component service, COM and DCOM have a new level of technology, so it is called COM +. COM + applications and other system services constitute the Windows 2000 component service project. Before getting started with ms dtc, let's take a look at component services.
Component Service Overview
　
Component Services is a tool composed of multiple different products. It uses the unified Microsoft Management Console (MMC) Management window. The component service is an embedded Management Unit of the console. The service content includes:
• COM + application services
　
• MS DTC
　
• Event Viewer Service
　
• System Service
　
• Microsoft Message Queue
　
To run the component service management tool, click Start> Assembly> system management tool, and then select the Component Service Startup console, 25-1.

　
 
Figure 25-1 "component service" Console
COM + application service
　
COM + application service is an application developed using Microsoft's COM + specification. COM + is an extension of the COM module. Com is used for the development of distributed applications and has been developed to COM +, making it easier for security and queue components to be developed on applications.
You can set your COM + application components in component service MMC. Follow these steps to access your COM + application (25-2): Expand component service → computer → my computer → COM + application.
On the screen, you will see all registered COM + applications. After you expand the data folder of your COM + application, you can choose to enter these applications.

　
 
Figure 25-2 COM + applications in the component service console window
6) MS DTC
　
In the component service console window you just saw, under the COM + application data folder is the ms dtc data folder. We will discuss ms dtc in detail in the next section, which will not go into detail first.
Event Viewer Service
　
This service tool was developed by window nt's event checking tool. The Event Viewer service in Windows 2000 is the same as Event Viewer in Windows NT, allowing you to access event records to access data. All applications, security data, system errors, and information data are stored in the event records. We must check these records at any time. You can find the event view data folder in the component service window, expand the data folder (25-3), and click the event record to view the data.

　
 
Figure 25-3 event view data folder window
System Service
　
System Service tools are developed by Windows NT Service tools. System services allow you to view and manage all service tools installed on the system. When you expand the service data folder, you can view system services (25-4 ).

　
 
Figure 25-4 expand the service data folder to view system service tools.
If you want to start or stop a service tool, click the service tool and press the right button to select the option to be executed (25-5) from the shortcut menu ).

　
 
Figure 25-5 shortcut menu
If you want to view or modify the properties of a service tool, select Properties From the shortcut menu in the shortcut menu to open the Properties window of the service tool (25-6 ).

　
 
Figure 25-6 window of the "General" tab in Service Properties
In the normal window, you can change the service tool settings or stop or suspend the service tool. Other tabs allow you to change the way the service is logged on to Windows 2000. Attributes are important to service tools. In this setting, you can choose whether to enable the Service tool automatically when the system starts, or change the account used by SQL Server and MS DTC. In addition, you can specify the actions to be performed when a service tool encounters a problem. In this window, you can also view the dependencies between services.
Microsoft Message Queue
　
Although the service component MMC does not display Microsoft Message Queue (MSMQ), it is considered a part of the component service because it also leverages ms dtc for external transactions. Microsoft Message Queue allows messages to pass transactional or non-transactional messages between different applications or systems. Applications Use message queues to deliver durable messages between servers ). The persistent message means that the message will not disappear due to system failure (such as power failure. If the system suddenly loses power, MSMQ will continue to run the message queue when the system replies. Microsoft Message Queue uses store-and-forward to deliver messages, which are stored in the queue even when a network problem occurs. Other functions of message queue are as follows:
• Offline message delivery: to use the Message Queue component to deliver messages, it does not mean that messages must be online. In addition, messages can be routed so that connections can be established between different communication protocols.
　
• Dynamic queue management: Message Queue does not need to be restarted when a queue is added or changed.
　
• Configurable message priority: the message queue sorts messages based on their priorities. You can set the priority to be sent first.
　
• Supports clusters: Message queues can detect clusters and support active/active and active/passive clusters.
　
• Integration with Active Directory directories: Message queues can use directory services in Active Directory.
　
• Communication with MSMQ 1: Message Queue can communicate with MSMQ 1. MSMQ 1 is the predecessor of message queue.
　
• Integrated with Windows 2000 security features: enhanced windows 2000 security features are available.
　
• Message backup and reply: You can back up messages and reply to messages when system problems occur.
　
• Use MMC for management: Message Queue Management functions are now the same as many other management tools and are embedded in the console.
　
As you can see, the Microsoft Message Queue System supports many features. However, this chapter will focus on the transaction messaging component in the message queue, that is, ms dtc.
Microsoft Distributed Transaction coordinators
　
As mentioned before, ms dtc is part of the Windows 2000 component service. (The concept of ms dtc already exists in SQL Server 7 .) The component service also contains COM +. COM + is used for non-transactional communication, while ms dtc is used for transactional messaging.
Ms dtc is mainly used to handle distributed transactions. Distributed transactions are transactions within a single SQL server that spans two or more databases. These databases can be placed in the same or different computer systems. Transactions between different data tables in the same database cannot be called distributed transactions. In the same system, front-end transactions between databases can be processed in general (non-distributed) ways. However, SQL statements are still processed in distributed transactions during execution.
Overview of MS DTC
　
When a transaction contains data from many different sources, the applications that provide the data must be coordinated. The importance of coordination lies in ensuring that transactions must be partially completed. In other words, not all data modifications are executed, or all data modifications are not executed. If a transaction is partially completed and partially failed, the transaction logic is not consistent and data may be lost. Ms dtc performs a two-phase transaction commit (two-phase commit) to confirm the transaction logic.
Two-phase transaction recognition has seen significant technological changes in recent years. This is not a new technology, nor exclusive to SQL Server. In fact, because both SQL Server and Oracle databases support two-phase transaction recognition models and ODBC, the two databases can also use two-phase transaction recognition for distributed transactions.
The two phases of two-phase transaction recognition are: Preparation and approval. In the application, initialize the ms dtc with the commit command to execute two-phase Transaction Approval. Ms dtc on one SQL Server coordinates with MS DTC on another system to handle a distributed transaction. The components that process two transactions in ms dtc are called resource administrators.
When you execute the approve (COMMIT) command, ms dtc notifies the resource administrator to start the preparation phase. The first stage (that is, the preparation stage) will execute all the actions required for approval, including clearing the buffer zone and writing transaction record files. The steps executed at this stage are similar to general standard approval steps. The only difference is that SQL server does not actually recognize transactions or lock the resources used by the exchanges. When all the pre-actions for an authorized transaction are completed, the resource administrator sends the prepared signal to the transaction administrator. When all resource administrators send a successful signal, the approval phase begins.
The approval phase is carried out in a distributed system. At this stage, if all the resource administrators send a success signal, the application receives a message indicating successful acceptance. If one of the resource administrators sends a failure signal, the restore command is sent to each resource administrator. Then, the application receives a message indicating that the request failed. When the system transaction takes too long, it also executes the recovery command. When the approval in a system fails, all other related systems in the distributed transaction will execute recovery commands.
Example of MS DTC
　
In this section, we refer to two examples of ms dtc in real life, you can see the importance of ms dtc in distributed transactions, and how the system handles unsuccessful transactions.
________________________________________
Real World Bank Transactions
For remittance transactions between banks, when the customer wants to remit the money from Bank A account 1 to Bank B account 2, the bank's computer has to execute two transactions: deduct the money from account 1 and add the money to account 2.

　
 
Let's talk about how to send money first. If both accounts are in the same bank, the system only needs to update the data of one database. However, if the two accounts are different banks, a remote transaction must be started. For the system, you have to deal with two transaction steps. The transaction can be performed in one of the two systems, or in the third system (Client System.

　
 
When a bank clerk executes a transaction on an application on the client computer, the application starts to call SQL to execute the task, and the SQL starts to execute the following items:
1. Update System A and deduct the amount of Account 1
2. approved transactions
3. Update System B and add the amount to account 2
4. approved transactions
This operation method is acceptable when it is determined that every system is operating completely normally, but it is not a completely safe operation method. For example, if only one system completes the transaction, but the transaction of the other system fails. The following situations may occur:
• The transaction process of system A fails, the transaction of system B cannot be executed, and the transfer fails.
　
• The transaction process of system A is successful, but the transaction of system B fails, and the amount disappears.
　
Of course, such transactions cannot happen. Therefore, the following transaction steps are considered safe transactions.
1. Update System A and deduct the amount of Account 1
2. Update System B and add the amount to account 2
3. The above two steps are recognized as a set of transaction procedures, and all or all of them are not recognized.
Ms dtc allows you to recognize distributed transactions as a group, that is, you can recognize a group of transaction steps, if one of the transaction steps out of the problem, execute recovery instructions, in this way, the above errors will not be generated.
________________________________________
________________________________________
Real-world e-commerce applications
E-commerce applications often need to process a large number of concurrent online transactions at the same time. Therefore, several systems need to deal with large transaction volumes. If a company uses only one computer system to deal with all transactions, it is not suitable. The following are possible conditions for a company:
• A system is overloaded: each computer and system has a certain amount of load, and too much transaction volume can easily overload a single computer and system.
　
• Business logic: in terms of business logic, it is reasonable to divide different functions into different systems. For example, you can place user data in one system and product data in another system.
　
• Outsourcing: E-Commerce uses outsourcing functions. For example, the accounting part may be outsourced to an accounting firm.
　
When transactions need to span multiple systems (or multiple databases in one system), distributed transactions are used to access system data (or databases ). If ms dtc is not used, the system transaction cannot be partially completed.
Now let's take a look at how e-commerce transactions use distributed transactions. In this example, customers use credit cards for online shopping. Let's take a look at the steps to complete a transaction.
________________________________________
________________________________________
Description
There are many types of e-commerce transactions. The examples we use here are for teaching reference only. They do not mean they are the best way to trade.
________________________________________
________________________________________
1. The customer connects to the website of the online merchant through the network and has not officially entered the merchant's database.
2. The customer selects product A and puts it in the electronic shopping cart.
3. To complete the above operations, the computer must enter the database to perform two transaction steps.
A. Search Steps to find out whether the customer is a new customer or an old customer. if the customer is an old customer, retrieve the data related to its cust_id. (Some applications perform the search step only during the purchase step .)
B. Put item A into the shopping cart data table.
4. When the customer is preparing to settle the bill, a transaction or a group of transaction databases will start to perform the following operations:
A. Read the shopping cart data table and find the items purchased by the customer.
B. Update the order data table and confirm that the New Order has been added to the data table.
C. Update the customer database and confirm that the bill will be sent to the customer. In this example, the customer data and order data are in two different databases, so they will be applied to distributed transactions.
D. Delete the item from the record in the shopping cart data table. (Some applications only delete records during batch processing .)
E. When the account data table and order data table are updated, the transaction is recognized and distributed transactions are completed.
5. Now, e-merchants will enter the warehouse database, prepare for shipment, and complete the procedure as follows:
A. query and retrieve the order data table.
B. Bind The searched goods to prepare for shipment.
C. The electronic merchant's system is connected to the credit card processor for payment requirements. This transaction mode is also considered a distributed transaction, because the payment requirement is recorded on the local end. (Some applications make payment requirements first when the customer settles the bill. This method is not a normal procedure, because authorization is required online with the credit card center at the time of settlement, the transaction time will be extended .)
D. Update the order data table and database before shipment preparation.
6. The goods are successfully delivered to the customer.
If ms dtc is not used for two-phase transaction recognition in this transaction, the following problems may occur:
O The payment request step is approved successfully, but the approval for the order data table and database update process fails, resulting in paid but not shipped.
　
O payment request step approval failed, but the order data table and database update step approval was successful, resulting in shipment did not receive money.
　
O The shipment process was approved, but the shipment was not delivered yet.
　
So the most important thing about distributed transactions is to confirm that every database in the transaction must be updated together, otherwise it will not be updated. If the transaction process is not coordinated, consistency between databases will be lost and many errors will occur.
________________________________________
Ms dtc Properties
　
The importance of ms dtc in the transaction process was mentioned earlier. Here we will discuss several ways to start ms dtc.
• Call remote programs included in distributed transactions
　
• Use the transact-SQL (T-SQL) Directive to start distributed transactions
　
• Update data from multiple ole db data sources
　
• Embedded ms dtc commands in your application
　
These three methods allow you to execute distributed transactions (25-7) on the system ). The SQL server at the transaction initialization side executes all the necessary actions to call ms dtc to execute distributed transactions that do not need to be executed manually, and SQL Server takes care of all the details for you.

　
 
Figure 25-7 initialize Distributed Transaction in SQL Server
If you start distributed transactions with the last method, the client's application interface and the SQL server interface on the network communicate with the ms dtc and SQL Server. The SQL Server Client helps coordinate distributed transactions. Figure 25-8 shows the Distributed Transaction architecture.

　
 
Figure 25-8 embedded ms dtc commands in the application to initialize distributed transactions
Ms dtc Program Design
　
This book is not written for program designers, so it won't touch too much on how to use and design distributed transactions. However, this section will briefly list several ways to start distributed transactions, test ms dtc with simple transactions.
The following actions can start distributed transactions:
• Access a remote data source in a transaction: as a result, the transaction will be upgraded to a distributed transaction. In a transaction, any distributed query will upgrade the transaction to a distributed transaction.
　
• Execute the begin Distributed Transaction command to generate distributed transactions immediately.
　
• Use REMOTE_PROC_TRAN-SACTION options in the SQL Server Installation option: executing a remote call program immediately upgrades transactions to distributed transactions.
　
• Call ole db or ODBC functions: ole db and SQL server have the syntax to start distributed transactions.
　
T-SQL DTC can be tested for distributed transactions through MS. You can use the begin Distributed Transaction command of the T-SQL to start distributed transactions and use the commit command to execute the approval procedure as follows:
Begin Distributed Transaction
Select employeeid from northwind. DBO. Employees
Select emp_id from pubs. DBO. Employee
Go
Commit
Go
First enter the first four lines of program code, and then enter the last two lines of program code. In this way, you can view the transaction in the Change List data folder in the Distributed Transaction Coordinator (in the component service MMC) data folder. To view this data folder, expand component service> Computer> my computer, and finally expand Distributed Transaction Coordinator in the component service console. When the Transaction View is complete, enter the last two lines in the above T-SQL code. Note: after the transaction is approved, the transaction cannot be found in the Change List data folder. Of course, most distributed transaction processes are complex (including update and insertion), and are not as simple as an example. This example provides a simple test method without changing any data tables.
In general, distributed transactions Use ODBC or DB-LIB APIs in a program to call to start or stop transactions. The Program Design of Distributed transactions is a little different from that of other transactions. When a transaction is established, it must be online and the automatic approval option should be disabled to prevent the SQL statement from automatically recognizing the transaction. When an application ends with one of the commands in commit or rollback, ms dtc begins two-phase transaction recognition.
________________________________________
Related information
For SQL Server application development, see inside Microsoft SQL Server 7.0 (Microsoft Press, 1999) co-authored by Ron soukup and Kalen Delaney ); if you are using Microsoft Visual Basic, see The Hitchhiker's Guide to Visual Basic and SQL Server (Microsoft Press, 1998) by William Vaughn ).
________________________________________
Managing MS DTC
　
Ms dtc is the installation option for Windows 2000. If you need this service, just click it when installing it again. There are two ways to start ms dtc: using an SQL service administrator or using component services in system service components (as described earlier in this chapter ). To enable the SQL Server service administrator tool, click Start> Assembly> Microsoft SQL Server> service administrator. Figure 25-9 shows the service administrator tool.

　
 
Figure 25-9 use the SQL Server service administrator to enable MS DTC
In the service drop-down menu, select ms dtc. Click Start/continue to start the service project. Click Stop to terminate the service project. You can select whether to enable the service automatically at startup in the check box at the bottom left.
Monitor MS DTC
　
To monitor ms dtc, you must use the component services management console. Start the console (as demonstrated earlier in this chapter), expand Component Services → computers → my computers → Distributed Transaction Coordinator, as shown in Figure 25-10.

　
 
Figure 25-10 "Distributed Transaction Coordinator" in the component service 」
In this data folder, you can see two options: Change List and change statistics.
Change List
　
The Change List (as shown in April 25-11) is a list of all distributed transactions being executed in the system. It examines the properties of a transaction and can enforce the recognition or abandonment of the transaction. The operation method is to click the right button in the Change List and select the appropriate option from the shortcut menu.

　
 
Figure 25-11 "Change List" shows ms dtc transactions
Change statistics
　
Change statistics (as shown in 25-12) can view distributed transaction information, such as the number of transactions in progress and the maximum usage, to provide an overview of ongoing distributed transactions, it is also helpful for future capacity planning. As you can see, it is not difficult to monitor, establish, and execute distributed transactions.

　
 
Figure 25-12 change statistics show ms dtc transactions
Summary
　
In this chapter, I learned the operation of MS DTC and the establishment of distributed transactions, but also basic concepts of the component services and technologies provided by Windows 2000, in addition to the management of component services. The following sections describe SQL Server rewriting.