SQL Server Implementation and Maintenance as an MCTS

To complete the Microsoft Certified IT Professional (MCITP) certification as a database developer or administrator, you have to be a well-rounded, database-aware individual. The new generation of Microsoft certifications is much more meaningful and maps more closely to the everyday work environment found in the real world. You will also likely find this particular exam quite challenging.

The 70-431 exam requires you to have at least a base level of knowledge about the entire SQL Server 2005 product. You need to know how to apply SQL Server objects and principles to everyday implementation and maintenance tasks. The exam is broad in nature and tests you across the full realm of the actual SQL Server software.

Remember that this is a Microsoft exam, and it is focused on the SQL Server product. To pass the exam, you need to know how the software interacts with both the hardware and the operating system. You need to know how to configure the software, where to store the files and data, and how to organize the placement of all the individual database elements and other application objects.

The 70-431 exam includes questions covering all SQL Server objects, including the appropriate creation and use of each object type. A business application has many supporting objects in the database management system over and above the data itself. You can expect to see questions that offer choices between a wide assortment of these objects; you need to be familiar with where each object type and technology is best implemented.

You can get all the information you need from the material presented in this book. If you're willing to tackle the preparation process seriously and do what it takes to gain the necessary experience and knowledge, you can take and pass the exam. In fact, the Exam Cram books and the companion Exam Prep books are designed to make it as easy as possible for you to prepare for these exams, but prepare you must!

The Ideal MCITP Candidate

To give you some idea of what an ideal candidate is like, the following is some relevant information about the background and experience such an individual should have:

Note

For more information on the ideal MCITP candidate, consult the Preparation Guide for Exam 70-431, at www.microsoft.com/learning/exams/70-431.asp.

• Candidates for this exam may be professionals who typically pursue careers as database administrators, database developers, or business intelligence developers.

• Candidates can be people who do not work with Microsoft SQL Server as a part of their primary job functions but who want to show their breadth of technology experience, such as developers, system administrators, and others.

• Candidates can implement and maintain databases by using specific instructions and specifications.

• Candidates for this exam should be expert in tools usage, user interface navigation, wizard usage, writing code in the appropriate language (Transact-SQL, CLR language, and other scripting languages), code debugging or syntactic issue resolution, and troubleshooting and accomplishing specific focused tasks by using code or user interface navigation.

You should also have at least one year's experience implementing relational databases.

Put Yourself to the Test

The following questions and observations are designed to help you figure out how much work you'll face in pursuing SQL Server certification and what kinds of resources you can consult on your quest. Be absolutely honest in your answers, or you'll end up wasting money on an exam you're not ready to take. There are no right or wrong answers, only steps along the path to certification. Only you can decide when you are ready.

Two things should be clear from the outset, however:

• Even a modest background in logic and reasoning is helpful.

• Hands-on experience with designing, coding, testing, documenting, and fine-tuning on SQL Server tools used to handle complex database systems is an essential ingredient for success.

Educational Background

1. Were you strong in mathematicsin particular, algebrain school? (Yes or No)

2. Do you enjoy solving logic puzzles? (Yes or No)

3. a) Development Track: Have you every taken any classes dealing with programming a computer in any language? (Yes or No)

   If you answered yes to any of the three previous questions, then you will likely be able to grasp most of the concepts presented that require the writing of SQL code.

   If you answered yes to all three, then you will be very comfortable with most of the concepts dealing with logic and syntax.

   If you answered no to these first three questions, then you may struggle with some of the development concepts and might want to do some preparatory reading or take a course to learn about some basic programming concepts.

   This site provides a good starting point for programming skills specific to SQL: www.w3schools.com/sql/sql_intro.asp.

   This Microsoft instructor-led course is an easy-to-understand look at programming for beginners: www.microsoft.com/learning/syllabi/en-us/2667Afinal.mspx.

   b) Administration Track: Do you have a background with SQL? (Less general coding theory is required, but a basic coding background in SQL is helpful.) (Yes or No)

   This site provides a good starting point for programming skills specific to SQL: www.w3schools.com/sql/sql_intro.asp.

4. Are you comfortable with databases and table relationships? (Yes or No)

5. Have you ever used Microsoft Access, dBASE, or any other database package? (Yes or No)

6. Are you comfortable using spreadsheet packages such as Microsoft Excel? (Yes or No)

   If you answered yes to any or all of the previous three questions, then you have the base knowledge to get started right away.

   If you answered no to these three questions, than you might want to look into some simple database management systems information. You can find many resources on the Internet if you search for "Introduction to Database." The following site is an online course that you can take for free to become more comfortable with the topic: www.sqlcourse.com.

7. Do you have a copy of SQL Server that you can practice with?

8. Do you have access at school or work to a server that you can use to help in your studies?

   If you answered yes to either or both of these questions, then you have solved one of the initial hurdles. Trying to pass an exam like this without getting regular practice with the software is next to impossible. If you answered no, then you can order a CD or download a 180-day evaluation copy of the product from www.microsoft.com/sql/downloads/trial-software.mspx.

Hands-On Experience

Perhaps the most important key to success on any certification exam is hands-on experience. If I leave you with only one realization after taking this Self Assessment, it should be that there's no substitute for time spent performing tasks and working with the product.

Tip

You can obtain the exam objectives and practice questions, and you can get other information about the new generation of Microsoft certification exams from Microsoft, at www.microsoft.com/learning/mcp/newgen/.

Tip

If you have the funds or your employer will pay your way, consider taking a class led by a professional instructor. This is a good idea particularly for those just starting out or with limited knowledge or access to state-of-the-art computer systems. Microsoft has designed very good courses that can be taken in most communities.

Testing Your Exam Readiness

Whether you attend a formal class on a specific topic to get ready for an exam or use written materials to study on your own, some preparation for the certification exams is essential. You pay for your exam attempts whether you pass or fail, so you want to do everything you can to pass on your first try. Not only can failed attempts be very expensive, but they can also be very discouraging.

Each chapter in this book includes practice exam questions, and Chapters 10, "Practice Exam 1," and 12, "Practice Exam 2," include practice exams. So if you don't score well on the chapter questions, you can study more by taking the practice exams.

For any given subject, you should consider taking a class if you've tackled self-study materials, taken the practice test, and failed anyway. If you can afford the privilege, the opportunity to interact with an instructor and fellow students can make all the difference in the world. For information about systems auditing classes, visit the Certification Program page at www.microsoft.com/learning/mcp.

You will find it helpful if you have taken certification exams before. Regardless, practice exams are helpful in determining readiness. Answer the following question:

1. Have you taken a practice exam on your chosen test subject? (Yes or No)

   If you answered yes to the previous question and you scored 90% or better, you're probably ready to tackle the real thing. If your score isn't above that crucial threshold, keep at it until you break that barrier. If you answered no, go back and study the book some more and repeat the practice tests. Keep at it until you can comfortably break the passing threshold.

Tip

There is no better way to assess your test readiness than to take a good-quality practice exam and pass with a score of 90% or better. When I'm preparing, I shoot for 95%+, just to leave room for the "weirdness factor" that sometimes shows up on Microsoft exams.

One last note: Hands-on experience using the actual product within the context of the exams is important. As you review the material for the exams, you'll realize that hands-on experience with database design concepts and best practices is invaluable.

Let's Get to It!

After you've assessed your readiness, undertaken the right background studies, obtained the hands-on experience that will help you understand the products and technologies at work, and reviewed the many sources of information to help you prepare for a test, you'll be ready to take a round of practice tests. When your scores come back positive enough to get you through the exam, you're ready to go after the real thing. If you follow this assessment regimen, you'll not only know what you need to study, but you'll also know when you're ready to take the exam. Good luck!