Microsoft SQL 2005 Exam 70-447

By Kalman Toth, Business Intelligence Architect

July 14, 2007

Microsoft Exam 70-447 is titled UPGRADE: MCDBA Skills to MCITP Database Administrator by Using Microsoft SQL Server 2005 . It is the second required exam for upgrade from MCDBA. The upgrade is 2 exams versus the 3 exams when starting from scratch.

The exam has a new twist to it: business cases. 35 of the tasks (second part 1 1/2 hour) are traditional multiple selection items, most radio buttoned, some check marked, and some pulling blocks to their places, however, 6 of the tasks are business cases whereby you get a complete description of a business operation with technical and business requirements and you are asked 4-7 mostly radio buttoned questions (15 minutes typical). An arranging blocks in order question maybe the sequence of setting up an encryption/decription scenario, or restore steps after a hardware failure.

There are 64 questions on the 3-hour test. Important to remember: when finished with a business case, you can go back a review all questions, change the answers, however, once you are out of it, you cannot go back it and the time leftover is just gone.

The entire exam is very practical in nature; there are no “trick” questions. A SQL Server 2005 expert can pass it easily. Also a beginner who mastered “SQL 2005 Grand Prix”, “SQL 2005 Grand Slam”, "SQL 2005/2000 & BI Triple Crown" or "SQL 2005 MCITP DBA Ace" can breeze through it. For beginners though probably requires a couple of tries.  Perusing Microsoft exam prep books helpful but I am doubtful that they are sufficient for passing. Also helpful internet available exam preps. While some of the exam preps usually have old, obsolete test questions, nevertheless, they are good to practice, get used to timing and also some of the explanations may prove to be good knowledge. Occasionally though the exam preps may be giving the wrong answer and the wrong explanation. Buyers beware!  Also some of the blogs discussing exam experiences maybe helpful.  Just Google it.

The multiple choice part stresses on the new features of SQL Server 2005: creating index with included columns, creating endpoints for database mirroring, using dynamic management views, when to use database snapshots and planning for failure/recovering from database loss.  Not all new though: login configuration, restore sequence or index statistics maintenance.

The business case studies part is really interesting. After a page or two of description and hardware platform chart, you are given a few questions. You cannot select the answer based on the question. You have to go back to description part and check for example the business requirement for down time after complete database failure.

While the exam is 240 minutes, part of it is for the preparation. I found that I was pressed for time on business cases part and plenty of time left on straight questions part.

The passing score is 700. The result score is the lower of multiple-choice and business case studies sections.

Overall 70-447 is a very well-done, practical and fairly enjoyable test. If you don’t pass the first time make sure you have paper and pencil for writing down the items you missed as soon as you leave the exam center(braindump). Do some studying and working with Management Studio and schedule a retake within 1-4 weeks.  When you take the second time you have several advantages: some of the questions will be the same, you are also used to the style of exam and time management.

Here is a helpful hint: when 3 of the radio-buttoned answers are from SQL Server 2000 and one is a new feature of SQL Server 2005, the likelyhood is high that the SQL 2005 answer is the correct one.