Service Broker Demystified Series - Summary

This blog series focused on the common complaints I hear about SQL Server Service Broker...namely that it is too confusing for a data professional to master.  The fact that there is no (good) GUI or monitoring tools doesn't help either.  In this series I simplified things and explained why SSB works the way it does.  I also provided tricks and shortcuts to make everything a little simpler.  SSB is a really great technology and I find new uses for it almost every day.  

Service Broker (SSB) is, IMHO, one of the most important features Microsoft has added to SQL Server in the last 15 years.  The ability to perform asynchronous data eventing directly in the data tier is going to be HUGE in the coming years.  Your app can do its data work without the user waiting on an hourglass or your app folks writing additional asynchronous client-side code.  All of this is the key to the "perception" of good application performance. 

Service Broker is also the key to the future of SQL Server too.  Look at the last few releases of SQL Server and you'll see a trend that some of the biggest features of various NoSQL solutions are being integrated directly into the core of SQL Server.  In-memory tables, column-oriented indexes, StreamInsight, HDInsight, and "eventual consistency", to name a few, all got their start in NoSQL products.  I believe SSB will be the "glue" that binds the next set of NoSQL solutions that will be integrated into SQL Server.  Examples...

  • true native sharding.  This can't be done synchronously and people need it.  SSB is the natural enabling technology.  I've rolled-my-own sharding solution using SSB.  
  • MapReduce in SQL Server.  Once you have sharding in SQL Server people will want to query and combine the sharded data using standard TSQL.  How better to do to implement a mapper than as an asynchronous SB message to another SQL instance?  Again, I've rolled-my-own MR tool using SSB.  
  • eventual consistency enhancements/improvements.  Microsoft gave us "delayed durability" which is one variety of eventual consistency.  The next natural step is to add data caching to the mix.  Query notifications, a component of SSB that few use, is the way I would do this.  

The big problem with SSB is that it scares EVERYONE.  

  • Data professionals hate it because the syntax doesn't feel like TSQL (take a look at those weird URIs for SSB object names).  Contracts, Servivces, and "poisoned messages" feels far too esoteric for a relational database.  
  • DBAs hate it because there is no good GUI to set it up, no good monitoring tools, and it requires far too much work to set it up and deploy it.  
  • App developers hate it because they learned that asynchronous code should be left to JMS/MSMQ/RabbitMQ.  "We should be taking things out of the data tier, not looking for more stuff to stick into it."  Further, SSB has its own lingo that directly contradicts concepts they learned with their messaging technologies.  For example...every messaging technology uses "fire-and-forget" monologs, but not SSB.  Fire-and-forget is verboten.  

This is the summary post for my Service Broker Demystified blog series where I tried to make things a little easier to understand for everyone.  Here are the topics I covered:  

 
Service Broker Demystified - Encryption Weirdness covers the first problem most noobs run into immediately...they set up two services and try to communicate and it fails with an encryption error.  Usually the noob will google the problem, fix it, and never really understand why the encryption error is occuring.  
 
Service Broker Demystified - SET ENABLE_BROKER vs NEW_BROKER covers what happens when you restore a database.  Most people have run into this problem but if you haven't, this post is for you.  Regardless, I'll explain the problem, why it occurs, and how I auto-magically fix it.  

Service Broker Demystified - [DEFAULT] is not the DEFAULT covers some goofy behavior when you do and do not specify the [DEFAULT] contract for a service.  

Service Broker Demystified - Contracts and Message Types: people get confused as to what the difference is between a contract and message type. Then they don't understand why both are needed. The fact is, like foreign keys, neither is REQUIRED, but both will help you find bugs and ensure accurate workflows if you use them. Just like FKs help to ensure accurate data.  
 
Service Broker Demystified - Why is there no ALTER CONTRACT statement? There isn't, get over it. DROP and CREATE if you must change a contract. We'll cover WHY it works this way.  This is actually a good thing.  

Service Broker Demystified - Why do we need Services and Queues? Why do we need both?  There always seems to be a one-to-one relationship with services and queues. So, why did Microsoft give us both?  To confuse us?  No, there are cases, albeit rarely, when it is helpful to have something other than a 1:1 relationship.  

Service Broker Demystified - Services: How can you determine if a service is an initiator or target? How does the DEFAULT contract change all of that?  Why should we even care if a service is an initiator or target?

Service Broker Demystified - [Initiators] and 'Targets': Why are initiators always placed in identifier brackets and Targets are specified as quoted string literals?  Once you understand this you'll start to see the bigger possibilities of Service Broker.  

Service Broker Demystified - Case-Sensitivity: Some SB objects are case-sensitive. Which ones?  Shouldn't they follow the case sensitivity of my server or database?  No.  Only "exposed" SB objects are case-sensitive.  This post will cover why.  

Service Broker Demystified - CLOSED conversations: yeah, they seem to hang around for awhile, sometimes. Why is that?  

Service Broker Demystified - Must my SQL Server be wired to the internet? I've heard this question quite a few times. People think their SQL Servers must have internet connectivity in order to use Message Types. Huh? It's the goofy SSB syntax that is confusing them.  No internet access required.  This post will cover this confusing topic.  

Service Broker Demystified - Fire and Forget Anti-Pattern:  You hear a lot about the "fire-and-forget" anti-pattern in SSB.  In this post I'll discuss exactly what that is and the simple rule you can follow to ensure you never get bitten by this bug.  

Service Broker Demystified - Can I model monologs? Yes you can! Most people actually want to model "fire-and-forget" monologs.  But this is seriously frowned upon.  In this post I'll show you how I do it, safely.  

Service Broker Demystified - Monitoring the Canaries covers how I monitor my SSB implementations.  There is no GUI that a DBA can easily look at to determine if all of your services are up and running.  These scripts are very simple and cover the absolute basics for SSB monitoring.  

Service Broker Demystified - How to Radically Simplify SB: Service Broker can be confusing. I've found that most implementations of SB I've worked on followed a handful of design patterns. If you create a "harness" that handles YOUR unique design patterns, you can simplify your life greatly.  I'll show you my harness that has saved me countless hours and headaches.  

You have just read "[[Service Broker Demystified Series - Summary]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.  

CONTENT
sql server service broker service broker demystified

Archive