One of the common questions I see asked in the newsgroups
quite often is what exactly does a DBA do? You hear answers that range from
“walk on water” to “look at the ceiling tiles counting the number of divots in a
given tile”. Of course the first quote was from a DBA in the field and the
second one was from someone that was very aggregated with their DBA.
So what exactly does a DBA do? Many organizations that
don’t have a DBA don’t realize how much a DBA actually does do (or can do). Many
DBAs are very under-utilized, which further strengthens the complaint that DBAs
don’t do anything. Really, it’s hard to classify a DBA in one category. In this
article, I’ll show you some of the types of DBAs and their job descriptions I
often see in the field. Keep in mind, that there are tons of other
roles/responsibilities that may not be mentioned here.
Production DBA
The production DBA spends most of his or her day trying to
work themselves out of a job. This is because that no DBA in his right mind
would run a backup manually every day. Instead, he would automate it and
schedule it using SQL Server agent. A true production DBA usually has the
following base tasks:
§
Install SQL Server – Usually, a production DBA will use the
corporate standard to install SQL Server. He may also develop an unattended
method of installing SQL Server. This also includes service pack deployments.
§
Install and deploy databases – A pure production DBA would use
pre-made scripts to upgrade and install databases.
§
Create backup plans – Based on the business needs of the company
(how valuable is your data), find the best solution for the budget and create a
backup schedule around the plan. The production DBA would also raise the red
flag if there are any vulnerabilities in his company.
§
Test disaster recovery scenarios – Performs regular random drills
to test the backup plan and to test the integrity of the company’s backups.
Needless to say, a backup is only as good as your ability to recovery from it.
§
Manage security – Work with the security department, the
development DBA and the product planners to determine how much access the
application will need to the database. The production DBA is the gatekeeper of
this access typically.
§
Sizes out machines – He will take in the benchmarking evidence and
find out what type of machine will be needed for deployment.
§
Receives projects from development DBAs - Works with the
development DBA for a clean handover of a product.
§
Performance tuning – This type of DBA tunes the physical layer of
the database. He may choose to do this by tuning the file structure and
organization, scheduling index rebuilds, and any hardware-type decisions.
One of the key tasks that I purposely did not mention above
was a pure production DBA does not control the data. He would only control the
physical implementation of the data, not the model or the data inside the
tables. I know very few pure production DBAs anymore. With SQL Server 2000, most
have migrated into a hybrid role, which I will discuss momentarily.
Development DBA
Development DBAs are tightly coupled with the developers of
a project. It is generally a good idea to have a ratio of 3 developers to each
development DBA. Some projects that I’ve seen need a 1:2 ratio due to the amount
of involvement in the DBA. This is typical in projects that use Analysis
Services, where the DBA has to create the cubes and ETL process. Here are a few
of the typical tasks that the development DBA does:
§
Data modeling – Creating the physical data model and modifying the
model on an as-need basis. This usually means working closely with the data
analyst and product planners. Some development DBAs find themselves also working
on a closer level with the analyst to explore what the logical model must look
like as well. This can be very time consuming and in most companies, it is
preferred that the analyst create the logical and conceptual models since they
know the business better than the DBA.
§
Creating DTS packages – This is a new task for a DBA in SQL Server
7.0 and greater. This is where the DBA must create processes to load and scrub
data.
§
Creating the installation scripts – This of course includes the
base installation scripts to create the tables, indexes, stored procedures and
initial data. This also includes though any upgrades. Controlling the database
builds is the most frustrating and time-consuming part of a DBA’s time.
§
Stored procedure writing – This is the most controversial item in
the job description. A pure development DBA writes, tunes and modifies the
stored procedures. Most DBAs do only assist in the complex queries, leaving the
simple ones up to the developers. My personal feeling on this topic is that I
can help tune 10 select stored procedures versus write 1.
§
Performance tuning – The development DBA handles the performance
tuning at the query and index level. He can do this by running Profiler to
determine where poorly running queries are and provide any assistance in
rewriting the query. He must also look at execution plans to determine where
indexes can be built to improve performance.
Hybrid DBA
In a
recent article, I discussed a new type of DBA that
I’ve noticed evolving over the past few years. As time passes, the line is
blurring between the two main types of DBAs to where almost no DBA is a pure
development or production DBA. This is how the hybrid DBA was born. This type of
DBA is ideal because he can walk a project from its infancy to deployment.
Essentially, performing a combination of all the tasks mentioned in the two
types of DBAs' portfolio. For more information about this evolution read "Death
of the Production DBA".
Other new DBA skills:
Loading test data
Manage the XML communication
Learn CLR for stored procedures (we'll go into this
topic in a later article)
Organization
With the tasks laid out, you may be wondering to whom would these DBAs would report? I’ve seen tons of varying organization charts for each type
of DBA. I prefer a central DBA group that supports, develops and analyzes
databases. I’ve also seen where a production DBA would report to the support
group (typically the same people supporting the Windows server machines). I
don’t like this model because it creates a divide between production and
development DBAs. This divide could lead to the production DBA asking the
development DBA, “How in the world could you hand me this?”.
I see even more controversy to where organizationally the
development DBA report. It seems that this type of DBA usually reports to the
development organization, which again creates an even wider divide between
support and development. These two groups of DBAs should have a tight bond and
constantly be sending each other feedback on what each other has in the pike and
what the production DBA is seeing in the server room.
