Voice of the DBA

Author: way0utwest

  • The Virtual Lab

    Over a decade ago, when the startup company that I worked for went bankrupt, I received a few servers and some other hardware as a partial severance. At the time it seemed like a good idea, and it was nice to have a few spare servers on hand. I would later use some of that hardware to work on SQLServerCentral. My wife, however, wasn’t thrilled since spare hardware, even powerful hardware, doesn’t buy groceries.

    These days the pace of technology change is high. SQL Server is a stable platform that many companies might install and use for a decade, but it seems we have new features to learn every 18-24 months as new versions are released. I think more and more of us have to understand a wider variety of features and platforms all the time, even if we use them sparingly. For many of us, that means some sort of lab if we want to gain any sort of skill with the products.

    One of the best things about virtualization, in my mind, is that it enables the average professional working with technology to build a huge lab for a fraction of the cost that it would have cost a decade ago. A relatively modestly priced laptop, something in the $600-800 range, will run most any technology you need, including Windows 2008 R2 Data Center in a VM. If you wonder how you can do this on a budget, check out Denny Cherry’s article on building a lab with VMWare or the Virtual Server series on SSC. You could do the same thing with Hyper-V or Virtual Box, and a subscription to MSDN or TechNet, something many of us get from our employers. If you don’t have access to those programs, there are evaluation editions of almost all software you can use.

    With an external hard drive, you could even have a portable lab that you can take with you anywhere.

    Steve Jones

    The Voice of the DBA Podcasts

  • Capacity Planning – Monitor and Extrapolate

    I wrote a post recently on capacity planning, and then thought of one more thing. In addition to planning for the future for new systems, you also need to regularly monitor your existing ones.

    Planning for the future, IMHO, isn’t something you want to do when you have a crisis, like running out of disk space. A good DBA is proactive, and looking to monitor resource usage and extrapolate out regularly to get an idea of

    1. when you’re overwhelm your current hardware
    2. how big to go on the next upgrade.

    Nothing worse than your boss complaining about performance, you recommending an upgrade and then having no idea what to ask for. You also don’t want to get caught in emergency situations when nothing has changes in your workload, other than you weren’t paying attention.

    I used to do this in a few ways, and there are numerous ones today. You can use a product like SQL Monitor, you can use the MDW from Microsoft if you have EE, you can use other products or roll your own. I have done all of these, except MDW, and they have all worked well.

    In terms of memory, I haven’t done this in some time, so I’ll let you get details from another source on how to monitor the usage over time on the modern versions. Here’s a good thread to get you started.

    In terms of CPU, I tend to monitor the raw CPU percentage over time, using a broad average across a whole day, watching the peaks. I would trend this out for 3-4 months, and see if it is increasing. Since CPUs can be hard to upgrade, you want to know this well in advance.

    Disk Space

    This is the big one, and quite embarrassing when you can’t capacity plan here. There are numerous ways to do this, but here’s what I’ve done.

    I monitor backup size every day and store it in a small administrative database. If I move the database, I move the backup information to the new instance as well. You can query the msdb tables for the sizes.

    SELECT DATEPART( yyyy, backup_finish_date) 'backup year' , DATEPART( mm, backup_finish_date) 'backup month' , DATEPART( dd, backup_finish_date) 'backup day' , database_name
, SUM(backup_size) 'backup_size' , type FROM msdb.dbo.backupset
 GROUP BY database_name
 , type , DATEPART( yyyy, backup_finish_date) , DATEPART( mm, backup_finish_date) , DATEPART( dd, backup_finish_date) ORDER BY DATEPART( yyyy, backup_finish_date) , DATEPART( mm, backup_finish_date) , DATEPART( dd, backup_finish_date) , database_name

    There’s a quick query, and you can perform some summaries of sizes by types of backup, convert to GB, etc. I used to run a similar query every day and do three things:

    • store the results
    • compare the last backup to the one before it.
    • extrapolate out the growth of backups to reach the disk size

    Obviously I wanted to track the results, and I would monthly create an old average of the sizes from last year so I wasn’t storing crazy amount of data.

    I compared the backup each day with the previous one to catch weird things. If the backups differed in size by more than 20%, I raised a flag. This was to catch changes in process, or potential errors from data loads or imports. Anything that changes 20% a day can be a problem, so this allowed me to find problems quickly.

    Lastly, I aggregated all backups on the instance, extrapolating out the growth across weeks, and estimating when I’d fill the disk. When this was inside of 2 months, I knew I better start asking for more space.

  • Ethics and Power

    Power corrupts.

    A famous quote, and it’s one of the the most true quotes I’ve seen in my life. When someone has power, they develop a sense of importance, their decisions seem to be more “correct” and their arrogance grows.

    It’s a sign of being human, and we have try to build in checks and balances into our governments and corporations to limit the abuses of power. We’re not any more perfect than our systems, and we continue to see our systems, and people, fall down.

    Being elected to a position confers power to an individual. It gives them responsibility, but it also seems to dull them in some way. The elected individual finds it harder to make the “right” decision, to stand up in an ethical way over time. It becomes common to justify their choices when they shouldn’t need to. A good ethical, moral decision shouldn’t need much justification; it should stand on its own, It’s the rare individual that can stand up for their beliefs in the face of criticism and follow their moral compass.

    The organization that I was proud to join a decade ago as a professional working with SQL Server has completely fallen down for me in many ways, and the leaders elected to positions have time and time again chosen to set their ethics aside for various reasons.

    “Let it not be said that no one cared, that no one objected once its realized that our liberties and wealth are in jeopardy. – Ron Paul “

    We don’t have wealth, or perhaps even liberties involved with our professional organization, but we do have pride, or we should have pride, and once again I have none.

    As the appointments were announced for the board of directors, I was not surprised, but saddened. An election was completed recently, with six people deciding to run, to devote their time and resources to serving the community.  Three were elected, by a narrow margin, and there were two resignations. With the election so recent, with so many others declining to volunteer for the board, one would think that it would be easy to appoint the fourth and fifth highest vote recipients to the board.

    How easy would that be? These were people approved by the nomination committee, validated by the community and voted for. Apparently the board felt that the nomination committee and the community must not have good judgment.

    Sri Sridharan, a friend, a volunteer, and an energetic worker on behalf of the Dallas community was not chosen. He has organized three SQL Saturdays, and was instrumental in bringing the SQL Rally to Dallas. He decided to run for the board of directors, something that few have done. Regardless of the qualifications of others, there is no argument that Sri is qualified for the board.

    UPDATE (2012/1/16): To be clear, I do not argue that James Rowland-Jones or Kendal Van Dyke are qualified, and I have no complaint with their qualifications.

    Apparently the board, led by its President, does not agree, and decided that someone the community wanted less, and someone that declined to run for the board, were better choices. I have no complaints against those appointed, but they did not deserve the position more so than Sri.

    I can only look at this as an ethical lapse by the board, taking their personal feelings towards a candidate into consideration over qualifications and community support. All those directors that voted for these appointments fall into one of the failings of elected officials, and they certainly do not represent me.

    It’s a sad day, one that has me viewing the organization as one that lacks professionalism, and simply an Association for SQL Server, without the P in front.

    Sri, my friend, my apologies to you as a member of the SQL Server community, and I can only empathize with you, having been in a similar situation.

  • WWBOD

    What Would Brent Ozar Do?

    I’ve titled this for Brent Ozar (web |@brento), What Would Brent Ozar Do, since he was the one that turned me on to this post about calculating your FU money. A polite way of describing FU money would be the money that allows you to live your life on your terms, without having to work again and without having to kowtow to anyone else, including your employer.

    When I was younger, I was very money driven, looking for new jobs or ways that would allow me to earn more money. I have been in and out of consulting, always looking to move up in the world. As I grew older, had a family, and (hopefully) grew wiser, I realized that most of the time I hadn’t be happier by making more money. Finding more money became a game, but the things that really helped me enjoy life were the challenges, the people, and the experiences I had in different places. It’s not my advice, and it’s not often followed, but it’s advice I think it well worth following: do something with your life that you enjoy.

    Most of us are not likely to get FU money early in our careers, but hopefully many of us will get close enough to live life on our terms when we retire. However this Friday, let’s have fun for a minute and think about what it would mean if you had the winning lottery ticket next week and received a check equal to your FU money.

    What would you do if you had the choice?

    If you didn’t have to work at your job anymore, would you go crazy like so many athletes. Would you keep your life relatively sane and embark on some other pursuit? Maybe you’re follow your musical dreams, like Allan Hirt. Maybe you’d go back to school and study something that interests you, maybe you’d move to Hawaii and surf every day. Drop a note in the discussion that lets us know how you’d change your life.

    I had this question put to me on vacation with some friends, and the first thing that popped into my mind is that I’d go work for Habitat for Humanity. I enjoy building things, working outside, and their mission is one that speaks to me. I hope that someday I’ll retire from technology and go work with them, and I feel confident it will happen. Someday.

    Steve Jones

    The Voice of the DBA Podcasts