How to use Service Broker to scale out SQL Server database applications

How to use Service Broker to scale out SQL Server database applications


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!

Daniel Ostrander

Related Posts

12 thoughts on “How to use Service Broker to scale out SQL Server database applications

  1. Janusz Maksymowicz says:

    Hi there. This is nice video but very low informative. After 13:38 min I still don't know what Service Broker is and what is the purpose of using it. You are talking like a robot or bored teacher on the same 1001 presentation. Please explain what Service Broker is, what is his purpose in general in simple words, put some simple non-technical diagram of it – like "if there is a 100 application, service broker is able to parallel the request from application or balanced the resources between application requests". In way that almost every technical person will understand. Not only some "few geek developers from your room in school". Come on, it's a video for people. BR,

  2. Janusz Maksymowicz says:

    Oh, and put some energy and positive attitude in your videos. Talking like a robot without any intonation is very hard to listening and understand above 1-2 minutes.

  3. Borka Siamese says:

    Hi there! We have not heard from you quite for a long. Is all alright?
    -Borka

  4. sahwa ahmed says:

    broker services receiving  requsest and sending  and used sql  and database

  5. autecuso says:

    It sounds like Service Broker could be customized to create a custom company-wide trouble-ticket/change control system when used in coordination with an sql server database.   Kewl.

  6. RadespotosR says:

    hello, why use service broker instead of threads?

  7. Asif Ashraf says:

    I am happy to know service broker. But SCALEOUT is a totally different concept. This means attaching more outsided resources to handle the load. You have not spoken on that point. How service broker can help on-demand in that regard?

  8. Clesben says:

    Video suggestion: High availability setup and config start to end

  9. Mercede says:

    For God's sake, next time put the camera far away and speak naturally.
    It felt like Siri just got a human body!

  10. VProjects says:

    Thanks for the informative video, I like the way you deliver the information – straight to the point.
    Back in 2005 I was excited by Service Broker (SB) intro, but actual implementation found cumbersome and we stopped supporting SB.

    Question: in these days (2018) how would you build asynchronous SQL queries processing?
    Would you still consider SB for messaging?

  11. Brandon Miller says:

    Thank you for posting this video. I'm wanting to do distributed computing. Right now, our system is dumping into a SB Queue and it activates several threads to process the queue inside of SQL Server. However, the CLR that it is calling downloads HTML from a website and sends it to an SMTP server. For whatever reason, this is much slower than taking the processing outside of SQL Server. I would like to write a distributed .NET Core app that works on the existing queue and make sure that it does not duplicate the work. Our customers do not want to receive multiple emails and they definitely want their email. Before, I relied a lot on SB to handle all of that and now I'm not sure what would happen if one external app received an item and started processing, but didn't complete.
    I've been searching for applications that have used SB and it is very hard to find them. They almost always use RabbitMQ.
    What are your thoughts?

  12. Hari Hariprasad says:

    Jon, I love your videos for their content. They are very informative. I also like your delivery style. Thanks for sharing your expertise! Keep up the good work.

Leave a Reply

Your email address will not be published. Required fields are marked *