What’s the Largest Database You’ve Worked With?

When hiring a production DBA, I start by asking them, “In terms of file size, what’s the largest database you’ve worked with?”

In a perfect world, we’d only hire very experienced candidates, people who’ve worked with larger databases than we’re dealing with. In reality, though, we can’t always afford those candidates – if they’re even available at all. For some positions (like a junior production DBA with a senior already on staff), we can live with a lower level of experience – someone who can grow into the position. In other positions (like a senior lead), we can’t afford to have them learning on the job.

Non-DBA readers: to find the answer to this question, remote desktop into the shop’s largest SQL Server and do a search for files with MDF or NDF extensions. Add all of the file sizes together, and make a note of the largest one. For example, the total size might be 200 gigs, with the largest file being around 100 gigs.

I generally classify servers into these vague size groups, going by total size of all databases on the server:

  • Under 50 gigs – these servers almost run themselves once the basic maintenance plans are set up. The Windows system admins manage the SQL Server without training, and management takes less than an hour per week. Everything is installed on the C drive without serious performance issues.
  • 50-250 gigs – The system admins encounter some performance bottlenecks. Initial setup should be done by a DBA, and basic maintenance plans may require some planning and regular attention. Requires some performance tuning, but can be learned by a new DBA. Management takes less than 4-8 hours per week once the server is set up correctly, but the initial configuration repairs (when a DBA takes over for the first time) may take a few weeks.
  • 250GB-1TB – Requires a full time database administrator on staff, although not one DBA per server. Backups must be done with specialized backup compression software, index and statistic updates may run for hours. Performance tuning should be done only by staff with experience. Management takes 4-16 hours per week once the server is set up by a trained DBA, but initial configuration repairs can take a month or more.
  • Over 1TB – Even the simplest tasks require experience from other servers in this tier.

Database administrators have a tough time upgrading their skills from one tier to the next without hands-on experience. A DBA can manage more servers of the same tier without training, but the larger the server gets, the more planning and training it takes to do a good job on maintenance.

DBAs will also rarely step down in size, because pay goes along with the size of the environment. If the candidate says they’re used to working with terabyte-sized data warehouses, but they’re willing to take a job managing a couple of 100gb servers, start asking why.

If the company can’t find or can’t afford a DBA with experience on their size of database servers, there are three workarounds:

Training from an experienced local mentor.

Ask each candidate a tough question: “If you ran into a problem you couldn’t solve, do you have anybody in town that you could call in to help?” They might have a contact, someone they’ve worked with before, who would be willing to lend a hand. If not, there’s always consultants, but their skills can be tough to judge too. Expect a junior candidate to need mentor help for a few days per month when upgrading their skills from one tier to the next.

Buy software to make the job easier.

Companies like Quest, Idera and Red Gate provide a lot of tools to automate database administration. They build experience into the tools, making it easier for junior DBAs to pinpoint performance bottlenecks, solve index and statistics problems, and automate manual tasks. Sure, the software costs money, but the upside is that a junior DBA with good software tools can sometimes outperform a senior DBA who does everything by hand. (That excludes the >250gb tier, though – get an experienced pro for that tier.) Another advantage is that these tools persist even when the staff turns over, and they’re reusable over time.

Send the DBA to a training school or camp.

I have to be honest: I hate this option. The trainers are all too often “book experts” who don’t have real-world experience. The school curriculum moves at a fast pace, spending a set amount of time on each of many subject areas that the junior DBA may not need help with. Instead of spending a week at a boot camp covering a zillion topics, the junior DBA would get much more help from a week of targeted, hands-on time with an experienced pro looking at their system’s specific problems. That way, the junior learns exactly what’s applicable to the problems their company is facing, not canned solutions for things they won’t encounter for months or years.

More DBA Career Articles

  • Moving from Help Desk to DBA – a reader asked how to do it, and I gave a few ways to get started.
  • Development DBA or Production DBA? – job duties are different for these two DBA roles.  Developers become one kind of DBA, and network administrators or sysadmins become a different kind.  I explain why.
  • Recommended Books for DBAs – the books that should be on your shopping list.
  • Ask for a List of Servers – DBA candidates need to ask as many questions as they answer during the interview.
  • Are you a Junior or Senior DBA? – Sometimes it’s hard to tell, but I explain how to gauge DBA experience by the size of databases you’ve worked with.
  • So You Wanna Be a Rock & Roll Star – Part 1 and Part 2 – wanna know what it takes to have “SQL Server Expert” on your business card?  I explain.
  • Becoming a DBA – my list of articles about database administration as a career.
Previous Post
SQL 2008 release date pushed back
Next Post
SQL Backup Software: Part 4 – Features Still Missing

12 Comments. Leave new

  • Just for fun, I’m going to come up with interesting things that “SQL” could possibly stand for, since I obviously have no idea what you are talking about. (If my silliness is going to offend some of your more serious readers, just let me know and I’ll stop. Wouldn’t want to embarrass you!”

    SQL: Squirrelly Querulous Lynchings

    tee hee hee. I’m going to have fun with this!

    Reply
  • It’s Structured Query Language, but I like the sounds of Salsa, Queso and Lager.

    Reply
  • Salsa, Queso and Lager 4tw. I will use this in the future.

    Reply
  • Size of database doesn’t necessarily correlate with complexity of management.

    For example, I used to have a half-petabyte database that was very simple to administer, because it consisted of one table split into three partitions, and a couple of procs. Was just used for processing address lists. Had no activity off business hours most days, all transactions were in large batches, only one of the tables needed any non-clustered indexes, inserts were sequential, there were no deletes, the only columns subject to updates were non-indexed, and if the database got corrupted or lost, restore from the prior night’s full backup was adequate, since lists could simply be re-run.

    That database is currently being administered by someone who can just barely spell “SQL”, and is most certainly NOT a senior DBA, and it’s doing just fine.

    On the other hand, I also used to have a 20 Gig database that had over 400 tables, was highly transactional (hundreds per second), needed 24×7 uptime, and where the loss of even 1 minute’s data would cause major problems for the business. That one required a significant amount of skill and expertise to administer. Definitely not something a junior DBA could have handled.

    Size does matter, but, as always, skill matters more. Both is best.

    Reply
    • GSquared – great point. Would you agree that those situations are exceptions to the norm, rather than the norm? I agree with you, though, in that if you’ve only got experience managing a 20 gig database, you need to spell out its complexity on your resume lest someone think you aren’t qualified to manage a much larger one.

      Reply
  • Igor Besprozvanny
    April 1, 2010 1:14 am

    link for a “are you junior or senior dba” goes to another article.

    Reply
  • I think you are right on with your estimations… We have an 80GB db that is growing rapidly, as we crossed the 75GB threshold, the amount of administration immediately increased. As we begin to reach the 100GB mark, I can see where we are going to NEED more experience.

    Reply
  • I’d be interested to know if your size conditions for each tier have shifted since 2008?

    Do you apply these these to both OLTP servers and data warehouse servers (dimensional in relational engines – not OLAP)?

    I find the use of data size as a form for generalising the complexity intriguing (due to its simplicity), although being predominantly developer focused within the data warehouse/BI space, I would lean to categorising complexity based on the number of functions the database server performs raised to the power of the number of other systems/applications it has to integrate with.

    Reply
  • Mathew Soczynski
    July 22, 2015 1:26 am

    I started on the Helpdesk and pretty much got moved into being a fulltime DBA. the thing that “changed me” i guess was having a microsoft premier support. Come onsite.

    reviewed my current 2012 cluster build. he aligned my design to microsoft best practices and explained things i had missed; and gave me some lessons on my skillgaps that I had missed in my configuration.

    best thing I had ever done to get myself back on track. I look back at it now and have suggested any future companies that I work for; to get a RAS on the environment done if they have credits with microsoft 🙂

    Reply
  • Richard L. Dawson
    January 26, 2018 11:10 am

    Brent, did you ever update this article since it was written? Would you post a link to it if you have?
    For example. I work for a software and application hosting company with numerous databases, the largest of which is over 600 GB. All in all, we have over 60 TB under management. I don’t and wouldn’t want to do this job without the folks I work with. Finally, to address the complexity portion there are also numerous interconnections between the databases that are handled through a web of jobs, replication, reports and home rolled log shipping that allows us finer control than the built-in version. In a transactional database system that handles financial or medical information, I would expect this to be the norm rather than the exception.
    I would love to hear what your opinion is now after 10 years.

    Richard

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}