Voice of the DBA

Author: way0utwest

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

  • Swarms

    First we had agile programming, designed to respond to quickly changing requirements for software development. Now we might have the addition of swarms as a way of getting things done in our jobs.

    One of the predictions that the Gartner Group has for the workplace in the next decade is the introduction of swarms as a more common way for companies to get things done. I think that a swarm is designed to be a rapidly formed, ad hoc group that comes together for a period of time. They are self forming, probably driven by one person that needs help with a problem and  either seeks out colleagues, or perhaps just somehow posts a request for help from people in the company.

    It sounds a lot like an action movie plot where the best soldiers are pulled together for a mission suited to their unique skills. Hopefully it’s more like a Mission Impossible team and not a Mystery Men one, though I wouldn’t be surprised to see more team formed like the one in the latter movie.

    This is really like the crisis team in a company, or a new project team  crossing multiple departments, but organized in a less-formal, and much quicker manner. I do think that more and more we will be called on to do work for people who are not our supervisors as businesses look to be more reactive and responsive to changing requirements.

    I can see us evolving some businesses to having a “guidance counselor” who approves our expense reports, performs periodic reviews, helps us manage our careers, and is available to discuss issues. I think many of us might prefer this to a direct manager that is looking to assign us work. It might be more interesting to go find your own work, or respond to requests from others inside the company.

    Steve Jones

  • T-SQL Tuesday #12 – Why Do You Need a DBA?

    TSQL2sDay150x150 It’s once again time for T-SQL Tuesday, the brainchild of Adam Machanic (Blog|Twitter). It’s a monthly blog party, designed to get bloggers to all write on a single topic on the same day. This month we have the party hosted by Paul Randal with his topic of why are DBA skills necessary?

    I enjoy participating, and if you want to host one of the monthly events, please contact Adam and get on the schedule. It’s not a lot of work, and it will be something that I’m sure you’ll enjoy.

    Is a DBA Needed?

    I’ve wondered about this at a few jobs. I know when I’ve automated checks and maintenance, gotten a solid DR plan running, and tuned most of the queries that I can, life is pretty easy as a DBA. I’ve often had time to try and learn more about SQL Server, be more pro-active with developers, and even spend a few minutes at the water cooler. After 6-12 months at a few jobs, I’ve often felt like I had things in control, and I could have worked 24 hours a week and had a very stable environment. 

    In fact, I tried to convince one boss of that, offering to take a pay cut and maintain his environment in 3 days a week. My boss didn’t go for it, mostly because the company didn’t want any part time people, but I think I could have saved them some money and been fine with the occasional consulting job.

    Not every environment is like that, but so many consultants walk into jobs where the SQL Server isn’t performing well, and the applications are slow. A crashed server can become a major event, and even cost the company significant revenue from systems not being able to handle the load or being down.

    DBAs are Insurance

    At the base level, a DBA provides some insurance for one of a company’s most valuable assets: it’s data. Many DBAs, even those that don’t know how to tune queries or design databases can implement the basics of the job in two areas:

    • backup/restore
    • security

    Without a doubt there are plenty of people that need to improve these skills, but most of them are better than the average system administrator. A DBA that understands these skills can really save money in a company that has any sort of disaster involving a database. I have seen many Windows administrators relying on their tape backup software and an Open file backup driver, or an agent based backup solution, most of which seem to fail when disaster strikes.

    In terms of security, so often a Windows administrator will grant all read/write rights to people accessing SQL Server. Developers can be even worse, granting db_owner privileges, or even using “sa” in their applications. A DBA can ensure that more granular rights are set up, which reduces the risk of any security issues.

    Even an expensive DBA provides some good insurance for a company that can pay off the first time a database needs a restore.

    DBAs are an Investment

    However someone that has strong skills working with SQL Server can be so much more than insurance. They can be an investment that vastly improves the amount of work that your databases systems can do.

    DBAs can tune your systems. They can ensure that SQL Server is set up properly, and that proper indexing is set up. The time savings can be a huge factor in allowing employees to be more productive and produce more revenue. If you develop pieces of your own software, a DBA is a must to ensure that you have well-written code.

    DBAs can find patterns in your data. I think the DBA is critical to ensuring the intelligence aspect of a BI system. The value of that should not be understated. As we capture more and more information about our businesses, it can be easy to start mis-interpreting or mis-understanding the meaning of that data. A good DBA will understand the business and the data, and help the company to understand what information is contained in the data.

    Not always, but often

    Not every company needs a full time DBA. I wouldn’t expect a small business that runs COTS systems with a system administrator to have one. But many companies, and likely every company that develops software based on a database (either in-house or shrinkwrap), should have DBA skills.  Having someone that understands the database, and can guide its use just prevents so many problems in the future.

    You might not need a full-time DBA, but you most likely do need those DBA skills if you have a SQL Server running in your data center.

  • Finding Your Dream Job

    My pre-conference session for the SQL Rally conference in the spring is called “Finding Your Dream Job”. Chris Shaw ( LinkedIn | blog | @sqlshaw)  and I put this together based on a few other presentations that we’ve both given before.

    Whether you vote for us, Kevin Kline, or Joe Webb, go vote. All of the sessions are good, and depending on what’s interesting to you, pick the one you’d like to attend.

    Vote Now

    Why Us?

    It’s a good question. After all, is it worth $59 or $99 for a day to get some ideas on how to work on your career? Chris talked a little about why this might help you in his blog. To add to his points with a few of my own.

    Promoting one’s self is hard: I find so many people that struggle with promoting themselves. I think we’re all fairly humble at heart, and that’s great.

    Except when you’re looking for a better job. Then you need to stand out and we’ll give you ways to do that.

    Learn the tools: So many people don’t know how to use the tools available to them. Or they might not even know of tools. We’ll talk about, and give you some good cheat sheets, the tools that you can use and how to use them.

    Tips and Tricks: How do you network with people? What’s a good way to blog to show off your knowledge? How do you handle the interview when you’re stumped? We’ll give you these answers and more. Ultimately it’s up to you to close the deal, but we’ll help you be prepared so that you look your best when you’re in front of people.

    It’s a competitive world. Stand out in it and we’ll help you learn how to do this.