Next Free Webcast:
SQL Service Broker Advanced Performance Tips and Tricks
We will be looking at some of the advanced features of SQL Service Broker as well as some of the advanced techniques which can be used to maximize the performance of the SQL Service Broker.
> Register Now
> Live date: 11/3/2010 at 12:00 Pacific
Featured Article(s)
Why should I use a WHERE clause (it’s just a delete)
If you are new to SQL Server and are looking for some best practices to stick by this series of articles is for you.
Featured Script
sp_DBA_ScriptServerDatabases
This procedure scripts all of the (except system, pubs, Northwind) databases on the specified server. The NoFileGroups param… (read more)
SQL Server Broker Service
I wanted to take some time today to talk about the SQL Server Broker Service for those of you who have not been exposed to it. In the most basic terms, the SQL Server Broker Service is a message queuing engine. It allows for development of robust systems that can take advantage of parallel processing, while delivering the same kind of ACID transactions we require of our database server.
Why would you want a message queue kind of capability? The biggest reasons are reliability and responsiveness.
When I send an Email to someone I know that at some point that Email will be delivered, or I will receive a notification that delivery was not possible. I don’t need to know what path the message followed or how it was transported; I just need to know that it got to the destination or not. If not, I can now handle the failure. Message queuing works in a similar format. You application submits your work to a defined queue. Queue handlers handle the messages off of queues and if complete the transaction is committed, if not, it is rolled back. If rolled back, my application is notified and I can handle the error.
In the mean time, my application was able to go about doing meaningful business other than waiting for the work to be completed. Responsiveness is more a perception of the end user. If I have a complicated set of logic needed to be completed I can send my request to a queue. Once the Broker Service completes my request, I can receive and handle the results. I’ll handle the results or the errors when they have been completed.
Microsoft integrated message queues into SQL Server as a Service Broker. This close coupling allows for SQL Server to have additional capabilities such as stored procedures that processes queue messages. It can even fire off stored procedures to handle messages, or increase the number of instances of a stored procedure if there are not enough to handle the load.
There is much more to the infrastructure about the Broker Service far beyond what I can put into a single newsletter. I hope this sparks an interest in the Data Broker for you. The Webcast on the 3rd provides a lot more insight into the Data Broker service and how it can increase the processing power of your data engine.
Are you using the Broker Services in SQL Server? Do you have any questions about it you would like us to consider? Want to share your experiences? Send your comments to btaylor@sswug.org.
Cheers,
Ben