Voice of the DBA

Author: way0utwest

  • A traveling man

    It’s been a long year for me. This year I’ve attended:

    1. SQL Saturday #60 – Cleveland
    2. SQL Saturday #66 – Colorado Springs
    3. MVP Summit
    4. Rocky Mountain Tech Trifecta v3.0
    5. SQL Saturday #63 – Dallas
    6. SQL Saturday #77 – Pensacola
    7. SQL in the City – London
    8. SQL Saturday #64 – Baton Rouge
    9. SQL Saturday #91 – Oklahoma City
    10. SQLBits #9
    11. PASS Summit
    12. SQL in the City – LA
    13. DevConnections Fall 2011
    14. SQL Inspire NYC

    That’s a lot of trips, and a lot of time away from home. It’s also possible I missed one or two in all the chaos of the year.

    Next year will be much quieter for me. I’ve already avoided a few trips, telling some people that I would not be attending their events in an effort to slow down and not be so burned out with travel.

    It’s been a good year, but a hard one and next year needs to be a little quieter for me.

  • Not having TDE in all editions is stupid

    A must read for those looking to protect their data

    The whole point of encrypting data at rest is to protect the database if physical files or backups are lost. In the Books Online (BOL) page for Transparent Data Encryption (TDE), it notes that “…, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data…”

    That’s what TDE is designed for, but the machines that are most likely to be stolen, laptops that contain Express Edition instances, can’t implement TDE. Why not? It’s an “Enterprise only feature”. Why? I assume this is a sales technique to force those companies required to implement data at rest protection will pay more for their instances, but in reality this results in less security for lots of SQL Server applications.

    Recently a healthcare organization was in the midst of performing an encryption rollout to laptops and an unencrypted desktop was stolen with sensitive data on it. I know that TDE wouldn’t prevent this, but how many machines lose data that is unencrypted? How many applications built with Visual Studio store data, potentially sensitive data, on a local Express instance? I know that people can encrypt their entire disk (and they should), but what about their backups? What about copying a file to another machine? There are no built in protections, when there easily could be.

    I can understand partitioning, Resource Governor, and a few other items being Enterprise only, but a security feature? That strikes me as a poor decision all around.

    Steve Jones

    The Voice of the DBA Podcasts

  • No Overtime

    overtime clock
    Are you working too much?

    It’s been a long time since I received overtime at any job. Most of my IT work has been with a salary, and the expectation that I would work as needed to accomplish my assignments. Early in my career I worked as a contract for an hourly rate, and while it didn’t necessarily lower the hours I worked, I did receive a little more pay on long weeks. From there I worked for a company that provided “comp time” when the hours exceeded 20 hours in a month. That seemed to help reduce hours more than anything.

    There’s a bill that’s in the US Congress that adds some new job classifications to the exemptions for overtime pay. In addition to the system administrative jobs, it now includes database and network professionals. I’m not sure how many people this affects as the overtime requirements disappear once you make more than $27.63 an hour. That equates to about an annual salary of 57,491, without the benefits. If you make less than this, you should be getting overtime.

    Does overtime pay matter to most IT workers? I don’t know. There are lots of workers who make more than US$60,000 a year, and are exempt, but I suspect there are many others who make less than this amount and work long hours without any extra compensation. This bill is aimed squarely at reducing pay for those people in the industry that make the least amount of money.

    I struggle with whether the benefits and abuses of a salary. On one hand salaries provide a level of security to workers, along with a guarantee of payment for work that cannot easily be measured in terms of output per unit of time. On the other hand, employers sometimes see a salary as a way to push workers to their limits, burn them out, and impose strict requirements on the work needed for deadlines, themselves often arbitrary.

    I like the idea of allowing businesses to pay salaries, and stabilize their cost structures, but I do think we ought to implement a few bumpers that prevent abuses. Limiting hours across a month or quarter and ensuring that employees can take their vacations would be limits that I’d like to see implemented. This bill doesn’t affect those and I’m not sure we’ll see any limits implemented, but I do think it’s up to each person to stand up to their employer and ensure they are not overworking themselves.

    Steve Jones

  • Autogrow guidelines

    I always enable autogrow on my databases. However, it’s there for emergencies, not as a space management tool. I monitor disk space, and I grow my files manually as space runs low. I want to control the growth, but in the event of a runaway process or some unexpected event, I want autogrow enabled to hopefully prevent a database crash.

    What level of autogrow do you enable? That was a question I saw recently and it made me stop and think a bit. I asked the question on Twitter, but got very few responses and no real guidance from others.

    In my mind, you want to enable a specific level of autogrow that will be likely to handle something unexpected in a single, or maybe a couple growths.

    The two options for autogrowth are:

    • percentage
    • fixed size growth

    In general I think a fixed size growth is the setting to choose. This offers more control and as your database size grows, it’s unlikely you want to grow at a percentage. A 10% growth of a 100MB database is 10MB, almost a rounding error on many of today’s drives.

    However a 10% growth on a 2TB database is 200GB, which could easily exceed the free space on the drives that make up a file,which is where you set the autogrow specifications:

    autogrow1

    As the file size increases, the disk space goes down, but the percentage growth goes up as well, exacerbating the problem.

    So what are the guidelines?

    I think that you have to look at the data growth and each database and what is possible for data growth in that database. Some some database that does ETL work I think it’s more likely you could have an unexpected large or duplicate import in a process that would cause larger growth.

    However the question I was asked talked about setting a guideline for hundreds of databases, where in the short term it’s impractical to review every database. For those, I offer up these guidelines, though I’m happy to have someone give better guidance.

    MDF Size File Autogrowth
    < 1GB 100MB
    1GB < mdf size < 50GB 2GB
    50GB < mdf size < 200GB 5GB
    200GB < mdf < 1TB 10GB
    > 1TB 50GB

     

    These are guesses on my part, based on some experience, but the largest database I ever managed was a 600GB one, so I’m guessing on the TB scale.

    I’d welcome your comments and experience in this area.