SQL SERVER – Introduction to Service Broker and Sample Script

Service Broker in Microsoft SQL Server 2005 is a new technology that provides messaging and queuing functions between instances. The basic functions of sending and receiving messages forms a part of a “conversation.” Each conversation is considered to be a complete channel of communication. Each Service Broker conversation is considered to be a dialog where two participants are involved.

Service broker find applications when single or multiple SQL server instances are used. This functionality helps in sending messages to remote databases on different servers and processing of the messages within a single database. In order to send messages between the instances, the Service Broker uses TCP/IP.

This transaction message queuing system enables the developers to build secure and reliable applications, which are scalable. The developers can design applications from independent components known as “services.” If the applications need to avail the functionality of these services, then it sends message to the particular “service.”

Loosely coupled applications (programs that exchange messages independently) are supported by the Service broker. The three components of the Service broker are as follows: conversation components (which consist of the conversation groups, conversations and messages); service definition components (which define the conversations); and networking and security components (defines the infrastructure used for exchanging messages between instances)

The maintenance of Service Broker is easy and it is a part of the routine database administration procedure. This is because this functionality forms a part of the Database Engine. Service Broker also provides security by preventing unauthorized access from networks and by message encryption.

Let us understand Service Broker with simple script. Script contains necessary comments to explain what exactly script is doing.

---------------------------- Service Broker -----------------------
-- In this exercise we will learn how to cofigure Servie Broker and send and recieve messages.
-------------------------------------------------------------------
CREATE DATABASE ServiceBrokerTest
GO
USE ServiceBrokerTest
GO
-- Enable Service Broker
ALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER
GO
-- Create Message Type
CREATE MESSAGE TYPE SBMessage
VALIDATION
= NONE
GO
-- Create Contract
CREATE CONTRACT SBContract
(SBMessage SENT BY INITIATOR)
GO
-- Create Send Queue
CREATE QUEUE SBSendQueue
GO
-- Create Receive Queue
CREATE QUEUE SBReceiveQueue
GO
-- Create Send Service on Send Queue
CREATE SERVICE SBSendService
ON QUEUE SBSendQueue (SBContract)
GO
-- Create Receive Service on Recieve Queue
CREATE SERVICE SBReceiveService
ON QUEUE SBReceiveQueue (SBContract)
GO
-- Begin Dialog using service on contract
DECLARE @SBDialog uniqueidentifier
DECLARE @Message NVARCHAR(128)
BEGIN DIALOG CONVERSATION @SBDialog
FROM SERVICE SBSendService
TO SERVICE 'SBReceiveService'
ON CONTRACT SBContract
WITH ENCRYPTION = OFF
-- Send messages on Dialog
SET @Message = N'Very First Message';
SEND ON CONVERSATION @SBDialog
MESSAGE TYPE SBMessage (@Message)
SET @Message = N'Second Message';
SEND ON CONVERSATION @SBDialog
MESSAGE TYPE SBMessage (@Message)
SET @Message = N'Third Message';
SEND ON CONVERSATION @SBDialog
MESSAGE TYPE SBMessage (@Message)
GO
-- View messages from Receive Queue
SELECT CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
GO
-- Receive messages from Receive Queue
RECEIVE TOP(1) CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
GO
-- Receive messages from Receive Queue
RECEIVE CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
GO
-- Clean Up
USE master
GO
DROP DATABASE ServiceBrokerTest
GO

Let me know what do you think of this script and how simply one can configure service broker.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Service Broker, SQL Download, SQL Scripts
Previous Post
SQL SERVER – Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location
Next Post
SQLAuthority News – Article 1100 and Community Service

Related Posts

69 Comments. Leave new

  • Philip Freeman
    June 30, 2011 10:02 am

    Pinal;

    Here is a real life example for you. We used Service Broker almost like a SCADA system. We took sql server 2008 express and WonderWare’s sql 2005 version and deployed it out into the field on our equipment. We used WonderWares ability to read the meters and store the data. We then created a stored procedure to capture the data on a per second interval and place it in a queue in the sql server 2008 express version. The express version has the ability to prioritize the messages. All 200 pieces of equipment send a message on a per second bases back to a central collection point. There is a single database on the data collection side that all of the data resides in and thus can be analyed at will. The central collection point database of course uses table partitioning to handle and query all of the data in a reasonable amount of time.

    Reply
  • Hareesh Raju
    July 21, 2011 5:45 pm

    Thanks for your notes. It is really good.

    Reply
  • Thank you very much Dave for this article, you made the complexity of Service Broker very simple and understandable. Two Thumbs Up for you. Thanks! ^_^

    Reply
  • Thank you very much

    Reply
  • Hi Pinal,

    Thanks for all of the time you invest in SQL Authority. You do a great job of teaching.

    I have a SQL application that must send several text files to a share on a client’s Windows server. I use SSIS to create the text file and save it to the client’s share; however, if the share is not available due to network issues, the SSIS package fails.

    Is this an appropriate use of SQL service broker.

    Thanks,
    Jon

    Reply
  • My respect and thanks for your guidance as always – especially as the code example actually works first time with no corrections needed – always appreciated. /BW/Dioscoredes/

    Reply
  • I have read quite a few supposed ‘examples’ of Service Broker, but one thing continues to puzzle me…. It is considered a messaging system… but are we talking messages like email or messages like instructions? Can these ‘messages’ execute t-sql on the recieving server? I have a trigger that I would like to execute a statement asynchronously on a remote server…. is this something Service Broker would be used for?

    Reply
    • Dana;
      Yes, at the very least you could pass messages to the activated stored procedures that would execute a set of predefined commands on the remote server.

      Reply
  • Hi Pinal,

    Copied the executed the whole script in one session (except the cleanup). Do not see any message in the table. What do I miss?

    Reply
    • Hello Dennis, I had to run the following ALTER statement:

      CREATE DATABASE ServiceBrokerTest
      GO
      ALTER AUTHORIZATION ON DATABASE::[ServiceBrokerTest] TO [sa];

      USE ServiceBrokerTest
      GO

      Reply
  • Nice article

    Reply
  • HI team,

    I need to do service broker should run in MSDB DB, SP should be in test1 db (My DB) I am not able to connect the from test1.SP_Service_broker to MSDB database service broker. Can any one help ….

    Reply
  • Thanks for the scripts dude.Very helpful indeed.

    Raj

    Reply
  • DROP DATABASE is one way to cleanup your ssb ;-)

    In a production environment you should call END CONVERSION from _both_ sides (target and initiator) – otherwise you will get in trouble (filling up the sys.conversation_endpoints catalog view).

    Even if both sides of the conversation are closed correctly, conversation
    endpoints for the target remain in sys.conversation_endpoints for 30
    minutes. This is to prevent replay attacks.

    Reply
  • ratheeshknair
    May 12, 2012 11:58 am

    Thanks Pinal for the article,

    PLEASE CORRECT THE SPELLING OF INTRODUCTION.

    Reply
  • This article is very delicious

    Reply
  • Hi Pinal! I ran the scripts against a SQL Server 2008 R2 instance but don’t see any messages in the queues? Do I need to configure something else before it works. Thanks in advance.

    Reply
  • good

    Reply
  • thanks

    Reply
  • Jaimeet Pathak
    August 22, 2012 6:19 pm

    Hi,

    This is a good article and also working fine, but our requirements is to send message to remote queue of remote service ,in that lots of things we have to do like certification creation , remote service binding, route creation etc. so can you please share example showing step by step implementation . We need some help on urgent basis. also in our architecture client machine is SQL Server 2008 Express R2 and Server is SQL Server 2008 Enterprise R2 .

    Thanks.

    Reply
  • Really very nice article.

    Reply
  • can you tell me, the new technology that implements message queuing system for reliable and secure data base to data base connection

    Reply

Leave a Reply