Service Broker Demystified Series

This blog series will focus 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'll try to simplify things and explain why things work the way they do.  SSB is a really great technology and I find new uses for it almost every day.  

Hypothetical questions:  

  • What are the three most important features added to SQL Server in the past 15 years (since SQL 7)?  
  • What will be the most important feature of SQL Server in the coming years?  

My answers:

  1. DTS/SSIS (especially the wizards): the ability to extract and load data without writing any "data marshalling" code allowed companies to migrate their old Access applications to SQL Server quite easily. For years I had tons of side work migrating applications from Access or FileMaker to SQL Server. The tool of choice is DTS/SSIS.
  2. The DMVs: I wrote about this recently. The ability to see what the server subsystem is waiting on is the key to understanding the performance characteristics of your application.
  3. Service Broker: The ability to perform asynchronous data eventing is going to be HUGE in the coming years. Also, the ability to let your app do asynchronous data work without the user waiting on an hourglass, and without additional asynchronous client-side coding, is the key to the "perception" of good application performance. Further, I'm convinced that Service Broker is going to be the tool to bridge the divide between SQL Server and NoSQL solutions. Some of the biggest architectural decisions with NoSQL solutions is sharding data and the asynchronous nature of mappers and reducers. Service Broker (SB) will likely be the underlying technology that allows Microsoft to finally give us true sharding in SQL Server. Also, how better to implement a mapper than as an asynchronous SB message to another SQL instance?
Everyone's favorite features list will be different  Perhaps you like the HADR features of the last few releases or even the in-memory table offerings.  To each his own.  We all have our own unique experiences.  This is my list.
 
I feel that not only has Service Broker (SB) been revolutionary in the past 9 years or so, but I obviously feel it is only going to get more important in the future. Anecdotally, SB seems to scare people.  Perhaps the syntax is too esoteric and not very "TSQL-like".  Or the lack of a GUI scares people away.  Or maybe people feel asynchronous eventing is best left to JMS/MSMQ or another tier and not in the db (if so I suggest you google "SODA" which is SOA with Data in the middle).   I've found two issues that scare people the most:  there is a lot of new jargon which are unfamiliar to data people (contracts, messages, services) and people don't understand the little idiosyncracies of SB.  And when you start to understand them then you'll find SB is actually quite intuitive.  
 
This post is the introductory post for a series I'm going to do called Service Broker Demystified.  This isn't a SB tutorial.  You won't learn the basics here.  I assume you know the basics already and just want to understand better some of the esoteric stuff that trips people up with SB.  Here are the topics we'll cover (these links may be bad until I get the posts finalized, check back often):  
 
Service Broker Demystified - Encryption Weirdness covers the first problem most noobs run into...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.  In this post I'll try to make it clearer.  
 

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 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 just 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. Why is that?  

Service Broker Demystified - Must my SQL Server be wired to the internet? I've heard this question at least 3 times. People think their SQL Servers must have internet connectivity in order to use Message Types. Huh? It's the goofy SB 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.  

 

 

CONTENT
sql server service broker service broker demystified

Archive