01: Some Facts
- Exam Name: TS: Microsoft SQL Server 2005—Implementation and Maintenance
- Exam Number: 70-431
- No of Questions: 40
- Minutes: 135
- Passing Score: 70% (700 / 1000)
- Exam Fee (Standard): $50
02: My Exam
- 39 Multiple Choice Questions and 1 Simulation question
- Took about 70 To 80 minutes for the questions
- Scored 1000
- Paid $40 for the exam (Got 20% discount @ SSI, Vadapalani, Chennai-26)
- Since I gave the exam within 6 month's of release, I’m being conferred with 'Charter Member' certificate.
03: Review of my exam
- About 10 questions are pretty basic (Each took < 20 seconds to answer)
- The simulation isn’t really tough (Single step only. Sorry, Can’t elaborate due to Non Disclosure Agreement)
- My prior experience with SQL Server 2000 helped answer some questions. Remember, this exam is about ‘the whole of SQL 2005’ which includes things from SQL Server 6.5 onwards, and not ‘New Features in SQL 2005’.
04: How did I prepare?
(I don’t recommend this strategy to all. In my case, I didn’t have any other choice, but to learn thru ‘Research and Practice’. Also, I’ve been playing with SQL 2005 for over 2 years, since Beta 2, followed by CTP releases)
- I was a subscriber to SQL Server Magazine and a member of PASS (Professional Association for SQL Server) http://www.sqlpass.org/ So, I got to enjoy plenty of materials (Chats, Articles, PPTs, PDFs, Scripts etc) to learn about SQL 2005.
- Hit the SQL 2005 Books online (Updated: Apr 2006) and the materials I had (See point 1) for those topics not covered in the above courses
- Bought and read the Apress publisher’s book “Beginning SQL Server 2005 for Developers: From Novice to Professional”. Don’t get deceived by the title. It’s good even for experienced.
- The book I was waiting to read “MCTS Self-Paced Training Kit (Exam 70-431) Microsoft SQL Server 2005—Implementation and Maintenance” has been added to the Books24x7 just a few days ago (Aug 2nd Week). This book was released in US at the end of June, but it’s still not available in India.
05: How can you prepare?
- If you are new to SQL Server: Learn and understand SQL 2000 – Skip DTS, Installation, and Index tuning wizard. Concentrate on database objects and queries (Simple ones)
- If you know SQL 2000, but new to SQL Server 2005: Buy, Read and practice (yes, practice) “Beginning SQL Server 2005 for Developers: From Novice to Professional” by Robin Dewson
- If you have expertise in SQL Server 2005: Read and practice (again, practice. What if you got 5 simulation questions?) “MCTS Self-Paced Training Kit (Exam 70-431) Microsoft SQL Server 2005—Implementation and Maintenance” available at http://www.books24x7.com/book/id_14335/toc.asp
- Take the assessments mentioned in the previous section
06: Tips
- Don’t ignore fundas like hardware / memory requirements, protocols and common port numbers
- Again remember, this exam is about ‘the whole of SQL 2005’. The basic syntax of SELECT, INSERT etc remains the same.
- Practice all the topics you could in your home machine. You should have no difficulty in practicing installation, basic queries, bcp, DTA, snapshots etc
- Of all the books available in Indian market, only the one specified above was written for the final release AND has some relevancy for the exam. All others were pre-releases or covers only new features.
MCTS 70-431 Review and Syllabus
07: More Tips:
Minimum requirements for processor, Memory and OS
SQL Server Profiler, including Tuning Template
Indexing commands like 'Alter Index All'
Service Broker - Create Contract etc
Backup and Restore commands, like RESTORE HEADER ONLY
Recovery models, Log shipping
Use of DB Snapshot
Attach and detach database files, and other backup methods
Using and managing SQL Jobs - On Success, On Failure
Replication, including Tracer Tokens
HTTP Endpoints - default for STATE
Locks, Deadlocks, Trace the Lock: Deadlock Chain etc
Various options while creating views, e.g: WITH SCHEMABINDING
When to use CLR, and its permission sets
New triggers, like DDL After Trigger
Data partitioning - Horizontal etc
Home Page of Seyed Ibrahim