Exam Profile: Transition Your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE: Data Platform (70-459)
Date: Feb 25, 2013
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.)
- Time Limit: 120 minutes
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.
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:
- MemoryAvailable MBytes
- Paging File% Usage
- Physical DiskAvg. Disk sec/Read
- Physical DiskAvg. Disk sec/Write
- Physical DiskDisk Reads/sec
- Physical DiskDisk Writes/sec
- Processor% Processor Time
- SQLServer: Buffer ManagerBuffer cache hit ratio
- SQLServer: Buffer ManagerPage life expectancy
- SQLServer: General StatisticsUser Connections
- SQLServer: Memory ManagerMemory Grants Pending
- SQLServer: SQL StatisticsBatch Requests/sec
- SQLServer: SQL StatisticsCompilations/sec
- SQLServer: SQL StatisticsRecompilations/sec
- SystemProcessor 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.
- Design, implement, and troubleshoot security.
- Create and modify constraints (complex statements).
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
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
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.
- Design T-SQL table-valued and scalar functions.
- Create and alter views.
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
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
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.
- Create and alter indexes.
- Design data integrity.
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
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
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.
- Troubleshoot and resolve performance problems.
- Collect performance and system information.
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
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
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.
- Design physical database and object placement.
- Design SQL Server instances.
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)
This objective may include but is not limited to: filestream and filetable; logical vs. physical design; file groups
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.
- Design tables.
- Design T-SQL stored procedures.
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
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
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.
- Design instance-level security configurations.
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
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.
- Design a monitoring solution at the instance level.
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
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)