PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005Exam News Exam 70-442: PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 is in development. This IT Professional (PRO) exam is expected to be released in its beta version at the end of February 2006 and released in its final version at the end of April 2006. Audience Profile Candidates for this exam are professional database developers who design and implement database solutions. They have three or more years dedicated to database work, which may include writing Transact-SQL queries, designing and implementing programming objects, optimizing databases, designing databases at both the conceptual and logical levels, and implementing databases at the physical level. The typical work environment is an enterprise or a medium-sized organization. Candidates should be experienced in using Microsoft SQL Server 2005. Candidates for this exam should be an expert in the following areas: Writing Transact-SQL queries Programming the database Troubleshooting programming objects (stored procedures, triggers, user-defined functions (UDFs), user-defined types (UDTs), and queries) Performing database tuning and optimization Designing databases, at both the conceptual and logical levels Implementing databases at the physical level Designing and troubleshooting the data access layer of the application Gathering business requirements
Credit Toward Certification When you pass Exam 70-442: PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005, you earn credit toward the following certifications: Instructor-led Courses for This Exam Course 2781: Designing Microsoft SQL Server 2005 Server-Side Solutions Course 2783: Designing the Data Tier for Microsoft SQL Server 2005 Course 2784: Tuning and Optimizing Queries Using Microsoft SQL Server 2005
Skills Being Measured This certification exam measures your ability to design database solutions using SQL Server 2005. Before taking the exam, you should be proficient in the job skills that are listed below. Skills measured by exam 70-442 Designing Efficient Access to a SQL Server Service Design appropriate data access technologies. Design an appropriate data access object model. Design a cursor strategy for a data access component. Decide when to use cursors. Decide how to maximize cursor performance. Detect which applications are using cursors and evaluate whether to remove them.
Design caching strategies. Design client libraries to write applications that administer a SQL Server service. Design server management objects (SMO) applications. Design replication management objects (RMO) applications. Design automation management objects (AMO) applications. Design SQL Server Networking Interface (SNI) for asynchronous queries.
Design queries that use multiple active result sets (MARS). Decide when MARS queries are appropriate. Choose an appropriate transaction isolation level when you use MARS. Choose when to use Asynchronous queries.
Designing a Database Query Strategy Write and modify queries. Design queries for retrieving data from XML sources. Select the correct attributes. Select the correct nodes. Filter by values of attributes and values of elements. Include relational data, such as columns and variables, in the result of an XQuery expression. Include XML attribute or node values in a tabular result set. Update, insert, or delete relational data based on XML parameters to stored procedures. Debug and troubleshoot queries against XML data sources.
Design a cursor strategy. Design cursor logic. Design cursors that work together with dynamic SQL execution. Select an appropriate cursor type. Design cursors that efficiently use server memory. Design cursors that minimize blocking. Design a strategy that minimizes or eliminates the use of cursors.
Designing Error-Handling Routines Design code that validates input data and permissions. Design code that detects and reacts to errors. Design user-defined messages to communicate application events. Designing a Transaction Strategy Manage concurrency by selecting the appropriate transaction isolation levels. Design the locking granularity level. Design transaction scopes. Design code that uses transactions. Performance Tuning a Database and a Database Application Optimize and tune queries for performance. Evaluate query performance. Detect locking problems. Modify queries to optimize client and server performance. Rewrite subqueries to joins. Design queries that have search arguments (SARGs). Convert single-row statements into set-based queries.
Optimize indexing strategies. Design an index strategy. Analyze index use across an application. Add, remove, or redesign indexes. Optimize index-to-table-size ratio.
Scale database applications. Specify a data-partitioning model. Design queries that target multiple servers. Implement scale-out techniques like federated database, service broker, distributed partitioned views. Design applications to distribute data and workload transparently. Identify code or processes that can be moved to a different tier to improve performance. Rewrite algorithms to improve efficiency.
Resolve performance problems. Analyze application performance across multiple users. Capture workload information. Find out the causes of performance problems. Specify resolutions such as: changing algorithms, scaling up, and scaling out, terminating a session.
Optimize data storage. Choose column data types to reduce storage requirements across the enterprise. Design appropriate use of varchar across the enterprise. Denormalize entities to minimize page reads per query. Optimize table width.
|
