Using Service Broker to Communicate With Other Database Servers

Conversation Groups

As some messages are related to others, Conversation groups are used to keep them together. If one message process fails, the service will automatically roll back the related message processes.

Service Broker Architecture

Here are all the concepts and objects of Service Broker together in pictorial format:

Source: MSDN

Applications exchange messages as part of a dialog. When SQL Server receives a message for a dialog, it places the message in the queue from the service for the dialog. The application or stored procedure receives the message from the queue and processes the message as necessary. As part of the processing, the application may send messages to the other participant in the dialog.

Practice

Now it is time to get your hands dirty. First, let's define some simple business logic to demonstrate Service Broker. Let's assume that we are doing a design for online purchasing. Our requirement is such that whenever a user places an order the details are sent to the inventory system. From the inventory system, the service will read a queue and update necessary tables. (For simplicity, I assume the trading system and inventory system to be in one database. In the real world, the two systems will be on two different servers and you will have to create end points to facilitate communication.)

 First we create a new database called SSSB.

USE [master]
GO
/****** Object: Database [SSSB] Script Date: 02/19/2007 22:55:11 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SSSB')
DROP DATABASE [SSSB]

CREATE DATABASE [SSSB]

Then we enable the Service Broker for this database.

ALTER DATABASE [SSSB] SET ENABLE_BROKER

Now we create an order table that will be in the inventory system.

CREATE TABLE [dbo].[tblOrders](
                [ID] [int] IDENTITY(1,1) NOT NULL,
                [ItemCode] [varchar](15) NULL,
                [Qty] [int] NULL,
                [OrderDate] [smalldatetime] NULL
 CONSTRAINT [PK_tblOrders] PRIMARY KEY CLUSTERED
(
                [ID] ASC
)
)

We have to create Service Broker objects. First we need to create a MESSAGE TYPE and a CONTRACT.

CREATE MESSAGE TYPE SendStockDetails
                VALIDATION = WELL_FORMED_XML;

The SendStockDetails Message Type will send the information about the entered stock. This will have WELL_FORMED_XML as the messages are supposed to be sent in XML format.

CREATE CONTRACT [MainContract]
(
                SendStockDetails SENT BY INITIATOR
);

CONTRACT specifies in what direction these message types are used.

The next step is to create a Queue (queSendStockSend) to send the stock details.

CREATE QUEUE queSendStockSend WITH STATUS= ON, ACTIVATION ( PROCEDURE_NAME = usp_updatetocks,
 MAX_QUEUE_READERS = 5, EXECUTE AS 'dbo' ) ;

The queue above has two important parameters. One is the procedure name. Whenever a message is sent to the queue, the given stored procedure (usp_updatetocks) will be activated. This sp should be created before the CREATE QUEUE statement. However, for the sake of presentation, sp will be discussed later.