Microsoft first introduced Service Broker as part of the relational engine of SQL Server 2005. Service Broker is an asynchronous messaging framework with which you can implement scalable, distributed, high available, reliable, and secure database applications based on SQL Server. Here are some examples:
As we start looking at the details of Service Broker, I want to give you a quick overview of the Service Broker objects with which you will be interacting, when implementing a basic Service Broker application.
Much of the action in Service Broker revolves around sending messages between services. A Service Broker service is an endpoint to which messages can be sent, and from which they can be retrieved. Each endpoint can implement some functionality from your database application that is executed asynchronously in the background. Messages are always exchanged between at least two services. A Service Broker service has the following characteristics:
Conversations are always started between two Service Broker services. The service which starts the conversation is called the initiator service, and the other service is called the target service. As you can see from this description, a Service Broker service is a native database object that has also links to other Service Broker objects.
A Service Broker service consists of at least the following four objects that are also created as native database objects:
Let’s have a more detailed look at these additional objects.
The message type describes the content of the messages that are exchanged between Service Broker services. Each message type is associated with a unique name. When you are sending messages across databases, you have to define the identical message types in each participating database. Each message type can be associated with a validation method that Service Broker applies to messages of that type. Service Broker currently supports the following four validation options:
Service Broker performs the validation as soon as a message is received at a service. If an error occurs, an error message is returned back to the sender.
After you have defined the necessary message types, you must group them together into contracts. A contract defines in which direction message types are exchanged between Service Broker services. A contract is therefore an agreement that states which message types a service can send and which message types a service can accept. Service Broker supports the following three sending directions:
Contracts also have to be defined in all participating databases, when you are sending messages across different databases.
After you have defined your message types and your contracts, you have to create the queues for storing incoming messages in Service Broker. Queues must be defined for both the initiator and for the target service, even when you are implementing one-way messaging with Service Broker. The reason for this is that Service Broker also sends some messages internally (e.g. when an error occurred on the target service); therefore you always also need a queue for the initiator service. Each received message is represented as row in a queue. A queue is internally implemented as a hidden table which uses a special locking mechanism. Each record in that queue is associated with different columns, which contains things like the message body itself, the associated message type, and other information such as the receipt date.
The last object, a service program, is a stored procedure that gets executed when new messages arrive. With Service Broker these stored procedures can be also started automatically as soon as new messages arrive on a queue. This feature is referred to as activation. Service Broker distinguishes between internal and external activation. Internal activation means that a stored procedure deployed within the database gets executed. External activation means that an external program (maybe a .NET application) gets started as soon as a new message arrives on a queue. By now you have a basic understanding about the various objects that are involved in a basic Service Broker application. The following figure shows these objects and how they relate to each other.
Figure 1: The various Service Broker objects and their relationship
After laying out the foundation of Service Broker, you will be now ready to implement your first Service Broker application. Your first Service Broker application is the famous Hello World application. The application will simply send a request message that contains your name in the message body from the initiator service to the target service. The target service receives this message, processes it, and finally sends back a response message with the famous “Hello World” and your provided name. Easy isn’t it? Let’s get started!
As discussed, almost everything in Service Broker is scoped to a database. Therefore you need to create a new database, as in Listing 1, which will host all the Service Broker objects you will be creating in the subsequent steps.
USE master GO IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'HelloWorldServiceBroker' ) BEGIN PRINT 'Dropping database ''HelloWorldServiceBroker''' ; DROP DATABASE HelloWorldServiceBroker ; END GO CREATE DATABASE HelloWorldServiceBroker GO USE HelloWorldServiceBroker GO
Listing 1: Creating a new database for the first Service Broker application
After creating your database for Service Broker, the next step is to define the necessary message types and the contract on which your Service Broker application is based. As I have said earlier, your first Service Broker application will consist of a request message and a response message. The direction of those two messages will be defined through one contract that your Service Broker application uses. Service Broker offers you the CREATE MESSAGE TYPE and the CREATE CONTRACT T-SQL statement for creating message types and contracts. Listing 2 shows the creation of these Message Type and Contract objects.
CREATE MESSAGE TYPE [http://ssb.csharp.at/RequestMessage] VALIDATION = WELL_FORMED_XML GO CREATE MESSAGE TYPE [http://ssb.csharp.at/ResponseMessage] VALIDATION = WELL_FORMED_XML GO CREATE CONTRACT [http://ssb.csharp.at/HelloWorldContract] ( [http://ssb.csharp.at/RequestMessage] SENT BY INITIATOR, [http://ssb.csharp.at/ResponseMessage] SENT BY TARGET ) GO
Listing 2: Creating the message types and the contract
As you can see in Listing 2, each message type and each contract is given a unique name by adding a unique string to them. As a best practice you can use the URL of your website or company to make it . easy to distinguish your message types and contracts from others and avoid name collision when moving your Service Broker application to a distributed environment. However, you can use any valid SQL Server object name to name your message types and contracts.
After creating your message types and contracts, the final step is to create your queues and your actual Service Broker services that are interacting with each other, as shown in Listing 3. For these tasks Service Broker offers you the CREATE QUEUE and the CREATE SERVICE T-SQL statements.
CREATE QUEUE InitiatorQueue WITH STATUS = ON GO CREATE QUEUE TargetQueue WITH STATUS = ON GO CREATE SERVICE InitiatorService ON QUEUE InitiatorQueue ( [http://ssb.csharp.at/HelloWorldContract] ) GO CREATE SERVICE TargetService ON QUEUE TargetQueue ( [http://ssb.csharp.at/HelloWorldContract] ) GO
Listing 3: Creating the services and their associated queues
As you can see, when creating a service, you have to reference previously created queue and contracts, that that Service Broker service implements. In your case, the one and only defined contract – the http://ssb.csharp.at/HelloWorldContract contract is implemented by the InitiatorService and the TargetService service.
After creating all of the necessary Service Broker objects you are finally able to start a conversation between both services and exchange messages. Service Broker offers you the BEGIN DIALOG CONVERSATION T-SQL statement for starting a new conversation between two Service Broker services. The conversation itself is identified through a UNIQUEIDENTIFIER that is returned as a result back to you. You need to store this identifier somewhere (such as in a local variable), because you will need it afterwards when you want to send messages across the open conversation with the SEND ON CONVERSATION T-SQL statement. The following listing shows the necessary steps to open a conversation and send a message through it.
BEGIN TRANSACTION ; DECLARE @ch UNIQUEIDENTIFIER DECLARE @msg NVARCHAR(MAX) ; BEGIN DIALOG CONVERSATION @ch FROM SERVICE [InitiatorService] TO SERVICE 'TargetService' ON CONTRACT [http://ssb.csharp.at/HelloWorldContract] WITH ENCRYPTION = OFF ; SET @msg = 'Klaus Aschenbrenner ' ; SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/RequestMessage] (@msg) ; COMMIT TRANSACTION GO
Listing 4: Opening a new Service Broker conversation and sending a message
As you can see in Listing 4, you need to specify the initiator service, the target service, and also the contract when you are establishing a new conversation between two Service Broker services. Optionally you can also define if encryption should be used or not. For simplification we are currently not encrypting anything, because we are just sending messages within a database. The most important concept to understand here is the fact that the TO SERVICE is specified as a string literal. In our simple Service Broker application this seems a little bit confusing, but just think a second and assume that the target service is deployed somewhere on a different physical SQL Server instance. In that case you might not be able to reference this remote deployed Service Broker service. Therefore you have to specify it here as a string literal.
Maybe you are wondering now how Service Broker can know where the actual target service is physically deployed? For that missing piece of information you have to create a route within your database. A route just describes where a specific Service Broker service is physically deployed. In our case the target service is deployed within the same database (within the same SQL Server instance), and therefore you don’t have to deploy any additional routing information.
When you have established the conversation between your two Service Broker services, and when you have sent your first message through it, you can finally check the TargetQueue queue of the target service, if the sent message was received successfully. For that case you just have to execute a simple SELECT against that queue:
SELECT message_type_name , CAST(message_body AS XML)FROM TargetQueueGO
This SELECT statement returns one record back to you, as shown in Figure 2, which is the actual message you have successfully sent from the initiator service to the target service.
Figure 2: The received message on the target service
If you want to physically receive and process the sent message, you have to use the RECEIVE statement against the queue object. With the RECEIVE statement you are also able to extract information from the received message into local variables, from which you do the actual message processing. In this example you are using the XML functionality provided by SQL Server to extract the name from the received message. With that name in your hand it’s very easy to construct a new response message and send it back to the initiator service through the SEND ON CONVERSATION T-SQL statement. Listing 5 shows the code you have to execute for the target service. This code can be also put into a stored procedure, which will then act as a service program for your Service Broker service.
DECLARE @ch UNIQUEIDENTIFIER DECLARE @messagetypename NVARCHAR(256) DECLARE @messagebody XML DECLARE @responsemessage XML BEGIN TRANSACTION ; RECEIVE TOP(1) @ch = conversation_handle, @messagetypename = message_type_name, @messagebody = CAST(message_body AS XML) FROM TargetQueue PRINT 'Conversation handle: ' + CAST(@ch AS NVARCHAR(MAX)) PRINT 'Message type: ' + @messagetypename PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX)) IF ( @messagetypename = 'http://ssb.csharp.at/RequestMessage' ) BEGIN -- Construct the response message SET @responsemessage = 'Hello World, ' + @messagebody.value('/HelloWorldRequest[1]', 'NVARCHAR(MAX)') + ' ' ; -- Send the response message back to the initiating service SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/ResponseMessage] (@responsemessage) ; -- End the conversation on the target's side END CONVERSATION @ch ; END COMMIT TRANSACTION GO
Listing 5: Receiving a sent Service Broker message on the target service
The great thing here about Service Broker is the fact that you can do message and data processing within the context of a local database transaction. When you think of other message technologies on the Microsoft platform (like MSMQ - Microsoft Message Queuing), you have always to create distributed transactions, because you are working with at least two different resource managers. In addition, distributed transactions have a lot of overhead, which leads directly to poorly performing database applications. Here you can see very well why an embedded messaging framework within the relational engine of SQL Server makes a lot of sense.
As you can see in Listing 5, you are receiving the message from the queue, constructing the response message, and finally sending the response message back to the initiator service. But what is the END CONVERSATION T-SQL statement? Every opened conversation in Service Broker must be also closed on both sides – at the target service and also at the initiator service. As soon as you execute an END CONVERSATION on one side of the conversation, Service Broker sends an end dialog message to the other side. This means that in this example, you have sent two messages back to the initiator service, the response message, and also an end dialog message. You can verify it very easily by querying the queue of the initiator service:
SELECT message_type_name , CAST(message_body AS XML)FROM InitiatorQueueGO
The results are shown in Figure 3.
Figure 3: The received messages at the initiator service
As you can see in Figure 3, the queue contains two messages – the response message and also an end dialog message that is further described through the http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog message type. This message type is internally used by Service Broker. Listing 6 contains the code that is necessary to process the received messages. Because you just receive one message at time you have to execute this batch two times, so that each message gets processed individually.
DECLARE @ch UNIQUEIDENTIFIER DECLARE @messagetypename NVARCHAR(256) DECLARE @messagebody XML BEGIN TRANSACTION ; RECEIVE TOP (1) @ch = conversation_handle, @messagetypename = message_type_name, @messagebody = CAST(message_body AS XML) FROM InitiatorQueue IF ( @messagetypename = 'http://ssb.csharp.at/ResponseMessage' ) BEGIN PRINT 'Conversation handle: ' + CAST(@ch AS NVARCHAR(MAX)) PRINT 'Message type: ' + @messagetypename PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX)) END IF ( @messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' ) BEGIN -- End the conversation on the initiator's side END CONVERSATION @ch ; END COMMIT TRANSACTION GO
Listing 6: Receiving a sent Service Broker message on the initiator service
After executing this code twice, you have processed the response message, and finally also closed the conversation on the initiator service side. Therefore the whole conversation was cleaned up and removed from memory.
Congratulations, you have now successfully implemented your first Service Broker application! In part 2 of this series we will have a more detailed look into Service Broker, where I will clarify why, and in which circumstances, the usage of Service Broker makes sense. Stay tuned!
