Simplifying IT, Empowering Businesses

What is Service Broker?

Service broker is basically composed of several elements like message types, contracts, a queue, and a service. It’s all about communication between applications. Its sent messages between services.

What are message queues?

Message queues are a way of sending asynchronous messages across boundaries. A queue is look like a table in SQL Server, but with a few minor differences. Each message is represented by a row in a queue.

The row contains the payload of the message and some other information, such as the associated message type, the receiving date, and the contract. These are the key features of queues…

• Guaranteed message delivery, if the target is offline the message will be held and delivered when it comes back online.
• Asynchronous communication.
• Public API’s.
• Transactions.
• Backup options.

How do they work?

As you can probably guess from the name when you send a message you typically specify a queue to send it to, this could be either local or remote. There will be some sort of receiver service that processes messages from the queue and possibly sends one back.

*Not all implementations guarantee in order processing of messages, MSMQ for example could have messages stored out of order due to one traveling faster over the wire than another.

Also, MSMQ could process messages out of order if you are processing messages in multiple threads, again Service Broker solves this by locking conversation groups guaranteeing all messages sent in that group will be processed in the order.

Why use them?

• Allow you to decouple dependencies on other applications by communicating through messages.
• Service broker has the ability to take offline or upgrade individual parts of the system with minimal impact to the end-users.
• We can control how and when the messages are processed. For example, you may send payment processing messages to a payment queue to be processed overnight when your server is at a lower load.

Why Service Broker?

It’s part of SQL Server so offers a lot of benefits Some of these being….

• Transactions
• Backups, as the queues are part of the database all you need to do is backup the database.

Example:

The scenario for this example is we are building a ticket booking website that will have 1000’s of concurrent users and we need the flexibility to scale quickly.

There are 2 components of this system that don’t need to happen in real-time and could possibly be deferred to periods of low activity, these are the payment processing and ticket printing.

This is how it is going to work…

• A user hits the “Create Booking” button on the web application to create a booking.
• The webserver inserts the booking record into the SQL Server database.
• The webserver sends a message to the payment processor target queue to process the payment for that booking.
• The payment processor service will pick the message up and process the payment
• If the payment is a success it sends a payment success message back to the payment processor initiator queue.
• If it fails it sends a fail message back
• SQL Server is monitoring the payment initiator queue for payment responses.
• If a payment fails it sets the booking record to payment failed.
• If the payment was a success it sets the booking table to payment success and sends a message to the ticket printer target queue.
• The ticket printing service picks up the message in the ticket printer target queue and prints the ticket.
• If this succeeds it sends a success message back to the ticket printer initiator queue
• If it fails it sends a failed message.
• SQL Server picks up the message from the ticket printer initiator queue and with sets the ticket to printed or failed.

So, this is the example of how service broker works. So now we are going to configuring service broker for asynchronous processing in SQL.

In this example we are going to configure for a Basic Service Broker configuration in as Single Database.

To get started we’ll need to create a database and enable the database for service broker usage:

It will create a new database. And If you want to check service broker is enable or not in your current database. You need to write this query to check.

If this query returns 1 then service broker is enabled on your database. And if 0 it means not enable on your database.

To Enable Service Broker on your database. Write this query:

After Enabling Service broker to Database. We need to Configuring Broker Components.

We need to create an object in the database this object will be a message type for message, and contract defines that how a message will be sent between services and queue and also between a queue and target service.

For the messages, need to create a message type for the request, which will be called AsyncRequest, and a message type for the result, it’s called AsyncResult. Both will use XML to send and receive the data required by the services.

The contract specifies that the AsyncRequest will be sent by the initiating service to the target service and that the target service will return an AsyncResult message back to the initiating service. The contract can also specify multiple message types for the initiator and target, or that a specific message type can be sent by any service if the specific processing requires it.

In this case, the service is named ProcessingService and will be created on the ProcessingQueue within the database.

Now we implement the store procedure for sending a new message to a broker service. Its send message to the ProcessingService.

Using the wrapper stored procedure, we can now send a test message to the ProcessingService to validate that we have set up the broker services correctly.

And the output will be:

While we could manually process the messages from the ProcessingQueue, we’ll probably want the messages to be processed automatically as they are sent to the

ProcessingService. To do this an activation stored procedure needs to be created in the database that we’ll test and then later its bind to the queue to automate.

The RequestQueue will also need to process the messages that are sent to it, so an additional procedure for processing the AsyncResult messages returned by the ProcessingQueueActivation procedure needs to be created.

On that point, all of the components are complete to fully automated processing. The only thing remaining is to bind the activation procedures to their appropriate queues, and then send another test message to validate it gets processed and nothing remains in the queues afterward.

Now we are going to test our Service broker.

Summary

Asynchronous processing in SQL Server we can configure it in a single database from that it allows only decoupled processing for long-running task. This can be a powerful tool for improving application performance, from an end user’s experience, by decoupling the processing from the end user’s interactions with the application.