Home > Articles > Data > SQL Server

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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020