Tips about exam 70-431 (MCTS: SQL Server 2005)
On Friday, 9 February, 2007 at about 11:30 AM I passed exam 70-431 and earned the title of MCTS: SQL Server 2005; now that the excitement is growing dim, I'd like to take the time to share my impressions on the new certification model embraced by Microsoft.
First of all, I've been quite impressed by the number of simulations included in the exam. For those of you who are wondering, simulations are a (relatively) new kind of questions, very different from the usual multiple choice ones we were used to face in the past: they consist in a series of interactive screens which (almost) closely resemble the ones you should see while working with the GUI tools included with SQL Server 2005. So, to pass this kind of questions, you should be familiar with these tools.
I must confess, I was always used to think a good DBA should know how to get the job done without GUI tools. As a professional developer, I like very much to code; and I like to work with my SQL Server using just clean TSQL statements. But the truth is that GUI tools often quicken the steps needed to do complete everyday jobs: think about restoring a database which has say 20 transactional backups to restore in order to get it functional... Admit it, how many lines of TSQL code are you going to type? Isn't it faster to click 4 buttons inside Management Studio? Sure, and Microsoft realized it. So, to pass this exam you should also know where to watch inside and how to work with Management Studio, Configuration Manager, Surface Area Configuration and the Profiler.
To my surprise, the number of questions regarding system views (most importantly the objects belonging to the sys schema) was bigger than the number of DML/DDL related questions; I think one of the most important requirement of this exam is to prove the attendee really knows how to check for performance problems inside SQL Server and how to fix them. Index creation and mainteinance is also very important: you should really know how indexes work, clustered vs. non clustered indexes differences and how they relate to performance, columns inclusion inside them, XML indexes (primary, kinds of secondary); two or so of the questions related to full text indexes mainteinance and updates. The total number of questions for my exam was 52.
The result of my exam really dazzled me; I got a 1000/1000 points! Since I'm a developer, I wasn't expecting so much from an exam which should belong more to a system administrator than to my role.
I prepared for the exam with the aid of two books (and Books Online, of course!): MCTS SQL Server 2005 - Self paced training kit from Microsoft Press and MCTS Microsoft SQL Server 2005 - Implementation and Maintenance from Sybex. Both are really good books but I found the Sybex one to be clearer in some aspects; the Microsoft Press one is really detailed and sometimes could lend you to confusion. My advice is to buy both the books and grab the core concepts from the Sybex book and deepen with the Microsoft one.