SQL Server 2005 : Implementing Service Broker

10/11/2010 2:10:39 PM
Service Broker is an inter-application messaging agent that provides queuing and messaging within a single instance of SQL Server or between multiple instances. Database applications can use Service Broker to utilize an asynchronous programming model. Unlike synchronous models, where activities happen live, in real-time, asynchronous processing operates under extended delays, often waiting for secondary processes to activate the continuation of the procedure.

In a synchronous model, the application often spends its time waiting for a response. In many situations, the response is nothing more than a confirmation that an action was completed. In other situations, responses may be needed from other applications or activities, and an asynchronous model would therefore allow processing to continue without requiring a wait.

In an asynchronous model, processing can continue in situations in which the response is not a requirement for processing to continue. To shorten the interactive response time and increase overall application throughput, you can implement this type of model by using Service Broker. Service Broker provides reliable messaging between servers and their applications. Messages are the interaction tools used between the service applications that utilize Service Broker. Service Broker uses TCP/IP to exchange messages. The messages are delivered using mechanisms that help prevent unauthorized access and provide for encryption.

The next few sections illustrate some of the coding and functionality of Service Broker. In each environment, the details change significantly. For that reason, the example described here is intended solely as a mechanism to illustrate functionality. If it were to be put in place in an actual environment, several additional pieces would be needed.

Designing a Service

A Service Broker service component is composed of several elements. When designing an application that will interact with Service Broker, you need to specify the following:

  • Message types— You define classes and provide names for the messages that will be exchanged.

  • Contracts— You define the direction of the message flow and which types of messages are utilized within a conversation. A contract provides the service with a set of message types.

  • Queues— These are the message stores. Queues are the vehicle that enables asynchronous communication between services to exist.

  • Services— These are the endpoints for conversations. Messages are sent from one service to another. A service specifies a queue and the contracts for which the service is targeted.

The basis for a Service Broker application is a conversation. Two or more applications communicate by creating conversations. Conversations occur between service components. When a conversation is initiated, it allows for the exchange of messages between services.

Defining Message Types

The first portion of a service is the message type. When you create a message type, you are creating a category of message that can be used by an application. With the creation, you may also specify any validation that is to occur against the message. The message content can be checked against an existing schema or have less stringent validation rules, as is the case in the following examples:

CREATE MESSAGE TYPE OpenPositionResponse
CREATE MESSAGE TYPE ApplicationReceived

If you are using an XML schema for validation, the schema must already exist within the database before you can create the message type.

Providing Contract Details

A contract is used to define the message types used within a conversation, and it determines which side of the conversation a message can be sent by. The initiating service specifies the contract to be used for any given conversation. The target service defines the contracts that the service accepts. You use the CREATE CONTRACT statement as follows:

( OpenPositionResponse SENT BY INITIATOR,
ApplicationReceived SENT BY TARGET)

The contract can involve any number of message types. Each conversation need not use all messages that are bound to the contract.

Creating a Queue

A queue is used to store messages. When a message arrives for a particular service, Service Broker places the message in the queue that is associated with the service. Message queues can be created with a number of different mannerisms.

The queue definition that is provided at the point of creation determines how messages are handled when they reach the queue. You can make changes in the queue definition by using ALTER QUEUE, as in this example:

CREATE QUEUE JobApplicationQueue

If no activation is defined, the queue must be explicitly handled and read by the services that interact with the queue. When you define activation, you provide the name of the stored procedure that will execute to handle the message. It is possible to turn the queue status on and off so that it is unavailable. If the activation status is turned off, the queue is still available, but the automated handling as messages are received does not occur.

It is not recommended that you retain messages over time. If the RETENTION setting is set to ON, messages stay in the queue after they have been received. It can degrade performance and increases maintenance on the system to periodically review and clean out messages from the queue.

When setting up the procedure execution security for a task, you can choose to execute the procedure as the owner of the queue, self (the person who created the queue), or another defined user.

Assembling Components into a Service

After all the components of a service have been created, the parts can collectively become the service. When you create a service, you assign the service to a queue and optionally provide the contract that will be used for the service communication to the queue, as in the following example:

CREATE SERVICE SubmitJobApplication
ON QUEUE JobApplicationQueue(JobApplication)

At this point, you initiate the service by using the communication and you initiate the messages by using some front-end application. Within each application, interactions with the queues is simply a matter of sending and receiving messages.

The Communications Dialogue

A dialogue encompasses a conversation. The initiator must begin the communication via the dialogue, and the target usually handles the message and ends the communication. Messages are sent to the corresponding queue using the dialogue via the SEND statement, as in the following example:

Declare @dialog_handle uniqueidentifier
Declare @XMLMessageContent XML

SET @XMLMessageContent = NCHAR(0xFEFF) +
N'<root>XML Message Content</root>'

FROM SERVICE SubmitJobApplication
TO SERVICE 'ProvideApplicationResponse'
ON CONTRACT JobApplication

;SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE OpenPositionResponse( @XMLMessageContent )

You read messages that are waiting in the queue by using the RECEIVE statement. When retrieving messages, a dialog need not be initiated. An example of message retrieval is as follows:

Declare @dialog_handle uniqueidentifier
Declare @msgtype nvarchar(256)
Declare @XMLMessageContent nvarchar(max)

;RECEIVE TOP(1) @XMLMessageContent = message_body,
@dialog_handle = conversation_handle, @mgstype = message_type_name
FROM ApplicationResponseQueue

The simple example that we have been using illustrates the functionality in a singular machine environment, using local object names. If the communication is going to occur between multiple machines, some additional setup is required. For communications over a local area network, for example, you need to create a TCP endpoint for the service broker to use, like this:

CREATE ENDPOINT ServiceBrokerEndPoint

To set up the security credentials that are needed to initiate a conversation with a service that is located on another machine, you need to define the credential binding to be used. You do this by using CREATE REMOTE SERVICE BINDING, as in the following example:

TO SERVICE '//YukonTwo:80/sql/Services'

With this binding you can identify a specific owner of the binding by using the AUTHORIZATION clause. You can specify the USER clause to identify the owner of the certificate associated with the remote service. It is possible to define the remote access as anonymous.

If ANONYMOUS = ON, anonymous authentication is used, and operations in the remote database occur as a member of the public fixed database role. If ANONYMOUS = OFF, operations in the remote database occur as a specific user in that database.

You can set up an HTTP namespace to be used. This makes it easier to access the services on the machine from sources external to the machine. You can initiate a namespace by using the following stored procedure call:

sp_reserve_http_namespace N'http://YukonTwo:80/sql'

The name can be a machine name or can be a standard DNS name for the location of the service.

Top 10 Video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer