How to work with service broker in sql server

Question

1) please tell me few scenarios where people use service worker in sql server
2) i heard service worker create queue then how to read value from that queue? what name is given to that queue ?

please share a code example that how to use service worker in sql server for a specific scenario. thanks

Answer 1

Hi @Sudip Bhatt ,

Per my knowledge and after some searching, there is no exact one thing named service worker in sql server.

Since you mentioned 'service worker create queue', you seems to talk about Service Broker.

In the official document about Service Broker, you could refer some details like '
When to use Service Broker' and the basic steps to create services, send messages and process message.

Service Broker is an asynchronous messaging framework with which you can implement scalable, distributed, high available, reliable, and secure database applications based on SQL Server.

Here are some examples:

• With asynchronous database triggers you are able to defer long running tasks inside a trigger to a later time. This will improve the performance of your database applications.
• With distributed message-based database applications you are able to decouple related tasks within your database applications and process these tasks independently from each other at different times. This will help you with the scale out of your database applications.

You could also refer complete examples of Service Broker from below links and check whether they are helpful to you:
Service Broker Part 1: Service Broker Basics
SQL Server Service Broker Explained

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I assume that you mean Service Broker.

Some example for Service Broker usage:

• Disconncted applications that need to talk to each other.
• Asynchronous execution inside a database.
• Notifications to other applications that something have happened.

Typically you read values from a queue with the RECEIVE statement, but you can actually run a SELECT on it as well. It is actually table with some extras around it. You call the queue whatever you want.

Even quick examples with Service Broker are a bit too lengthy for a forum post. Instead I would like to recommend you this very good introduction to Service Broker: This book written by Roger Wolter who was the Program Manger for Service Broker:
https://www.amazon.com/Rational-Server-Service-Broker-Guides/dp/1932577270/ref=sr_1_4?dchild=1&keywords=Roger+wolter+service+broker&qid=1602710633&sr=8-4

Many computer books are thick as a brick, but this is a neat thing on 220 pages.

Answer 3

please tell me how to read value from queue which would be created by service broker.
what would be queue name?

You can use the RECEIVE command to get messages from a queue and the queue name is defined by yourself.
Yes, you can implement everything in plain T-SQL, see Service Broker for getting started.

Answer 4

long time back i have worked with service broker using c# but now i am interested to know without any language like c# how can i use service broker in sql server only? is it possible to monitor any table with service broker from sql server and get newly added or updated records from the queue created by service broker.

As Dan says this is the SqlDependency class, and more generally, this feature is known as Query Notification. This particular feature is only available from the client-side of things. That is, you cannot set this up in T-SQL only.

But Query Notification is not a feature of Service Broker itself. Rather, Service Broker is a component that is used to implement Query Notification. There are more features in SQL Server that uses Service Broker, one example is Database mail. If you have ever used sp_send_dbmail, you have used Service Broker without knowing. That is, this procedure does not send the mail itself, but it puts a message on a Service Broker queue, and an internal queue reader takes care of it sends the mail.

And this is a pattern you can use yourself. Say that when a row is inserted, updated, deleted in a table, a lot of data elsewhere needs to be recalculated, but you want for the recalculation to complete. So rather running the recalculation directly from a trigger, you post a message on a Service Broker queue, and this will trigger an activation procedure on the other end which performs the calculation.

Again, this is just one example of what you can use Service Broker for. As I also mentioned, Service Broker can also be used to let databases on different servers talk with each other asynchronously. That is, it could be an alternative to linked servers.

Have you ordered the book I gave a link to? The links that Melissa gave can be useful too.