Voice of the DBA

Author: way0utwest

  • The Titanic Cloud

    Nothing is Unsinkable

    Is a cloud provider unsinkable? This piece from CNN shows an image of the Titanic as it talks about Amazon’s recent Amazon Web Services outage, which took down parts of many sites, including Reddit, FourSquare and the New York Times.

    While I think Amazon has done an amazing job of building technology well, they are no different than any large provider. Google has had issues with their services and Hotmail recently had a failure that lost data for some people. Salesforce.com has gone down and Oracle’s “unbreakable” moniker has proven to be untrue, and the unsinkable Titanic wasn’t the first large endeavor to fail.

    What’s interesting to me is that AWS wasn’t down for me. I didn’t use any of the services mentioned at the time of the outage, so as far as I was concerned, nothing happened. That’s the luck of the draw and as we move to a more connected world, with our systems reaching people all across the globe, we have a large variety of customers who will never notice our issue. The flip side is that it’s more likely at least someone will notice any of our issues.

    There isn’t a foolproof way to build a computer system that runs 100% of the time. As much as every large provider in the “cloud” will try to convince you otherwise, there will be issues. If the cloud is right for you, and fits your business, make sure that you have good backup plans in place, consider architecting your systems to work across different providers, and ensure that you always, always, always have good backups.

    More than ever I think this mantra applies to people managing data in the cloud: good backup, good resume. You need one of them.

    Steve Jones

  • T-SQL Tuesday #18 Coming Next Week

    Other than the week I hosted it, I’ve been caught off guard by T-SQL Tuesday most months. This time I caught Bob’s Twitter announcement about #18. The topic this month is CTEs, so you have a week to get things ready.

  • Cascading Human Error

    One mistake could cascade

    I caught this short piece on the Amazon EC2 outageand while it does highlight a potential problem in the world of the cloud, there was one part of it that really stuck out to me. This quote in particular was one that has me worried about the future: “It’s the kind of error an operator could makes as a wrong choice on a menu or the entry of the name of the last network worked on instead of the one needed. In short, it was a human error that’s all too likely to occur with anyone momentarily preoccupied with the price of mangoes or a flare up with a spouse. “ That’s a little scary, and while it’s not necessarily a reason to avoid the cloud, it is potentially a reason to avoid the extremely large companies like Amazon and Microsoft. They have built these large infrastructures, using lots of standardization and hopefully, automation. In the places where humans do have to type commands or make configuration changes, it’s possible that they make a mistake. While that can happen in any size company, when it happens for Amazon, Microsoft, or Google, a large number of people can be affected. In many ways I think we have a problem with extremely large, standardized, and highly dependent infrastructures. While being able to deploy a configuration change to a 1,000 servers at once or reboot 800 for a patch is cool, it’s also potentially a problem. We used to manage nearly 1,000 Windows servers and patch the large majority at one time with SMS, but we also held our collective breaths whenever hundreds of them rebooted at once. One mistake, and it makes for a very long night. And a very long day when you are explaining the issues to management. I don’t think we will ever eliminate human error, but we can definitely minimize it. Strong QA processes should require automated deployments and ensure the exact same deployment run on QA is run in production. If we can stick to that type of process, regardless of the delays it might create, we can minimize lots of human error. Steve Jones

    The Voice of the DBA Podcasts

  • Foreign Keys Help Performance

    I have always put FKs into my database for data integrity purposes. I’ve worked on enough applications that didn’t have FKs, or any RI in place and it was always a nightmare when the application broke down or there were enhancements that allowed duplicates, orphans, or other data integrity problems.

    However I ran across an old post form Grant Fritchey that shows Foreign Keys do more than that. They can actually help performance because the SQL Server database engine knows that there is data in the related tables that matches because of the FK relationship.

    Does that matter?

    If you read Grant’s post, and you should, it shows two different queries of the same data, but one has FKs enabled. That results in a much smaller execution plan, hitting fewer tables. I took Grant’s test and added one more twist.

    I ran both queries in the same batch, with the execution plan. Guess what I found? Check out this image:

    query1

    Guess which query has FKs and which one doesn’t? If you read Grant’s post, you’ll realize the first one has the FKs, but more importantly, if you look at the relative percentages of the batches, you see that there’s a 9x difference in resources.

    Use FKs. They do more than protect data, they speed things up.