Voice of the DBA

Tag: SQL Connections

  • 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.

  • Leaving (for) Las Vegas

    Tomorrow I’m off to Las Vegas for SQL Connections. Since tomorrow is also T-SQL Tuesday #012, I’m writing a pre-trip blog a day early.

    vegas1[1]

    I’ve never been enthralled with the image of Las Vegas, but I went there earlier this year with my wife for a vacation and had a great time. I did finally gamble at a card table, and actually won money, but it’s not something that I’m overly excited to do again.

    This is my first trip ever to SQL Server Connections and DevConnections. I had hoped to go last year, but my wife broke her arm and I ended up cancelling my trip. This year SQLServerCentral is sponsoring a track again with some great speakers to teach you about SQL Server. We have:

    • Brad McGehee
    • Allen White
    • Buck Woody
    • Glenn Berry

    I elected not to speak so that I could see more of the show and report on what it’s like. With more and more people becoming hybrid DBAs, I think this is a great format for a show. Many people these days fine themselves needing skills in other technologies, like Windows, Sharepoint, .Net or something else.

    I am curious to compare this event with others, and see if it might be a great place for SQL Server professionals to get a wide variety of knowledge that can help them work with the different aspects of IT that they need in their jobs.

    I’m also looking forward to sitting down with a few friends I haven’t seen in some time and catching up on what’s happening in their lives.

    If you live in the area, but won’t be at the show, I’ll also be speaking at the Las Vegas SQL Server User Group on Thursday night, so come out and say hi.

  • Shopping

    In about a week and a half, the SQLServerCentral opening night party takes place at the PASS Summit. And while I won’t be there, I’m in charge of the prizes that will be going out to everyone attending.

    In the past I’ve usually done a couple big Best Buy trips to load up on prizes, but this year I tried a few new things. Up until about two weeks ago, I was worried about my budget since the registration numbers were down for my referral code. So I went looking for some lower priced prizes since I tend to value some level of quantity and try to get more prizes out to people.

    Fortunately, about that time I ended up with an email from ThinkGeek. With my kids by my side, we surfed around the site and ended up with a few things. I won’t list them all, but I did get some of these:

    I also did hit Best Buy and grab a bunch of DVDs that were on sale. Among the titles I got were:

    • Iron Man 2
    • Hancock
    • Matrix all 3 in one
    • Batman – all 4 in one
    • more!

    As I wandered around, however I saw some other interesting things and grabbed a few:

    and

    And since my budget was a little higher than expected, I grabbed nice prizes. I wasn’t sure that I’d get one of these, but I did (these are going to Connections, but I have another).

    In the past we’ve saved three big prizes for the most profitable gambling winners, though I’m not sure I’ll do that for the iPad. Likely I’ll have someone do a drawing from the tickets at the Exceptional DBA presentation and award it then.

    Good luck to everyone!

  • The Hybrid

    I’m off to SQL Server Connections next week and looking forward to the conference. This is my first time attending, but I have wanted to go for some time. This is one of the few events that is not specifically focused on one technology. In fact, it’s actually 7 conferences in one, co-locating events on ASP.NET, Silverlight, SQL Server, Visual Studio, Sharepoint, Windows, and Exchange.

    It seems that more and more of us working in information technology are called upon to work with a variety of products and platforms. While you might primarily be a DBA that deals with T-SQL or SQL Server administration, you probably are called upon to use other skills as well. You might need to help with Windows security, or Exchange administration, or even do some .NET development. As wide as the SQL Server platform is, it seems that often we have the need for skills with other technologies.

    Even if you don’t actually work in other areas because of regulations like Sarbanes-Oxley, understanding those other technologies can really help your career. You might better understand how to integrate SQL Server into your environment or better secure an application. You might even be able to offer ideas that  help a co-worker in their job troubleshoot a problem.

    I’ve felt that the most talented people in IT usually have a varied background and understand a wide variety of technologies. They bring that wide view of the forest to bear on a problem, and often find a solution because they can think laterally and are not tied down to the boundaries of a specific product.

    Steve Jones