This chapter is from the book
This chapter is from the book
This chapter is from the book
Service Broker
In replication, data is exported from one server to another, but it always stays in the format of a database. In Notification Services, data is delivered to subscribers, using phones, e-mail, or other formats. Although you can send data between systems using these technologies, they are often used in a unidirectional method, sending data from a server to another system or user. Earlier I explained that there are distinct advantages in using a disconnected system such as replication and Notification Services, which are in effect store-and-forward mechanisms. The SQL Server 2005 Service Broker extends this concept to the entire application.
With the Service Broker, you can build an entire infrastructure that is "loosely coupled." That means that the system does not receive immediate feedback from each application that the tasks are complete, just that the task has been received.
Microsoft uses the analogy of the Post Office to explain the Service Broker. For the DBA learning about an SOA, this is a great way to think about the process. I use that analogy to describe what happens at a physical Post Office and then extend it to show the parts of the Service Broker. In the "Take Away" section, I show you a simple Service Broker program example that you can extend to do useful work on your production servers.
Using the Post Office to Understand the Service Broker
Let’s assume you want to place an order for a new computer through the mail. You write a letter, place it in an envelope with instructions for the final location (the address), and drop it into any receptacle that the Post Office recognizes as a pick-up point. The message is picked up and brought to the local Post Office, sorted, and sent to the next Post Office in the chain all the way down the line until it reaches the Post Office closest to the recipient. That Post Office delivers the message to the recipient, who opens the envelope and reads your instructions. They initiate several actions in multiple departments, and you receive a confirmation message and then a follow-on package from the company, which completes the transaction.
Most of us use this kind of system every day, without considering the advantages it provides. For one thing, it provides a distribution of function. You write the message and receive the goods, without any knowledge or concern about the route the message will take. The Post Office carrier picks up the envelope without knowledge of its contents or final destination. The Post Office sorts the message and routes it without knowledge of the message. The recipient reads the message without concern for its previous route or future destination. The process is then reversed. As long as everyone does his or her part, there is no need to have an active governing system that needs to know about every component, from the routes to the message content. In other words, one person does not have to follow the letter from beginning to end.
Another advantage is that a single message might cause a flurry of activity at the receiving end. If you order something through the mail, that triggers several departments at a company, such as purchasing, shipping, marketing, and so on. All with just one small order that you sent: you do not have to send an individual message to each department to have them start working on your order. In fact, each department is working on several orders at one time.
The Service Broker works the same way. Each component has a specific function, and items can be "dropped off" at each point with the guarantee that the component will perform its function having no knowledge or control of any of the other parts. Developers can write complex systems by coding their individual parts with the Service Broker and letting each work on multiple activities at once.
Let’s take a look at each of the parts of the Service Broker as it applies to the Post Office analogy. Along the way I explain how the two relate and some of the information you will need to implement and maintain your own system.
The Postal System and the Service Broker
As a conceptual "umbrella" to the whole process is the idea of a postal system. Someone (in the United States, Benjamin Franklin) originally institutes and lays out the rules for getting messages from sender to receiver. In the case of the Service Broker, the SQL Server 2005 database engine works in terms of conversations, which are similar to a single-direction motion in the postal system. For instance, the postal carrier picks up your mail from your mailbox and carries it to the local Post Office. That is a conversation in Service Broker terminology.
Happily, the message in the Service Broker is the same as the message in the postal system, referring both to the enclosure (envelope) and the payload inside it. The only difficulty is making sure you define whether you are talking about the whole thing or just the payload inside. I try to make that clear as we move along.
In a simple example where the recipient is in the same town that you are, the message leaves the local Post Office and moves to the recipient’s mailbox. That is another conversation. Taken together, the two conversations along with the message form what Microsoft calls a conversation group.
When your letter gets to the Post Office, it is stored there until it moves on. In the Service Broker, this is handled by storage called the queue.
Once inside the Post Office, the letter moves from place to place to be properly sorted. Those internal processes that keep everything in the right place in the Service Broker are called dialogs. The Post Office stamps a "cancellation" across the message to show when it was received. If you are in the United States, April 15 (the day our tax forms are due) is one date you want to make sure you see on your envelope. Dialogs in the Service Broker do the same thing. They stamp each message with an order number so that they are guaranteed to be correct. This is important in transaction-type applications because one line in an order might change the one prior to it.
The Post Office has employed several people to do all the moving, sorting, and delivering. In the Service Broker, the employees are called services, and activations make them work. We discuss those further in a moment.
The Post Office actually handles more than just one type of letter. You might send a postcard, or letters of various sizes. You might send a package, too, and even specify special handling instructions. In the case of the Service Broker, this is referred to as a message type. Because applications can send everything from text to music files, each is treated a little differently. Most of the issues I have run into using the Service Broker involve a mismatch of types.
The letter is finally read by the recipient. In the Service Broker, the system sends an EndDialog message, which ends the conversation.
That is the broad overview of the physical layout; now let’s drill in a little to the traffic flow from the sender to the receiver.
The Mail—Message Types and Contents
The postal process starts with the mail message itself. Whenever you look at a piece of mail, it has at least two components: the envelope and the contents of the message inside. The envelope has a required size, it must have the addresses for both the sender and receiver in certain places and be readable, and it must have the right amount of postage. You form a contract between yourself and the Post Office that you will follow the rules and they will deliver the letter.
Envelope—Message Types
To send a letter, you have to subscribe to the rules they lay out for envelopes and postage. In the Service Broker, these are analogous to message types. A Service Broker message type has two basic values: XML and NONE.
In the XML type, SQL Server attempts to check the validity of the document prior to processing it. Anything that can use text can use the XML type. You can also specify that you want to process it with a particular XML schema document to verify the contents.
If you are sending anything other than text, such as a binary file, you can use the message type of NONE. That type instructs SQL Server not to check the validity of the contents.
The message type is specified when you send the message with the SEND and RECEIVE T-SQL commands, and you can create new ones with the CREATE MESSAGE TYPE command in T-SQL. I show you this process in the "Take Away" section at the end of the chapter.
Contents
You can send entire books in a single pass through the postal service, but that is not always a good idea for the Service Broker. You are working with a computer system that uses your regular network lines, so it is usually best to break down the messages into smaller pieces.
You create the message in your originating program, called the initiator. Most of the time the documents are passed around using XML files.
The Sender—Initiators, Contracts
When you send mail, you are responsible for writing the message on some type of paper, for recording the address properly (both yours and the recipient’s) on the envelope, and using proper postage. The postal service guarantees that if you follow those rules, they will deliver the letter. That is a contract, and in the Service Broker you have exactly the same type of arrangement.
The user or a program, called the initiator, enters the information they want to send or receive. The program runs a stored procedure, which creates a message and begins a conversation with the proper Service Broker service program, which I explain in a moment.
The Postal Carrier—Physical Transport, Protocols, and Remote Service Bindings
Although it is invisible much of the time, you are still using some method to move this data from client to server. In the Post Office, postal carriers drive vans or walk a route to pick up and deliver mail. In the Service Broker, physical transports (such as the network infrastructure) and protocols (such as TCP/IP) handle the movement from one location to another.
That might seem obvious, but it is important to consider it in the overall picture. If the Post Office ignored the fact that they use vehicles in their work, they might not perform the routine maintenance they need or plan for redundant vehicles. It is the same in the Service Broker. You need to be aware that there are physical parts to your system and plan for maintenance and backups in case they fail.
You can secure the entire communication process with certificates if you want. This provides a high level of encryption so that the message is difficult to break.
The Post Office—Endpoints, Queues, and Activations
I have been explaining the Service Broker using Microsoft’s postal service analogy, but I have moved in the direction of the client to the receiver. I have done that on purpose so that you can see how it all fits together, but in the postal service as well as the Service Broker, that is not how things really get built. Before a user ever sits down to write a letter, the Post Office has to be built first. That is the same way that it is in the Service Broker; you normally build the middle first and work out from there.
Each Post Office has a set of doors that only the postal carriers are allowed to use. In the Service Broker, this is called an endpoint. Endpoints are network connections directly into a service. The conversation groups I described earlier send messages between services using endpoints. Much of the DBA’s time is spent creating and tracking endpoints.
In the Service Broker, the queue is similar to a single storage location at the Post Office. A queue is the data repository for all the messages. Unlike the Post Office, one message can be sent from a queue to multiple locations. I guess it is more like the Post Office than I care for; many people do get lots of the same junk mail.
In the Post Office, an employee is assigned to work on certain kinds of mail, in certain areas of the Post Office. In the Service Broker, each queue is associated with a stored procedure or a managed program in the CLR layer, called a service program. When a message is placed in the queue, the service program is activated and begins to process the message. Most of the work the developer does is within these stored procedures. In effect, these stored procedures are the workers in the Post Office.
This brings up another advantage in having Service Broker running directly within the database. This architecture removes the need for multiple services to be installed on the operating system. Because the queue is associated with a stored procedure, the stored procedure "wakes up" each time there is work to do, but not before. Nothing has to run in the background, constantly watching for work to do.
Another important part of this analogy is that whereas a postal worker might send the message to the final recipient, the worker might also send it to another postal worker for some additional processing. In the Service Broker, the stored procedures might do the same thing. Depending on what the stored procedure code is, the message might be processed and a final message sent or sent to yet another procedure for more processing.
The Recipient—Calling Programs
When the processing is complete, the conversation is terminated. Your original calling program can either collect the status from the Service Broker or move on without checking.
Security
Unlike other database applications, the Service Broker is based on passing messages between systems. Because of that, you are not as concerned with individual user accounts. It is a bit more like the application security explained in Chapter 4, "Security," because a single account is used to access the system on the user’s behalf.
In the Service Broker, you focus your security on two areas: dialog and transport. Dialogs have to do with the messages, and transport has to do with the network. Implementing both makes the system secure.
Dialog security is set up by creating a user in the application database and then a certificate for that user. From there, you can use a Service Binding mechanism to associate the user and the Service Broker. The application uses that user to send and receive data.
For transport security, you set up a server login and a certificate in the master database and then send the certificate to the calling programs and the DBA for the application. The system uses this login to process the transactions.
Monitoring and Performance Tuning
To manage and monitor the system, you get four new dynamic management views and three Performance Monitor objects and counters.
The dynamic management views are as follows:
|
sys.dm_broker_activated_tasks |
Shows all of the stored procedure activations |
|
sys.dm_broker_connections |
Shows all of the Service Broker network connections |
|
sys.dm_broker_forwarded_messages |
Shows the messages that are in the process of forwarding |
|
sys.dm_broker_queue_monitors |
Shows the queue monitor that manages activation for a queue |
To display the Performance Monitor values, add the following objects and counters to your trace.
|
SQL Server, Broker Activation Object |
Shows stored procedure activations |
|
SQL Server, Broker Statistics Object |
Shows general Service Broker information |
|
SQL Server, Broker / DBM Transport Object |
Shows the interaction between the Service Broker and database mirroring |
The two most important tuning strategies for the Service Broker are planning the layout and optimizing the stored procedures that act as the service programs in the application. Proper planning for the infrastructure is normally why you have implemented the Service Broker to begin with. When you analyze a solution, always ask whether an asynchronous architecture is possible or preferable, and then implement the layout accordingly.
Like most applications, the biggest bang for the effort is in tuning the T-SQL code that runs within the stored procedures. One strategy that you might want to alert the developers about is the use of the WAITFOR statement. This instructs the stored procedure to wait for an event to occur prior to looping through the code. Adding this statement is important in a disconnected architecture because the message might not be complete when the service program is activated.