Resources for the accidental DBA

Question

Within the Microsoft platform, most of the enterprise level programs (SharePoint, any of the System Center apps, any of the Dyamics apps, etc) all run on top of SQL Server. For administrators of these programs, SQL Server is often a black box that is installed as a prerequisite to whatever program is their primary focus. As a result, there is very little (if any) planning that goes into the SQL side of the installation, leading to problems that surface somewhere further upstream.

• Transaction Logs that fill drives
• No maintenance plans (or uninformed ones, such as plans that both reorganize and rebuild the indexes)
• Unmanaged autogrowth
• Databases and logs on the same spindles
• Poorly chosen RAID levels
• No backup (or recovery plan)

So... what types of problems do "accidental DBAs" tend to hit, and what resources would best help an accidental DBA get up to speed on SQL planning, administration and performance tuning basics?

Answer 1

Checkout the series of articles and Q&A columns I write for TechNet Magazine - they're mostly written with the Accidental (we call it 'involuntary') DBA in mind.

Top Tips for Effective Database Maintenance was written specifically as a primer for involuntary DBAs to understand DB maintenance issues.

Understanding Logging and Recovery in SQL Server

Common SQL Server Security Issues and Solutions

Understanding SQL Server Backups - part 1 of a 3-part series. Part 2 will be on using restore (in the Sept 09 issue) and part 3 will be on recovering without backups (in the Nov 09 issue)

You should also checkout my blog and my wife's blog (not advertizing or anything just info) - we both blog a huge amount on a variety of technical levels.

One good series of posts to look through are the editorials for the results of my weekly surveys. They are usually around a broad topic that would help involuntary DBAs. The editorial posts start with 'Importance of' or 'Important'. In fact this week's survey is on being an involuntary DBA - very timely!

We understand the involuntary DBA thing really well - in fact Kimberly and I teach a couple of days of the SharePoint Microsoft Certified Masters class so the SharePoint admins know what to do with their SQL Servers (we also teach a full week of the SQL one).

Hope this is useful to you.

Answer 2

Sean, I understand where you're coming from.

We're in a similar boat here, as I would expect are many others. Not withstanding today's economy.

Despite repeated complaints to management, (including senior business management), our situation is this; The self-appointed "DBA" (in a separate, `development team' on another floor) unfortunately knows less than a junior wielding two O'Reilly books and a KB print-dump. She's got the job, and is great at pouring honey into the ear of the person who also pours honey into the ear of the higest muckety-muck.

Surely, it would be ideal to be able to learn the DBA "trade", but again.. What we want and what we can have are often very different things. :)

I, personally have run into the following problems, which (to echo squillman's rather blunt, but not altogether incorrect) did require much of the googling.

• Tranlogs. You're right. What the heck were these things? So we had to restore a database and server, what does `replay the tran logs' mean, exactly? :)
• Wait, what do you mean these databases just get bigger? How do we shrink them? Or at least maintain their growth?
• Standardization of installations across different servers, (this image is for "dev", this image is for "prod" and this little image cried all the way home, from the market. :)
• Maintenance scripts and how to help manage the databases over a long period of time, (kind of like growing houseplants and making sure they don't turn into kudzu.)
• Always making sure, the proggies go on the C:\, the logging and/or databases go on the D:\, which kind of formulated our standardization, (C:\ is two mirrored disks, D:\ is usually a RAID5 affair.)
• Having to purchase a separate SQL licence and client for backups.
• Check out managing users that the development team assigns to the SQL database itself, managing DBO roles, etc. Ensure you've got a good security model when it comes to user rights within the database.
• Research of a domain service account that the SQL services can operate as. What rights that service account needs, if any at all.

(You've hit some pretty good ones, in your post.)

Since you're operating at a handicap like some others, make sure you spread the SQL knowledge amongst the team, if you can. Share what you know, teach others the same. Be friendly. It's a real pain having to wear the SQL hat, but at least many eyes and thought processes are better than one single one.

However above all, try like the devil to get a DBA on-staff. :)

Answer 3

I got the title DBA guy about a year into my job. This was about 5 months ago. Since then I have been reading various blogs from the 500,000 ft view to the (sometimes hitting the hard deck at 500 ft) 250,000 view, to the 500 ft view. Also,SQLServerPedia is your friend; they have a lot of good stuff for the accidental DBA.

I have been thrown into situations that made me feel uneasy. For example, I have been doing backups since I was “given” this job so Fulls, diffs and t-logs were on hand for my first restore of production data, nobody else seemed panic-y, so I figured I couldn’t show how queasy I felt. More times than not, I go overboard when I put on my DBA hat, but I figure it isn't my full time job (network administrator) so I should be 'better safe than sorry'.

Answer 4

Top Tips for Effective Database Maintenance

Answer 5

Start with the tactical efforts. If your database is crashing or not working well, focus on resolving those problems.

Next start with more strategic items: backup and restores. Know how to restore your databases inside and out, and create detailed procedures to prevent costly mistakes during a production outage.

If you don't have hardware to test major changes and things like backup/restore -- figure out how to get it.

Answer 6

When I hire a Junior DBA I bought her the Microsoft® SQL Server(TM) 2005 Administrator's Companion. It is the book I wish I had when I was starting out.