Turn me into an Admin/DBA
September 18, 2007 4:53 PM Subscribe
How do I learn to be a professional Server Administrator/Database Administrator?
I work at a small company that is involved in data-warehousing and data services. I've had plenty of work experience with hardware and databases. I handpicked components and assembled our main database server. I write queries all day long (I probably do it in my sleep, too). I still feel like a novice. Due to some recent departmental shakeups, I'd like to increase my qualifications, not only to make myself more valuable to the company(job security), but also in case I need to strengthen my resume.
I'd like to learn more about server administration, hardware purchasing, and server technologies. I'd also like to learn some formal database administration skills (we use SQL Server, and have about 175mm distinct records, spread over about 200GB in a low transaction database).
Are there any curriculum, courses, certifications, or books that will help me in this endeavor? Any tips from experienced admins out there? My college and work background is mainly CS and IT, btw.
I work at a small company that is involved in data-warehousing and data services. I've had plenty of work experience with hardware and databases. I handpicked components and assembled our main database server. I write queries all day long (I probably do it in my sleep, too). I still feel like a novice. Due to some recent departmental shakeups, I'd like to increase my qualifications, not only to make myself more valuable to the company(job security), but also in case I need to strengthen my resume.
I'd like to learn more about server administration, hardware purchasing, and server technologies. I'd also like to learn some formal database administration skills (we use SQL Server, and have about 175mm distinct records, spread over about 200GB in a low transaction database).
Are there any curriculum, courses, certifications, or books that will help me in this endeavor? Any tips from experienced admins out there? My college and work background is mainly CS and IT, btw.
Good question. "DBA" covers a lot of territory, from more network-admin/hardware to to SQL programmer. (I am frequently mistaken for a DBA, but I'm not, really, as I don't do anything with hardware.)
Celko's books are highly recommended (but I've only skimmed them a bit).
My personal advice would be to familiarize yourself with the SQL Standard (SQL-99), and with different RDBMSes and their quirks and compliance to the Standard.
You mention that you wrote queries all day long, but have you desgned a databse, that is, written DDL? If not, write some DDL and see how that influences DML, efficiency, and usability.
(E.g, I need to represent voters' votes over several years: which table structure is better, one table with a voter id column and one column for each election, or multiple tables? Justify your answer with the queries you'd need to use each version, and the query timings for each version. Then justify your answer with an abstract argument that doesn't go to efficiency.)
posted by orthogonality at 5:38 PM on September 18, 2007 [1 favorite]
Celko's books are highly recommended (but I've only skimmed them a bit).
My personal advice would be to familiarize yourself with the SQL Standard (SQL-99), and with different RDBMSes and their quirks and compliance to the Standard.
You mention that you wrote queries all day long, but have you desgned a databse, that is, written DDL? If not, write some DDL and see how that influences DML, efficiency, and usability.
(E.g, I need to represent voters' votes over several years: which table structure is better, one table with a voter id column and one column for each election, or multiple tables? Justify your answer with the queries you'd need to use each version, and the query timings for each version. Then justify your answer with an abstract argument that doesn't go to efficiency.)
posted by orthogonality at 5:38 PM on September 18, 2007 [1 favorite]
The areas of focus I would recommend for you are:
Query tuning and performance (this might be less of an issue for you if your db is super low traffic): learn how to use SQL Profiler, SET STATISTICS TIME and IO, learn about processes and how to identify those that are having a negative impact on the server, locking/blocking why it happens and how to address it.
Database design: Relationships, 1-1, 1-many, etc., normalization/BCNF, schema design, E/R diagrams
Maintenence plans: Transaction log backups, Full database backups, restoring from backups
Server Technologies: Clustering (I do a Active/Passive setup), storage (separate your logs from you mdf's), Replication
Becoming intimately familar with these are what has helped me most in a similar position. I go online or use SQL BOL when I need answers. If you get enough self study and daily experience in these areas, then you'll have a much easier time when you start looking towards certifications like MCDBA, if that's of interest to you.
posted by gatorbiddy at 6:35 PM on September 18, 2007 [1 favorite]
Query tuning and performance (this might be less of an issue for you if your db is super low traffic): learn how to use SQL Profiler, SET STATISTICS TIME and IO, learn about processes and how to identify those that are having a negative impact on the server, locking/blocking why it happens and how to address it.
Database design: Relationships, 1-1, 1-many, etc., normalization/BCNF, schema design, E/R diagrams
Maintenence plans: Transaction log backups, Full database backups, restoring from backups
Server Technologies: Clustering (I do a Active/Passive setup), storage (separate your logs from you mdf's), Replication
Becoming intimately familar with these are what has helped me most in a similar position. I go online or use SQL BOL when I need answers. If you get enough self study and daily experience in these areas, then you'll have a much easier time when you start looking towards certifications like MCDBA, if that's of interest to you.
posted by gatorbiddy at 6:35 PM on September 18, 2007 [1 favorite]
(Oracle OCP here)
Seconding synaesthetichaze's suggestions , though i would question the value of ubuntu , especially with Oracle , and linux rules out SQLSERVER(pah~!).
MYSQL is nice , for what it is , but without wishing to start a DB war , but its not a system i would chose to further my DBA skills.
Oracle Enterprise Unix (RH) , or OpenSuse and then download Oracle 11g (or 10g). Read the docs, especially the architecture guides , they really are the meat.
Then build , decide on a project , perhaps try to build ur work system in Oracle or such.
Architecture - Design - Performance - Backups- It sould like u have the SQL skills , but have u used Analytic functions for example , interesting area that i wasnt really up on till i investigated.
My suggestion of Oracle isnt to really push it over SQLServer, just the OS + RDBMS are free , and different from what u are used to.
Good luck,
posted by burr1545 at 1:39 AM on September 19, 2007
Seconding synaesthetichaze's suggestions , though i would question the value of ubuntu , especially with Oracle , and linux rules out SQLSERVER(pah~!).
MYSQL is nice , for what it is , but without wishing to start a DB war , but its not a system i would chose to further my DBA skills.
Oracle Enterprise Unix (RH) , or OpenSuse and then download Oracle 11g (or 10g). Read the docs, especially the architecture guides , they really are the meat.
Then build , decide on a project , perhaps try to build ur work system in Oracle or such.
Architecture - Design - Performance - Backups- It sould like u have the SQL skills , but have u used Analytic functions for example , interesting area that i wasnt really up on till i investigated.
My suggestion of Oracle isnt to really push it over SQLServer, just the OS + RDBMS are free , and different from what u are used to.
Good luck,
posted by burr1545 at 1:39 AM on September 19, 2007
I guess someone should mention Microsoft's SQL Server 2005 Express Edition since it is free. It is probably not as fully featured as the enterprise edition or Oracle's free edition but is an option if you need to stay with MSSQL.
posted by CuJoe at 2:51 AM on September 19, 2007
posted by CuJoe at 2:51 AM on September 19, 2007
All good suggestions up thread, and I'll add security to the list. Get familiar with user roles and permissions, and what level of access to grant to database objects that balance the needs of the application with security. Also, get familiar with the error logs, web server logs, and event logs on your system and find or build tools that will help you monitor them. I've been using Log Parser for Windows to filter and parse various log files into a SQL Server database for analysis.
As for books, I'm always going to the Transact-SQL Reference chapter of SQL Server Books Online. Dry but informative.
posted by hoppytoad at 8:31 AM on September 19, 2007
As for books, I'm always going to the Transact-SQL Reference chapter of SQL Server Books Online. Dry but informative.
posted by hoppytoad at 8:31 AM on September 19, 2007
This thread is closed to new comments.
Ubuntu is free and easy and everyone will probably recommend that. I hate Ubuntu. SuSE is a better OS. AIX is my preferred environment, but it costs money too. So, I would recommend buying a cheap machine for home use, installing Ubuntu and trying to do something with it.
As far as dbadmin skills, most enterprise level DBs have a free version. Oracle. DB2. And of course, MySQL.
So you could slap one of these database products on your new, free OS and go to town. You will need a goal, or you'll never get anything done, trust me. I suggest a LAMP stack website thing to start out. In order to get truly in depth, however, you will need to look at performance tuning, disaster recovery options, reliability/redundancy stuff (i.e., RAID), and more advanced database stuff than I know about (database partitioning? I don't know).
Basically my answer boils down to: do it yourself. I've never been to a class that taught me more than sitting down and doing something did.
posted by synaesthetichaze at 5:21 PM on September 18, 2007 [2 favorites]