27

Ten Things a Junior DBA Should Learn

Entry Level Database Administrator

There is more to being a Junior DBA than knowing SQL.  The DBA works at the intersection of the database, server, operations group, and developers.  A DBA must understand concepts from all these facets of IT as well as be able to draw upon knowledge of their production environment to troubleshoot performance, hardware, and software issues. Below is a list of the ten topics I feel every entry level DBA should understand.  The list I created stems from my experiences working with databases as both a DBA, Developer, and Manager.   When looking to hire entry level DBA’s, the interview questions I ask are drawn from these areas. If you’re looking to start a job as DBA, then you’ll want to be familiar with these topics.

Backup and Restore

Any DBA worth their salt should know the DBMS’ (Database Management System’s) built-in methods to backup and restore data, such as using Oracle Recovery Manager, but in addition to these built-in utilities, it also makes sense to understand what third party offerings exist. Enterprise backup solutions are used in many larger IT shops.  Be familiar with products such as NetBackup or NetApp SnapManager. As a junior DBA it would be impressive if you knew these tools existed and that not all backups are created equal.  That is to say, just because you back up the database files, doesn’t mean get a good backup… in fact, you didn’t.

SQL Server 70-461 Study Plan

Basic optimizations

It is important to know when to recommend when an index should be created.  You should know some basic indexing strategies.  When are clustered indexes appropriate?  When should you use a covered index? Also know how your database optimizer works.  Does it rely on special table statistics?  How do you update those? Know what it means to reorganize tables and indexes.  When should they be reorganized, and what can you do to automate the process?

Software Developers and Junior DBA's

Developers

Software developers can make or break a database.  It is important that you can work with others to help them write efficient queries.  You need to help them understand that one call to a database is much more efficient than one thousand!  In most cases it is faster run one query that returns a 1,000 rows, than it is to call 1,000 queries that return one row each. As a DBA you should help them understand when it is better to perform processing on the DBMS rather than in code.  Pulling volumes of data across your network to perform a sum is most likely slower than writing a query with an aggregate function.

Storage Systems

In most databases the main bottle neck is disk access.  Understanding where your database is stored and how the DBMS accesses the physical data is important.  Is your database on local disk or a SAN (Storage Area Network)? If you company has a storage team, get to know them, and understand what tools they use to monitor IOPS (Input/output Operations per Second).

 SQL Server 70-461 Study Plan

How to read a query plan

As a junior DBA you should know how to generate and read a basic query plan.  I wouldn’t expect you know fully understand all verbiage, but several key phrases, such as “Full Table Scan,” and “Nested Loops” should jump out as red flags. Also, you should understand, when the optimizer recommends a change be made, why that change would work, and what the trade-offs are in making it.  For instance, the optimizer may recommend that an index be created.  It this to compensate for poor programming?  Also, if you add it, could something else suffer, such as a process to insert or update data?

Knowledge of normalization

Normalized tables are the foundation of a well-designed relational database, yet they can also be its bane. A junior DBA should understand and know how to put data into 1st, 2nd, and 3rd normal form.  Why is normalization important and when can it become a liability? Knowing the difference between a Primary, Foreign, and Unique key is important.  Also is understanding and knowing how to enforce one-to-one, one-to-many relationships.

Knowledge of SQL

It might seem obvious, but a DBA should have a really good grasp of both SQL DML (Data Manipulation Language) and DDL (Data Definition Language). DML covers items such as Select, Update, Insert and Delete.  You should understand all the major clauses such as WHERE, GROUP BY, HAVING, and ORDER BY.  In addition you should be comfortable with sub queries and joins. DDL covers items such as CREATE TABLE and ALTER TABLE.  A junior DBA should understand how to create and modify tables and indexes and know the difference between deleting records, truncating a table, or dropping it! And… don’t forget views!

Operating System

As a DBA you need to be familiar with the OS (Operating System) your DBMS lives within.  You should understand nuances of your operating system, such as security settings, integration with Active Directory, LDAP (Lightweight Directory Access Protocol), and naming conventions.  Also, how is your DBMS started?  What scripts are used to start, shutdown, or lock users out of your database?

Scripting

To become efficient it is important to know when OS scripting, such as PowerScript, can help you manage your servers.  Consider having ten or more DB servers?  If you had to shut down all the DBMS on them, would you individually log in and manually shut them down or use a script? In my book, if you want to get an edge on other junior DBA’s you learn scripting.  This will only make you more efficient at your job.

 SQL Server 70-461 Study Plan

Stored Procedures and Triggers

I made stored procedures and triggers a separate category since they’re more like programming than pumping out SQL statements.  Regardless, you should know when to use them and recommend to your developers their virtues versus writing SQL in code. Also, many third party applications ship with Stored Procedures and Triggers.  Being able to read them and understand their logic can go a long ways when trying to troubleshoot a performance issue. The sooner you can avoid treating them like black boxes, the quicker you’ll understand and become more comfortable with the databases you support.

Conclusion

I would really like to know what you think of these topics.  Are there some you feel are more important than others?  Also, what other topics would you add to the list?  Are there some you would remove? Your feedback is really important.  I would really like to know what you think.  This is one of those area, where there isn’t really a right or wrong, but I bet y’all have an opinion! Leave a comment and let me know.

Kris Wenzel
 

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • Rob says:

    Thanks for the post. I am a SQL n00b and this really helps.
    I appreciate the post.

    Rob

  • Sergio says:

    Very useful post! I’m self taught in programming & databases so it’s VERY difficult to know how I compare to those who are formally taught. I’m applying to several positions as a DBA and I can see where some of my weaknesses are. Thanks!

  • Martin Jones says:

    I am trying to write an informal database thesis. This article proved useful in introducing me to DML and DDL. Also remind me how important normalization is! and that its spelt with a z not an s!

  • Allen J. Scott says:

    I currently work on the ops team in the NOC and this gives me more to research and understand the data I monitor on a deeper scale. Outstanding!

  • Joseph Pellegrino says:

    Currently in college and learning about how to become a DBA. This was helpful in terms of helping me understand the basic concepts/ responsibilities of a junior DBA and where I should apply my research and studying.

    Thanks alot! If there are any more basics that you would suggest that I begin to learn that would be awesome !

  • Paresh Zawar says:

    I liked it most ! And as I am new to DBA job I will get so much of help it.
    I will try to learn each and every thing mentioned here. Thank you for such a wonderful post which gets comment from an lazy guy .
    Will be happy if you share more details about DBA !

  • nad says:

    I m about to graduate in sql server ,and all the job postings are requiring some experience.would you please advice us if there are any material(e.g books,websites) where you can get hands on real life experience(set of projects) of daily common tasks of a junior or intermediate SQL SERVER DBA? thank you in advance

  • Durga says:

    Hi Guys,

    I have an issue with my SQL DB.

    we have space issue on our DB. its found that one table is continuously growing .. it stores session ID and details etc. This table has no date/time column. now the problem is I can’t delete some random from this table but I want to delete based on the age of the record being inserted. without date/time column how can I retrieve older records from this table?

    This table has no possibility no join with any other tables in the DB.

    thanks!!

    • Before you go any further add a new column to your table to capture the record’s age. That way you won’t find yourself with this issue in the future…

      To identify the records you can delete try to see if you can use the session ID to infer the record’s age. You may have already done this, but it is worth mentioning it, as this will be your safest approach to identify records.

      If this isn’t possible, then you may try using %%physloc%% with is an UNDOCUMENTED (eg dangerous) way to return a record’s physical location.
      Here is an example you can try:

      SELECT BusinessEntityID,
      LastName,
      %%physloc%% AS [%%physloc%%]
      FROM Person.Person

      Note %%physloc%% works in 2008 and above.
      If you haven’t reorganized your database you may be able to use the physical location to infer age. The assumption you need to validate is that the record’s age is dependent on the physical location (e.g. the small their address the older the record).

      Whatever approach you finally decide on:
      1. Test
      2. Make backups

  • Lee says:

    I think that understanding how to manage security access to the database would be important. Knowing how to create and manage logins, users, fixed server and database roles is important. When someone asks for access to the database, either for themselves or an app they are writing, knowing how to create the right security objects with the right permissions is a good thing.

  • Naveen says:

    To be an DBA..am I supposed to know any programmings!!
    Please….say..I want to know…

  • Naveen says:

    To be an DBA..am I supposed to be strong in programmings!!
    Please….say..I want to know…

  • nad says:

    Hi Naveen
    From my perspective, I can tell that companies are nowadays more demanding in terms of required skills. Like you, I’am a DBA without any programming skills. I did 8 months training in a private college to become a DBA. The training focused on the common task of a DBA, writing SQL queries and preparation for the exam. When I was on the process of enrolling at the college, both the academic advisor and the teacher told me that I don’t have to be a programmer as I will work as DBA not as a database developer. To my surprise, when I was looking for a job as a junior DBA almost all the job posting were asking for additional skills that we didn’t cover. I had then to spend more time in the library to learn more associated skills such as Poweshell scripting. Yet, it is not sufficient! As you might notice, hardcore programming language such as .NET and C# are always requested. This being said, it is not impossible to learn programming languages. I think both you and me we are exactly at the same situation. To put it short,if I am planning to land a job as a DBA no matter what, I would get any technical related job(for the sake of your CV) even if it has nothing to do with database(because sometimes it is a matter of survival)and learn the languages in parallel either online or evening classes. I hope it helps

  • ABHINAV VANAM says:

    Are these qualities required for oracle dba or sql server dba?
    Im trying to get a job as a junior dba (sql server).

  • Shahzad says:

    i am sick of triggers and procedures at all………

  • Awesome Article…!!!! Thanks for sharing such a wonderful information with us. Glad to know the information DBA,which is so helpful for everyone…Thanks a lot..!!!!

  • Martin mutwiri says:

    I like the 10 guidelines of becoming a DBA am a 3rd computer science student. Thanks.

  • Lakshman says:

    It is very helpful thank you for sharing the post…!

  • John says:

    Good one mate

  • report this ad
    >