Getting Even with SQL

I’m pretty excited about the new MCDBA
certification. I think it’s going to be a hot ticket for
database administrators seeking to bring that little something
extra to the table along with their résumés. The SQL Server
7.0 Administration exam, which I’ll be reviewing here,
is one of the core requirements for the new certification,
joined (no pun!) with its companion exam, Implementing
a Database Design on SQL Server 7.0. The other core exams
for the Microsoft Certified Database Administrator are
Supporting Windows NT Server 4.0 and Supporting Windows
NT Server 4.0 in the Enterprise.

SQL
Server 7.0 Administration (71-028)

Reviewers’
Rating: “Looks like the
fairest SQL Server exam ever. Be solid
on Windows NT administration, then get
out there and build some actual databases
with SQL Server 7.0. Migrate some databases
to 7.0 from both SQL Server 6.5 and other
products. Profile and test your SQL 7.0
databases, then study the online help
to fill in any gaps in your knowledge.
You should then be in good shape to tackle
this exam.”

Title:
Administering Microsoft SQL Server
7.0.

Number of questions
in reviewed version of exam:
113 on beta; fewer on actual
exam.

Time allowed:
115 minutes.

Current Status:
Live as of April 1999.

Who should take
it?
Network administrators with SQL
Server 7.0 installations, and SQL DBAs
and developers designing and implementing
database solutions with SQL Server 7.0.
The exam is an MCSE elective and MCDBA
core.

The product around which the new exam
revolves, SQL Server 7.0, is so different from the earlier
version, I expected the test to focus more on the differences
between releases. Not so. In fact, this exam appears to
have been written from the ground up for version 7.0 and
bears little resemblance to its predecessors. I took the
SQL Server 6.0 and 6.5 exams for both Implementation and
Administration, and this one’s nothing like them.

If you’re a network systems engineer,
be happy! This is the first SQL admin exam you have a
decent shot at passing without taking the Implementing
a Database course or acquiring equivalent knowledge. No
free lunch though. In exchange for not needing to know
much query-specific syntax, you’ll need a broad understanding
of administrative issues regarding everything from how
to fit SQL Server into your network to understanding NT
user administration.

This review is based on the beta exam.
That means you can’t expect the production exam to look
exactly like the one I took. But you can probably expect
the same kinds of questions. On my exam, I had a mix of
multiple choice questions that include both single answer
(radio button) and multiple answer (check box) responses.
You might also see scenario questions that include a description
of a situation, a primary objective and some secondary
ones, and some response to the objectives. Your job is
to figure out how well the response addresses the objectives.

Getting Ready

I generally use a product in either a test or production
environment (both if possible) before preparing to take
any exam. I can see the value in using third-party practice
tests to help fill in any gaps in product knowledge, though
I don’t use them much myself. Working with a product,
even extensively, doesn’t necessarily impart breadth to
your competence. So I use the Microsoft Official Curriculum,
read the white papers, and browse the Microsoft KnowledgeBase
to fill in those gaps. I use the Microsoft Exam Preparation
Guide as a checklist while I prepare to sit for exams,
checking off topics I feel comfortable with and investigating
the others. As an MCT, I have a rubric that serves me
well for gauging my comfort level with a subject: Could
I stand in front of a dozen people and intelligently discuss
the topic?

Tip: Read the white papers—first
the Features guide, then the ones on the Storage Engine,
the Query Analyzer, and Replication.

Let’s look at the major topics and
see how they relate to this exam.

Planning

You’ll need to assess whether to use NT accounts or SQL
Server logins and understand when you can utilize the
NT group structure. Plan the use and structure of SQL
Server roles. Server roles play an important part in the
product now; the Microsoft guidelines advise using the
SysAdmin server role as an installation and recovery account.
Know your protocols and which network libraries get installed
by default. Understand the relationship between sort order
and collation type.

Tip: Make sure you’re rock-solid
in your understanding of Microsoft’s domain and group
models. Confused about what makes a connection trusted?
Sort it out before this exam. Be especially sure that
you understand the security context difference between
an application and an operating system service and how
this will affect both the SQL Server agent and Windows
9x systems.

Installation and Configuration

Be sure that you understand the different methods of
installing and upgrading SQL Server. Know how to migrate
a SQL 4.2 or 6.0 database to SQL 7.0, and know about the
installation scripts. Know how to install SQL Server on
the various flavors of NT as well as on Windows 9x. I
had a number of upgrade questions and a fair number of
questions involving how to configure hardware resources.

Tip: Know what installation
options require rebuilding databases. Understand the built-in
fault-tolerance features of NT and when third-party RAID
is more appropriate.

Managing and Maintaining Data

Here’s the arena where some of the most notable differences
between this product and its predecessors appear. The
people who constructed this test seemed quite eager to
make sure examinees understand both the uses and advantages
of the file-oriented storage. They also threw in several
questions that require you to understand the backup and
restore features of SQL 7.0. They didn’t slack off after
that though; you’d better understand when, where, how,
and why to use the SQL Server Agent along with its associated
jobs, alerts, and operators.

Tip: Forget about devices,
and learn about file groups. Make sure you understand
the differences between transaction log and differential
backups—and how to restore both. Pay attention to the
way the transaction log is now implemented.

Replication

You’ve probably noticed that replication is important
in SQL 7.0. Though I had few questions about the topic,
those I did get required me to make some fairly subtle
distinctions. Be clear on the relationship between model
and type of replication. Take time to understand the new
merge replication and what changes to the database it
requires. Make sure you know the roles of distributor,
publisher, and subscriber, and the difference in where
resources are allocated in pull subscriptions vs. push.

Tip: Autonomy and latency
aren’t just buzzwords. Know how each type of replication
affects both. Understand the benefits of immediately updating
subscribers.

Configuring and Managing Security

Here’s another area where you can forget about the old
way of doing things. Microsoft has given up the Unix security
model used in previous versions of SQL Server in favor
of one that’s more NT-like. Assimilate the changes to
DCL (data control language), especially the “revoke” and
“deny” statements. Be aware of the relationship between
NT accounts, SQL Server logins, and built-in administrator
logins. Know how they have to be mapped to user accounts
or roles within the database to get user access. I strolled
across one question about the guest user account, so make
sure you understand that one as well.

Tip: Understanding security
context in the NT world is your best foundation for understanding
the positions that roles—both server and database—play
in SQL Server 7.0.

Monitoring and Optimizing

Expect to be grilled on all of the following: Performance
Monitor, the SQL Server Profiler, the Event Viewer, and
SQL Query Analyzer. Conspicuous in its absence: the Network
Monitor; but maybe that’s why the NT Enterprise exam was
made core for the new MCDBA title.

Tip: Read up on this one.
The Microsoft curriculum covers monitoring but not optimizing.
Read the Index Wizard white paper.

This exam should present few problems
to any SQL administrator quite familiar with Windows NT
administration (the concepts are key here, not the admin
tools) and with sufficient time to work though and understand
the new features of SQL 7.0. By my accounting, the beta
test hit on almost every topic listed in the exam preparation
guidelines—so don’t sacrifice breadth for depth when you
prepare. Read the white papers and play with the tools,
especially DTS and the SQL Profiler. You’ll be ready to
take this exam with confidence—it may not exactly be easy,
but it is quite reasonable.