You may worked with Service Broker in SQL Server. I am just learned Service Broker and implemented practically with an examples which i evaluated. Before moving on it i would like to introduce service broker, not in the deep but having some overview. Service broker appliees to SQL Server 2005 and newer version. You can read more about SQL Server Service Broker here, http://msdn.microsoft.com/en-us/library/ms345108(v=sql.90).aspx

Overview:
Per msdn, with Service Broker, internal or external processes can send and receive guaranteed, asynchronous messaging by using extensions to Transact-SQL. It provides queuing and reliable messaging for SQL Server. It can be used for single database communication, two databases of sigle sql server instance and two databases of different sql server instances for exchanging messages between them.

Here we have small demonstration with an examples for the single databases conversation only. Here we will created database objects and start conversion betwen them within single database only.

Service broker has two end called TARGET and INITIATOR, so target and initiaor will be on same database for our example.

What we do?
1.Create a request & reply MESSAGE TYPE for target and initiator.
2.Create a CONTRACT that specify the messages between target and initiator.
3.Create an QUEUE for target and initiator.
4.Create a SERVICE for for the created Queues above.

So as per above list we will enable database for Service Broker which will be used for this demo. After that we will create message types for request and reply. Contract will be created after that. Finally we will create queue for target and initiator and services will be created for those queues.

-- Using master database
USE MASTER
GO

IF EXISTS(SELECT name FROM sys.databases where name = 'SourceDB')
DROP DATABASE SourceDB
GO

CREATE DATABASE SourceDB
GO

-- Enabling SourceDB database for service broker
ALTER DATABASE SourceDB
      SET ENABLE_BROKER;
GO

-- Using service broker enabled database.
USE SourceDB;
GO

-- Creating requst and reply message types 
CREATE MESSAGE TYPE
       [//DB/SourceDB/RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//DB/SourceDB/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO


-- Creating contract that specify the conversion
CREATE CONTRACT [//DB/SourceDB/DBContract]
      ([//DB/SourceDB/RequestMessage]
       SENT BY INITIATOR,
       [//DB/SourceDB/ReplyMessage]
       SENT BY TARGET
      );
GO

-- Creating queue on target 
CREATE QUEUE DestinationQueue;

-- Creating query on initiator
CREATE QUEUE SourceQueue;
GO

-- Creating a target service for above created queue 
CREATE SERVICE
       [//DB/SourceDB/TargetService]
       ON QUEUE DestinationQueue
       ([//DB/SourceDB/DBContract]);

-- Creating a initiator service for above created queue 
CREATE SERVICE
       [//DB/SourceDB/InitiatorService]
       ON QUEUE SourceQueue;
GO

These are the objects which will be used for service broker. We will also create some another objects for which service broker used. We will create one table and capture data change track in another table which will be created as below script.

-- Creating table
CREATE TABLE TransactionTable
(
TranId int,
TranType varchar(20),
TranDate datetime,
)
GO

-- Creating a history table of above main table
CREATE TABLE TransactionHistory
(
HistoryId INT IDENTITY(1,1),
XMLHistory XML
)
GO

Now we need to capture the data change tracking history in another table with service broker, so we will create trigger on main table and service broker code will be used in trigger. It is just capture the old and new data from magic tables and send from initiator to target and use dialog handler to send request message on conversion.

-- Creating a trigger on main table 
-- for insert, update and delete operation
CREATE TRIGGER TR_TrnHistory
ON TransactionTable
FOR INSERT, UPDATE, DELETE
AS
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg XML;
DECLARE @TranId UNIQUEIDENTIFIER;

-- Create a unique id for each conversion
SET @TranId = NEWID()

-- Begin dialog
BEGIN DIALOG @InitDlgHandle
     FROM SERVICE
      [//DB/SourceDB/InitiatorService]
     TO SERVICE
      N'//DB/SourceDB/TargetService'
     ON CONTRACT
      [//DB/SourceDB/DBContract]
     WITH
         ENCRYPTION = OFF;

-- Capture data change from magic table in xml format 
-- and assign to variable
SET @RequestMsg =
		
	   (SELECT sender.* FROM
       (
       SELECT @TranId as Id,'Inserted' as ope,* FROM inserted 
       UNION ALL
       SELECT @TranId as Id,'Deleted' as ope,* FROM deleted 
       ) sender FOR XML AUTO );


-- Start conversion and send request message to target
IF @RequestMsg is not null
BEGIN

;SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE 
     [//DB/SourceDB/RequestMessage]
     (@RequestMsg);
END

To receive the messages from initiator and process them we need receive dialog handler which will receive the messages from queue and process them. After it will insert into history table. Let us create one stored procedure to read the queue to receive the incoming messages.

-- Creating stored procedure
CREATE PROCEDURE SB_ReceiveData 
AS
BEGIN
SET NOCOUNT ON 

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg XML;
DECLARE @RecvReqMsgName sysname;

-- Creatig a loop to get the message one by one from queue
WHILE (1=1)
BEGIN

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM DestinationQueue
), TIMEOUT 1000;

 IF (@@ROWCOUNT = 0)
    BEGIN
      ROLLBACK TRANSACTION;
      BREAK;
    END

-- Inserting records in history table
IF @RecvReqMsg IS NOT NULL
BEGIN
INSERT INTO TransactionHistory(XMLHistory)
SELECT @RecvReqMsg
END

COMMIT TRANSACTION;
END
END

GO

Congratulations we have done all above stuff and now out of developing mode. But we need to enter in testing mode now to review how service broker works. So we will do some transaction in table to test it actually. 

Testing:

-- Inserting a record
INSERT INTO TransactionTable
SELECT 2,'Insert',Getdate()

-- Updating a records
UPDATE TransactionTable
SET TranType = 'Update',
TranDate = GETDATE()
WHERE TranId = 2

-- Deleting a record
DELETE FROM TransactionTable
WHERE TranId = 2

GO

Now Trigger will be fired when we perform above DML operation and data send to queue as a request message. To read the message we need to call stored procedure. So let us call it. Before executing stored procedure we will check the rows from both tables and same will review after executing stored procedure.

SELECT 
* 
FROM TransactionTable

SELECT 
* 
FROM TransactionHistory
GO

EXEC SB_ReceiveData
GO

SELECT 
* 
FROM TransactionTable

SELECT 
* 
FROM TransactionHistory
GO