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)
69 Comments. Leave new
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.
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
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.
Hi Pinal,
Service Broker is only usable for Email and sending Messages? Anything else can we do?
Leo
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.
Hi Buddy,
I need sql scripting to implement FIFO method for stock valuation related to stock market. Thanks in advance.
Regards
Amit
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’;
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
A good Simple sample…this would help…
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
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?
Nice article. Helped me understand what going on. Thanks.
Can I make the service broker communicate between two databases?
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.
One method is to make use of the result of this query
select ‘ALTER DATABASE ‘+name+’ SET ENABLE_BROKER’ from sys.databases where name in (‘db1′,’db2’,..)
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
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.
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
Thanks Pinal,
The simplicity of this example was a great starting point.
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
Madhav,
You would need to use Database Replication for that.