The SQL Server Challenge
You don’t have to be a DBA for a worldwide organization to pass this
test, but you’d better know all the ins and outs of large database administration.
If you work with relational database servers, you can’t
help but notice that Microsoft has been making a strong
push toward the enterprise-level database storage marketplace.
Microsoft continues its successful journey with SQL Server
2000, the successor to the popular SQL Server 7.0 platform.
Specific areas of improvement in the database platform
include key enhancements related to reliability, scalability,
performance and availability. On the surface, SQL Server
2000 may not look like a huge upgrade (certainly not as
dramatic a change as the move from SQL Server 6.5 to SQL
Server 7.0), but when you drill down into the product,
you’ll find many important architectural changes.
Server 2000 Admin
“Challenging overall, but passable with
Installing, Configuring and Administering
Microsoft SQL Server 2000 Enterprise
Went live Jan. 25, 2001.
Who Should Take
It? Elective credit for MCSE;
core requirement for MCDBA.
Querying Microsoft SQL Server 2000 with
a Microsoft SQL Server 2000 Database
With exam 70-228, Microsoft wants to test your ability
to administer SQL Server 2000 in large environments. In
fact, the recommended “audience” for the exam is someone
who has at least one year of experience with SQL Server
2000 in an environment that ranges from “50 to 5,000 or
more users” and with “databases as large as 2TB.” That’s
a fairly tall order. Rest assured that if you know what
you’re doing, you could get by without being a DBA for
one of the world’s largest databases. I took the beta
version of this exam and found it to be challenging overall.
However, if you really work with SQL Server often and
have taken the time to learn about its many features,
you should do well.
I found the exam to be a fair assessment of my SQL Server
skills. The questions I encountered were detailed and
focused in specific topics related to SQL Server administration.
Performance monitoring and optimization seemed to be hit
pretty hard, as was configuration of a database server
given specific business requirements.
The questions themselves were fairly straightforward.
The majority were standard multiple-choice questions,
but the test also included several multiple response questions
(where you’re required to choose two or three answers).
All required a good understanding of how SQL Server 2000
A good way to prepare for any exam is to start with the
topics presented in Microsoft’s Preparation Guide. In
this case, the goals truly do focus on features of SQL
Server 2000 that I find to be relevant to the real-world
work of DBAs. Let’s start by looking at the specific areas
of the product that Microsoft expects you — a potential
enterprise-level DBA — to understand.
Installing and Configuring the
Microsoft has gone to great lengths to make the installation
and basic setup of SQL Server 2000 as simple as possible,
and it really shows. In many cases, you’ll find yourself
choosing all of the default installation options for SQL
Server. However, a good DBA must understand the meaning
and relevance of database sort order and character sets.
It’s important to know how different versions of SQL Server
can exist on the same machine and how you can upgrade
from earlier versions of SQL Server. Also, you should
understand how multiple instances of SQL Server 2000 work.
Tip: You can run multiple
instances of SQL Server on the same machine at the same
time. The default instance can be SQL Server 6.5, 7.0
or 2000, but additional instances can be SQL Server 2000
only. Make sure you know how to upgrade databases. To
upgrade databases from an instance of SQL Server 7.0,
you must use the Copy Database Wizard. To upgrade from
SQL Server 6.5, you must use the SQL Server Upgrade Wizard.
Perhaps one of the most important and common job functions
for a database administrator is to create and manage actual
databases. Important concerns include where to store data
files and transaction log files and how to optimize storage
for multiple databases. For small database servers, you
can get away with ignoring some of these issues. However,
in larger and more heavily used installations, file placement
can dramatically affect performance. Be sure you know
where to place data files given a specific server disk
configuration. Once the databases have been created, understand
the operations for automatically expanding database and
transaction log files.
Although some people who find themselves in a strict
DBA role (vs. that of a database developer) may not work
with all of them frequently, you should understand how
various database objects work. The list includes tables,
indexes, constraints, views, stored procedures, user-defined
functions (UDFs) and triggers.
Tip: When creating databases,
it’s recommended that you place database files and transaction
log files on separate physical hard disks from transaction
log files. Note that placing log files on different logical
partitions can actually decrease performance. Also, understand
how various levels of RAID should affect data file placement.
Managing, Monitoring and Troubleshooting
A good DBA will always proactively monitor the databases
for which he or she is responsible. Fortunately, many
features in SQL Server 2000 make this potentially complex
task much more manageable. Keep in mind that your goal
is to optimize resource usage and that the most valuable
resources on your servers include processor utilization,
physical memory usage, physical disk I/O and network performance.
It’ll probably come as no surprise that you should understand
the various backup methods available in SQL Server, including
recovery modes (full, bulk-logged and simple), full backups,
differential backups and transaction log backups. Note
that the way you design your backup operations will affect
how you can restore information.
Tip: Here’s one area where
practice will really help. Create some sample databases
on a test server and try using various backup and recovery
methods. It helps if you have a sample application that
can generate queries that run against SQL Server. Monitor
the cache hit ratio and the number of batches that are
executing. Also, use SQL Server Profiler to monitor some
sample queries and see what types of information you can
get from the traces.
In its objectives Microsoft places a heavy emphasis on
a DBA’s ability to keep a database server running optimally.
Specifically, you should know what the common Database
Consistency Checker (DBCC) commands do and how and when
you should use them. Be sure you understand how indexes
work and how to determine if your indexes are fragmented.
Remember that on enterprise-level servers, you can’t just
go around rebuilding indexes whenever you want; this can
reduce database performance. Also, know how and when you
might want to use materialized views (also referred to
as “indexed views”).
But wait, there’s more! Know how to monitor performance
using the various SQL Server 2000 features that are at
your disposal: Performance Monitor, SQL Server Profiler,
SQL Server Alerts (and how they interact with the Windows
NT/2000 Event log), features in Query Analyzer, and the
Index Tuning Wizard. For example, do you know how to provide
output from a SQL Profiler trace or a graphical execution
plan generated by Query Analyzer and determine the potential
cause of the performance problem? Such tasks are challenging
but important to understand.
Extracting and Transforming Data
Wouldn’t it be nice if every relational database system
in the world could communicate through a common language?
Although the Structured Query Language (SQL) is a huge
step in the right direction, there are many differences
among real-world relational database systems. Large organizations
tend to have many different types of data repositories.
These can range from Excel spreadsheets to mainframes
to various types of database servers. In an enterprise,
DBAs are often responsible for moving and copying data
between various systems. That’s one reason that Microsoft
has included the Data Transformation Services (DTS) with
Be sure you understand the various methods for transferring
data in and out of SQL Server, including DTS, BULK INSERT
and bulk copy (bcp). Just as important, understand the
capabilities and limits of each so you can decide the
best solution for a given situation. Also, be sure you
know how to set up linked servers and perform distributed
queries (that is, queries across multiple database servers).
Know which utilities are the right tools for the job.
Tip: Be sure to play around
with DTS. Even if you don’t have access to large databases,
you can use the user-friendly tools and tasks to move
data between databases on your machine. Also, be sure
to take advantage of the fact that you can install multiple
instances of SQL Server on the same machine. This is great
for simulating distributed queries and data movement between
Managing and Monitoring Security
In many organizations, the company’s crown jewels are
stored in databases. Therefore, it’s the job of a DBA
to ensure that only authorized users are able to access
information. Needless to say, you need to understand security.
As an enterprise-level DBA you should understand that
there are multiple levels of security in SQL Server 2000.
Just as Windows 2000 allows you to set permissions on
various objects such as logins, shares, files and folders,
SQL Server allows you a flexible set of security options.
You should thoroughly understand SQL Server’s authentication
modes. Also, be sure you know the various security features,
including server logins, server roles, database users,
database roles, application roles and object-level permissions.
All are important and must be administered carefully.
Tip: Microsoft recommends
that you use Windows-based authentication whenever possible.
Be sure you understand how this works and situations in
which you may have to use mixed-mode authentication. Also,
be sure you understand the interactions between SQL Server
logins, database roles and database users. The differences
are important, and using them effectively can save a great
deal of administrative burden.
You can get more information about
administering SQL Server 2000 in several
places. Here are a few of my favorites.
No Easy Task
General knowledge in other areas will also be helpful
in your job as a DBA. For example, know what SQL Server’s
Analysis Services are designed to do; also, have an overview
of the concepts behind data warehousing and data mining.
In order to prepare for the exam, be sure you spend plenty
of time with the product and its tools. I’ve worked with
SQL Server 2000 since the beta days, and I still occasionally
find new and useful features or “gotchas.” A good way
to go about preparing for an enterprise-level exam is
to imagine realistic scenarios and think of solutions.
Make no mistake: Understanding SQL Server 2000 is no easy
task, and the exam will make sure you’ve done your homework.
But, if you have a solid background with the software,
you’ll find the exam to be tough, fair and (best of all)
passable. Good luck!