Voice of the DBA

Author: way0utwest

  • Hadoop and SQL Server

    There has been a lot of media attention to Hadoop in the last few years. In fact, Microsoft has spent a lot of resources to build the HDInsight version of the platform and integrate it into SQL Server. I’ve read quite a bit about how to setup and query with Hadoop, but haven’t used it for a real project. In fact, it seems relatively few people seem to be finding it to be a replacement for, or better solution than, SQL Server. We published a great introduction to Hadoop written by David Poole awhile back, and recently I ran across another nice writeup from someone I think is a very talented SQL Server professional.

    Michelle Ufford (@sqlfool | b) wrote a piece asking if Hadoop is better than SQL Server. Michelle notes that Haddop is a different platform, and it’s a great way to consume lots of data. In fact, she has a graph from EMC talking about the data explosion and how we still at the low end of the exponential growth curve of data production. It’s a sobering thought and I tend to agree with Michelle and EMC on the growth of data.

    I had hoped Microsoft would do more with Filestream and Filetable to help meet the challenges of large volumes of data, but it seems that very little has been done with those features in the last version of SQL Server. I have little hope that additional investment will come in the future. Instead, it seems Microsoft is leaning towards using Hadoop as one way to process and consume large volumes of data.

    I wrote about Hadoop in 2009 when it was a young project, and I suspected it would enhance and work with, rather than supplant, the RDBMS. There are certainly other technologies out there to help with this, but if you are working with large volumes of data that exceed what a single instance of SQL Server can handle (at a reasonable cost), you might think about learning a bit about Hadoop. It might not solve your issues, but if it can, it would be good to know something about it.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • T-SQL Tuesday #62 – Healthy SQL

    tsqltuesdayThe invitation for T-SQL Tuesday this month comes from Robert Pearl. It’s called HealthySQL and it’s a topic I like. I’ve spent a lot of time in my career being proactive with SQL Server databases, and keeping them healthy is important.

    If you’d like to participate in the party, just write a blog post and publish it on the second Tuesday of the month.

    However, if you’d just like to participate, take the topic anytime and write your post.

    Healthy SQL Server

    I would guess most of us would prefer to be healthy over being sick. While it’s hard to be extremely strict in taking care of ourselves, many of us do make efforts to eat better, exercise, etc. to maintain our physical health. If we don’t, then we get sick and are usually miserable.

    The same thing can happen to our SQL Server instances, and if they get sick, many of us are miserable as well. Mostly because we might be working late, working overnight, or being yelled at by managers.

    I learned a long time ago that no matter how well I take care of myself, I’ll get sick at some point. And no matter what I do for my SQL Servers, at some point they’ll have issues. However if the issues could have been easily prevented, it’s embarrassing and it calls into question my capabilities as a DBA.

    I’ve got lots of stories of how I’ve prevented issues by keeping my servers healthy, but there are really a few simple things I’ve set up and worked on. I included these in my Avoiding a DBA’s Worst Days with Monitoring talk. I need to blog about these in more detail, but here they are with a few notes.

    Backups

    The core of any data system is the backup. If you have the data in a backup (and the backup is good), then you can recover from any other issues. As a result, I’ve tried to be sure that I have backups setup, monitoring on the backup jobs, and a process to make a copy of the backup somewhere else.

    Space

    Running out of disk space is one of the simplest things to avoid and one of the most embarrassing to deal with. I’ve written about placeholders, but you should be monitoring space, have placeholders available, and proactively look for more space as you get low.

    Security

    Security is important, and we read about issues all the time. However apart from hackers, loose security often means that users, developers, or anyone else can cause issues in your database. Tight security can be a pain, but it really prevents a lot of issues.

    Resources

    Resources refer to the hardware and software that allow your systems to function. If you don’t have enough resources for your workload, no one is happy and you are listening to complaints. Being proactive, looking to tune queries, reduce fragmentation, add indexes, reindex, update stats, and more are important here.

    Deployment

    Face it, we’ll always be changing our systems with new patches, code, etc. Building a decent test routine is important as we want to prevent deployments that will make our servers unhealthy. However having a plan to deal with issues (because you will have issues) means thinking about rollbacks or other methods of undoing problems.

  • It’s a Problem When a SysAdmin Can’t Log In

    I showcased a demo recently that looked at a potential issue with an application where a user used a simple update statement to gain privileged access to a system. That’s scary, and it’s a potential issue for many applications that handle their own authentication.

    The problem with this type of attack is that it can be hard to detect. A system administrator might not log in with their account for some length of time, during which the attacker has free reign in the system. However even if the administrator logs in, they might not detect the attack.

    How often have you entered your credentials for a system and it informs you that something was typed wrong. How many times have you locked out your account accidentally with too many bad password entries?

    Have you ever thought that your account was under attack?

    I know some people will have their account unlocked and retry their password, but I know plenty of administrators that would just reset their password.

    That’s bad.

    You should always check the login times for your privileged accounts. In fact, I’d like to be sure that you are using your privileged accounts regularly so that if someone were to gain access, you’d know it. Or if someone changed your password, you’d be aware.

    Treat your sysadmin account for what it is: a privilege, and a target for attack. We are seeing more and more attacks on our systems, and I expect the problems to only grow, not shrink.

  • Selling Automation to Ops

    The DevOps movement isn’t new in some companies. It’s the same coordination and teamwork that has existed for a long time between the development and operations staffs. Developers take advantage of the skills in Operations to get standardized environments for their work, and let the Ops people manage (and track) changes. Operations people talk to developers about the challenges and issues faced in production, and the let the developers build applications that can easily be deployed. The sharing of information ensures each group knows what the other faces, and the regular contact builds bonds and respect between employees. Neither wants to let the other down or make someone else’s job any harder than it needs to be.

    However that’s not the case in many companies where developers view Operational staff as complainers that slow the process down. Operations staff see developers as wild and irresponsible, tossing code into production that they don’t need to support and haven’t tested. Both of these views are correct in that each side sees a reality in the process that makes their job more difficult.

    Ultimately I believe it’s up to developers to change things. Those of us that build the software need to respect the problems that instability causes and learn to help ensure that our changes can be deployed smoothly. The development side of an organization has more skill in tracking changes in version control, in managing the movement of those changes among environments, and in programming systems. We should be working to help push that knowledge through to Operations personnel that become responsible for our applications.

    That means we need to build scripts and tools to migrate our changes and give them to Operations. I’d recommend that we learn how to automate the configuration of our development systems, as well as script our changes. Most modern platforms allow us to programmatically make changes, so let’s do that. Then let’s take a few hours and show Operations people how to use these scripts, and let them setup and change our development environments. It will be slow at first, but they’ll learn to make changes faster, but also bring stability to every environment from development to QA to production, and can ensure we have the same configurations everywhere. We’ll have one less thing to manage, and our changes will get deployed faster, but also more consistently.

    Ultimately we all want the same thing. Better software delivered to customers faster. We want to Ship Safe, and Ship Often.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.