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!