Home > Articles

  • Print
  • + Share This
Preparation Hints

Exam Objectives

The objectives for Exam 70-457 as stated by Microsoft are as follows:

Create Database Objects

  • Create and alter tables using T-SQL syntax (simple statements).
  • This objective may include but is not limited to: create tables without using the built-in tools; ALTER; DROP; ALTER COLUMN; CREATE

  • Design views.
  • This objective may include but is not limited to: ensure code non regression by keeping consistent signature for procedure, views, and function (interfaces); security implications

  • Create and alter DML triggers.
  • This objective may include but is not limited to: inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers

Work with Data

  • Query data by using SELECT statements.
  • This objective may include but is not limited to: use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new code items such as synonyms and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; CASE versus ISNULL versus COALESCE

  • Implement sub-queries.
  • This objective may include but is not limited to: identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement

  • Implement data types.
  • This objective may include but is not limited to: use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use which data type for columns

Modify Data

  • Create and alter stored procedures (simple statements).
  • This objective may include but is not limited to: write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of stored procedure results; create a stored procedure for data access layer; program stored procedures, triggers, and functions with T-SQL

  • Modify data by using INSERT, UPDATE, and DELETE statements.
  • This objective may include but is not limited to: given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement

  • Work with functions.
  • This objective may include but is not limited to: understand deterministic and non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)

Troubleshoot and Optimize Queries

  • Optimize queries.
  • This objective may include but is not limited to: understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios in which they would be used

  • Manage transactions.
  • This objective may include but is not limited to: mark a transaction; understand begin tran, commit, and rollback; implicit vs. explicit transactions; isolation levels; scope and type of locks; trancount

  • Evaluate the use of row-based operations vs. set-based operations.
  • This objective may include but is not limited to: when to use cursors; impact of scalar UDFs; combine multiple DML operations

  • Implement error handling.
  • This objective may include but is not limited to: implement try/catch/throw; use set based rather than row based logic; transaction management

Install and Configure SQL Server

  • Plan installation.
  • This objective may include but is not limited to: evaluate installation requirements; design the installation of SQL Server and its components (including drives and service accounts); plan scale up vs. scale out basics; plan for capacity, including if/when to shrink, grow, autogrow, and monitor growth; manage the technologies that influence SQL architecture (including service broker, full text, and scale out); design the storage for new databases (drives, filegroups, partitioning); design database infrastructure; configure a SQL Server standby database for reporting purposes; Windows-level security and service level security; Core mode installation; benchmark a server before using it in a production environment (SQLIO, Tests on SQL Instance); choose the right hardware

  • Install SQL Server and related services.
  • This objective may include but is not limited to: test connectivity; enable and disable features; install SQL Server database engine and SSIS (not SSRS and SSAS); configure an operating system disk

  • Implement a migration strategy.
  • This objective may include but is not limited to: restore vs. detach/attach; migrate security; migrate from a previous version; migrate to new hardware; migrate systems and data from other sources

  • Configure additional SQL Server components.
  • This objective may include but is not limited to: set up and configure Analysis Services (AS), Reporting Services (RS), and SharePoint integration in a complex and highly secure environment; configure full-text indexing; SQL Server Integration Services (SSIS) security; filestream; filetable

  • Manage SQL Server Agent.
  • This objective may include but is not limited to: create, maintain, and monitor jobs; administer jobs and alerts; automate (setup, maintenance, monitoring) across multiple databases and multiple instances; send to "Manage SQL Server Agent jobs"

Maintain Instances and Databases

  • Manage and configure databases.
  • This objective may include but is not limited to: design multiple file groups; database configuration and standardization: autoclose, autoshrink, recovery models; manage file space, including adding new filegroups and moving objects from one filegroup to another; implement and configure contained databases; data compression; configure Transparent Data Encryption (TDE); partitioning; manage log file growth; Database Console Commands (DBCC)

  • Configure SQL Server instances.
  • This objective may include but is not limited to: configure and standardize a database including autoclose, autoshrink, recovery models; install default and named instances; configure SQL to use only certain CPUs (for example, affinity masks); configure server level settings; configure many databases/instance, many instances/server, virtualization; configure clustered instances including Microsoft Distributed Transaction Coordinator (MSDTC); memory allocation; database mail; configure SQL Server engine including memory, filffactor, sp_configure, and default options

  • Implement a SQL Server clustered instance.
  • This objective may include but is not limited to: install a cluster; manage multiple instances on a cluster; set up subnet clustering; recover from a failed cluster node

  • Manage SQL Server instances.
  • This objective may include but is not limited to: install an instance; manage interaction of instances; SQL patch management; install additional instances; manage resource utilization by using Resource Governor; cycle error logs

Optimize and Troubleshoot SQL Server

  • Identify and resolve concurrency problems.
  • This objective may include but is not limited to: examine deadlocking issues using the SQL server logs using trace flags; design reporting database infrastructure (replicated databases); monitor via Dynamic Management Views (DMV) or other Microsoft product; diagnose blocking, live locking, and deadlocking; diagnose waits; performance detection with built-in DMVs; locate and if necessary kill processes that are blocking or claiming all resources

  • Collect and analyze troubleshooting data.
  • This objective may include but is not limited to: monitor using Profiler, collect performance data by using System Monitor, collect trace data by using SQL Server Profiler, identify transactional replication problems; identify and troubleshoot data access problems; gather performance metrics; identify potential problems before they cause service interruptions; identify performance problems, use Extended Events (XEvents) and DMVs; create alerts on critical server condition; monitor data and server access by creating audit and other controls; identify IO vs. memory vs. CPU bottlenecks; use the Data Collector tool

  • Audit SQL Server instances.
  • This objective may include but is not limited to: implement a security strategy for auditing and controlling the instance; configure an audit; configure server audits; track who modified an object; monitor elevated privileges as well as unsolicited attempts to connect; policy-based management

Where to Go from Here

After you pass the Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 exam, you may want to take the Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2 (70-458) exam.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.