question

DaniSQL avatar image
DaniSQL asked

What topics/tools should I need to know to become best Production DBA?

Hi All,

Currently I am working as a production DBA but I had engineering background (EE to be specific) and I fell in love with SQL Server and I am now trying to learn SQL Server everyday in lot of ways. I learn at work, I read blogs, articles, white papers, watch videos and attend online seminars and spend a while everyday here in ask-ssc. However, unlike most DBA's who had a developer knowledge/experience, I have little programming knowledge. Sure I can edit and use t-sql scripts but it’s hard for me to work as a development DBA and tune codes and work with developers and help them design a better program. Also I believe having minimal programming knowledge held me back from getting the most out of what I read everyday.

What I love about being a production DBA is solving problems and becoming a master of technical skills and I definitely don’t want to be a developer but I want to know enough to be able to become best production DBA. At first I was thinking to focus and learn C# in great detail so that I can be effective at my work but at this stage I figured I better use the time to learn more SQL Server to be effective at my current job. I need your help to prioritize what I should learn in the next a year or two. What I have in my mind is T-SQL, C#, PowerShell, SysAdmin knowledge, Security and compliance knowledge, enough SSIS knowledge for DBA, little bit of SSRS and SSAS. But I don’t know from which to start and what material to use.

I guess my question is how did you become the DBA you are now? What topics/subjects/resources/tools did you learn first? And what do you think I should learn to become best production DBA? Which knowledge/tools do u think is a must? also what is a must best practice for DBA's(besides learning constantly?

Thanks,

[feel free to edit the question if i didnt articulate my question clearly and every bit of advice is appreciated]

sql-serverbest-practicepersonal-development
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
Is this a record for this site in terms of number of comments left on answers? It's certainly more than I've run across before now.
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
@Tim: I don't know. But I am very happy every one chips in. It get awesome insights and hope will make use of it.
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
Thank you all very much. All answers are Very Good Answers but I had to choose one!
0 Likes 0 ·

5 Answers

·
TimothyAWiseman avatar image
TimothyAWiseman answered

Let me caveat by saying that I know many out there will disagree with at least some of these, but it works for me. Differently people tend to learn differently:

  1. Get Certified. I think this will be my most controversial suggestion, but it works for me. Getting certified does a couple of things for you. If you are just learning, it gives you a target to aim for and a way to structure your initial studying. Even if you never take the test, I personally find that structure in study useful. The practice tests (and the real one for that matter) can also help expose weaknesses in your skill development. And the certification itself may (or may not) help you get a job. I would never hire someone just because of a certification, but I would interview them because of one...And on the other side, when I was first starting out one interviewer told me directly that I got the interview because of the certifications on the resume.

  2. Learn to program. You already mentioned this one. If you have never programmed before (or very little), I would recommend Python as a place to start instead of C#. It is easier to learn, very user friendly, and I personally frequently use it in production environments tied tightly to SQL Server. C# is a good choice as well, though I personally find it harder to learn. It is designed to work with SQL Server and you can write CLRs that you import directly into SQL Server with it. I consider it vital to know at least the basics if you are working with SQL Server (though I consider it a second language not the first).
    Powershell is nice to learn, but I consider that a low priority. It definitely has a place in a DBAs tool kit, but I tend to use Python for what most people would consider to be PowerShell's strong suite and I find Python much easier to use (of course, I learned it first, so I am biased). There are some things Powershell is very good at, but you can easily substitute python or C# for Powershell, you often can not say the opposite.

  3. Learn at least basic set theory. In a sense SQL is an implementation of set theory. While you will likely never directly be called upon to provide a set theoretic proof, knowing at least the basics will give you a much deeper understanding of what SQL is, what it is supposed to be, and how it works. It will also help you really think in sets which in turn helps you avoid cursors. One book that I recommend to get started is Applied Mathematics for the Database Professional. It is not overly deep, but you are the target audiance which is good and it makes an awesome primer to go deeper if you want to.

  4. Write. One good way assure yourself you really know something is to write and publish. This both helps you and helps the community, and the feed back from the community on what you write can really help. I used to write occassionally for SQLServerCentral.com and Simple-Talk.com, and I found those great learning experiences. (I haven't published anything lately because I am trying to finish my master's degree right now. Complex Function Theory in particular is challenging...ok its absolutely brutal...Maybe over summer I'll finish another one)

Finally, I do consider at least the basics of SSIS to be a necessity for a production DBA. As for SSAS and SSRS, that depends on your job description. They are certainly good things to know, but many organizations have other analysts that specialize in SSAS and SSRS which makes it less significant for a production DBA to really master on a deep level.

And of course, both helping out and reading other answers on a site like this really helps.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
No one else seems interested so I might as well chime in. It's Friday! Certification? Are you nuts? What a serious waste of time! There, I feel better. Seriously though, there's nothing wrong with certification, at all. But as someone who interviews people & reviews resume's, I don't find it to be a good measure of their worth as a DBA. It's 60/40 to those who have been certified and bad, to those who have been certified and good, in my experience.
1 Like 1 ·
DaniSQL avatar image DaniSQL commented ·
I agree certification should not be used as a measure of once knowledge and it has little use for experienced/junior DBA's. But for starters it is useful as a guide if they study hard and not use brain dumps to pass the exam. When I started I dont know any DBA in person, I dont have DB to play with and I had no knowledge of what production DBA's do . So I started studying for the certification and used the topics listed to focus and to gain knowledge. It worked for me. Also believe it or not HR's still give lot of weight for certifications and its a Good thing if you don't have fat resume :-)
1 Like 1 ·
venkatreddy avatar image
venkatreddy answered

Thats really looking good Dani,I too truly from Electrical Engineering background.I will suggest you few things which may help you.

1)To become a good DBA,i believe in strong that have good knowlwdge in T-SQL programing. Those who qualified with indepth T-SQL programming will have a chance of moulding DBA from developer stage itself.Since i too a Jr.DBA with development and admin(my core area includes both development and administration)

2)Exposure towards the production environment will hepls you better.

3)Concentrate on core T-SQL programming like working with creating constraints over tables,tables joining,complex sub-queries,developing complex stored procedures,functions,triggers,cursors,views etc..

4)Better to have good exposure towards the query optimization techniques,estmating the execution plans etc.

4)As part of the DBA requirements have strong knowledge on indexes creation,dynamic management views etc..,which helps you in tuning the database and server performence.

You did mentioned SSIS,SSRS,SSAS will helps you better when you working with integration,reporting and admin services,as i believe these BI tools are must now a days for any DBA.

7 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
SSRS & SSAS are specializations that might not come into play in some shops, so spending time on them really needs to be a judgement call. If I had to pick, I'd spend more time on SSRS than the other. Other than that, good advice.
1 Like 1 ·
DaniSQL avatar image DaniSQL commented ·
Thanks Ven. SSRS,SSAS are way down on my list, but we need a little bit of SSIS to move around data in the office. BTW R u suggesting I go ahead and study TSQL in depth without having a deep knowledge of at least one programming language? If it is possible to master TSQL first i will prefer to do that too. Also I know better understanding of indexes can take me a long way and I am working on my tuning skills but after i analyze queries it always comes down good programming skills(which currently I dont have).
0 Likes 0 ·
venkatreddy avatar image venkatreddy commented ·
Good,put strong efforts over T-SQL..coz u really comes to know how importent it is.It simply costs nothing as learning SQL programming diffrent from C#,VB etc.At intial stage you may go through simple examples and then taste complexity.
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
I am with Grant. SSRS and SSAS are good skills for any DBA to have, BUT many shops don't use them at all and many others have specialists that focus just on those and don't need the production DBA to worry about it. They are worth learning, but I would put them very low on the list.
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
@Tim, @Grant: How about SSIS? How much of SSIS knowledge do u think is necessary for DBA's?
0 Likes 0 ·
Show more comments
Grant Fritchey avatar image
Grant Fritchey answered

I'll toss a few comments into this one:

  1. Get Paranoid. You're first goal as a production DBA is to protect the company information contained within that database server. You need to back it up, check it for consistency, performance maintenance, secure it... All the while, racing ahead of some type of corruption or user error, or bad program, or what have you. You need to protect that data, so some paranoia will be good.
  2. Test your backups. This falls under paranoia, but it's the most common problem you'll see out there. Lots and lots of people have backup routines in place, but very few validate that their backups work
  3. Practice. Practice running a restore or a recovery to a point in time. You want to know how to do it now. Not when the production server is offline and you're under the gun. But practice everything else. We do deployments by restoring our production systems to a different server and then running our scripts until we get a flawless deployment. Then that script, without changes, is run against production. Again, this goes back to paranoia, but you can't go wrong being paranoid.
  4. Learn set theory. It really is a useful way to think because that is how the server thinks.
  5. Learn More. You can't possibly know enough. There's always something else to understand, so learn as much as you can. Hit books, magazines, web sites, blogs, user group meetings, conferences.
  6. Teach. Nothing ensures you understand things better than explaining it to someone else. You can write articles, give presentations, or just hang around here answering questions. You'll get some right, you'll get some wrong, but you'll learn more and share with others.

That about sums it up for me, at least as a starting point.

13 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

venkatreddy avatar image venkatreddy commented ·
Grant,is it OK my answer for Dani.
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
Thanks Grant. All great points. I am definitely paranoid but when it comes to testing backups I don't have much hardware to work with and they are getting me one. Except user groups and conferences I use every material that i can get in my cubical to learn. I am in it for the long haul and I will definitely hangout here more. BTW I got your book after Brent Ozar recommended it but i didnt start reading it yet. Do u thing I will get more out of it after I learn much about programming language and Advanced TSQL or it doesn't matter at all?
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
Grant, you already wrote a book? What is it focused on?
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
Tim r u kidding? Check out this: http://www.amazon.com/Server-Performance-Tuning-Distilled-Experts/dp/1430219025 Also check out this review by Brent Ozar http://www.brentozar.com/archive/2009/04/book-review-sql-server-2008-query-performance-tuning-distilled/
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
I can't look at it while at work, but I will take a look this weekend.
0 Likes 0 ·
Scot Hauder avatar image Scot Hauder commented ·
Grant did a fantastic job with that book and there isn't really anything similar to compare it with (yet). Pick that up, plus anything by Itzik Ben-Gan and the MVP deep dives and you will be well on your way to enlightenment
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
Thanks @Scot. I have all of'em on my desk even if i haven't read them yet:-) I also have SQL Server Internals by Kalen Delaney. Once I figure out where to start first i will be on my way to enlightenment. By the way I recommend "SQL Server 2008 Administration in Action" by Rod Colledge for anyone who is junior or mid level DBA. I have ready it and I like it very much. It is an well written and an easy read and will help anyone to grasp the necessary concepts and skills to administer a SQL Server Database.
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
I am a big fan of Itzik Ben-Gan's work.
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
@Tim:Itzik Ben-Gan's work are on TSQL. Do you recommend working on my TSQL skills at first instead of Python/C#? Also If I choose to learn Python instead of C#, do u think it will help me(in addition to scripting administrative tasks) in understanding how applications work so that I can tune queries and help design good applications ?
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
@Dani, Whether you should focus on learning more SQL first or learning another programming depends on how comfortable you are with SQL now. From what you have said of yourself, I think it would be time to pick up another programming language. And learning Python did help me understand how applications work and got me started on application design. I came to SQL from a mathematics degree and learned Python next and then C#(well, working on learning C# now really.)
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
@Tim: I appreciate it. Thanks!
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Thanks @Scot. Appreciated.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
@DaniSQL The book is advertised as advanced, but I wouldn't say it is. It's pretty strongly intermediate. Probably, you can pick it up, read it and get stuff out of it. Parts of it might go a little past where you are now, but not far I suspect.
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

As you might expect, Grant and Tim have given some excellent answers, and Venkat has some valid points too. Here are a couple of things that have helped me:

Mistakes are good
Why would I say that? Because mistakes are what you learn from best. Nothing says 'best practice' like a mistake. Test, test and test again, to try and ensure that you pick up mistakes before they get into production. Also, take an active & supportive interest in other people's mistakes - they are also a good opportunity for you. And if you make a mistake, then be open & honest about it. It's rare that people get a hard time from owning up to their mistakes.

Know your limits
Sometimes you'll come to a problem that just doesn't fit into your mental space. Maybe it's that you haven't covered the problem area in enough depth, maybe you haven't had enough coffee that morning. But don't try and 'soldier on'. Ask someone, ask here and let some people help you out. It can even be helpful if you're 'mostly' sure. Get a second pair of eyes on that problem.

Push your limits
With the last item in mind, don't be afraid to try something that is outside your comfort zone. If it doesn't work out, don't 'soldier on', call it a learning experience. But you'll probably find quite a lot of the time you'll surprise yourself. Given the way you have written the question, I don't think you'll have a problem.

Understand the problem domain
The problem domain that declarative languages (e.g. T-SQL) address is quite different to the problem domain that imperative languages (e.g. C#, Python, Java etc) address. Get a good handle on what those separate domains are, and understand where things are best implemented. You don't want your SQL server to output and format the HTML for your web page. Neither do you want your middle tier or presentation layer trying to relate rows to each other manually. This one is a little obvious, it would seem, but having a firm knowledge of what should reside where, and what the bigger picture is, will always be a good thing.

Hopefully that helps... +1 for a well worded and thought out question, by the way.

Edit ->

Tools
Tools are good. There are plenty around - use them to make your life easier, but don't let them get in the way. There are three classes of tool that really every DBA should have: An intellisense app / IDE, a schema comparison / rollout tool and a documentation tool. There are lots of other helpful tools, but I would say those three are a must.

7 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
As always beautiful points. I particularly want to second the fact that you should not be afraid of making mistakes. But do try to do them in the test environment.
1 Like 1 ·
venkatreddy avatar image venkatreddy commented ·
+1 Matt,i too have full stuff now.
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
Thanks Matt. Excellent Advice and hope I will use all of'em to grow as a DBA. As you said mistakes are good but when u are the only Production DBA and you are junior mistakes can be costly and irreversible and must be avoided. I got into trouble in my second week for the same reason. Regarding tools, since I am a click DBA I depended on monitoring tools heavily when I start the job but now I started scripting all of my maintenance and monitoring tasks. I dont have any of the tools you mentioned right now but I tried them from d/t vendors for two weeks and am planning to get in the future.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@DaniSQL - some are available free... All my tools have free community editions, and SSMS Tools Pack is also a great add-on for SSMS that is free. Apex SQL Code - free. Red gate SQL Search - free. :)
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
Thanks Matt. I already have RedGate's SQL Search and Toad modeling from Quest and I will definitly try ur tool and the others. Thanks!
0 Likes 0 ·
Show more comments
Blackhawk-17 avatar image
Blackhawk-17 answered

I think it is important to define DBA in the context you wish to pursue. A lot of the advice is geared towards becoming a devloper as oppposed to either a support/system DBA or an application DBA.

If that is where you are headed learn T-SQL. Then follow up with .NET languages. The difficulty is keeping Set-based objectives and event-driven code separate in your mind to fully exploit each language's potential.

If you are starting with support, learn T-SQL and PowerShell. A little VBScript and batch wouldn't hurt either.

The major differences are whether you will be working external to the Data Engine and need to manipulate things in the O/S, you are architecting solutions, or you will be taking developer code and integrating and tuning it for your PROD environment. Not every shop classifies these roles the same and in some there is a lot of cross-over.

Define your vision of what you believe a DBA to be and then follow the path that helps you get there. And remember that the path will have many twists and turns and sometimes will lead you to somewhere you weren't expecting but it's all part of the journey.

3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image DaniSQL commented ·
Thanks Blackhawk. i have a vision and I want to become best Production DBA. What I am trying to figure out is how to get there. The skills required are vast and in addition to learning how SQL Server Internals work I decided to learn one programming language in depth because I want to be able to work with developers on applications and tuning queries for i cant always index and optimize hardware to get good performance.Also i need to learn one scripting language to do my DBA job. At my current job I am only responsible to maintenance and availability of DB and I need to learn more on the side
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
Good points. There are different types of DBA out here, and even if you specify "Production DBA" that might mean different things in different places. I do think most of the good advice under this question is applicable to most types of DBA, but how it it is weighted and prioritized will vary between one type and another.
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
@Tim: Great point. BTW I say Production DBA in the context of this discussion http://ask.sqlservercentral.com/questions/2439/differences-between-a-production-and-a-development-dba
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.