Just little about C#, .NET, SQL Server, SharePoint and SAP

Exam 70-431: TS: Microsoft SQL Server 2005—Implementation and Maintenance

Posted by zieglers on January 23, 2007


Today I got my 3rd TS exam, TS: Microsoft SQL Server 2005—Implementation and Maintenance. I passed with a score of 982. However, this time it wasn’t easy.

I can say that whoever underestimates the difficulty level of this exam will get a huge disappointment, especially when (s)he faces with the first simulation question. 🙂

This is an hybrid exam. I mean, you’ll find questions asking you to make single/multiple selections. Besides, you will also get simulation questions which simulates the SQL Server 2005 Management Studio environment. Since I got the test this morning, let me give you some hints about simulations.

There were 12 simulations and 40 single/multiple selection questions in the exam. Btw, I got the exam in Toronto, Canada. The number of questions may vary in different regions.

1 – installing a sql server instance, server2, with the same features and configuration of a given example instance, server1.

2- finding out view(s) causing error while reporting and deleting them. you need to figure out errant view(s) by looking at their SQL statements. Not that easy, I’d say! Good Luck! 🙂

3- killing a process with an open transaction.

4- fixing a view which causes error while inserting rows.

5- creating a database with the given details. To answer this question, you need to know the difference between RAID0 and RAID1.

6- creating a linked server and implementing the impersonation.

7- modifying a user-defined function which calls a CLR procedure which attempts to write to local file system. Ok, here is the hint: Know how to deploy assemblies and the meaning of Safe, Unsafe and External_Access 😉

… and some more! I can’t remember the rest now. 😦 If I do in time, I’ll add them as well.

 This is not a kind of certification exam that you can pass only by studying testking or braindumps, sorry guys! You need to know how to navigate through the Management Studio and also where you find the specific properties of objects.

It’s so easy to fail the exam if you don’t do good at simulation questions. Only solving the others won’t save you!!! The thing I like about the new generation of Microsoft Certifications is that not only it examines the theory but also the practice and experience you have on the subject.

To conclude, ensure that you had enough practice with Management Studio before you get the exam!

Good Luck!!! 🙂


60 Responses to “Exam 70-431: TS: Microsoft SQL Server 2005—Implementation and Maintenance”

  1. datakix said

    Hey Zieglers,

    What book(s) did you use to prepare/study?

    How long did you prepare?

    Do you have any links to other sites you may have bookmarked whilst studying?

    How would you rate the Microsoft 70-431 Training Kit Exam Simulator (on CD) compared to the real exam. I’m trying to figure out if this book/exam simulator is sufficient or should I also purchase Exam Cram and Transcender too.


  2. zieglers said

    Since I used to work as a technical trainer, I only used MOC material. Namely,

    • Course 2779: Implementing a Microsoft SQL Server 2005 Database
    • Course 2780: Maintaining a Microsoft SQL Server 2005 Database

    Also, I used to give seminars to Microsoft’s mid-market customers. During those seminars, I was showing the audience all the demonstrations which reside in the above MOCs.

    If you somehow find a chance to see them and apply yourself, you’ll see that those demonstrations are more than enough for you to get prepared for the exam questions and simulations.

    Anything asked in the exam is explicitly stated in those demonstrations. They are for sure better than any exam simulator. Exam simulaters can be useful, however they are not enough if not supported with additional reading.

    Using simulators, you may understand the question as well as the answer. But, it may not help to ease finding the answer for a similar question always. At times even it can be misleading.

    My suggestion is whichever product you use, always make additional reading about the things asked in the questions. Also, try to practise them if applicable. 😉


  3. G said

    Congrats, I am planning to go for that exam as well, but in my area, I can not access to any course e.g. 2279 2280, (It is in another town)
    I think I’ll grab a book and start reading, but I also got testking on hand for reference. yeah it is all about know how.

    Again, thanks for sharing this. Good luck for the rest of them if any 😉

  4. Gehad said

    today, i failed in 70-431 exam, i answered the multiple choice part 100% wright but in the simulation part it was 15 questions which i solved only 6 wright then i get 42% as a total, i think if you didn’t pass one part of the exam, they gave you the mark of the failed part only 😦

    i’ve the simulation questions 12 of 15 , but i need their solution. if anybody can help me to solve them and sharing them , or guiding me for solution it will be highly appreciated

    best regards
    Gehad Salah

  5. zieglers said

    hey gehad,

    i’m sorry to hear that you failed it. But look at the bright side of it: now you know what you need to concentrate on!

    why don’t you write here the remaining of the simulations that you remember –other than the ones i stated in the post–, so that we can discuss about their solutions?

    Or you can begin with asking the ones you have in mind.


  6. John Fleming said

    Thanks for hosting this topic!
    Question, what is an MOC? And does anybody know if Microsoft is going to add Courses 2778 or 2779 to their eLearning curriculum?

  7. John Fleming said

    Correction 2779 & 2780

  8. zieglers said

    MOC stands for Microsoft Official Curriculum. When you attend a course offered by a Microsoft Certified Learning Solutions Partner, MCTs (Microsoft Certified Trainers) have to follow a specific curriculum while lecturing the course.

    As for exam 70-431, MOC 2779 (http://www.microsoft.com/learning/syllabi/en-us/2779afinal.mspx) and MOC 2780 (http://www.microsoft.com/learning/syllabi/en-us/2780Afinal.mspx) are recommended for a candidate. (http://www.microsoft.com/learning/mcp/mcts/sql/default.mspx)

    Although these courses are not a must for this exam, the labs which are taught in those MOCs will definitely make you proficient while solving simulation questions of the exam.

    As for e-Learning Curriculum, i’m not sure if they are gonna be added.


  9. Tareq Ali said

    Hi Guys,
    I failed too today, It is shame, I did exactly the same as Gehad.
    Here are the questions I have got:
    1. You have a user defined date type, you need to drop dependant columns:
    My answer was: I deleted nothing, I check the depend object, and I have found trigers, I open the triggered I have found no code so I didn’t drop any column
    2. four question about backup database, exactly the same (or may be I’m ideot enough to not notice the difference)
    it say what should you do to backup the database to a d:\backup\aw.bak?
    3. drop invalid views? (you open view and see what is wrong with it) it was easy question
    4. Created database (trick is where to store the log (c: –> I think tape1, d: –> tape1, E: —> tape2) you have to put log file on reliable drive ( I never used these type of drive)
    5. Kill process qeustion
    6. you have to delete a user (sql login) but he is owner of a schema so you have to change the schema owner then drop the user (was easy)
    7. you have a funtion in .net assembly which need access to extral resource (I changed the assembly property to “extranl access” or something with that meaning)
    8. you need to minize the log file for db when import data (I changed the recovery mode to be ‘block-logged’

    I’m really sad for failing and very frustured, I passed sql 2000 exam with 1000 score and now I got 420

  10. Gehad said

    thanks for your support, i’ve got TestKing7.1, it has the same exam simulation questions but most of them without solution, how can i share it with you to get the right solution ?
    i can conp & past the questions here if its OK, or anybody help me with upload technique

    thanks 🙂
    Gehad Salah

  11. zieglers said

    Hi Gehad,

    Please do not copy&paste anything here. Let me get another section in the site ready for this 70-431 simulation discussions. It’s going beyond this post’s subject.
    If we want to discuss each and every question in detail, let’s do so under different threads.

    I’ll provide the link soon..


  12. Gehad said

    hi Zieglers

    i’m waiting for your new link or any updates 🙂


  13. zieglers said

    Hi Gehad,

    Please post your first question here: https://zieglers.wordpress.com/2007/04/12/70-431-simulation-questions-discussion/


  14. cobra007 said

    Writing soon, wish me luck guys…

  15. technovator said

    You’re right Ziegler…

    I took this exam last week thinking it will take me about 45min to 1hr but I was there for 2hrs

    It’s not that the questions are extremely hard if you know what you’re doing (i.e. if you studied), but don’t think you’ll just walk out of this one easy.

  16. alexfromromania said

    Hi to all….i need your help, pls

    maybe is not the right forum to post this question but i urgently need…

    I need a t-sql function to verify the validity of an account IBAN

    thx a lot


    mailto: dionian@gmail.com

  17. hvoronina said

    hi guys,
    i would like to explain question 55 of exam 70-431 (post1102):
    if you make alias LastContact to the field lastcontact then order by SQL will work with alias. In this case alias and the field have the same name (neglecting Case) but different data types : field is datetime alias is nvarchar. Ordering of datetime values will be by years (then months, days) but for nvarchar with format(MM/DD/YYYY) as they begin from months – ordering will be basically by months (then days and years). If you make another alias then this Order by will refer to field itself and result will be reasonable.
    good luck!!

  18. sajidwasim said

    Today I passed the MCTS Exam[70-431]. I got 1000/1000 in MCQ type questions and 940/1000 on simulation type questions. I did a mistake on a simulation type question otherwise I could have gotten the full marks. Anyways, I am happy that I passed with a good marks. If anybody requires any help or suggestion please do let me know.

  19. sajidwasim said

    Please Download the materials from here


    I am sure will get the 100% marks if you follow everything that it contains.

    Don’t forget to give me a BIG thanks when you pass the exam with 100% marks 🙂

  20. dpdba said

    @Sajid…. the folder has expired. can u please post it again? thx

  21. dpdba said

    @Sajidwasim…. the folder has expired. can u please post it again? thx

  22. sajidwasim said

    Get a Free second chance to pass your Microsoft Certification Exam. Worried about your Microsoft certification exam? Don’t sweat it. If you don’t pass the first time, Microsoft is giving you a second chance, free.


  23. dpdba said

    Passed it today. 900/1000. 70-444 to go…

  24. nabeelasif said

    I have Passed my exam with 1000/1000.
    Thanks to everyone.

  25. sabamaaz said

    Hi Everyone,
    I’m also preparing for sql 7-431 exam and have been extending my test dates for a month now. I have some study materials such as MOC and testking.
    Nabeelasif, If you don’t mind, can you advice me how to prepare for this test. I have also done simulation questions that were in the testking. Do you think this is enough or not.


  26. nabeelasif said

    Hi Sabamaaz,
    Yes its fine. Just sure the answer of the simulation question

    …A new written company policy states that nested trigger execution is not allowed….

    Only choose the answer from SQL Server properties window not from the Database properties. Just click ok on database properties windows.


  27. sabamaaz said

    “Only choose the answer from SQL Server properties window not from the Database properties. Just click ok on database properties windows.”

    I don’t understand what do you mean? Do you mean during the test? plz elaborate.

  28. nabeelasif said

    Yes I am talking about the test.
    Simulation Question:
    You are a database administrator for your company.
    The only non-system database on the server is AdventureWorks.
    A new written company policy status that nested trigger execution is not allowed. You need to ensure that this policy is implemented by modifying the appropriate server and database settings in the Database Properties dialog box or the Server Properties dialog box, or both of these dialog boxes, in SQL Server Management Studio (SSMS).
    Answer: Set the “Allow Triggers to Fire Others” to FALSE
    Hope this time you will understand.

  29. sabamaaz said

    Thanks yeah I figured it out. I saw the question, you need to change both the server and the database settings.
    I have another question, are there any case study questions in the test?


  30. dhanupriya said

    hi……am preparing for d 70-431 exam…can anyone pls guide me in d books 2 learn…materials n so on…..am a fresher….i wnt 2 kno d duration of d xam.no of questions………..complexity level n so on…pls help me…
    thnks n advance

  31. dhanupriya said

    hi……am preparing for d 70-431 exam…can anyone pls guide me in d books 2 learn…materials n so on…..am a fresher….i wnt 2 kno d duration of d xam.no of questions………..complexity level n so on…pls help me…
    thnks n advance

  32. nabeelasif said


    You should first go through the book Microsoft 70-431 SQL Server Implementation and Maintenance Training Kit. You should also take a grip on Microsoft SQL Server Management Studio.


  33. flippantvain said


    I am taking the 70-431 in 2 weeks. I have been using SSMS for 3 yrs, and feel comfortable in it.
    Could you tell me what areas they seem to concentrate the Simulation questions on? There are several things I have never had to deal with, and I want to make sure I experiment with the relevant things.
    Example: in 3 yrs, I have not dealt with replication (in any form), nor ever set up a linked server. Clearly I am going step-by-step through the 431 TK, and following each example live in SSMS.

    What should I definately study in detail?

  34. alecswan said

    I am taking 70-431 in a week.

    Were there any questions or simulations for managing XML data? I am wondering how much time I should spend on memorizing all FOR XML formats, XPATH and XQUERY expressions, SQLXML and such?


  35. ferozk said

    anyone knows what happened to this thread for 70-431 discussion:


    says page not found.

  36. moiskito said

    I’ve studied the book SQL 2005 Training Kit and now I’m practising with some exam simulators I got free on the net. I have one month for my exam.

    Does anyone know how long the exam takes? How much time to spend in each question? Some tricks?

    Passing or failing all come back to post about the exam. Wish me luck as well.

  37. narenderrawal007 said

    I cleared 431 exam last week,got thousand first time in any MCTS exam.Earler couldn’t make it,stuck in sims.felt same like everybody.
    This time ,everything went fine 🙂

  38. ferozk said

    narenderrawal007, Iam sitting the exam next week, anytips on the sims will appreciate.


  39. moiskito said

    I got my exam today in the morning.
    Being studying for this the last 2 months, everyday.
    I already work with SQL and it wasn’t that easy.

    35 questions and 12 sims in 170 mins.

    I spent a long time studying XML. Waste time, only 2 easy questions. Well, for me, now it’s easy.

    Study more about Mirroring, Views (indexed views) and Backups.

    The sims are trick. Some looked incomplete for me. Poor questions.
    It’s good to have had a good practice with SSMS.

    The Microsoft Training Kit book is incomplete about Assemblies and CLR.
    Read extra material to understand that. 2 questions and 1 sim about it.

    Go ahead and good luck. I got it, 1000!

  40. deamondragon said

    I need cd materials (sample code and databases) that came with this MS Press Book: “Microsoft SQL Server 2005 Reporting Services Step by Step”.
    If anybody is interesting for this book, here is the link:

  41. ravinderia said

    Good afternoon to all!

    Urgent help needed for the exam 70-431. can anyone send me the
    latest questions and simulation dumps.

    my email is: ravinderia@gmail.com

  42. 99trinity said

    Hi Ziegler
    Thanks for hosting this site, I’m planning on sitting 70-443 exam and hoping you may be able to provide a link for study material, thanks Ziegler … cheers


    • sanjayng1 said

      Hi Charles,
      Was Ziegler able to provide you with a link for the study material? I am taking the test on Thursday and would greatly appreciate your help. Thank you!


  43. yousofzai said

    I have passed my 70-431 exame 1000/1000

    • sql70431 said

      Hi Yousofzai
      Can you advise what materials I should read up which is related to the question in the exam. Will be taking test next week any help is appreciated.

    • dhebe said


      Am sitting for 70-431 in 2 – 3 weeks. Is there anywhere that I can link to useful free simulations? I am scoring perfectly on the multiple choice questions.

      Thanks for your help, Dhebe

  44. 99trinity said

    Hi sanjayng1 sorry have not received a link, hey possibly ask yousofzai

  45. dhebe said


    Am sitting for 70-431 in 2 – 3 weeks. Could anyone send me a link to free simulations?

    Thanks, Dhebe

  46. sql wildcard,sql wildcards,sql rollback,rollback sql,sql copy table,sql sum,sql mirroring,sum sql,sql cluster,sql server performance,truncate in sql,backup sql,backup sql database,backup sql server,sql performance,date functions in sql,sql over,trunc…

    […]Exam 70-431: TS: Microsoft SQL Server 2005—Implementation and Maintenance « Zieglers[…]…

  47. Projektowanie Stron Warszawa…

    […]Exam 70-431: TS: Microsoft SQL Server 2005—Implementation and Maintenance « Zieglers[…]…

  48. Replicas de relógios Relógios com Maquina ETA Valjoux…

    […]Exam 70-431: TS: Microsoft SQL Server 2005—Implementation and Maintenance « Zieglers[…]…

  49. CISSP Certification…

    […]Exam 70-431: TS: Microsoft SQL Server 2005—Implementation and Maintenance « Zieglers[…]…

  50. seo|vps|hosting|dedicated|server…

    […]Exam 70-431: TS: Microsoft SQL Server 2005—Implementation and Maintenance « Zieglers[…]…

  51. access database development…

    […]Exam 70-431: TS: Microsoft SQL Server 2005—Implementation and Maintenance « Zieglers[…]…

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: