Voice of the DBA

Category: Editorial

  • Locking Your Disk

    disk drive
    You should be protecting your disks with encryption.

    This editorial was originally published on Feb 28, 2008. It is being re-run as Steve is on holiday.

    With the tremendous growth in disk sizes and the trend towards more and more people using laptops, someone sent me a note about protecting that data that got me thinking. The SQL Server space has grown tremendously, and not just up. We have SQL Server Express and SQL Server Compact Edition, both of which are designed to run on smaller devices, with (supposedly) smaller data sets. However my phone has more storage than quite a few hard drives I’ve owned in my life and my new laptop rivals the storage in my 3 year old desktop.

    It seems that often that administrators don’t think about the data that gets moved off their servers and with new replication and ETL technologies, a significant amount of data might be duplicated on other instances of SQL Server away from your primary database.

    And your boss might be expecting that you’ve ensured its security.

    So this Friday, I had a poll to see who’s thinking about the issue.

    Do you use disk encryption?

    Or do you think it’s important? Should you be using it for your laptops?

    My laptops really just carry my writings, articles I’m editing, books, etc., and no financial information. I do have Password Safe on them with databases, and I suppose that someone could crack that if given enough time. However since I’m not carrying around data that involves other people, I haven’t worried about it.

    I used to have an encrypted disk when I worked for JD Edwards. At first I thought it was a pain, but after a month or so, it wasn’t a big deal. Except when I had booting issues one morning. Talk about being anxious for a few hours. Fortunately the manufacturer has included utilities and a help desk person was able to get my disk unencrypted and then re-encrypted again.

    Disk encryption is a good idea and it definitely protects accidentally releases of data. If you carry around client data, financial information, or identity information, I’d highly recommend you use some type of encryption to protect the data. However, as this article shows, that might not be enough.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed: or now on iTunes!

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.

  • Happy Thanksgiving

    It’s Thanksgiving in the US and I’m in the mountains of Colorado enjoying the holiday while skiing with my family. Hopefully many of you are having a quiet day if you’re at work, or enjoying the day off if it’s a holiday.

    I’ve included a few humorous mistakes from the past month’s podcasts, so enjoy them and I’ll be back next week with new thoughts on the world of databases and SQL Server.

    Steve Jones

    Windows WMV Bloopers

    iPod MP4 Bloopers

  • The Identity Debate

    This editorial was originally published on Mar 5, 2008. It is being re-run as Steve is on vacation.

    The Identity property might be one of the most controversial constructs in SQL Server, or at least right up there with the NULL/No NULLs debate. Someone sent me a note awhile back about this debate and I thought it might be worth bringing forward in an editorial.

    When I was getting started in SQL Server in the early 90s, I learned about primary keys, natural keys, and the ways you go about modeling a system. It all made good sense, especially at the time when I was working with systems that took years to build and were based on very well understood processes with tight controls on the data involved. So I learned to look for natural keys.

    A few years later I moved to a smaller company, considerably more agile, that was trying out new way of doing business. In the process of trying to revamp their systems, I found that things I was told were “unique” or “set in stone”, weren’t. Not even close, more like set in Play-Doh. And even then in a very humid environment with no danger of things drying out.

    I ran into identity field at some point and found that they were lightweight, solved a lot of problems, didn’t interfere with natural keys, and above all, were easy for a DBA to remember and work with when building applications or troubleshooting issues.

    However there is often a huge debate out there on what the value of identity colums is and how should they be used. Some people, like Joe Celko, rail against their use in a relational system. Others use them everywhere, making them clustered primary keys for every table. Many people are somewhere in between, using them where it fits, and sticking with natural keys if they are identifiable. Some people even use identities as their primary key, relegating natural keys as alternates with a separate unique index.

    I don’t think we put out enough information here, or on any other database site, that would help people better model their data and build relational systems. It’s a hard topic to write about and it’s hard to do it well without making everyone’s eyes glaze over.

    But I wanted to put this out there and see what you think about identity columns and their use (or mis-use) in today’s databases. They are definitely a tool that’s available to the SQL Server professional and one that I bet everyone has an opinion on. We surely value those opinions and as with many of these debates, I’m looking forward to learning a thing or two.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed: or now on iTunes!

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.

  • A New World Record

    TPC Logo
    SQL Server is doing well in the TPC benchmarks.

    This editorial was originally published on Mar 4, 2008. It is being rerun as Steve is on vacation.

    Editor’s note: The current TPC-E record is 4,614.22 tspE.

    The product hasn’t even been released, but there are already two TPC-E benchmarks completed for it. Neither of these is a world record in price performance, but the NEC benchmark takes the top Performance slot with 1,126tpsE transactions. That’s nearly double the previous SQL Server 2005 mark from Unisys, but to be fair it was twice the CPUs and cores, though the same number of threads. And a significantly larger price tag.

    What’s even more interesting is the IBM entry, which took 3rd place on the Performance chart and 5th place on the Price/Performance chart. However this system is a smaller system, a 4 way system (quad core) that used the same amount of RAM as the 2nd place system (128GB) and much less than the top performance system (512GB).

    There was also an ETL world record, where Len Wyatt posted a blog entry about his team loading 1TB of data from flat files, moved across a network and loaded into a SQL Server database in less than 30 minutes. The actual time was 1794 seconds, which I’m sure excited Len and his team.

    Now benchmarks are just that, they’re a way to compare systems using a common set of application software and measurements. They don’t necessarily apply to the real world as there are all sorts of tricks (disabling checkpoints, RAW volumes, etc) that companies play to tweak these systems. Some of these shouldn’t be allowed in my mind, like RAID 0 disks, which wouldn’t be in a production system.

    These are new benchmarks, so it’s hard to tell what impact or relation they have on the real world. These new systems also used Windows 2008, which might have affected the speed at which things get done. There are definitely networking improvements in there, so time will tell if these are really, really fast systems or just what we’ll expect from the new versions of Microsoft servers.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed: or now on iTunes!

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.