EXAM 70-457: Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1

If you  have plans to upgrade your MCTS in SQL Server 2008 or are already deep in studying (like me), here are the skills measured that you can use as study guide and training materials that might be of help. If you need a buddy reviewer let me know via the comments box below or follow me on twitter so we can help each other out.

Skills Measured

Create Database Objects

– Create and alter tables using T-SQL syntax and not built-in tools, DROP, ALTER, alter columns, design views, triggers, nested triggers, types of triggers, handling of multiple rows in sessions, performance implication of triggers

Work with Data

– Review querying data using SELECT with ranking functions to get the top(x) number of rows for multiple categories, write queries using synonyms and joins, write efficient queries using all types of joins, CASE versus ISNULL versus COALESCE, given a table with constraints, determine which statement set would load a table, sub-queries, when to use which data type for columns, cte statement, with statement

Modify Data

– Create and alter simple stored procedures, write a stored procedure given a set of requirements, modify data using INSERT, UPDATE and DELETE statements, use OUTPUT statement, create and alter user-defined functions (UDF),  performance issues when using UDFs, troubleshoot and optimize queries, manage transactions, evaluation of row-based operations versus set-based operations, error handling implementation, when to use and not to use cursors

Install and Configure SQL Server 2012

– Includes topics on planning an installation, installing SQL Server and related services, migration strategy, configure addition server components, SQL Server Agent management.

Maintain Instances and Database

– Management and configuration of databases, configuration of other SQL Instances, implement a clustered instance

Optimize and Troubleshoot SQL Server

– Includes topics on identifying and resolving concurrency problems, collection and analysis of troubleshooting data, audit SQL Server.

Training Materials

As this exam is an upgrade exam, you are most likely not to find any training kit or materials for 70-457. There is also no practice test available in MeasureUp. I managed to find this book though on Amazon. It covers most of the topics in the skills measured and a great help because it has a question and answer portion on each topic discussed and has a lot of screen shots. The fonts are bigger too, so good for people with poor eyesight like me.

Look instead for training materials on base exam like the 70-462 and 70-463. The training kit for exam 70-462 is already out http://blogs.msdn.com/b/microsoft_press/archive/2012/08/08/new-book-training-kit-exam-70-462-administering-microsoft-sql-server-2012-databases.aspx. If you are currently in Singapore, I saw this book in Kinokuniya bookstore a few days ago. I plan to get this one too.

Download an SQL 2012 Developer Edition to play with and also work with TechNet/MSDN virtual labs. The training book may require you to prepare a couple of VMs and domains, the virtual lab will come in handy at this point. I find this one very helpful as you can play with Enterprise Editions features such High Availability with AlwaysOn. Just yesterday I was able to practice on AlwaysOn feature of SQL Server 2012 among 3 servers and adding replica and failing on one server. So cool!

For a guide on ways to install and configure SQL Server 2012, you may also want to read this.

Happy studying and good luck!


2 responses to “EXAM 70-457: Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1

  • Kenneth Fisher

    As it happens I am studying for this exam. Unfortunately no twitter though. I haven’t had a chance to read the rest of your posts on it yet, but they are on my list for tonight. I’ve gotten the Measureup exams for all 3 of the base tests for the MCSA. My assumption (let’s hope it holds true) is that if I can pass the 70-461 and 70-462 practice exams then I’ll be good to go for the 70-457.

    • Carla Abanes

      Just a quick note on that. 70-462 and 70-463 are base exams for MCSA credit in your certification for MS SQL Server 2012. 70-457 is an upgrade exam. Yes, you are also right that if you can pass both in MeasureUp then you’re good to go for 70-457. The review materials are very similar and the topics and percentage allotment are very near. Good luck to us!

Leave a Reply

Fill in your details below or click an icon to log in:

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


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.


Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment