August 18, 2008 at 9:47 am
(Wasn't sure where else to post this...)
I've been working with SQL since 2000 in one way or another but have only been a full-time DBA for a short time. There is such a wide array of knowledge and skills to keep on top of that I find it difficult to pick the most important things to study and track with the limited time I have.
What I'd like to know is what are the top 20% of things that will cover 80% of the situations I'm likely to encounter? We have a lot of people on here with a ton of experience and I'm hoping to get some feedback from people who've been in the trenches for a while.
I recently sent somebody in a sys admin role this article http://technet.microsoft.com/en-us/magazine/cc671165.aspx by Paul Randal. Here are the things he lists:
* Managing data and transaction log files
* Eliminating index fragmentation
* Ensuring accurate, up-to-date statistics
* Detecting corrupted database pages
* Establishing an effective backup strategy
I think this is a great list. It seems to me that in addition to this list, a DBA has to be skilled in:
* Staying on top of security and applying patches/updates
What else though?
Thanks,
Rob
August 18, 2008 at 10:22 am
That's a good list and gets you most of the way there. I'd add some good T-SQL knowledge for writing reports. Seems like those come up constantly and there are always needs to summarize data in some way. If you can get SSRS running, then you can build nice looking reports.
Tuning queries is something worth looking into as well. Grant Fritchey's book (promoted in the newsletters ads from this site) and Itzik Ben-Gan's book on T-SQL (Inside SQL Server series) are two I'd recommend.
August 18, 2008 at 11:05 am
The only thing I'd add to the "must have" list is:
a method for monitoring performance
a process for updating structures/code/procedures based on performance monitoring
If you do what you listed and the above, more often than not, you've got the bases covered.
Oh, practice restoring databases. You want to do it when there's no pressure so when there is, you've practiced. I don't know how that fits on the list, but it needs to be there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 18, 2008 at 11:41 am
Here is my list in order of importance (my opinion only of course):
1. Backup and Restore. If you can't get the data back after a problem you aren't a DBA.
2. Security.
3. Query Optimization. This includes reading execution plans, performance monitoring like Grant mentions, and knowing T-SQL like Steve mentions.
4. Database Maintanence. CheckDB, etc...
5. Server Maintenance. Service Packs, etc...
The list could go on for awhile. I think all the parts that are mentioned in the article you reference fit in those 5 areas as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 12:13 pm
Do you think it is worth worrying about and studying things like clustering, replication, mirroring, etc if they aren't a part of your current position or do you think that advanced SQL features like that are something a seasoned DBA with a solid grasp of the fundamentals should just learn as their position requires?
Thx,
Rob
August 18, 2008 at 12:25 pm
I've been working with SQL Server since 1999 and know next to nothing about clustering as I've never worked on a database large enough or really, critical enough to warrant clustering. If I needed clustering I'd bring someone in to do it and teach me how to manage it.
I think it is fair to say that if you know the other stuff you should be able to pick up the skills you mention as needed. I don't study clustering because I don't foresee a need for it where I am at. If I did, or if I thought I'd be moving on to someplace where it is needed I'd study it.
SQL Server has grown into such a big product with SSIS, SSAS, SSRS, etc... that I don't have the time or ability to know it all, I know the parts I work with fairly well and I know where to go to get the information on the areas I don't know (books, user groups, SSC, people).
Now if you were studying for the MS Exams, you probably need to know a lot more of the enterprise level stuff.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 12:39 pm
I agree with Jack. You're just not going to be able to learn things in a detailed way that you don't work with daily.
That said, you should have a good working knowledge of what these things are, what they do, what their limitations are, so you can make sound judgements that you might need that functionality. Then, you'll have to get a consultant or learn like mad. That doesn't mean learning how to implement them, maintain, sustain, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 18, 2008 at 1:57 pm
I agree with Jack, but would switch 3 & 4
August 18, 2008 at 2:03 pm
I'm with Jack on learning that stuff later. Learn what you need to, pick up the other stuff as it comes up or you have time.
I've learned about clustering, but all theoretical. Like Jack, I've never needed to cluster a server in my job.
August 18, 2008 at 2:36 pm
Great replies everybody.
One more question, in his article Paul mentions "Detecting corrupted database pages". How often is this really a problem?
August 18, 2008 at 2:52 pm
I can only recall having seen it once and that was in an index so you can drop and recreate the index to repair it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 2:55 pm
Rob Symonds (8/18/2008)
Great replies everybody.One more question, in his article Paul mentions "Detecting corrupted database pages". How often is this really a problem?
The dreaded "It all depends". Hardware issues will cause this error to be raised. I've been luck enough to only have this occur once in nearly 10 years. But, I've also been lucky enough to have good, solid hardware and a proactive infrastructure team. All my production databases have 'detect torn pages" enabled and I do a DBCC CheckDB weekly, at a minimum, to make sure I know what I have. Good, viable backups are the most critical key for recovering from issues like you've mentioned.
Edit - And my one time, Like Jack's, was also on an index.
-- You can't be late until you show up.
August 18, 2008 at 2:56 pm
I've experienced data corruption only once in the six years I've been a DBA. I'm guessing it's a pretty rare occurrence, but when it happens, you really need to be prepared to deal with it. It took me an entire weekend to fix the corrupt database, which was the result of many smaller problems adding up to one big one - sort of the perfect storm (and nightmare) for a DBA.
Also - I'm glad to hear there are seasoned DBAs out there who do not have a lot of practical experience with clustering. I thought I was the odd DBA. :hehe:
August 18, 2008 at 3:48 pm
I've experienced data corruption 5 times so far this year for about 350 SQL Servers. In all cases, the corruption was caused by bad disk drive drivers, SAN drivers or SAN misconfiguration. That is a 0.204% failure.
Although rare, recovering from this failure will be critical to your job and when you are successfull, will be remembered.
This is very easy to simulate, so get a computer where SQL server is running and do this:
Create a database and a table.
Set recovery mode to full
Take a full backup.
Insert some rows.
Take a transaction log backup
Insert some rows.
Take a transaction log backup.
Insert some rows.
Now simulate the failure by stopping SQL Server, deleting the database's mdf file, and then start SQL Server.
Can you recover with no data loss?
SQL = Scarcely Qualifies as a Language
August 18, 2008 at 6:08 pm
I would guess that I've seen data corruption about twice a year for... quite a while. That includes development and QA systems where silly and crazy stuff occurs just a little more often than in production. Like everyone else, most of the time it's because of some piece of hardware that errored or is failing or has failed. Usually it's an index that's corrupted and simply recreating it fixes the issue. Out of sheer paranoia, I do a consistency check before every full backup and if the consistency check fails, I raise an alert and don't perform the backup. I think I've only had to do a DBCC repair three or four times in fifteen years and a repair with data loss once that I recall.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply