Exam Reviews

Getting Even with SQL

SQL Server 7.0 administration is a new animal compared to SQL 6.5, and so is the Admin exam. You'll find it broad, fair, and ready to go adaptive out of the box.

• By Keith D. Jablon
• 06/01/1999

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.

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.

Reasonable, Not Easy

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.