Extensions - Service Broker (Intro)

Good afternoon everyone!

Apologies for not writing on here for a couple of days - on Monday I was in the test centre sitting my MCTS 70-433 exam and I passed! The past 2 days I have been very busy in the office sorting things out so I wasn't in the position to blog.

However... clean slate today and I am going to briefly cover another one of the SQL Server Extensions, called the Service Broker. As a Junior Developer, I've never had to use this myself however it is still very important to understand what its purpose is and how it can be used within your solution.

The purpose of the Service Broker (which was initially released in 2005 version of SQL Server but then expanded upon during future releases) is to send and process messages. The Service Broker mainly deals with:

Queues

Messages are pushed into a queue between the sender of the message and the recipient of the message, whilst relying on the Service Broker to ensure that the message reaches the intended recipient.

Dialogs

Dialogs are basically "conversations" where 2 participants (the sender and the recipient) are sending messages between each other. The dialog is the saved conversation between the two.

Conversation Groups

A conversation group is a collation of similar dialogs between multiple services in SQL Server. An example of this would be a Customer Relationship Management (CRM) having a GetCustomerInformation service, which would need to collate information from a customer data service and maybe an invoice service to see what items each customer has purchased.

Uses of the Service Broker

The Service Broker can be useful in any application which involves multi-threading (where multiple processes share the same resources and need to access the same information at the same time, but are able to execute independently from each other). Here is a list of some of the more typical uses:

• Triggers.
• Query processing.
• Data collection. 
• Server-side processing. 
• Data consolidation.
• Batch processing (on a large scale).

Benefits of using the Service Broker

There are many benefits that come with using the Service Broker:

Improved Performance. 

All data, messages and logic behind the application are stored within the same database, which means that less work will have to be carried out in order to retrieve them from an external source. Also, this means that the messaging system and the database will never be out of sync with each other, as one relies on the other to operate, unlike in older systems where the Service Broker was run separately and wasn't integrated within the SQL Server environment.

Easy to maintain and administrate.

Messages can be ordered and coordinated, which will make it easier for the database administrator to maintain. Prior to the release of the Service Broker in 2005, it was difficult to keep on top of the number of messages coming into the system and when a single application produces thousands of messages which could arrive in the wrong order, it's easy to see why this proves such a challenge for database administrators.

You would naturally assume that messages would arrive in the exact order that they have been sent however this isn't usually the case. A good example of seeing this in action is by describing someone viewing a video over the Internet. The video content is sent via packages (the progress of the capture and ordering of these packages is what the secondary bar is when looking at a video. Many thanks to YouTube for their buffer bar!):




When the playback of the video is faster than the speed which the packets are being received and reordered, this is referred to as buffering. Databases need to co-ordinate and order messages too - and the Service Broker with SQL 2005 onwards makes this process much easier to manage.

Flexibility between linked applications.

The use of queuing means that an application can send a message to the queue whilst still continuing with its own processing. The Service Broker is then relied upon to ensure that the message reaches its destination, the application itself doesn't deal with the actual sending process itself.

When multiple programs need to read from the same message queue, this used to cause problems in the past too. However the Service Broker fixes this issue by placing associated messages into conversation groups and then locking these groups when one particular instance is processing messages. This means that other applications can work on other conversation groups at the same time without locking complications.

Automatic activation makes applications scalable. 

Activation means that the application will scale itself accordingly depending on the number of messages that are in each queue, therefore assigning just the right number of resources to each message queue. The Service Broker will be able to automatically determine the scalability, however this isn't a default setting and you will manually need to turn this option on in order to take advantage of what it has to offer.

Final Word...

As a conclusion, the Service Broker is a neat add-on for SQL Server. Please note that this really is only a brief introduction as to what the Service Broker is and in the future, I will go more in depth with code examples on how to set up the Service Broker on your database.

What Next...

Well I have worked through the majority of the MCTS exam book now and I have to admit that writing down what I have been learning and practising has really helped me for my exam. Therefore, to conclude this week I will be writing about my experience in studying for my Microsoft exam, along with advise and tips on how to prepare.

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.