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
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.
Pinaldava you are a wonderful instructor…
thanx very much
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
Excellent article/example. Concise and very easy follow sample.
Simple and easy to understand.
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.
Thank you Pinal.
But sqlmaster is right not even one question was answered.
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 – 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
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”.
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.
Thanks
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?
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.
Thanks
Naushad
Thanks for sharing @Naushad
how to implement service broker in multiple database, sending from one database and receiver is another db
You need to create services, queue and activation procedures in those database.
Nice article, Pinal Dave. But the million dollar question remains unanswered: where does my stored procedure fit in that does the actual work?
I WANT TO SEND MESSAGE TO MULTIPLE USERS AT SAME TIME CAN I SEND? VIA SERVICE BORKER
Messages can be sent to multiple target services (i.e. Multicast).
This capability has been supported since SQL 2012.
See latest update towards bottom of following tink… https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-service-broker?view=sql-server-2017
Cheers
Can you please tell me how to take backup using SSB services?
Thank you sir..
It’s so easy way to understand for new folks…
I want to remove my SendService which is alerady linked with another queue.
For Example :
ALTER DATABASE [DBName_xxx] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO
— Create Message Type
CREATE MESSAGE TYPE TstMessage
VALIDATION = NONE
GO
— Create Contract
CREATE CONTRACT TstContract
(TstMessage SENT BY INITIATOR)
GO
— Create Send Queue
CREATE QUEUE SendQueue_XXX
GO
— Create Receive Queue
CREATE QUEUE ReceiveQueue_XXX
GO
— Create Send Service on Send Queue
CREATE SERVICE SendService_XXX
ON QUEUE SendQueue_XXX (TstContract)
GO
— Create Receive Service on Recieve Queue
CREATE SERVICE ReceiveService_XXX
ON QUEUE ReceiveQueue_XXX (TstContract)
GO
This is my process.
Now I am getting error for creation of “SendService_XXX “, because it is already created in another database and linked with another queue.
Now I want to delete “SendService_XXX” this service which is present on another db.
How can i do this.
Please suggest.
Hi Pinal Dave,
i need to call sp in service broker
inside trigger this sp will be called
how can i achieve this
my main goal is to achive the DML Tracking with the following information
just started analaysis of Data changs (DML) tracking in sql server. but couldnt find best way to track the DML changes in sql server like as follows.
i need to know
1. who performed this changes
2.when performed this changes
3. Host name and Ip address of that machine .
4. on which tables DML operations performed.
in ssms there is a option : Schema change history but its not helped.
i have gone through some articles but couldnt’ find best solution.
kindly help me on this
any help that find solution highly appreciate.
thanks in Advance.
Best, Siddu
just started analaysis of Data changs (DML) tracking in sql server. but couldnt find best way to track the DML changes in sql server like as follows.
i need to know
1. who performed this changes
2.when performed this changes
3. Host name and Ip address of that machine .
4. on which tables DML operations performed.
in ssms there is a option : Schema change history but its not helped.
i have gone through some articles but couldnt’ find best solution.
kindly help me on this
any help that find solution highly appreciate.
thanks in Advance.
Best, Siddu