Understanding SQL Server Yukon Service Broker

Jesus Rodriguez
12y
13.9k
0
0

Article

Abstract

The new version of Microsoft SQL Server "Yukon" comes together with a set of technologies capable of enlarging the horizon for database applications. One of the most remarkable technologies is the Service Broker that makes it possible to build database-intensive distributed applications. The Service Broker actually implements a set of distributed communication patterns to add messaging capabilities to SQL Server applications. In this article we explore the core concepts of the Service Broker and show how to use its potentialities to build applications with message interchanging.

The Broker Pattern

Currently, distributed applications are one of the most exciting areas in software development. The first kind of distributed applications was based on the client-server model. In most of these applications, clients have to be concerned with server communication details. For instance, the client should know the IP Address of the machine hosting the server as well as other details. The solution to these problems comes with the Broker pattern when it is used in order to hide the implementation details of remote service invocation by encapsulating them into a layer different from the business component itself. This layer enables clients to invoke operations on the server side just as they would on a local interface. The implementation of this pattern can hide the server communication details inside the broker that has traditionally added other functionalities such as locating clients and so on. One of the most famous brokers in the history of distributed programming has been the Object Request Broker in the CORBA standard that has added some novel concepts to the distributed application development.

The YUKON Service Broker

The YUKON Service Broker is an implementation of the Broker pattern that comes as part of the current version of YUKON and that makes it possible to build reliable, asynchronous message-based database applications. The use of the Service Broker allows the developers to build distributed applications delegating all the system level messaging details to the Service broker and concentrating their efforts in the problem domain. The Service Broker lets SQL Server applications send and receive messages. This technology also adds advantages to the messaging capabilities like reliability or asynchronous messaging.

The Service Broker exposes the distributed application components as services holding conversations among them. It also manages these services in sending, receiving and processing messages. Within a typical Service Broker scenario a source service sends a message to a target service. On its arrival, the Service Broker inserts it into a queue associated with a target service and then it can be processed by a service program. Once the message is processed, the target service responds. Due to the messaging potentialities of the Service Broker, services can communicate for days using an asynchronous communications approach.

The Service Broker Architecture

Technically, the Service Broker can be viewed as a group of T-SQL extensions used to create components to develop message-based applications. The service functionality is provided for by a set of SQL Server objects:

Service Program: A service program may be a stored procedure written in a SQL/CLR manner used to process messages. It can send messages to be processed by other service programs.
Queue: A Queue is the component that stores the messages before they are processed by a service program associated with them. This queue provides asynchronous messaging between services and offers other potentialities like lock-related messages.
Message Type: A Message Type represents a message format used to communicate with services. Specifically, they can communicate with each other by interchanging message type instances.
Contract: A Contract is a set of names of message types that specifies the direction of a message in a conversation.
Service: A service is a logical endpoint that represents a collection of the above mentioned objects performing some specific task. Services are stored in SQL Server and have a name.

Service Broker applications base their communication on a conversation. A conversation is a component that correlates and orders the messages that services receive. Theoretically, a conversation is a message exchange pattern that represents the same idea as a human dialog. The conversation support is a technological innovation in the services technologies. Some Web Services technologies like Indigo and WebLogic Server address this issue in different ways. In the case of the Service Broker it relies on some SQL Server terms:

Dialogs: Dialogs represent a bidirectional conversation between two Service Broker endpoints.
Service Instances: The service instances identify conversations working together to achieve some tasks.
Routes: A route is used to direct messages to the correct services even between different instances of SQL Server.
Remote Service Bindings: Remote Service Bindings associate a remote service with a user in a local database.

Figure 1: Service Broker architecture.

Message Types

Inter-service communication is carried out by interchanging messages. Just as sentences follow a structure in real life conversations, messages sent to services must also follow some format dictated by the Message Type used to describe the message contents (binary or XML data). In the case of XML messages, the data Service Broker always checks whether the content is valid or not, that is, it checks if messages are appropriately structured or if they follow some XML Schema. Message Types always remain in the database.

Contracts

The fact that a service exposes a contract to describe the operations it supports constitutes a pillar of the Service-Oriented Architecture. A Service Broker contract describes which messages may be used to perform a desired task and also which endpoints may use specific message types. For instance, a contract may specify that some messages may only be sent by the target service.

Queues

The role of a queue is to store messages. It is directly associated with a service. When a message arrives, the Service Broker inserts it into the queue. Likewise, when the application receives a message, the Service Broker deletes this message. A queue is also associated with the service program representing a stored procedure or program activated when the message is received. The maximum number of service programs to be activated when a message arrives is specified in the definition of the queue. This makes it possible to increase the message processing performance. The queue stores the message content and the information related to it, for example the contract, the relationship with other messages, etc.

Service Programs

The Service Program is the entity that processes the messages. Normally, it is either a stored procedure or a managed external program. As stated above, the Service Program is activated when a message arrives to a queue extracting the message and processing it. When it does this, it can send messages to other services.

Services

The service identifies a set of tasks performed by various objects specifying the contracts and the queues that store the messages. A Service represents a logical endpoint grouping other objects. To create a service it is necessary to define the message types to be sent or received as well as the contracts, the service program to process the messages and the queue that is supposed to hold the messages.

Dialogs

Dialogs represent conversations that involve two endpoints: the initiator and the target. As part of the contract and based on its role, it is necessary to define which messages can be sent or received by a service. A key aspect in any conversation is the message reliability ensured by the receipt acknowledgments of the dialog messages. The Service Broker, in turn, saves all the messages in a conversation as soon as they are acknowledged by the other endpoint in the conversation.

Another important aspect in a conversation is its lifetime. When the initiating application begins the dialog, it may specify the lifetime of this dialog. The service programs at both endpoints must terminate the dialog when they finish their work.

Routes

A route identifies an address used by the broker to send messages to a service. It does so in a unique manner. It is used whenever the Service Broker needs to send a message during a conversation. More than one route can be defined for a service while the broker may choose one at random.

Remote Service Bindings

To communicate with remote services the Service Broker should use certificate-based security. A remote service binding objective is to associate a local database user with a remote service name. The messages sent to a remote service are encrypted using the public key of the local user certificate associating this public key to a user in the remote service with permission to store messages in the remote service queue.

Service Instances

The Service Broker uses Service Instances to identify a group of related dialogs. Technically, the Service Instance is represented by a unique identifier data type. When the broker receives a message, it adds the service instance identifier to the message before inserting it into the queue.

Another effect caused by the use of the Service Instance identifier is to provide exclusive access to the messages. An isolated Service Program can at a given time read messages with the same Service Instance Identifier. The Service Instance identifier remains valid until all conversations related to this identifier end. The Service Instance identifier is the primary key in all tables that maintain the state of the applications. This feature simplifies the retrieval of data associated with applications.

Further Remarks

The Service Broker represents a significant beginning to provide a queue-based messaging technology fully integrated to database applications. Its architecture is very interesting in regards to the way it merges concepts such as Service, Contract, etc from the "Service-Oriented Architecture" with others that make up the queue-based messaging technologies. Consequently, the resulting architecture is very powerful and simple. I believe that in the future, the Service Broker might upgrade itself with some other features from the messaging technologies like message-filtering, message header processing, maybe SOAP messaging, message sequencing, among others.

Using the Service Broker

Up to now we have been exploring the Service Broker concepts and architecture. Now we will implement a brief example to show the use of some of the main components of the Service Broker. Suppose there is a system that manages customer information provided by an external entity (another system perhaps). We have designed a very simple Service to receive an XML message with data identifying a customer, to insert that message in a database and to log a message indicating that the action was performed.

The first step can be to define the message structure. An XML Schema can define this. The following code adds the XML Schema to the database.

CREATE XMLSCHEMA '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified" targetNamespace="ServiceBroker/Samples/FirstSample/Contact">
<xs:element name="ContactInfo">
<xs:annotation>
<xs:documentation>Comment describing your root Element </xs:documentation>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element name="Contact">
<xs:complexType>
<xs:sequence>
<xs:element name="email" type="xs:string"/>
<xs:restriction base="xs:string">
<xs:pattern value="[0-9\(\)\-]*"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
xs:attribute name="name" type="xs:string"
<xs:element name="phone">
<xs:simpleType>
use="required"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Now that we have a schema to define the message, we may create a message type and use the previously defined XML Schema for the encoding.

CREATE MESSAGE TYPE
[ServiceBroker/Samples/FirstSample/AddContact]
ENCODING XML
WITH 'ServiceBroker/Samples/FirstSample/Contact'

This sample merely includes the sentence meaning and its use but not its full syntax. The "CREATE MESSAGE TYPE" sentence defines a message type object by setting the message_type_name and by providing an ENCODING type to define the way in which all messages that belong to the same message type are encoded. The ENCODING type may be XML, VARBINARY or EMPTY and in the case of XML it can be associated to a Schema definition. If we need to change a message type definition, we may use the "ALTER MESSAGE TYPE" sentence.

Now that we have defined the message we need to describe a contract to specify what messages the service can send-receive. The following code shows how we developed this part in our example.

CREATE CONTRACT
[ServiceBroker/Samples/FirstSample/AddContactContract]
[ServiceBroker/Samples/FirstSample/AddContact]

Using the "CREATE CONTRACT" sentence we provide the name of the contract and the message type that this service can send-receive. Contracts are created and persist in each database participating in the conversation. In the "SENT BY" clause we define which conversation endpoint sends the message. The possible arguments are: INITIATOR, TARGET and ANY.

When we define a dialog we are ready to send and receive messages in it. We send a message using the SEND sentence where we set the message type, the message body and the conversation handle returned in the "BEGIN DIALOG" sentence. In the following code we create an XML message and later create a dialog specifying the ContactService as a target. Finally, we send the message to the service.

In our sample we specify that the INITIATOR of the conversation is sending a message of the message type ServiceBroker/Samples/FirstSample/AddContact that we declared above. However, if we want to provide the service with a response message as part of the conversation the code would have to appear like this.

CREATE CONTRACT
[ServiceBroker/Samples/FirstSample/AddContactContract] [ServiceBroker/Samples/FirstSample/AddContact]
SENT BY INITIATOR,
[ServiceBroker/Samples/FirstSample/AddContactResponse]

In this case ServiceBroker/Samples/FirstSample/AddContactResponse represents another message type that we had previously declared.

Now we have both messages and the way to interchange them. The next step would be to define the queues to store the messages that the conversation endpoints might send. The following code creates the queue of our example.

CREATE QUEUE ContactQueue
WITH STATUS = ON,
ACTIVATION
(
PROCEDURE_NAME = AddContact,
MAX_QUEUE_READERS = 5,
EXECUTE AS SELF
)

When we create a queue we need to set a valid SQL Server identifier as its name. Then, using the STATUS clause, we have to specify if the queue will be created in a disabled state or not. Afterwards, we set the Service Program that will be activated when a message is received in the queue. Previously, we had to define a stored procedure to act as a Service Program and to process the message.

In our example, we define two stored procedures: AddContact and ProcessMessage. The AddContact uses the ProcessMessage and acts as a Service Program for the ContactQueue. The code of these stored procedures is provided with the sample code of this paper.

Another issue to consider would be the number of instances of the Service Program to be activated when processing the messages. When a message arrives to the queue, the Service Program will be activated, and when another message arrives, another instance of the Service Program will be activated. The number of instances that can be active at a time is limited by MAX_QUEUE_READERS. The "EXECUTE AS" clause determines the SQL Server login for the Service Program to run.

Next, a definition of the Service follows. It represents an endpoint in a Service Broker application and its specific role is defined in the contract. In our sample the service definition may look like this:

CREATE SERVICE [ServiceBroker/Samples/FirstSample/Services/ContactService]
ON QUEUE [ContactQueue]([ServiceBroker/Samples/FirstSample/AddContactContract])

As part of the service definition we specified the Service Name, its contract and its associated queue. We may also specify other attributes like the service owner and whether the messages should be kept in the queue until the conversation is over.

To use the service, all we need to do is to begin a conversation with it. We do this using dialogs. As mentioned above, dialogs represent message exchange conversation patterns that we use for inter-service communication. We create a Dialog using the "BEGIN DIALOG" sentence. As part of the Dialog definition we obtain a dialog identifier that identifies a dialog in a unique way. We indicate the services that act as INITIATOR and as TARGET in the dialog. Besides, we specify the contract exposed by the target services and we define the message sequence in the dialog. We can also set other attributes like the dialog lifetime, the encryption of messages -whether they are encrypted or not- the related dialogs and the related service instances among others. The following code shows the creation of a simple dialog.

DECLARE @dh uniqueidentifier
BEGIN DIALOG @dh
FROM SERVICE [ServiceBroker/Samples/FirstSample/Services/FirstService]
TO SERVICE 'ServiceBroker/Samples/FirstSample/Services/SecondService'
ON CONTRACT [ServiceBroker/Samples/FirstSample/SampleContract]

DECLARE @xdoc xml('ServiceBroker/Samples/FirstSample/Contact')
SET @xdoc = '<ci:ContactInfo xmlns:ci="ServiceBroker/Samples/FirstSample/Contact">
                 <ci:Contact name="">
                     <ci:email>[email protected]</ci:email>
                     <ci:phone>4654654</ci:phone>
                 </ci:Contact>
             </ci:ContactInfo>'
--convert the message to a binary format
DECLARE @binmsg varbinary(max)
SET @binmsg = cast(nchar(0XFEFF) + cast(@xdoc as nvarchar(max)) as varbinary
(max))
--init the conversation
DECLARE @dh uniqueidentifier
BEGIN DIALOG @dh
FROM SERVICE [ServiceBroker/Samples/FirstSample/Services/ContactService]
TO SERVICE   'ServiceBroker/Samples/FirstSample/Services/ContactService'
ON CONTRACT [ServiceBroker/Samples/FirstSample/AddContactContract];

--sends a message
SEND ON CONVERSATION @dh
MESSAGE TYPE[ServiceBroker/Samples/FirstSample/AddContact]
(@binmsg)

At the other end of the conversation we must receive the messages. We perform this action using the RECEIVE or SELECT sentence. Both sentences retrieve messages from a queue. The main difference being that on the one hand when we use RECEIVE a message is removed from the queue and on the other hand, with SELECT the message remains in the queue. As part of both sentences we specify the WHERE clause in which we can set some filters to the receiving action. We also accomplish the receiving action with a WAITFOR clause to set the time that the receiving operation needs when waiting for a message to arrive to the queue. It will need this time if the queue is empty or if no message matches the filter in the WHERE clause.

The following code shows the receive part of the Service Program associated with the ContactQueue.

DECLARE @dh uniqueidentifier
     DECLARE @msg varbinary(max)
    -- retrieve the message from the queue
      WAITFOR
(
        RECEIVE TOP(1) @dh = conversation_handle, @msg = message_body
        FROM ContactQueue

),
TIMEOUT 15000

In this case the Service Program waits 15 seconds for a message and retrieves both the message body and the conversation handle.

By mixing all these features and others that are part of the SQL Server YUKON we may add messaging capabilities to our database applications.

Conclusions

The SQL Server Service Broker technology represents a powerful bridge into the database and messaging-based applications. Using this technology we can develop robust services that perform the messaging aspects of some SQL Server applications. Before the Service Broker we could only incorporate this type of capabilities when using external technologies like MSMQ but now, it is possible to develop messaging applications using native SQL Server technology. Personally, I think that the Service Broker may bring about other potentialities to increase the strength of the applications. However, the fact is that the Service Broker offers a set of benefits never dreamed of before by database application developers.

Up Next

Ebook Download

View all

Functions in SQL Server: Practical Guide

Read by 5.2k people

Download Now!

Learn

View all