A preliminary study of SQL Server Service Broker

This article outlines how to take advantage of the new Service Broker features in SQL Server 2005 to speed up and simplify the development of SQL Server applications based on asynchronous queued operations. (Note that in the sample file, the programmer's comment is in English, which is translated into Chinese for easy Reader's understanding.) ）

First, the introduction

One of the major achievements of SQL Server 2005 is the ability to implement reliable, scalable, and full-featured database applications. Integration with the. NET Framework 2.0 Common Language Runtime (CLR) enables developers to combine important business logic with stored procedures, while new features in T-SQL and XML extend the range of available data operations and the storage capabilities available to developers. Another important feature is SQL Server Service Broker, which adds reliable, scalable, and distributed asynchronous functionality to database applications.

Second, why write asynchronous queued operations?

When we developed SQL Server 2005, we talked to people who had successfully developed large, extensible database applications. It turns out that almost all of their applications have one or more operations that are performed in asynchronous queues. The settlement activity in the stock trading system is queued so that it can be settled in the background and other transactions are processed on the front end. The shipping information in the order entry system is placed in a queue and will later be read by a shipping system running on another server (usually in another location). The travel reservation system makes the actual reservation after the customer completes the route and sends a confirmation email after the booking is complete. In all of these examples, much of the work is done asynchronously, thus increasing the responsiveness of the system so that users do not have to wait for the work to be completed before they can receive a response.

In most large systems, after careful analysis, many of the processing can be done asynchronously. Although queued tasks in an application do not need to be completed immediately, the system must ensure that these tasks are reliably completed. Service Broker makes asynchronous queued tasks reliable and easy to implement.

Another advantage of asynchronous execution of some tasks in an application is that the processing of these tasks can be completed when resources are available. If the shipping task in the order entry system can be executed from the queue, the shipping system does not have the ability to handle the peak order load. In many order-entry systems, the peak order rate may be twice times or three times times the average order rate. Because the order entry system must have the ability to handle peak loads, a large amount of processing power is idle for a large part of the time. If the asynchronous task is queued and executed during peak times, the utilization of the system is significantly increased.