Wednesday, 3 December 2008

Tips for 70-431: MCTS SQL Server 2005

Exam 70-431 (MCTS SQL Server 2005) is one of the more popular exams and one of the certifications that always comes high in the list of most desired certifications.

Note: I originally posted this guide on Boards.i.e. sometime ago but I reposting it again with some more information.

It is a mix certification requiring the hats of both an admin and a developer which makes it a challenge.

A couple of things still apply. You should take a read of this post which gives you ideas on what you should be doing to get yourself in the head space for this exam. Also have a look at the exam matrix for this exam.

Now this is also one of the few exams where you can say that it contains both questions and simulations! Once it has been mentioned in a public live meeting we can say it as its not covered under NDA! So this is one of those rare times.

A word about simulations. It is possible to get full marks in the questions and not so good in the simulations and still fail because you didn't demonstrate the knowledge. Don't let this deter you, as for most people the simulations are easier than the questions because they provide a visual reference.

You can pick up a simulations demo here which is a good thing if you haven't tried an exam that contains them.


There are 2 very good ones. The MS Press training kit book and the Sybex Training Kit book

You will probably need one if not both. Now, no book will fully prepare you for the exam, so its a good idea to get some hands on practice for this.

To do this download a copy of the SQL Server 2005 trial and a copy of Windows Server 2003 trial and create 2 (yes 2!) virtual machines with them. You can create one and copy it and then rename the server from within the virtual environment.

If you have picked up the MS Press training kit it comes with a 180 day evaluation of SQL Server 2005 Enterprise.

Now why on earth do I need 2 machines. This is so you can practice things like setting up replication, log shipping and database mirroring. It will also get you used to using the UI more so that you will be better prepared for the simulations.

Will the express editions do. Not really. Some of the higher end functions are not supported on these versions so you will loose out. Have a look at the product comparison here

Also if you are working with the Development edition, it is the very same except for the licensing.

The main goals of using the software are

1. It will get you familiar with the UI
2. It will reinforce the stuff you have learned in the book.
3. You should be able to do most of the topics from the exam matrix
4. You will start seeing some of the common problems that happen when doing certain operations and learn to troubleshoot them.


Couple of things to remember

1. Least principle.. The idea behind this is that it is always the least permissions or least administrative or least performance impact. Keep this in mind. This means that the solution should only use the permissions necessary to perform the task. Or require the least amount of changes to achieve the goal or impact the system the least.

2. Differences between the different RAID levels, their performance and also how to distinguish the different ones from their numbers and names.

3. Security! Know it! Its seems obvious but understanding the implications of security and security principles is a bit thing for all MS software.

4. If there is an option between new and old tech, new tech nearly always wins. MS tend to use their exams like advertisements at times, showing the benefit of the newer stuff. So as a general rule of thumb its new tech over old.

No comments: