Resources for MCTS SQL 2005 (70-431)

Ginger

After seeing quite a few people asking about this particular exam, I have collated some information together that should answer most questions that people.

About the exam

You need a passing score of 700 out of 1000. The exam itself consists of a mix of questions and simulations. This is common in these type of product exams especially in relation to server software.

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

And asking is the exam hard is a relative question. If you are working in DBs everyday this exam has some tricky aspects but like every exam some people will find it extremely easy, others will say its the devils own handiwork.

Books

First off get a book. The recommended one is the MS Press MCTS Training Kit: Implementing and Maintaining SQL Server 2005 (ISBN: 978-0735622715)

Link: http://www.amazon.co.uk/MCTS-Training-Kit-Implementing-Pro-Certification/dp/073562271X/ref=sr_1_1/026-9189271-4020436?ie=UTF8&s=books&qid=1188815318&sr=8-1

There are a couple of different ones out there. Be aware that none cover everything you need to know. Why not? Because the exam as stated above a mix of questions and simulations. If you dont know your way around the interface you probably wont be able to do the simulations.

It will however give you the basics and run through the features of SQL Server 2005 that you will need to know.

What DO you need to know?

The milllion dollar question. Well, the easiest answer is to follow the exam topic matrix as detailed on the MS Learning site. Scroll down to the skills being measured.

This lists what they can examine you on. Its fairly extensive and can seem overwhelming. But .. if you are working in this stuff quite regularly then it wont be that bad. Thats the crux and leads me on the next thing you should be doing.

Link: http://www.microsoft.com/learning/exams/70-431.mspx#E3DAC

Software

Get a copy of the trial version of SQL Server 2005 Enterprise edition. Install it on a virtual machine if you can. You should be then practising setting up security, standard admin tasks and playing with the new features of SQL 2005. This will do a couple of things.

1. It will get you familiar with the UI
2. It will reinforce the stuff you learnt 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.

The Trial version can be got from http://www.microsoft.com/downloads/details.aspx?FamilyId=6931fa7f-c094-49a2-a050-2d07993566ec&DisplayLang=en

If you get the MCTS book listed above it comes with a 180 day free trial of SQL Server 2005 Enterprise. If you are working with the Development edition, it is the very same except for the licencing.

Sample Exams

There are plenty of providers out there saying they give sample questions. MeasureUp is one of the few certified sample providers. You should invest in a sample question set purely to get used to the exam format especially if this is your first exam. Wording will cause you the most hassle as you need to be very thorough in reading the questions. This goes for every MS exam.

Finally

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 neccessary 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

I hope this helps and answers most of the questions that you have in relation to the exam and how to prepare for it

Static M.e.

Thanks for the Info Ginger!!

This will prove very helpful I've just got the MS press book for this exam and have started studying.

I'm by no means a DBA but I manage 15 or so SQL 2005 Dbs so I need to know how to best maintain them hence the study