70-451 – Designing a Database Solution – Service Broker


Design a solution by using Service Broker.
This objective may include but is not limited to: designing services, contracts, activation, routes, message types, queues, remote service binding, priorities

My Take

From the set of requirements it seems like we will need to understand Service Broker pretty well, from message types, queues, service and contracts to the routes that are set up between instances , the bindings required and finally some of the new features such as the priorities.

I am interested enough in Service Broker that I am going to try to look over more than just the subjects listed by going through the full range of tutorials (well, not quite all… I don’t have multiple instances set up on this machine). I have had a small interest in Service Oriented Architectures for the past few years, particularly after working for my current company and seeing how much of a mess it can be to get information between the various systems. I think, if applied correctly, learning Service Broker could do a fair amount for my understanding of how to implement something of that nature.

Designing Services

The Creating Service Broker Applications page gives a decent view of what a service broker application is and how it is that we will outline what steps are necessary to complete this design. In effect, the service broker application will be used to send and receive messages, of a particular message type, through services defined by a contract relevant to both the initiator and the target participants. These participants are defined in the routes of the service broker application. (NOTE: Services can use the GET CONVERSATION GROUP to lock a conversation to preserve the state of the conversation and processes multiple message for that group.)

The services themselves are defined on a particular queue and include the authorization and contracts which they will accept. The real power here is in the exposure of the contract to the service broker application.


Contracts are an agreement between two services of an application to determine how to complete a particular task. The contract specifies which of the message types each of the participants are allowed to use. If the services associated with the contract are in separate databases you will need the definition of the contract in each.

For more information see, Creating Service Broker Contracts.


There are two distinct types of activation for Service Broker, internal activation and external activation. Internal activation works with stored procedures while external activation works with independent applications. For the external activation, an sql event is produced to indicate that the external program should start another queue reader.

There are many ways to start an application as discussed in the Choosing a Startup Strategy article.

  • With Internal Activation the service queue monitor activates a stored procedure when necessary. This ensures that the application be written as an sproc. But it also ensures that no additional code is necessary for activation.
  • With Event Based Activation the application is run in response to a specific event. This can be anything from a trigger to an alert or job to an event notification.
  • The scheduled task describes an application which is set to run on a schedule. This is convenient for batch processing applications.
  • The startup task describes an application that is started one time, typically when the computer starts or when SQL Server starts. This type of application will remain running and processing messages as they arrive. Note that this type of application can consume more resources than the other types listed. However, it does not consume as many resources starting up the application when a new message arrives on the queue.


Routes determine where messages are to be delivered. There are three basic components of a route: Service Name, Broker Instance Identifier, and Network Address. The network address can be an actual machine address, a keyword that restricts access to the local machine, or a keyword used to determine the address by the service name. These address can point to the actual service, or to a forwarding service.

Service Broker Routing consists of three distinct steps: Finding the matching routes, Choosing a route and locating the destination service.

Message Types

Message Types are a definition which specifies the name and content of each message. They must be created in each database that participates in the conversation.  It should be noted that you can specify the authorization as well as whether or not you will have valid XML or not, even having it check against a particular schema, as well as if you expect the message to be empty.


Queues are the mechanism that stores the messages for the service broker application. Service broker manages the queues through the services associated with it, and presents a view of the queue in a fashion that is similar to a table. However, one can only select from the queue. To modify records on the queue, one has to work through the service broker applications with commands such as SEND, RECEIVE and END CONVERSATION.

NOTE: There is a new option for Queues in 2008, the "POISON_MESSAGE_HANDLING" property which specifies whether or not to disable a queue after five consecutive transaction rollbacks.

Remote Services Binding

Remote Service Binding is used to establish credentials as the relate to the local database user, the certificate for that user and the name of a remote service. This ensures the security necessary for the conversations that interact with a remote service by providing certificates for dialog security.


Conversation Priorities are one of the new features for Service Broker in 2008. They allow the messages with higher priority to be sent and received prior to the messages with lower priority. This can provide different tiers of service for the application. These priorities can be assigned to particular contracts, local services or even remote services. They are defined by an integer value between 1 (lowest) and 10 (highest).

NOTE: Priorities levels are only applied if the HONOR_BROKER_PRIORITY database option is set to ON.

This entry was posted in SQL and tagged , , . Bookmark the permalink.

2 Responses to 70-451 – Designing a Database Solution – Service Broker

  1. Robert says:

    I like your attitude about Service Broker. I think it is one of the most under-utilized and powerful features in SQL Server. Most developers would rather write a Windows service than learn to use it. It\’s unfortunate.

  2. Pingback: MCITP 70-451 Links Page | Destination: Change

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s