I have been very quiet on this blog of late and one reason for that is I have been very busy on a client project that I would like to talk about a little here.

The client that I have been working for has a website that runs on a distributed architecture utilising a messaging infrastructure for communication between different endpoints. My brief was to build a system that could consume these messages and produce analytical information in near-real-time. More specifically I basically had to deliver a data warehouse however it was the real-time aspect of the project that really intrigued me.

This real-time requirement meant that using an Extract transformation, Load (ETL) tool was out of the question and so I had no choice but to write T-SQL code (i.e. stored-procedures) to process the incoming messages and load the data into the data warehouse. This concerned me though – I had no way to control the rate at which data would arrive into the system yet we were going to have end-users querying the system at the same time that those messages were arriving; the potential for contention in such a scenario was pretty high and and was something I wanted to minimise as much as possible. Moreover I did not want the processing of data inside the data warehouse to have any impact on the customer-facing website. As you have probably guessed from the title of this blog post this is where Service Broker stepped in!

For those that have not heard of it Service Broker is a queuing technology that has been built into SQL Server since SQL Server 2005. It provides a number of features however the one that was of interest to me was the fact that it facilitates asynchronous data processing which, in layman’s terms, means the ability to process some data without requiring the system that supplied the data having to wait for the response. That was a crucial feature because on this project the customer-facing website (in effect an OLTP system) would be calling one of our stored procedures with each message – we did not want to cause the OLTP system to wait on us every time we processed one of those messages. This asynchronous nature also helps to alleviate the contention problem because the asynchronous processing activity is handled just like any other task in the database engine and hence can wait on another task (such as an end-user query).

Service Broker it was then! The stored procedure called by the OLTP system would simply put the message onto a queue and we would use a feature called activation to pick each message off the queue in turn and process it into the warehouse. At the time of writing the system is not yet up to full capacity but so far everything seems to be working OK (touch wood) and crucially our users are seeing data in near-real-time. By near-real-time I am talking about latencies of a few minutes at most and to someone like me who is used to building systems that have overnight latencies that is a huge step forward!

So then, am I advocating that you all go out and dump your ETL tools? Of course not, no! What this project has taught me though is that in certain scenarios there may be better ways to implement a data warehouse system then the traditional “load data in overnight” approach that we are all used to. Moreover I have really enjoyed getting to grips with a new technology and even if you don’t want to use Service Broker you might want to consider asynchronous messaging architectures for your BI/data warehousing solutions in the future.

This has been a very high level overview of my use of Service Broker and I have deliberately left out much of the minutiae of what has been a very challenging implementation. Nonetheless I hope I have caused you to reflect upon your own approaches to BI and question whether other approaches may be more tenable. All comments and questions gratefully received!

Lastly, if you have never used Service Broker before and want to kick the tyres I have provided below a very simple “Service Broker Hello World” script that will create all of the objects required to facilitate Service Broker communications and then send the message “Hello World” from one place to anther! This doesn’t represent a “proper” implementation per se because it doesn’t close down down conversation objects (which you should always do in a real-world scenario) but its enough to demonstrate the capabilities!

@Jamiet

-----------------------------------------------------------------------------------------------

/*This is a basic Service Broker Hello World app. Have fun!
-Jamie
*/

USE MASTER
GO
CREATE DATABASE SBTest
GO
--Turn Service Broker on!
ALTER DATABASE SBTest SET ENABLE_BROKER
GO
USE SBTest
GO
-- 1) we need to create a message type. Note that our message type is
-- very simple and allowed any type of content
CREATE MESSAGE TYPE HelloMessage
VALIDATION = NONE
GO

-- 2) Once the message type has been created, we need to create a contract
-- that specifies who can send what types of messages
CREATE CONTRACT HelloContract
(HelloMessage SENT BY INITIATOR)
GO
--We can query the metadata of the objects we just created
SELECT * FROM   sys.service_message_types WHERE name = 'HelloMessage';
SELECT * FROM   sys.service_contracts WHERE name = 'HelloContract';
SELECT * FROM   sys.service_contract_message_usages
WHERE  service_contract_id IN (SELECT service_contract_id FROM sys.service_contracts WHERE name = 'HelloContract')
AND        message_type_id IN (SELECT message_type_id FROM sys.service_message_types WHERE name = 'HelloMessage');

-- 3) The communication is between two endpoints. Thus, we need two queues to
-- hold messages
CREATE QUEUE SenderQueue
CREATE QUEUE ReceiverQueue
GO
--more querying metatda
SELECT * FROM sys.service_queues WHERE name IN ('SenderQueue','ReceiverQueue');
--we can also select from the queues as if they were tables
SELECT * FROM SenderQueue  
SELECT * FROM ReceiverQueue  

-- 4) Create the required services and bind them to be above created queues
CREATE SERVICE Sender
  ON QUEUE SenderQueue
CREATE SERVICE Receiver
  ON QUEUE ReceiverQueue (HelloContract)
GO
--more querying metadata
SELECT * FROM sys.services WHERE name IN ('Receiver','Sender');

-- 5) At this point, we can begin the conversation between the two services by
-- sending messages
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message NVARCHAR(100)

BEGIN
  BEGIN TRANSACTION;
  BEGIN DIALOG @conversationHandle
        FROM SERVICE Sender
        TO SERVICE 'Receiver'
        ON CONTRACT HelloContract WITH ENCRYPTION=OFF
  -- Send a message on the conversation
  SET @message = N'Hello, World';
  SEND  ON CONVERSATION @conversationHandle
        MESSAGE TYPE HelloMessage (@message)
  COMMIT TRANSACTION
END
GO
--check contents of queues
SELECT * FROM SenderQueue  
SELECT * FROM ReceiverQueue  
GO
-- Receive a message from the queue
RECEIVE CONVERT(NVARCHAR(MAX), message_body) AS MESSAGE
FROM ReceiverQueue
GO
--If no messages were received and/or you can't see anything on the queues you may wish to check the following for clues:
SELECT * FROM sys.transmission_queue

-- Cleanup
DROP SERVICE Sender
DROP SERVICE Receiver
DROP QUEUE SenderQueue
DROP QUEUE ReceiverQueue
DROP CONTRACT HelloContract
DROP MESSAGE TYPE HelloMessage
GO

USE MASTER
GO
DROP DATABASE SBTest
GO