mctssqlserver2005

MCTS SQL Server 2005

MCTS 70-431 Review - Indian Audience

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