SQL Service Broker in SQL 2005

Service Broker in SQL 2005 – (2 yrs later)

What is service broker – It is a platform for async queued database applications... that's it :)

Steps involved to get started for SQL Service broker -
1 Create Message
2 Create Contract
3 Create Service (Sender/Receiver)
4 Create Queues

5 Assign Service to the Queue (to read queues)
6 Start a Dialog
7 Start a conversation (unique ID)
8 Send Messages

You can create Queues on same database on the same server or different servers.
You will need to create a Route specifying the Address to which the messages should be routed.

Important question though: Why messaging in database? May be the below points justify:-
1. Reliable service oriented arch
2. Parallel stored procs
3. Asynchronous triggers
4. Batch processing – Dialogs for ordering, Activation for parallelism, Distributed batch (multiple queues sitting remotely)

References –
Very good blog URL to understand SQL Service Broker - http://www.sqlservercentral.com/columnists/sSampath/anintroductiontotheservicebroker.asp
MSDN search – http://Msdn.microsoft.com/library/default.aspx – "Service broker" (tons of good information)

You can download SQL 2005 Express product to get started – http://msdn.microsoft.com/vstudio/express/sql/download/

lets get to the code to understand what we are talking about. You can follow these steps in SQL 2005--

--We will use adventure works as the sample database
USE AdventureWorks
GO

-- First, 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

-- 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

-- The communication is between two endpoints. Thus, we need two queues to
-- hold messages
CREATE QUEUE SenderQueue

CREATE QUEUE ReceiverQueue
GO

-- 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

-- 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

-- Send a message on the conversation
SET @message = N'Hello, World';
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE HelloMessage (@message)
COMMIT TRANSACTION
END
GO

-- Receive a message from the queue
RECEIVE CONVERT(NVARCHAR(max), message_body) AS message
FROM ReceiverQueue
-- Cleanup
DROP SERVICE Sender
DROP SERVICE Receiver
DROP QUEUE SenderQueue
DROP QUEUE ReceiverQueue
DROP CONTRACT HelloContract
DROP MESSAGE TYPE HelloMessage
GO

Happy learning :) - D