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:
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 JobPosting VALIDATION = VALID_XML WITH SCHEMA COLLECTION JobPostingSchema GO CREATE MESSAGE TYPE OpenPositionResponse VALIDATION = WELL_FORMED_XML GO CREATE MESSAGE TYPE ApplicationReceived VALIDATION = NONE GO
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:
CREATE CONTRACT JobApplication ( 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.
A critical component of Service Broker functionality is the definition and use of message queues while a dialogue is taking place. You should carefully study the various options available for queues and the associated dialogues.
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 WITH STATUS = ON, RETENTION = ON, ACTIVATION ( STATUS = ON, PROCEDURE_NAME = ProcessResume, MAX_QUEUE_READERS = 1, EXECUTE AS SELF)
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>' BEGIN DIALOG CONVERSATION @dialog_handle FROM SERVICE SubmitJobApplication TO SERVICE 'ProvideApplicationResponse' ON CONTRACT JobApplication WITH ENCRYPTION = OFF ;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 STATE = STARTED AS TCP ( LISTENER_PORT = 4037) FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)
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:
CREATE REMOTE SERVICE BINDING JobAppServBind AUTHORIZATION [dbo] TO SERVICE '//YukonTwo:80/sql/Services' WITH USER CertOwnerUserName, ANONYMOUS = ON
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:
The name can be a machine name or can be a standard DNS name for the location of the service.