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

Click to Download Scripts

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

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

About these ads

55 thoughts on “SQL SERVER – Introduction to Service Broker and Sample Script

  1. Hi Pinal,

    You explained what is Serived Broker very nicely. But what is a real life application of service broker architecture in sql and how to use it?

    Can you explain real life example to understand service borker?

    Thank you.

  2. Hi Naidu,

    Main thing is: Service Broker is asynchronous Process.

    In real life example, we have situation where we need to process some additional steps. We can complete our transactions before processing those additional steps, like to send email of updates.

    so our transaction can commit and user don’t need to wait until mail is sent. In this case we can use service broker.

    We just need to insert Message in Queue, Queue will process this message from queue in FIFO manner. Transaction can complete after inserting it queue.

    So in our example, to send mail from service broker, mail will be send by Service Broker till then transaction should not have to wait.

    Moreover, Service Broker is reliable too, If there is any problem in connection or SQL services are down, then also when services are up, it will start reading queue and process messages.

    So in real life case we can use it for Send email, Keep history info etc..

    Tejas

  3. hi! PIndal, i’m experiencing data storage problem ’cause i’ve a queue growing up this size constantly. how can i clean up it?

    • Did you set a limit on the number of messaging processing threads that the broker can spawn for each queue? SQL server should spawn new threads if it detects that the messages are coming in faster than the current number of threads can deal with, up to the max number that you have set.

  4. Hi Pinal

    Can you tell me if there are any concerns or security issues with regards to enabling Service Broker on the MSDB database.

  5. Hello.
    Its very very good.
    I used it and i am sure i is very good,
    i suggest it to every body use it to learning.
    your website very good.
    thankfully.

  6. Hi Buddy,

    I need sql scripting to implement FIFO method for stock valuation related to stock market. Thanks in advance.

    Regards
    Amit

  7. Excellent article. I’m going through the 70-433 training right now and your example gave the same information more concisely with greater lucidity.
    I’m thinking about using this feature in future application implementations. I assume that the message types, queues and conversations will be set up before hand.
    When sending and receiving messages, are users likely to create stored procedures for that purpose?
    Example: EXEC sendMessage @Message=’Hello World’;

  8. The above example is really good, especially for beginners in service broker to get in quickly. I was wondering how above sample can be tried across different servers/database

    Thanks
    Deepak

  9. Hi Pinal,

    You have given the example for one database only and did the process of sending and receiving the message in same database.
    How can we communicate for two different server databases ?
    Where i need to with above script for two different server? databases ?

    Thanks,
    Paresh Prajapati

  10. Hi Pinal,

    I created a view to retrieve the message from the queue with the following query:
    SELECT Queuing_order AS MessageID, CONVERT(NVARCHAR(MAX), message_body) AS Message
    FROM dbo.SBReceiveQueue

    In my application i want to retrieve these queue messages in an Oracle 10g database. But when i query data from the view as created above, it drops off the column “Message”.

    Can you please help me with this?

  11. Hi there –

    Is there an easy way to switch on Service Broker for a whole database server at once? Or should I just run:
    ALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER
    GO
    for each database in sys.databases?

    I want to use it to send myself email about backup jobs etc. that run on all databases.

  12. 1. FYI… When a databse gets restored from otehr server a Broker Id is required. ALTER DATABASE Set NEW_BROKER
    2. to clear of the queue, use the conversation handle in the queue and end conversation;

    ex: For each Conversation handle
    End CONVERSATION

    DECLARE @handle uniqueidentifier
    DECLARE conv cursor for select distinct conversation_handle from SBSendQueue WITH (NOLOCK)
    OPEN conv
    FETCH NEXT FROM conv into @handle
    WHILE @@FETCH_STATUS = 0
    BEGIN
    END CONVERSATION @handle WITH cleanup
    /*select CONVERT(NVARCHAR(MAX), message_body) AS Message,@handle from DBO.SBReceiveQueue WITH (NOLOCK) WHERE [conversation_handle] = @handle*/
    FETCH NEXT FROM conv INTO @handle
    END
    CLOSE conv
    DEALLOCATE conv

  13. Hi Pinal

    I want to Syncronize my database kept at two different locations having different IP’s How can I use SQL Broker Service to do that

    Eagerly Waiting for Your Response

    Madhav Mishra

    • This is not how or why you should use SB. Use either db replication, or backup/restore from the first db to the second (depending on whether you use one for prod and the other for reporting, whether it’s one db partitioned into two, or whether it’s two copies of the same db’s being used for prod and the data need to be kept consistent. Your question has very scanty details on what you are trying to achieve.

  14. Hi Panal Sir,

    Very nice article help me a lot , now i am finding advance level matter so that we can implement it in projects .

    Thanks

  15. Thanks for the script.

    I would really appreciate it if you could demonstrate another example for MULTIPLE INSTANCES.

    In vain Ive been wrestling to make it.

    Thanks

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

  17. 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! ^_^

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

  19. 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/

  20. 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?

  21. 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?

  22. 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 ….

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

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

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

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

  27. Event Description: Could not start Service Broker for database id: 7. A problem is preventing SQL Server from starting Service Broker. Check the SQL Server error log for additional messages.

    can anyone help me with this issue?
    I have checked the Error logs and there is not any information.

  28. Hi. Can u tell me how to use service broker in sql 2205 for communication between two database on remote server. That is Database X is on server A and Database Y is on Server B. both server are connected using TCP Connection

  29. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

  30. so everyone is spending their time in reading your article,appreciating you, asking you questions. But the moderator doesn’t even care about getting back to even single person. That just shows you don’t care about replying but only putting out your articles.

  31. Hello Pinal,

    I am newbie in MS SQL and facing a issue in importing CSV file to my MS SQL Table. Someone told me I can resolve the issue using Service Broker and since I follow your blog regularly, I thought you might help me on this.

    Let me explain my problem:

    I have 2 tables:- 1# Stock_Symbol 2# Stock_Symbol_Daily_Quotes

    In Stock_Symbol there are 2 columns – Stock_ID & Stock_Name
    In Stock_Symbol_Daily_Quotes there are ID; Stock_ID; Price; Time; Date; High; Low; etc.

    From the following link – http://finance.yahoo.com/d/quotes.csv?s=BBDB.TO+NT.TO+GE+MSFT&f=snl1d1t1ohgdr I am manually can get any stock information as CSV.

    But I want to do this automatically and directly to Stock_Symbol_Daily_Quotes table. I am seeking for some T-SQL which will allow me to download the stock price in every 15 minutes.
    BBDB.TO+NT.TO+GE+MSFT – this will come directly from Stock_Symbol table.

    Is there a way to do that, I tried lot of option, I know if the csv file located locally I can do the import and doing dynamically and directly from web is what I am not able to do that.

    Hope I am able to make you understand my query, please help.

    Regards,
    Daipayan

  32. When i try this i got error message in middle. the error message is below. pl. help in this regard
    Msg 137, Level 15, State 1, Line 2
    Must declare the scalar variable “@Message”.
    Msg 137, Level 15, State 2, Line 3
    Must declare the scalar variable “@SBDialog”.
    Msg 137, Level 15, State 1, Line 5
    Must declare the scalar variable “@Message”.
    Msg 137, Level 15, State 2, Line 6
    Must declare the scalar variable “@SBDialog”.
    Msg 137, Level 15, State 1, Line 8
    Must declare the scalar variable “@Message”.
    Msg 137, Level 15, State 2, Line 9
    Must declare the scalar variable “@SBDialog”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s