email article printer friendly more resources

The Secret Life of a DBA
Learn the seven main tasks a database administrator needs to complete on a daily basis.
by Buck Woody

September 23, 2004

In July 2004, Wired reported that Professor Chang Liu and his team of structural engineers at the University of Illinois at Urbana-Champaign unveiled a "smart brick" packed with electronic sensors to continuously monitor a building's structural integrity, both for routine maintenance purposes and during an emergency. Among the brick's sensors are a thermistor (to track temperature changes), a two-axis accelerometer, and a multiplexor (to measure vibration and movement).

Liu's colleagues were nonplused. "Professor Liu has obviously not worked with a civil engineer on this project," University of California engineering Professor Steven Glaser said. "For one thing, bricks are now used as a facade rather than a load-bearing element."

What's true with building a structure is also important in database system administration. You need to know which parts of the structure you're administering are "load bearing," that is, the areas that are critical to the success of the system.

In previous articles, I've covered database theory, tools, and uses. In this article, I explain the primary tasks a database administrator faces on a daily basis. To be sure, there are far too many things that an administrator does to be covered in a single article, but the items that I'll discuss here are the most important.

There are several lists you can find on the Internet and in books that offer various checklists and outlines for a database administrator's job, but I've created a list with just seven areas.

Perhaps you're not interested in becoming a DBA—"I already have a career, thank you." That's fine. However, you might be asked to manage a database system in addition to your other tasks, so pay close attention to the first three areas.

I've arranged the areas in order from most important and easiest to learn, and from most-frequently to less-frequently used. Of course, any one of these might become the most important in your day, but if you've inherited a system, you'll want to learn these areas in this order.

For all of these areas, either the Full or Simple Recovery model works the same way, with the exception of the way the transaction log is handled during backups. (To learn more about the Recovery Model settings, see Resources for a link to my previous article on database architecture.)

1. Back Up & Restore Databases
By far the most important thing you will ever do as a database administrator is to ensure that there are reliable, scheduled backups on your system. For the most part, this task is easy to plan and implement, but the larger your database the more choices there are to make.

Let's begin with the backup types. First, there's the Full Backup. This copies all the data in both the database and the transaction log, and empties the log. It takes the most time and space (backups are compressed only slightly with SQL Server's built-in backup). Full Backup is by far the easiest type to restore because this is the only file you need to have on hand to restore.

You should design your backup strategy to take a full backup as often as possible. A common scenario when the database is smaller than 50 GB or so is to perform a Full Backup every day. You might perform a Full Backup less often if the databases are larger.

The second type is the Differential Backup. This operation backs up all the data since the last Full Backup. If you're performing a Full Backup once a week due to a large database, a good strategy might be to take a Differential Backup each day. Note that this type of backup will grow as the week progresses. To restore a database using this strategy you'll need the latest Full Backup and the latest Differential Backup files.