SQL Service Broker on Sql 2005 or 2008

I have been working on Sql Service broker since last few days. I have heard lot about it but I have never tried it before. When I started working on it, I felt it is very simple and easy to use.

I could use it in my application which enabled easy implementation of asynchronous programming. Initially I have tried using it on Sql 2005 and then later used it on Sql 2008. Sql 2008 has not much enhancements on this feauture but has fixes which I had faced on SQl 2005. I had very tough time getting service broker to work after restoring the database but this was very straight forward on Sql 2008.

We need to enable Service broker on the database where we are planning to use it
ALTER DATABASE [Databasename] SET ENABLE_BROKER
This may require Server restart sometimes because this statement takes lot of time if the DB is in use and sometimes it may fail. After enabling broker service, you need to create master key encryption

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '{Password}';

The session key is used to perform the conversation and to secure the session key, the encryption is used.

Once the encryption key is set, lets start setting up the service broker

Create message type

CREATE MESSAGE TYPE [ItemMessageType] AUTHORIZATION [dbo] VALIDATION = WELL_FORMED_XML

Create Contract
CREATE CONTRACT [ItemContract] AUTHORIZATION [dbo] ([ItemMessageType] SENT BY INITIATOR)

Create Queue
CREATE QUEUE [dbo].[ReceiveSaveQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[OnReceiveSaveMessage] , MAX_QUEUE_READERS = 10 , EXECUTE AS N'dbo' ) ON [PRIMARY]

Note: The stored procedure OnReceiveSaveMessage will be activated when there is a message received on ReceiveSaveQueue. I would suggest to add the activation once you test the stored procedure. It is very difficult to debug the stored procedure if there is any error. And if the stored procedure fails more than the count specified on MAX_QUEUE_READERS, the Queue will get disabled.

Reading the records form Queue
select * from ReceiveSaveQueue

Creating the service
CREATE SERVICE [ReceiveSaveService] AUTHORIZATION [dbo] ON QUEUE [dbo].[ReceiveItemSaveQueue] ([ItemContract])