Exam Profile: Transition Your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE: Data Platform (70-459)

Date: Feb 25, 2013

This article profiles the Microsoft Transition Your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE: Data Platform (70-459) exam, meant for those who have already achieved one or both of the MCITP Database certifications. 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-459 exam is meant to be taken by those who have already earned the MCITP: Database Administrator 2008, or MCITP: Database Developer 2008, certification and who wish to transition their skills to MCSE: Data Platform on SQL Server 2012.

After passing the Transition Your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE: Data Platform (70-459) exam, you complete the requirements for the MCSE: Data Platform 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.)

Type 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.

For local installations, you must run Setup as an administrator. If you install SQL Server from a remote share, you must use a domain account that has read and execute permissions on the remote share.
Be sure to run SQL Server services by using the lowest possible permissions. You can do this by associating SQL Server services with low privileged Windows local user accounts, or domain user accounts.
Performance Objects and Counters used to track SQL performance include the following:
- Memory—Available MBytes
- Paging File—% Usage
- Physical Disk—Avg. Disk sec/Read
- Physical Disk—Avg. Disk sec/Write
- Physical Disk—Disk Reads/sec
- Physical Disk—Disk Writes/sec
- Processor—% Processor Time
- SQLServer: Buffer Manager—Buffer cache hit ratio
- SQLServer: Buffer Manager—Page life expectancy
- SQLServer: General Statistics—User Connections
- SQLServer: Memory Manager—Memory Grants Pending
- SQLServer: SQL Statistics—Batch Requests/sec
- SQLServer: SQL Statistics—Compilations/sec
- SQLServer: SQL Statistics—Recompilations/sec
- System—Processor Queue Length
Some common reasons for query time-outs are:
- The application starts using a query not optimal for the index
- Hardware changes/Configuration changes
- Increased load
Adding role members in the development environment can overwrite changes made in production to the list of role members when you deploy. The easiest way to test dynamic security is to add role members. These role members should be removed before you deploy the model.

Preparation Hints

Review the Exam Objectives below and make sure that you are familiar with them. The Transition Your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE: Data Platform exam is designed for those who have experience in this environment. Always check the Microsoft site for the specific exam you are going to take. In this instance, the site is http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-459.

There are many web sites and blogs that can help you to research topics, but be careful to fully research the information you read. It is not advisable to try to find sites that list questions and answers for several reasons. First, you don’t know if you will be asked a specific question and second, the answers given in a blog may be inaccurate and third, you need to understand the information to adequately prepare.

When taking the exam, read each question carefully. Microsoft is notorious for adding a lot of unneeded information in their questions. Make sure that when you click on a choice, that it is really marked. Be careful clicking anywhere on the screen. I found that by inadvertently clicking near the scroll bar on the right of the screen, I actually changed an answer. You get a single piece of paper and a marker for writing. You can use a small amount of time before you even start the exam to make notes once you enter the test area. Sometimes there is even a questionnaire at the beginning of the test that does not count against your test time. You can even use this time to write down notes, facts, tables or other information by taking your time between answers.

Recommended Study Resources

Introducing Microsoft SQL Server 2012 by Ross Mistry and Stacia Misner (Apr 7, 2012)

Professional Microsoft SQL Server 2012 Administration by Adam Jorgensen, Steven Wort, Ross LoForte and Brian Knight (Apr 24, 2012)

Exam Objectives

The exam objectives are broken up into eight different categories. The 70-459 exam measures your ability to accomplish the technical tasks listed below. The objectives for exam 70-459 as stated by Microsoft are as follows:

Implement Database Objects

Create and alter tables.

This objective may include but is not limited to: develop an optimal strategy for using temporary objects (table variables and temporary tables); manage a table without using triggers; data version control and management; create tables without using the built-in tools; understand the difference between @Table and #table

Design, implement, and troubleshoot security.

This objective may include but is not limited to: grant, deny, revoke; connection issues; execute as; certificates; loginless user; database roles and permissions; contained users; change permission chains

Create and modify constraints (complex statements).

This objective may include but is not limited to: create constraints on tables; define constraints; performance implications

Implement Programming Objects

Design and implement stored procedures.

This objective may include but is not limited to: create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of stored procedure results; create stored procedures for data access layer; analyze and rewrite procedures and processes; program stored procedures with T-SQL and CLR#; use table-valued parameters; encryption

Design T-SQL table-valued and scalar functions.

This objective may include but is not limited to: ensure code non-regression by keeping consistent signature for procedure, views, and function (interfaces); turn scripts that use cursors and loops into a SET-based operation

Create and alter views.

This objective may include but is not limited to: set up and configure partitioned tables and partitioned views; design for using views and stored procedures, and remove the direct usage of tables

Design Database Objects

Design tables.

This objective may include but is not limited to: data design patterns; develop normalized and de-normalized SQL tables; understand the difference between physical tables, temp tables, temp table variables, and common table expressions; design transactions; design views; understand advantages and disadvantages of using a GUID as a clustered index; understand performance implications of # vs. @ temp tables and how to decide which to use, when, and why; use of set-based vs. row-based logic; encryption (other than TDE); table partitioning; filestream and filetable

Create and alter indexes.

This objective may include but is not limited to: create indexes and data structures; create filtered indexes; create an indexing strategy; design and optimize indexes; design indexes and statistics; assess which indexes on a table are likely to be used given different search arguments (SARG); column store indexes; semantic indexes

Design data integrity.

This objective may include but is not limited to: design table data integrity policy (checks, private key/foreign key, uniqueness, XML schema); select a primary key; data usage patterns

Optimize and Troubleshoot Queries

Optimize and tune queries.

This objective may include but is not limited to: tune a badly performing query; identify long running queries; review and optimize code; analyze execution plans to optimize queries; tune queries using execution plans and database tuning advisor (DTA); design advanced queries using pivots and utilizing common table expressions (CTE), design the database layout and optimize queries (for speed and/or data size); understand different data types; basic knowledge of query hints; tune query workloads; demonstrate use of recursive CTE; full text search; control execution plans

Troubleshoot and resolve performance problems.

This objective may include but is not limited to: interpret performance monitor data; impact of recovery modal on database size, and recovery; how to clean up if .MDF and .LDF files get to large; identify and fix transactional replication problems; detect and resolve server hung failure; identify and troubleshoot data access problems

Collect performance and system information.

This objective may include but is not limited to: use Data Management Views to determine performance issues; from system metadata; gather trace information by using the SQL Server Profiler; develop monitoring strategy for production database; run a profiler trace and analyze the results; use profiler to troubleshoot applications; collect output from the Database Engine Tuning Advisor; extended events

Design Database Structure

Design for business requirements.

This objective may include but is not limited to: business to data translations; identify which SQL Server components to use to support business requirements; design a normalization area; de-normalize by using SQL Server features (such as materialization via indexed views)

Design physical database and object placement.

This objective may include but is not limited to: filestream and filetable; logical vs. physical design; file groups

Design SQL Server instances.

This objective may include but is not limited to: create a specification for hardware for new instances; design an instance; design SQL to use only certain CPUs (including affinity masks); design clustered instances including Microsoft Distributed Transaction Control (MSDTC); memory allocation

Design Databases and Database Objects

Design a database model.

This objective may include but is not limited to: design a logical schema; design a normalized database; design data access and data layer architecture; understand the relational model; design a normalized data model; design a database schema; create and maintain a schema upgrade and downgrade script that include the most optimal schema deployment and data migration; Entity-Attribute-Value (EAV) modeling, generalization/specialization, star-schema; optimize the design for normalization to the right level for the application; design security architecture; understand impact of collation, ANSI NULLS, and QUOTED IDENTIFIER

Design tables.

This objective may include but is not limited to: data design patterns; develop normalized and de-normalized SQL tables; understand the difference between physical tables, temp tables, temp table variables, and common table expressions; design transactions; design views; understand the performance implications of # vs. @ temp tables and how to decide which to use, when, and why; use of set-based rather than row-based logic; filestream and filetable; semantic engine; sequences; row/page compression; data type selection

Design T-SQL stored procedures.

This objective may include but is not limited to: write a stored procedure to meet a given set of requirements; design using views and stored procedures to remove the direct usage of tables

Design Database Security

Design an application strategy to support security.

This objective may include but is not limited to: design security; implement schemas and schema security; design maintenance, including SQL logins vs. integrated authentication, permissions, and mirroring issues; use appropriate mechanisms to enforce security roles and signed stored procedures; encryption; contained logins

Design instance-level security configurations.

This objective may include but is not limited to: implement separation of duties using different login roles; design and implement a data safety strategy that meets the requirements of the installation; choose authentication type, logon triggers, and regulatory requirements; transparent data encryption; DDL triggers

Design a Troubleshooting and Optimization Solution

Troubleshoot and resolve concurrency issues.

This objective may include but is not limited to: examine deadlocking issues using the SQL server logs; design the reporting database infrastructure; monitor issues via DMV; diagnose blocking, live locking, and deadlocking; diagnose waits; performance detection with built-in DMVs; know how concurrency affects performance

Design a monitoring solution at the instance level.

This objective may include but is not limited to: design auditing strategies including XE, Profiler, Perfmon, and DMV usage; set up file and table growth monitoring; collect performance indicators and counters; content management systems; policies

Where to Go from Here

After you pass the Transition Your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE: Data Platform exam, you may want to take the following exams:

Querying Microsoft SQL Server 2012 (70-461)
Implementing a Data Warehouse with Microsoft SQL Server 2012 (70-463)
Developing Microsoft SQL Server 2012 Databases (70-464)
Designing Database Solutions for Microsoft SQL Server 2012 (70-465)
Implementing Data Models and Reports with Microsoft SQL Server 2012 (70-466)
Designing Business Intelligence Solutions with Microsoft SQL Server 2012 (70-467)