Voice of the DBA

Tag: syndicated

  • Going Big with SSDs

    Nimbus Data just sold 100TB of SSDs to eBay

    I found a report recently that eBay had implemented 100TB of SSD storage from Nimbus Data for their virtual infrastructure. That’s a huge amount of very fast storage, which definitely helps explain how the eBay auctions seem to run so quickly. Apparently eBay isn’t the only company that’s moving to SSDs for part of their infrastructure as Facebook spent $69million on FusionIO storage.

    SSD storage isn’t cheap, with eBay reporting $25k for a 2.5TB device, which is about 2.5 times the cost of my rough pricing from Dell for a similar HDD device. The SSDs have amazing performance, though I’m not sure what the reliability is compared to HDDs. There are plenty of vendor studies that promise long lasting drives, but I think we’ll have to see what more people report as they deploy these drives over time.

    I’ve never worked in a company similar to eBay or Facebook that had a very large IT budget and could afford to over-spend to achieve high levels of performance. I’ve always had to analyze the price/performance ratios and choose hardware that met our needs, at a reasonable price. However I am glad that companies that need high performance are willing to try new technologies, and hopefully release the results of their investment at some point in the future.

    More and more people are starting to implement SSD drives, but not as their only storage. I find companies using these drives in limited places, as tempdb drives, log drives, or for certain filegroups. The premium paid for these drives means they need to be used where they provide that highest level of performance for the price, which isn’t every place you store data in SQL Server.

    As SQL Server DBAs, we need to learn more about the impact of SSDs in different parts of our systems, plan for them to fail  more often than HDDs for now, and more importantly, consider designing in some type of archiving or partitioning strategy that can tier storage into faster, and slower, sets of data.

    Steve Jones

    The Voice of the DBA Podcasts

  • When Do You Take a Full Backup?

    This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

    When is a Full Backup Taken?

    Most people might answer this with “every day”, or “as often as needed”, but those are a little nebulous, and not necessarily correct.

    I do recommend that you backup as often as you can, which is every day for most people. Those with large databases might go once a week, or even once a month, but you want to get a full backup periodically. If you have a really large database, say 50TB or greater (in 2011), you might opt for a yearly SAN snapshot of some kind, but you need some full backup as a base.

    So when do you backup your database? Here’s a list of some times you might think about:

    • As often as you can on a regular schedule (daily, weekly, monthly, etc)
    • Before applying a Service Pack or CU*
    • Before applying a Windows patch*
    • Before upgrading your application*
    • Before a large data load*
    • After restoring your instance in a DR situation

    That last one might throw people, but I’ve seen more than a couple situations where someone recovered a database in a DR situation and never setup backups. And there was another failure.

    Whenever you create a database, either from scratch or after a restore, make a backup.

    * Note that if you have Enterprise Edition or above, you can use Database Snapshots to substitute for a full backup in some of these situations.

  • Two Types of Tail Log Backups

    In a recent thread I noted that a tail log backup is essentially a regular log backup, but made with the intention of restoring the database because something is wrong with your data file. Gail Shaw (blog | @SQLIntheWild) pointed out that that’s not quite true. There are two parameters that you need to add to the BACKUP LOG command. Thanks to Gail for the correction, and here’s a little more data.

    There are three options you have with a tail log backup are:

    • WITH NORECOVERY
    • WITH CONTINUE_AFTER_ERROR
    • WITH NO_TRUNCATE

    I covered the third one in doing some practice backing up of the tail log. The second one is noted in Books Online as one that you should use in the event that the database is offline and inaccessible. That will allow you to recover the last log backup (hopefully).

    The first one is recommended as the one you use when the database is going to be restored and you want the end of the log.

    As I mentioned in my previous post, this is a core DBA skill. It is what will allow you to recover a database with zero data loss.

  • A Really Bad Day

    I was out of town for work one day, literally on the other side of the country, in a car bound for the remote office when my cell phone rang. I had flown into LaGuardia Airport in New York City and needed to drive to Connecticut, to an office just outside of Hartford. The company was paying, and it was more convenient for me to fly into NYC and then take a chartered car to CT.

    I glance at caller ID and it’s a client of mine back in Boulder, CO who I do some occasional SQL Server work for. I answer, thinking that he needs help with some SQL command and I can score some brownie points here without charging him.

    “Steve, I have a problem”

    The words that anyone in technology is loath to hear. Even if you’re being paid by the hour, clients that cause issues are often going to be more trouble than the payment is sometimes worth. I much prefer to hear “Steve, we’d like to do x. How can we do that?”

    I ask him to tell me what’s wrong and he said that RAID card was throwing errors on the development server, so he performed a SQL Server backup, copied the backup to a file server and then replaced the RAID card, rebuilding the array. When he went to restore the server, he got an error.

    I am the backup and restore champ, at least of my little world. “Read me the error, “ I say, confident I can solve this for him before we arrive at the office..

    “The media set has 2 media families but only one are provided”

    Uh, oh, I’m thinking, and not because of the poor grammar. This is not good and I’m not going to be able to fix this. Breaking bad news to a client is never fun, even if it’s not your fault. I’ve lost a couple because they were so upset they let me go at some bad news. That’s actually OK with me because if they can’t tell the difference, they are likely to leave me when I least expect it anyway and I’d rather know sooner than later.

    I explain that he must have used two backup files and he needs to add the second one in there. The guy is insistent that he didn’t pick two files when he made the backup and he copied the right one off. He even included the date in the filename (like I showed him) and reads it back to me.

    I know what’s happened. He’s made an inadvertent striped backup, and since the RAID array was blown away, he’s likely lost the other file. I try to keep from laughing as I explain patiently what happened. He’s upset, then he’s confused, then he’s unhappy. I can picture the sad panda face on him from 2,000 miles away as he realizes that he’s not getting this restore to work. The driver must hear it in my voice as well since he’s smirking in the rear view mirror.

    To add misery here, he wasn’t copying backups off this development server to save space on the file servers. He’s lost a few weeks of work now, and he has to break the news to the developers, who likely don’t have backups of their own.

    I sign off, not sure what to do as I get to my destination. It’s a good example of why SQL Server still needs a DBA, or someone with decent DBA skills. Despite the ease with which you can get many things done, not understanding why you do them cause big problems.