Hi, everyone!
Today I'm going to introduce you to Service Broker, which is a suite of features in SQL
Server that provides transactional messaging and queuing services.
Using these features can allow applications to scale up or out, they can take advantage
of asynchronous processing, and they can even improve system reliability, depending on the
architecture.
While Service Broker was introduced all the way back in SQL Server 2005, it doesn't really
get the attention I think it deserves.
There are also a lot of objects involved in setting up a Service Broker application, so
people may shy away from it thinking that it's too complicated.
There is a bit of truth to that, especially when it comes to debugging, but the upsides
to having this functionality built right into a database is very compelling.
At this point, you're probably thinking this is yet another feature that has to be installed
everywhere it's needed.
Happily, that's not the case, and Service Broker is installed with every SQL Server
instance, whether you want it or not.
If a database engine instance is installed, it has Service Broker available.
Even Express Edition has it, although there's one minor restriction that prevents scaling
out using only Express Edition.
In this video I'll give an overview of the features of the Service Broker features by
way of a couple of broad abstract examples.
In each case, I'll show how SQL Server itself uses Service Broker to implement its own built-in
functionality.
Finally, I'll talk about the Service Broker database objects and what they do in more
detail.
So what does Service Broker let us do?
Let's say we have an application process that can be broken down into a sequence of distinct
tasks.
Maybe one of the tasks takes a long time to complete, but doesn't need to finish before
proceeding with the rest of the tasks.
Using Service Broker, the processing of the task itself can be replaced by sending a message
to another application to instruct it to perform the task and return a response when it's done.
This operation is transactional, which means it will either commit or roll back as part
of a regular database transaction.
This is great because if an error occurs somewhere else in the process, you don't have to worry
about those messages being sent when they shouldn't have been.
In fact, all messaging operations are transactional, not just sending.
Going back to the example, the abstraction -- or loose coupling -- of the task in this
way means a few different things:
First, the end user experience will be more responsive.
Making a request for some work to be done takes less time and fewer resources than completing
the work immediately as part of the larger process.
Second, if the system architecture is set up for it, the other application no longer
needs to be on the same physical machine.
Service Broker lets application ecosystems scale out very quickly and easily without
application changes.
Overall, yes, there is a bit more overhead, but it allows the use of other physical hardware
where it might not otherwise have been possible.
Even if the processing happens in the same database, the work can be deferred and then
only happen when the system is under reduced load.
As I eluded to earlier, there is a slight restriction when passing messages between
two instances of SQL Server.
Messages cannot be passed directly between two instances of Express Edition.
This is to prevent scaling out Service Broker applications beyond a single instance without
paying for the product.
If messages are sent within the confines of only a single instance, there are no restrictions
at all.
Third, if the system that does the asynchronous processing becomes unavailable for some reason,
the original process would carry on as usual.
Messages that are sent while the system was offline still end up in the queue, and get
delivered automatically when the system becomes available again.
Service Broker guarantees reliable message delivery, which lets developers focus on business
logic rather than error handling.
SQL Server uses this type of architecture to implement Database Mail, which not coincidentally,
was also introduced in SQL Server 2005.
When the Database Mail system stored procedure in msdb is called, this doesn't actually send
the message immediately.
It first does all the synchronous processing required, including things like validation
and running an optional user-defined query.
Then, instead of attempting delivery immediately and waiting for a response, a request to send
the e-mail, including all the necessary information, is placed into a queue.
The Database Mail external processing application is launched, which receives the requests from
the queue, actually attempts to deliver the e-mail messages, and then returns a status
back to Database Mail in msdb.
In a little bit, after I've explained how the Service Broker objects work, you can go
into msdb and inspect the Database Mail objects yourself.
While there's no visibility into the external application code, the rest of the objects
are right there in plain sight, and it's pretty informative to learn how Service Broker works.
Another way to use Service Broker is to deliver notifications, and take an action when a notification
arrives.
This is kind of like the first example, except that no response is sent back to the initiator.
A mechanism called activation launches a process in response to the queue receiving messages.
The process is responsible for receiving the incoming messages from the queue and taking
appropriate action.
Internal activation runs a stored procedure within the database.
Because the procedure is launched by SQL Server itself without a user directly invoking it,
a user principal must be specified when configuring activation.
Because no one invoked the procedure directly, this can make debugging the procedure a little
bit difficult.
SQL Server helps out by automatically directing user interactive output to the Error Log.
That said, you may find it more convenient to set up your own error handling and send
error messages to a table in the database instead.
External activation is where an external process, such as a .NET application, processes messages
waiting in the queue.
This type of activation is more appropriate when resources external to SQL Server are
necessary to complete the task.
In particular, when web services are involved, this is the method that should be used.
Both types of activation have the ability to launch multiple processes to handle a large
volume of messages in parallel.
This can happen if activation is configured for it, when the messages meet certain criteria,
and the actual processing of the messages takes long enough.
For external activation, you're free to multi-thread the application to parallelize things yourself.
SQL Server uses this type of notification delivery for a subsystem called Query Notifications.
An external application can subscribe to be notified when the results of a SELECT statement
may have changed.
SQL Server sends notification messages to a queue, where the client application picks
them up and takes appropriate action.
In the .NET Framework, several classes exist with a lot of this client logic already built
for you; the most common is a class called SqlDependency, which you may have heard of
before.
This notification technique is useful not only for alerting external applications that
some condition exists in the database, but also for doing things like client-side cache
invalidation.
Using Query Notifications is much more efficient than the usual alternative of polling the
database to see if the current state change, or to query the information just-in-time,
and not caching at all.
Now, just a caution, because you may be new to Query Notifications: the implementation
of this feature isn't free and it isn't meant to scale to thousands of client applications.
It's meant more for middle tier types of applications, such as web servers or for background processes.
Alright, let's move on now and talk about the Service Broker objects themselves.
There are quite a few, but if you remember that Service Broker is fundamentally about
queues of messages, you'll have no problem understanding them.
All the other objects are built up around the queue, so that's where we'll start.
A queue is just a holding area for an ordered set of messages.
Queues are implemented by SQL Server as views over internal tables; otherwise, there's nothing
really special about them.
The Service Broker commands that interact with queues implement all the correct locking
logic for you.
A message in a queue consists of a varbinary(MAX) field for the payload, and several metadata
fields.
By the way, in case it isn't obvious, in the diagram on the screen, you'll see the object
type as it appears in Management Studio, the DDL statement needed to create that type of
object, and also the system catalog view to view the metadata.
Some additional catalog views that expose the many-to-many relationships between some
of these objects will appear in the lower left-hand corner as we go.
The next type of object is services.
Earlier, when I was describing how messages get sent to queues, I was a little bit inaccurate
for the sake of simplicity.
Messages don't get sent directly to queues; they get sent to services, which then place
the messages on their associated queues.
Services expose the interface for interacting with the outside world.
This abstraction provides a named endpoint for communication and also other functionality
such as message validation.
As I mentioned before, a Service Broker message is just a varbinary(MAX) payload: there's
no special meaning or format to the message except that which the developer defines.
Since SQL Server implements XML internally as a binary structure, XML messages are a
natural payload for Service Broker messages.
Service Broker exposes ways to validate the format of a message by using an object called
a message type.
A message type can be defined to validate the payload in several different ways: to
not validate the payload at all, meaning it's simply a binary chunk and that's it; to validate
that it must be NULL; to validate that it must be well-formed XML; and finally to validate
that not only is it well-formed XML, but also that it conforms to the schema defined by
a XML schema collection in the database.
XML schema collections aren't specific to Service Broker, but I've included it in the
diagram anyways to show the multiplicity of the relationship.
Services send messages back and forth between each other, and so it becomes necessary to
validate the types of messages that are sent in one or both directions during the conversation.
A Service Broker contract specifies exactly that, and defines how the service should be
communicated with.
In other words, this is the service's interface or API.
Or, a service may not have a contract assigned, which means anything goes and there's no validation
at all.
Interestingly, multiple contracts can be associated with a single service.
When a conversation is initiated, though, only one contract may be specified, which
makes it clear what's going to happen during that conversation.
I'm going to cover the last three types of objects very quickly because they're less
important to understand than the ones already on the screen.
All of them deal with how local services interact with remote services.
Broker priorities specify that messages are sent or processed with different relative
priorities.
Routes associate remote service names with network addresses, which makes it very easy
to redirection without application changes.
Route objects created in msdb are used to direct incoming service requests to databases
in the local instance.
Finally, remote service bindings specify the certificate to use for encrypted conversations
when communicating between two different databases.
In a full Service Broker application, that's potentially a lot of different types of objects
in play, and Microsoft includes a utility called ssbdiagnose in every installation.
This utility can help debug Service Broker configurations and conversations.
Again, though, the most important Service Broker objects are the ones I've highlighted
on the screen, so if you learn those, you'll be in great shape.
If you're interested in learning more about Service Broker, peeking at Database Mail in
msdb is a pretty good place to start.
I've also put a link below to Microsoft's own Service Broker samples on CodePlex, which
are full examples with programmer comments.
If you're not already using Service Broker in your applications, I hope I've given you
some ideas about how to use messaging and asynchronous processing to make it better.
Service Broker can really provide a lot of advantages, especially since all this functionality
is built right into your databases.
If you liked this video, please remember to give it a thumbs up, and subscribe to the
channel to see more videos in the future.
Thanks for watching!