This article profiles the Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 (70-457) exam, for IT professionals holding MCTS certifications who wish to transition to SQL Server 2012. Pearson IT Certification provides a variety of exam preparation tools to help our customers in their quest for certification. As part of our service to you, we have developed this Exam Profile series. Each profile is developed based on the testing experience of one of our trainers or authors. You won’t get exact questions or answers, but you will get a real feel for the exam. Each profile describes question forms, trouble spots, hints for exam preparation, and recommendations for additional study resources. Find out what you can expect to see on the exam and how you can better prepare for it.

The 70-457 exam is meant to be taken by those who have already earned any one of the MCTS certifications on SQL Server 2008 and who wish to transition their skills to MCSA: SQL Server 2012.

After passing the Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 (70-457) exam, you complete the requirements for the MCSA: SQL Server 2012 certification.

Exam Details

Number of Questions: Approximately 45-55 questions (Since Microsoft does not publish this information, the number of exam questions may change without notice.)

Types of Questions: This test format is multiple choice and multiple choice multiple answer. You may also see several scenario questions.

Passing Score: 700

This passing score does not mean that you must answer 70 percent of the items correctly in order to pass the exam. The actual percentage varies from exam to exam and may be more or less than 70 percent. There is no penalty for guessing. No points are deducted for incorrect answers. If a question specifies that you must choose multiple correct answers, you must choose the exact number of correct answers specified in the question in order to earn a point for that item. Some of the questions on the exam may not count toward the calculation of your score. Microsoft will often throw a question in that is meant to gather data that will help them improve the exam.

Time Limit: 120 minutes

Trouble Spots

As with any exam, it will vary from person to person as to what is deemed to be difficult. Be sure you are familiar with each of the topics in the exam objectives listed below.

Transact-SQL is central to using SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

SQL Server built-in functions are either deterministic or nondeterministic. Functions are deterministic when they always return the same result any time they are called by using a specific set of input values. Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values.

The account used to install SQL Server should have Full Control share and NTFS permissions on the SMB share folders. The SQL Server service account and SQL Server agent service account should also have Full Control share permissions on the SMB share folders.

Shared memory and VIA are not supported on failover clusters. Guest failover clustering is supported in SQL Server 2012. SQL Server failover cluster installation supports Local Disk only for installing the tempdb files. Make sure the path specified for the tempdb data and log files is valid on all cluster nodes. During a failover, if the tempdb directories are not available on the failover target node, the SQL Server resource will fail to come online.

SQL Server editions that are supported on Windows Server 2008 R2 64-bit x64 Standard edition are also supported on Windows Small Business Server 64-bit x64.

If you change the account that runs SQL Server, first decrypt the files under the old account and then re-encrypt them under the new account.

During the setup of SQL Server Express, a login is added for the BUILTIN\Users group. This will allow all authenticated users of the computer to access the instance of SQL Server Express as a member of the public role. You can safely remove this group to restrict Database Engine access to computer users who have individual logins or are members of other Windows groups with logins.