The Secret Life of a DBA
Master these 7 tasks and you'll be well on your way to managing a professional SQL Server database.
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.
A Log Backup operation backs up all the data in the transaction log since the last Full Backup, Differential Backup, or Log Backup. Think of this as a mini-Differential Backup. The difference is that the database must be in the Full Recovery model to work. Log Backups are often taken every few hours or so and provide the most safety for your data. To restore a database using transaction log backups you'll need the latest Full Backup (and the latest Differential Backup if applicable) and all Log Backups that you take.
A final type of backup is the File Backup. SQL Server can separate its data onto several files for performance and safety, and these files can be backed up individually. You'll often see this arrangement when the whole database simply can't be backed up due to extreme sizes. A rotating File Backup strategy is used to back up pieces of the database as time permits. Restoring this type of database is the most difficult. You'll need all the File Backups to restore the database.
Here's a concrete example. To perform a Full Backup directly to a disk, simply open the Query Analyzer tool, connect to the server in question, and type the following:
BACKUP DATABASE databasename TO DISK = 'c:\temp\backupname.dat'
Replace databasename with the name of the database you want to back up and c:\temp\backupname.dat with the drive, share, or other location on the disk that you want to back up to. The extension can be anything you like, but .dat is the default.
The WITH INIT qualifier replaces the file (if it exists) with the one you're creating. Without this qualifier, the backup is appended into that file, and you'll need to specify which backup to restore from when you need it.
If you want to back up the database to tape, the following command sends the database to the first tape device:
BACKUP DATABASE databasename TO TAPE = '\\.\TAPE0'
To back up a database graphically, open Enterprise Manager, double-click any server's name, and then locate the Tools menu item. Select Wizards, then open the Management item and select Backup Wizard. This wizard will lead you one step at a time and help you back up your databases.
Your overall backup strategy should answer the question, "How much data can we really afford to lose?" There will be some time lag in a database failure and the restore process. If you're only taking a Full Backup once a week, that's how current your restore will be. If the database fails on Saturday you're out six days' worth of data.
2. Create a Maintenance Plan
After you verify that the database has been backed up recently, you should ensure that the database is maintained properly. The maintenance you need to concern yourself with involves three broad areas: Make the database safe; make sure its physical files are in good shape; and make it go fast.
There are several commands and graphical tools you can use to perform this maintenance. Luckily, SQL Server provides a complete Wizard that will guide you through all the maintenance most average databases need. The Wizard even includes Full and Log Backups, so it can automate that for you as well. In addition, the Wizard can send you an e-mail that will tell you how the maintenance went for that run.
To begin the Wizard, open Enterprise Manager and drill down to the Management object. Right-click the Database Maintenance Plans object and select New Maintenance Plan ? from the menu that appears (see Figure 1).
You could select the defaults for all screens if you like and the system will be at least minimally protected. It's a better practice to at least change the scheduling optionsthe defaults are weekly. If the database is below 5 or 10 GB or so you should seriously consider performing all maintenance daily.
The Wizard will also schedule your backups for you. You can send these backups to disk or tape, and the Wizard will also erase the old backups for you.
Additionally, you're given a choice to create an "operator" so that you can receive mail containing the status of the maintenance.
Note that you'll need to make sure the SQL Server Agent service is started on the server, and it's normally best to set that service to Automatic. It's also best if that service is started as a user that has access to a mail account.
You need to familiarize yourself with the commands as the database grows in complexity and size so that you have more control over the timing and impact of the maintenance, but you should at least ensure that you've run the Wizard on all your databases. You can learn more about the maintenance commands (most notably the DBCC variety) in Books Online.
3. Create a Disaster Recovery Plan
Once the database is backed up safely, and you've given some thought to its maintenance, you need to create a disaster recovery plan. This is a document that is publicly available to the technical staff that explains what your environment is, how it is backed up, where those backups are stored (preferably off-site), and how you will recover in the event of a hardware, software, or natural disaster.
It's not enough to create this planyou have to test it. I've visited several shops that claim to have completely thought out their disaster recovery plans. When I ask them to test the plan on a lab environment, it's always an eye-opener when things don't go as planned.
Your company or organization depends on you as the technical professional to be able to tell them what their "exposure" isthat is, how long it will take them to get back to business in the case of a fire, theft, flood, or hardware failure. I've been through all of these, and a tested plan is money in the bank.
4. Create and Modify Database Objects
The commands used to access or update data in a database are called Data Manipulation Language (DML). The commands used to create or modify database objects that hold or control data (such as tables or views) are called Data Definition Language (DDL).
The DDL commands that create database objects usually begin with the word CREATE, such as CREATE TABLE, followed by various qualifiers. The commands that change the objects once they are in place usually start with ALTER, and those that delete the objects normally begin with DROP. Although the exact syntax for each object is beyond the scope of this article, you can learn more by studying each of these statements in Books Online.
You can also do the same thing using Enterprise Manager, and in many cases this method is a bit safer. You will find many wizards on the Tools menu item that will guide you through creating everything from a database to a table.
Whether you're using commands or a graphical tool, the most important thing to do when you're modifying database objects is to have a back-out strategy. That means you should have at the least a backup of the database, and it's also a good idea to right-click the object you're about to change in Enterprise Manager, select All Tasks from the menu that appears, and then select Generate SQL Script. This will create a set of DDL commands that can re-create the object should you need to.
Another important thing to keep in mind before you create, modify, or delete an object is to know exactly what the change will affect. You should never access a commercial program's base tables directly unless you are expressly told to.
5. Tune Performance
Performance tuning begins with design. If your system is designed poorly, all the hardware and software upgrades you make won't reach their full potential.
The next step is collecting metrics. SQL Server provides a rich array of Windows Performance Monitor objects and counters to assist you in forming a baseline. Once you have captured a "normal" system load, you can check these metrics against the areas defined below.
Assuming your database has a good design (or if you're stuck with one from a commercial package), there are three areas that you need to concentrate on for the best possible performance.
Having adequate hardware resources for your system can't be overestimated. You need to concentrate on memory and hard drive response, then CPU, and then network trafficin that order. You can monitor these items using Microsoft Windows' Performance Monitor tool. You can also use your hardware vendor's system tools to assist you in discovering hardware bottlenecks.
In addition to the hardware, the general operating environment is also important for optimization. A proper network infrastructure is a governing factor, as well as the operating system configuration for the server. A good understanding of your network and the servers your database runs on is imperative. Any other programs should be eliminated from the server so that the database does not have to compete for resources.
Server and Database Settings
The most complex part of optimization is the way SQL Server and the databases are set up. Here you'll face such decisions as file layouts, drive optimization, CPU assignments, and more.
Fortunately, SQL Server is largely self-tuning, as far as locking, memory allocation, and so forth. For more in-depth analyses, you'll either need to spend some time learning the effect of these parameters or use a tool such as Spotlight or Prognosis. These programs make diagnosing performance issues much easier.
6. Analyze Capacity
Capacity analysis is the mixture of experience and science that you'll need to estimate the size of your database environment. In rare cases, you're given a small database on adequate hardware for just a few users.
In more complex environments, you'll need to create an Estimated User Profile. This user profile includes the type of user (heavy, light, etc.) and the hours the user will access the system. To properly profile the user against the system, the testing environment for the code should be monitored to develop a per-unit costhow much CPU, RAM, network, and disk activity is taken for an individual activity. Once you have that cost, you can pair that up with the user and develop a "per-user cost," which will give you a rough estimate regarding how much each server can handle. Add in the number of users by category, and you can create a capacity analysis.
You need a separate set of calculations for sizing the storage. In some cases, your software application vendor will provide this information, but in others you'll have to calculate this for yourself.
The normal process to calculate database size is to take the average byte count for a row of typical data in a table. Multiply this number by the number of estimated records, and you have the table size. Add up all tables, estimate at least half that size for indexes and add a growth factor, and you get an estimate for space.
7. Create a Data Model
Creating an effective data model for implementation is one of the most difficult tasks. We can break it down, however, into three manageable steps.
As with the other tasks in this article, we're taking an abbreviated look at these steps in the process. There are entire books devoted to each step, but this outline will give you an idea of the complexities involved.
Define the Entities
The first step when creating a model is to define the "entities," or nouns that your database will store. Have the users define the things they want to store by using sentences such as "We need to keep information regarding our clients and their orders."
Pull out the nouns from the sentences, such as "clients" and "orders." Later, these entities will become your tables.
Define the Attributes
Next, take the entities from step one and ask what the users need to know about them. For instance, they might want to know about the name, address, and phone number for a client. These items become the "attributes" of the entities. Later, these attributes become the columns for your tables.
Define the Relationships
With the tables in place, you can define how they are related. You begin by using sentences, such as "One client can have more than one order. One order can have only one client."
With those relationships verbalized, you can begin to set into place the key fields in each table that relate them. At this stage you apply "normalization rules" that help you break up the tables even further.
There are many other important subjects you'll need to learn as a DBA, not the least of which is security. For more information on that subject, read "Use the Best Security for SQL Server" (see Resources). There are also some great Web sites that can help you learn more about SQL Server. One of my favorites is www.InformIT.com. You should also bookmark www.microsoft.com/sql for all things SQL Server.