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.
USE ServiceBrokerTest
-- Enable Service Broker
-- Create Message Type
-- Create Contract
-- Create Send Queue
-- Create Receive Queue
-- Create Send Service on Send Queue
ON QUEUE SBSendQueue (SBContract)
-- Create Receive Service on Recieve Queue
ON QUEUE SBReceiveQueue (SBContract)
-- Begin Dialog using service on contract
DECLARE @SBDialog uniqueidentifier
TO SERVICE 'SBReceiveService'
-- Send messages on Dialog
SET @Message = N'Very First Message';
MESSAGE TYPE SBMessage (@Message)
SET @Message = N'Second Message';
MESSAGE TYPE SBMessage (@Message)
SET @Message = N'Third Message';
MESSAGE TYPE SBMessage (@Message)
-- View messages from Receive Queue
SELECT CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
-- Receive messages from Receive Queue
RECEIVE TOP(1) CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
-- Receive messages from Receive Queue
FROM SBReceiveQueue
-- Clean Up
USE master
DROP DATABASE ServiceBrokerTest

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)

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


    Liked by 1 person

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


  3. Hi Pinal

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


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


  5. 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’;


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



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

    Paresh Prajapati


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


  9. Hi there –

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

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


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

    DECLARE @handle uniqueidentifier
    DECLARE conv cursor for select distinct conversation_handle from SBSendQueue WITH (NOLOCK)
    OPEN conv
    FETCH NEXT FROM conv into @handle
    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
    CLOSE conv


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


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



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



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


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


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



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


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


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

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

      USE ServiceBrokerTest


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


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


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


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



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


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


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

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


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



  27. 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”.


  28. Hello Pinal,
    I would like to know how I can user the service broker to manage the executions of stored procedures with priority, for example I have 3 procedures, they insert date into diferent tables (3 tables), the third table depends of the second and the second depends of the firstone, with foreign keys, I need to put in a queue each procedure, because I need them to be executed one by one.


  29. Hi Pinal,

    Excellent article. I have followed same step for creating Service Broker and it is working fine. I am using external activator, there facing one issue when i am using with AGL Name in my external activator config (EAService).

    server=;database=TestDb;Application Name=External Activator;Integrated Security=true;
    — It is working fine when we use directly server name instead of AGL name but we should not use server name because if fail-over happen than might be server2 will become primary server so we need to manual change connection string with server2.

    server= ;database=TestDb;Application Name=External Activator;Integrated Security=true; — It is with AGL listener name, it is not working
    Above same connection string is working fine when we use in .net application, access from web.config file to .net exe/asp.net web site etc.

    Throwing error message as “EXCEPTION ERROR = 32, No enabled application monitor is on behalf of queue [serverName].[DbName].[TargetQueueName]”

    I have tried with AGL name as all caps letter, small letter, IP address, with port number etc but still same issue.

    Could you please help on this?


  30. I am working on one of application where user send the query to a particular vendor online.

    This activity involves the following
    1. Prepare the email message to be sent to the vendor
    2. Prepare the email message to be sent to the user
    3. Prepare the email message to be sent to the admin
    4. Save the information to the database
    5. Process the information and send the email OR SMS notification to the respective participants i.e vendor,user and admin.

    The complete process takes around 1.5 to 2 minutes to execute. Initially, I have written all logic in c# dotnet, however, after some time I realize that the solution is not reliable and scalable. In addition to this, the process is time consuming and tedious and performance is also getting impacted as number of user grow. This lead to develop some frustration to the user.

    I have decided to re-invent my solution by considering reliability,scalability, asynchronous (for better performance) and loose coupling. So, I have decided to use service broker architecture as it supports all these mechanism very well.

    I have re-designed the above as per following

    1. I just prepare the message related to email or other information at c# dotnet side and push the same into database. It just takes 5 to 10 sec to execute.
    2. There is separate background process called as Service Broker which deliver the reliable messaging such email or sms notification to the user, vendor or admin. it also perform the logging and other related stuff.

    This solution possible reduced the overhead for maintaining the logic also provide some robustness.



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