I have a practical use, in fact this article is very well timed as I'm writing the proof of concept app later this week.
Service broker is handy in situations where you need ordered delivery of messages and network communications is spotty.
I have a situation where data travels over a satellite (not great bandwidth), and sometimes one of the communicating parties can't hit the satellite. I was using MSMQ, but have problems when the message folder exceeds a certain size and other things that generate "insufficient resources" errors.
Plus with MSMQ you can get ordered delivery OR guaranteed delivery, but not both - and I need both. So we have some ugly code that shuttles messages in to a database and does the resequencing for us, and that in turn has issues when scaled out. This will eventually handle about 1.5 million messages a day flowing between three dozen end points, so scaling is a serious issue.
Plus many of my communication endpoints are running Windows 2000, which means I only have MSMQ 2.0, which makes my issues with scaling that much worse.
With service broker, I get guaranteed delivery, FIFO, no problem with broken links causing messages to pile up (SQL is great at handling big chunks of statefulness, that's what its built for). Since the messages I'm sending are data that's already on the SQL server, it keeps the internal network traffic down within the endpoints (no querying the data off of SQL to some app server to get it to MSMQ).
That all assumes it'll handle hundreds of gigs of messages per day without going boom, which is why I'm doing a POC. But it looks promising