Service Broker Demystified - Must my SQL Server be wired to the internet?

Service Broker uses a lot of syntax that is unfamiliar to data professionals.  This scares them away from using SSB.  For instance, DBAs will see references to URLs like http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification and immediately assume that their SQL Server will need to be wired to the internet to resolve those references.  It doesn't.  Those are simple URIs (namespaces) and SSB never connects to them.  In this post I'll prove it and show you why they are used.  

I've heard comments that Service Broker is too confusing and hard to use. I started a blog series called Service Broker Demystified because SSB really isn't terribly difficult, but there are some concepts that trip up new users.

Today we are going to cover some goofy syntax that worries security-minded DBAs.  

Service Broker noobs get concerned when they see syntax that is unfamiliar to them.  For instance, look at the BOL example syntax for CREATE MESSAGE TYPE:

"Oh my...if I use SSB I'm going to store stuff on a share somewhere.  Wait, those UNC paths have forward slashes.  I'm confused."

Then just below that entry in BOL the DBA sees the syntax for creating an XML schema for Message Type validation: 

By now some DBAs are really freaking out thinking that they now need to have their secure SQL Server on the Net so they have access to http://www.w3.org/2001/XMLSchema.  And what if that website goes down during peak production hours?  

Never fear, you don't need to create shares for Message Types or give your SQL Server access to w3.org.  In both cases those addresses can be changed to anything you like and everything will run perfectly.  Even if w3.org goes down your SSB infrastructure will still work.  Your SQL Server will not connect to those addresses, EVER, and everything is totally secure.  Those addresses are there merely to aid in naming and documenting your objects.  

Uniform Resource Identifiers

The goofy naming system in both of the above screenshots are URIs.  A URI is merely a string of characters that are used as an object identifier.  How you contruct your URIs is entirely up to you.  In my previous examples in this blog series I named my objects using a "flat" URI.  Sample object names were "ExpenseMessageType", "ExpenseSubmissionContract", "AccountingService", etc.  That naming convention works well as long as your SSB design only contains a few objects.  With hundreds of these objects things become unwieldy.  All of these items, for example...contracts, are stored in flat metadata tables.  Searching and grouping becomes difficult without a good naming convention.  Note the screenshot with my poorly-named contracts interspersed with MS-supplied contracts.  

SSB objects adhere to the standard naming rules for any SQL Server object.  So, pick a URI that works for you.  A good URI starts with a protocol and then a hierarchy of objects from less-to-more specific.  Here are some examples of URIs:

• http://MyCompany.org/absolute/path/to/URI/and/resource
• ftp://SomeCompany.com/SSB/Contracts/Accounting/Expenses/ExpenseSubmission
• gopher://EnterpriseModel/Accounting/SSB/Expense  (if you remember the gopher protocol...you're old)
• URN:animal:mammal:human:Dave

Those links do not have to work but the general theory is that URI links should point to the documentation for those services.  But again, your SQL Server will not attempt to access those resources.  They are there merely for naming convention and documentation purposes.  

The Best Practice is to name your "exposed" SSB objects using a URI.  "Exposed" objects are those objects that can be consumed by "users" with the correct authorization.  Exposed objects are contracts, services, and message types...which are all specified during the BEGIN DIALOG CONVERSATION and SEND ON CONVERSATION statements.  Queues are not exposed so they don't need to follow this practice (but could/should).  

By using a taxonometric URI you avoid name clashes since object names must be unique.  For instance, I may model my SSB application such that messages of type "Status" are sent between services.  Simultaneously you may also be developing a new feature that uses the same named message type, which causes a problem.  Instead we can both use Status within a separate namespace within the URI.  If you're a programmer then basically you should set up your URIs like you set up your classes.  SSB is meant to communicate across db and instance boundaries. If you don't declare a namespace and take this into consideration you get even more name clashes and confusion. 

Some DBAs have adamantly disagreed with me and have insisted that the URI must really be a URL (an URL is a type of URI) and must be reachable.  Let's prove that isn't the case...you can download the repro script here.  We are going to create some oddly-named SSB objects.  

We have all kinds of naming conventions there and of course those URIs/URLs have no "presence" on my network.  Let's send some messages using those objects:

We just need to make sure that the messages arrived at the target service/queue and didn't error anywhere.

...and you can see that everything arrived as expected...

Summary

Lot's of DBAs are scared of SSB because of the URI naming.  I had a DBA tell me that all of the constant calls to schemas.microsoft.com would cause scalability problems.  Hopefully in this post I've put your mind at ease if you too were scared of these issues.  These URIs are just names and can be anything you like.  No calls are being made to external resources to validate the schema of a message.  

You have just read "Service Broker Demystified - Must my SQL Server be wired to the internet?" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.