Introduction
When I first heard of the mysterious service broker I was intrigued to find out what it was and what it could do. So I did some reading up and discovered it can play a part in building a range of enterprise solutions. One feature that generated a great deal of interest for me was the asynchronous stored procedures. When writing asynchronous architecture in the past I’ve had to use application languages to orchestrate asynchronous database operations. But now apparently I can write them at the database level.
This isn’t precisely true. Service broker enables you to call stored procedures asynchronously but not write asynchronous stored procedures. This document will hopefully get you familiarised with service broker’s components and understand some of its capabilities.
My case is what I would call a "lab case" and is not intended to represent any sort of production solution.
After reading this document you will have basic understanding of what service broker is, what basic components make up a service broker solution and be able to write your own asynchronous database processes.
Ingredients List
Here is a list of all the components discussed in this document. All of which are available in the accompanying code (except the SQL server platform of course). I used SQL Server 2005 or above (I’m developing against SQL 2008 R2 standard, so you might need to revise the code other platforms)
- One source database
- Service broker enabled on your source database.
- Two message types
- One message contract
- Two queues
- Two services
- One log table
- One errors table
Switch on Service Broker
SQL server is locked down when you get it out of the box. So remove that bubble wrap and enable service broker on the database you’ll use for this lab.
ALTER DATABASE TestDB SET enable_broker
Message Types
You will need two message types. You could consider your message types to be two people conversing at a counter like a landlord & patron or a hardware shop keeper & a customer. They both have their own dialect in the conversation so we need to define a message type for each of them. For this example I’m simply specifying that I want the messages to be xml types. You can explore the additional options at http://msdn.microsoft.com/en-us/library/ms187744.aspx
For production systems you should consider using XML schemas to validate your XML form.
-- Message used to speak shopkeeper (service) CREATE MESSAGE TYPE [//HardwareStore/RequestMessage] VALIDATION = WELL_FORMED_XML -- Message used to speak to customer (service) CREATE MESSAGE TYPE [//HardwareStore/ReplyMessage] VALIDATION = WELL_FORMED_XML
Contract
A contract can be compared to the definition of a conversation between two people with a specific agenda. If we come back to a previous example of a hardware shop keeper & customer we will have specific requests like "do you have a hammer", "do you have some nails" and the store owner would reply "yes I have 3 hammers", "yes I have a fine selection of nails". In this example we can see that the customer initiates the conversation with a request and the shop keeper, the target of their request, replies.
CREATE CONTRACT [//HardwareStore/ShopKeeper_Customer_Protocal]
(
[//HardwareStore/RequestMessage] SENT BY INITIATOR,
[//HardwareStore/ReplyMessage] SENT BY TARGET
);
Queue
A queue does exactly what it says on the tin. If you don’t know what a queue is then I would suggest reading up on stacks and queues before reading on. A queue’s representation in SQL Server can be closely compared to a table. For example "select * from queue" will show you the contents of a queue in tabular form. Now imagine our customer comes into the shop and rattles off 100 items to be requested from the shop keeper. There is no way the shop keeper can check his inventory, return replies to customer and keep up with this incredibly demanding customer's requests.
So the clever shop keeper writes a list (queue) as the customer is rattling of 100 items and relays items on the list to his team in the back of the shop, who in turn check the inventory and return availability counts to the shop keeper. So the shop keeper tries to get his own back on the demanding customer and doesn’t wait for him to finish his 100 item request but starts returning the 100 inventory counts to the customer as they come in from back of shop. As the customer is unable to remember all the returning inventory counts, his must write the down (queue).
-- The customer maintains a list of responses from the shopkeeper(service) CREATE QUEUE CustomersResponseToAskList -- The shop keeper has a list of requests from the customer CREATE QUEUE CustomerRequestList
Service
Services define a service broker solution’s end points. They’re the mechanism for sending and receiving messages to and from the queues. They enforce the contract (conversations) that will be conducted between queues and they are the main point of interaction for your calling code. In the shop keeper & customer paradigm we should consider the shop keeper & customer as individual services. The customer has direct access to the list (queue) of items he want to receive information about. The shop keeper has direct access to the list (queue) of requested items he needs to gather information about. Both the shop keeper and the customer have a understanding of how to conduct themselves within the shop environment (contract) and this understanding leads to a polite conversation with the correct protocol (message types).
-- The customer knows about its list and knows how to converse with the shop keeper.
CREATE SERVICE
[//HardwareStore/Customer]
ON QUEUE CustomersResponseToAskList
(
[//HardwareStore/ShopKeeper_Customer_Protocal]
);
-- The shop keeper knows about his list and the way he and the customer will converse.
CREATE SERVICE
[//HardwareStore/ShopKeeper]
ON QUEUE CustomerRequestList
(
[//HardwareStore/ShopKeeper_Customer_Protocal]
);
Activation, the Show Stopper
When we talk about asynchronous queries "Activation" is the show stopper. It lets us do some very interesting things and I would encourage reading up on its capabilities. For the purpose of this exercise we will be using simple internal activation.
So far we know what the characteristics and behaviour of the customer and shop keeper is all about, but we haven’t really understood what his backroom staff are doing. Well we know that they have a predefined task that they can repeat consistently time and time again. The result of the task, the inventory counts, is passed backed to the shop keeper. I would say they sound a lot like stored procedures and as there are many replicating the same task I would say there are many instances of the same stored procedure.
So how does our shop keeper (service) communicate with the back room staff (stored procedure instances)? Simply by taking an item off the list (popping the queue) and instructing each member of staff (stored procedure) to carry out their duties and fetch inventory counts. But it is too much for the shop keeper satisfy all the requests from customers, manage the list and keep all his staff busy. So he employs a back office manager (Activation Stored Procedure), who manages the operation for him, relieving the shop keeper of the additional responsibilities.
-- Employ the staff manager to get those workers working. -- See full details regarding parameters at msdn ALTER QUEUE CustomerRequestList WITH ACTIVATION ( -- Yes we want activation to start straight away. STATUS = ON, -- We want to target a local procedure delegate. PROCEDURE_NAME = StaffManager, -- We want to process current requests, 20 is enough for this example. MAX_QUEUE_READERS = 20, /* I've not covered security in my article as it should have it deserves its own independent article.*/ -- Here I have simply created a SQL login with dbo privileges to execute the delegate sp. EXECUTE AS 'devlogin' );
Two Part Activation Stored Procedure
I have split the activation behaviour into two parts: a pop stored procedure & a processor stored procedure. The pop sp manages retrieval of messages from the shop keeper request list (queue) and the processor sp translates the message into something that can be executed using SQLXML. After execution the pop sp then passes a reply (message) back to the customer (service) which completes the request (conversation). You could consider the pop stored procedure as the staff manager who well manages the workers (processor stored procedure).
CREATE PROCEDURE StaffManager
AS
BEGIN
DECLARE @ReceivedRequestDlgHandle UNIQUEIDENTIFIER;
DECLARE @ReceivedRequestMessage XML;
DECLARE @ReceivedRequestMessageType SYSNAME;
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
WAITFOR -- Pop the request queue and retrieve the request
(
RECEIVE TOP(1)
@ReceivedRequestDlgHandle = conversation_handle,
@ReceivedRequestMessage = message_body,
@ReceivedRequestMessageType = message_type_name
FROM CustomerRequestList
), TIMEOUT 5000;
-- We will wait for 5 seconds for a message to appear on
-- the queue.
-- it more efficient to cycle this sp then to create a
-- brand new instance.
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK
BREAK
END
IF @ReceivedRequestMessageType =
N'//HardwareStore/RequestMessage'
BEGIN
DECLARE @ReturnMessage XML;
DECLARE @ReplyMessage XML;
BEGIN TRY
-- Instruct worker to execute the request.
EXEC Worker @ReceivedRequestMessage,
@ReplyMessageOUTPUT
END TRY
BEGIN CATCH
-- Record errors.
INSERT INTO ExecutionErrors(
ExecutionRequest,
ErrorMessage )
SELECT @ReceivedRequestMessage,
ERROR_MESSAGE()
END CATCH;
-- Send reply with results.
SET @ReplyMessage = (
SELECT @ReceivedRequestMessage,
@ReplyMessage FOR XML PATH('ExecutionComplete'));
SEND ON CONVERSATION @ReceivedRequestDlgHandle
MESSAGE TYPE [//HardwareStore/ReplyMessage]
( @ReplyMessage );
END
ELSE IF @ReceivedRequestMessageType =
N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
OR @ReceivedRequestMessageType =
N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
END CONVERSATION @ReceivedRequestDlgHandle
END
COMMIT
END
END
Plus the worker stored procedure...
-- The worker sp understands the request format, extracts the execution information and executes the request.
CREATE PROCEDURE Worker
@RequestMessage XML,
@ReturnMessage XML OUTPUT
AS
BEGIN
DECLARE @ParamDefinition NVARCHAR(4000)
SET @ParamDefinition = STUFF((
SELECT
',' AS[text()],
collection.param.value('@parametername','sysname') AS[text()],
' = ' AS[text()],
CASE WHEN
collection.param.value('@dbtype','sysname') LIKE '%char%'
OR
collection.param.value('@dbtype','sysname') = 'sysname'
THEN '''' + collection.param.value('@value','nvarchar(4000)')
+ ''''
ELSE
collection.param.value('@value','nvarchar(4000)')
END AS [text()],
SPACE(1)AS[text()],
NULLIF( LOWER(collection.param.value('@direction','sysname')), 'input')AS[text()]
FROM @RequestMessage.nodes(
'/sqlobject/sqlcommand/sqlparametercollection/sqlparameter')
AS collection(param)
FOR XML PATH(''),TYPE).value('.','nvarchar(4000)'),1,1,'')
DECLARE @Query NVARCHAR(4000)
SET @Query =N'EXEC '
+ @RequestMessage.value('(/sqlobject/sqlcommand/@commandText)[1]', 'nvarchar(4000)')
+SPACE(1)
+ @ParamDefinition
-- Declare table to hold result. This is a somewhat crude
-- method of processing a request/result.
-- A more elegant solution is waiting to be discovered.
DECLARE @InventoryCount TABLE (ItemVolume BIGINT)
-- Process the request and save result.
INSERT INTO @InventoryCount
EXEC (@Query)
-- Convert the result recordset to xml and pass it back to the
-- caller (OUTPUT var).
SET @ReturnMessage = (SELECT ItemVolume FROM @InventoryCount FOR XML PATH('WorkerReport'))
END
Worker Capabilities
The worker may be able to carry out many tasks in the hardware store. But for this lab session we will declare just one possible task. We will define the task of counting the inventory. Again we will use a stored procedure to encapsulate the repeatable process. As it would take a lot work to define an actual hardware store warehouse in SQL tables, I will simply use table counts to represent some mock hardware store inventory counts. The stored procedure will simply accept a table name and return its total row count.
-- Create a very simple sp for the purpose of demonstrating service broker.
CREATE PROCEDURE uspInventoryCounts
@tableName SYSNAME
AS
BEGIN
EXEC('select COUNT(*) from '+ @tableName)
END
GO
Making a Service Request
The customer needs to make request to the shop keeper, he has all the knowledge about how to communicate with the shop keeper so it just needs to be persisted in a repeatable process.
CREATE PROCEDURE CustomerRequest
@RequestMessage XML
AS
BEGIN
DECLARE @InitDlgHandle UNIQUEIDENTIFIER
BEGIN TRANSACTION
-- Create a conversation between the customer and shopkeeper
-- using the appropriate protocol.
BEGIN DIALOG @InitDlgHandle
FROM SERVICE[//HardwareStore/Customer]
TO SERVICE '//HardwareStore/ShopKeeper'
ON CONTRACT
[//HardwareStore/ShopKeeper_Customer_Protocal]
WITH ENCRYPTION =OFF;
-- Start the conversation my sending.
SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE[//HardwareStore/RequestMessage]
( @RequestMessage );
COMMIT TRANSACTION
END
Receiving Processed Replies
As we mentioned before the trades person (service) will keep a list (queue) of all the inventory results passed back from the shop keeper (service). The trades person now needs to process that list and decide if they want to just make a note of items that aren’t available, ring another supplier, make more enquires, etc. For the purpose of this exercise we will just make a permanent store (table) of results and end the conversation.
CREATE PROCEDURE CustomerLogging
AS
BEGIN
SET NOCOUNT ON
DECLARE @ReceivedDlgHandle UNIQUEIDENTIFIER;
DECLARE @ReceivedReplyMessage XML;
DECLARE @ReceivedReplyMessageType SYSNAME;
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
WAITFOR -- Pop the queue to get the next message for processing
(
RECEIVE TOP(1)
@ReceivedDlgHandle = conversation_handle,
@ReceivedReplyMessage = message_body,
@ReceivedReplyMessageType = message_type_name
FROM CustomersResponseToAskList
),TIMEOUT5000;
IF (@@ROWCOUNT= 0)
BEGIN
ROLLBACK
BREAK
END
IF @ReceivedReplyMessageType =
N'//HardwareStore/ReplyMessage'
BEGIN
INSERT INTO ExecutionLog( ExecutionRequest,
ExecutionResult,
MessageBody )
SELECT @ReceivedReplyMessage.query('/ExecutionComplete/sqlobject'),
@ReceivedReplyMessage.query('/ExecutionComplete/WorkerReport'),
@ReceivedReplyMessage
END CONVERSATION @ReceivedDlgHandle
END
-- TODO: add some unexpected behaviour code here.
COMMIT
END
END
Press Play
Now that we have everything in place and the scene is set we should get on and start processing some requests. The following script will execute the tasks involved in the life cycle of a request and return results. The request is represented by a xml document, which describes a stored procedure call to "uspInventoryCounts" and some parameters for that SP. The worker stored procedure we declared earlier knows how to shred this XML and execute the request.
For production systems you should consider using XML schemas to validate your request XML form.
-- Define customer request
DECLARE @RequestMessage XML
SELECT @RequestMessage =
CAST('<sqlobject>
<sqlcommand commandText="uspInventoryCounts" commandtype="storeprocedure">
<sqlparametercollection>
<sqlparameter parametername="@tableName" dbtype="sysname" direction="input" value="sys.objects"/>' +
'</sqlparametercollection>
</sqlcommand>
</sqlobject>'AS XML)
-- Make Customer request
EXEC CustomerRequest @RequestMessage
-- If run immediately after the request is made you will be able
-- to see your request on the shopkeeper queue just before it gets popped.
SELECT *
FROM CustomerRequestList
-- Check for problems and see if we have a response.
SELECT *
FROM ExecutionErrors
SELECT *
FROM CustomersResponseToAskList
-- Pop the response from the customer queue and persist the results in the log.
EXEC CustomerLogging
-- View the log
SELECT *
FROM ExecutionLog
You should see the following results after execution:
The first rowset is empty because the request was popped from the customer request queue before we could query the queue and see the request. The second rowset is also empty because no errors were generated. The third rowset shows the response message from the shopkeeper and the fourth rowset shows the customers log of the responses he/she got when asking the shopkeeper about inventory counts.
Multi Requests
If we got this far and one request has processed successfully, then it is time to build some confidence in our system and create many concurrent requests.
To accomplish this I have adapted the previous single customer request script to repeat its request behaviour a set number of times, in this case 10 times and return the same rowsets as before. I have iterated the master.sys.all_views object to retrieve system view names as parameters for "uspInventoryCounts".
-- Define customer request and make many requests about inventory counts
DECLARE @RequestMessage XML
DECLARE @i INT= 0
WHILE @i < 10
BEGIN
SET @i = @i + 1
SELECT @RequestMessage =
CAST('<sqlobject>
<sqlcommand commandText="uspInventoryCounts" commandtype="storeprocedure">
<sqlparametercollection>
<sqlparameter parametername="@tableName" dbtype="sysname" direction="input" value="' +
(SELECT fullname FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY name)
ASn,
SCHEMA_NAME(schema_id) +'.'+ name AS fullname
FROM master.sys.all_views
) x
WHERE n = @i) +
'"/>' +
'</sqlparametercollection>
</sqlcommand>
</sqlobject>' AS XML)
EXEC CustomerRequest @RequestMessage
END
If this code is run immediately after the request is made you will be able to see your requests on the shopkeeper queue just before it gets popped.
SELECT * FROM CustomerRequestList You can also check for problems and see if we have a response with this. SELECT * FROM ExecutionErrors -- Check the shopkeepers responses. If empty, up the number of requests to make the system busy and you will see rows on the queue here SELECT * FROM CustomersResponseToAskList -- Log the responses EXEC CustomerLogging -- View the log SELECT * FROM ExecutionLog
You should see the following results after execution:
You should see 11 logged customer requests/response records if you ran this script immediately after the single request script.
Finally{};
Hopefully my article and example code has given you some exposure to a rich feature available in today’s Microsoft database offering. If this document inspires some ideas or helps with the general understanding of queue’s and SOA then I have achieved what I set out to do. I know I have only brushed over some topics here and I apologise for that, but there was just too much to detail in one article.
Please check out the full code as there are some helpful files and comments not mentioned in the article. I leave you with two tips that will help you with your own service broker development.
- Put all calls in try/catch and log to an errors table for everything you do.
- Check the SQL server logs.
If bad things are happening to your queue’s or services this is where you’ll find out about it.
