Voice of the DBA

Tag: Service Broker

  • Message Queues in Software

    Message queues are great ways to scale out your application, among other uses.
    Message queues are great ways to scale out your application, among other uses.

    I had high hopes for Service Broker when it was introduced in SQL Server 2005, but it doesn’t seem that many people have bothered to architect their applications to take advantage of it. I do see some people starting to use it, but it hasn’t been anywhere near the levels of adoption that I would expect.

    I ran across a piece on 10 reasons to use a message queue that points out a number of possible ways that queuing could help you. There are some great ideas, including a few suggestions for scalability and resiliency for your application. One of the more interesting ones to me is the idea of using a queue to buffer the slower processes that may be a bottleneck in your application.

    I still think this is a great way to build applications, especially distributed ones, using queues instead of linked servers, ETL, etc. However until we get more people developing in a service oriented architecture, and getting experience, I think we will struggle to see message queuing gain widespread acceptance. This is a departure from the way most developers are comfortable with building an application, and the way that queues work in SQL Server certainly confuses many DBAs.

    I’d challenge many of you to think about using queuing in any applications where you are moving data from one database to another, or trying to trigger an action on a remote machine. It’s a great way to scale out your systems, and it’s a very solid, reliable architecture for your systems.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Scaling Out

    Scaling Out
    One way to scale out

    When I first heard about Service Broker coming in SQL Server 2005, I knew it would be slow to gain traction, and many people would not see the power of guaranteed messaging. However I thought over the next 3-4 years it would catch on as we companies looked to scale out their databases to multiple physical servers.

    That hasn’t really happened, and it seems Service Broker has not been widely adopted by many database developers. It surprises me since it seems like a fantastic way to move data between servers. I’m not sure if too many data professionals think a message means a substantial delay in the movement of data, or if they don’t trust the architecture, but there don’t seem to be many people using this feature in SQL Server.

    They should, however, and I saw a great writeup on scaling out SQL Server with Service Broker. It talks about ways in which you might think about improving your application’s performance, especially at larger scales, but implementing Service Broker as a part of your architecture. It has advantages over replication, and it makes sense to me that this could be an extremely flexible way to handle your peak loads.

    However it isn’t the solution to all problems. It has a steep learning curve, the tools have not advanced very far, and the documentation and descriptions can be confusing and incomplete. I had hoped that things would improve over time, but this feature seems to have been largely ignored. However Service Broker and messaging are great technologies, and I’d encourage you to spend some time learning about it, and look for places in your environment that it might solve scale problems.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Service Broker – SQL Server Connections

    These are some notes and thoughts from sessions and my time at SQL Server Connections/DevConnections in Nov 2010.

    Denny Cherry gives a nice warning at the beginning of his sessions. His parents were sailors, so beware of the

    Service Broker is alien to so many people. I agree with that. The idea of sending a message, or a insert somewhere and having it process “later” in some other transaction is scary. People are worried. They don’t like the idea of asynchronous processing.

    However “later” isn’t some long time in the future. You can have queues process immediately and automatically, and “later” is often milliseconds after you’ve sent the message.

    There also is a guarantee of delivery in the queue, so you have essentially transactional consistentcy, just not everything on one transaction that must complete at the same time.

    People use XML to send the data since it’s a flexible format. I agree. As heavy as XML can be with all the overhead of the tags, it’s as flexible as things come, and it’s much easier for me to understand and work with than some type of delimiter that gets in the way of the data you are processing. Not that XML doesn’t have issues, but I think it works well.

    Denny says that you should have queries ready to check your queues when you set up Service Broker since the first time you do it, it probably won’t work. I can attest that I have had issues, which seem to be a combination of a confusing technology and a dearth of documentation that makes it easy.

    Mr. Cherry’s former company was sending 2-3mm messages/hour through Service Broker. That’s a good scale of things happening in an instance. Open conversations can cause issues, but if you can process them quick enough, then you will be in good shape.

    ssbdiagnose is a good tool (command line app) to help you figure out what is happening.

    Denny uses multiple message types to let the receiver know that something is done. He has a second message type that is a “conversation switch” in which he sends an empty message that lets the receiver know that this set of data is done. That’s a good technique and one I hadn’t thought of. I’d send some EOM in a normal message type, but having a second type makes some sense.

    Leaving retention on is a bad idea. No easy way to purge. That wasn’t something I was aware of, though I haven’t sent 2mm messages/hour.

    Denny also uses two queues, one for messages and one for acknowledgements. This prevents a line of acks from blocking other messages, which you might have with one queue.

    Watching Denny send messages in a basic send/receive SSSB queue, I can see why this hasn’t necessarily caught on. There’s no “wow” moment. Seeing a conversation take place and an acknowledgement are just not exciting. But there are huge possibilities here for auditing, distributed processing and more.

    Seeing messages moving between instances in SSMS isn’t that impressive. I think there’s a chance here to write something like the Database Mirroring demo app from Kimberly Tripp that shows queries being run in real time. A series of messages shown on different instances, and being processed would be a simple, nice, .NET app.

    No errors are thrown if you aren’t processing queues, which isn’t great, but it’s what I expected. So you need to make sure that you are properly watching and managing queues, especially if you have autogrow enabled on your databases.

    Messages are delivered in order, only on a single conversation. So if you have multiple conversations, the messages may not be in chronological order. Denny recommends a single queue and then periodically end the conversation with a random value and restart it.

    Service Broker is a neat technology and if you haven’t tried using it, you might want to check out its capabilities.