Exams > Search for an Exam

Preparation Guide for Exam 70-441

PRO: Designing Database Solutions by Using Microsoft SQL Server 2005

Updated: April 11, 2008
Related Links
•Register for this exam
•Take a practice test
•Join an MCP newsgroup
•Find a course
•Buy a Microsoft Press book
On This Page
Exam newsExam news
Audience profileAudience profile
Credit toward certificationCredit toward certification
Preparation tools and resourcesPreparation tools and resources
Skills measuredSkills measured

Exam news

The Microsoft Certified Professional (PRO) exam, Exam 70-441: PRO: Designing Database Solutions by Using Microsoft SQL Server 2005, became available in January 2006.

Top of pageTop of page

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 following areas:

•

Writing Transact-SQL queries

•

Programming databases

•

Troubleshooting programming objects (stored procedures, triggers, user-defined functions (UDFs), user-defined types (UDTs), and queries)

•

Performing database performance tuning and optimization

•

Designing databases, at both the conceptual and logical levels

•

Implementing databases at the physical level

•

May be involved in designing and troubleshooting the data access layer of the application

•

Gathering business requirements

Top of pageTop of page

Credit toward certification

When you pass Exam 70-441: PRO: Designing Database Solutions by Using Microsoft SQL Server 2005, you earn credit toward the following certification:

•

Microsoft Certified IT Professional: Database Developer

Top of pageTop of page

Preparation tools and resources

To help you prepare for this exam, Microsoft Learning recommends that you have hands-on experience with the product and that you use the following training resources. These training resources do not necessarily cover all of the topics listed in the "Skills measured" section.

Classroom trainingMicrosoft E-LearningMicrosoft Press booksPractice tests

Course 2781: Designing Microsoft SQL Server 2005 Server-Side Solutions (three days)

Course 2782: Designing Microsoft SQL Server 2005 Databases (two days)

Collection 2781: Designing Microsoft SQL Server 2005 Server-Side Solutions (eight-hour series)

Collection 2782: Designing Microsoft SQL Server 2005 Databases (16-hour series)

MCITP Self-Paced Training Kit (Exam 70-441): Designing Database Solutions by Using Microsoft SQL Server 2005

Microsoft SQL Server 2005: Database Essentials Step by Step

Microsoft SQL Server 2005: Applied Techniques Step by Step

Inside Microsoft SQL Server 2005: The Storage Engine

Inside Microsoft SQL Server 2005: T-SQL Programming

Inside Microsoft SQL Server 2005: Query Tuning and Optimization

Programming Microsoft SQL Server 2005

MeasureUp (Measureup.com)

Self Test Software (Selftestsoftware.com)

Microsoft online resources

•

Learning Plan: Get started by creating a step-by-step study guide that is based on recommended resources for this exam.

•

Microsoft SQL Server 2005 – Learning Portal: Find special offers and information on training and certification.

•

Microsoft SQL Server TechCenter: SQL Server–related resources from Microsoft and the broader SQL Server community.

•

Microsoft SQL Server Developer Center: SQL Server 2005 articles, downloads, support, community, and more for developers.

•

Microsoft Learning Community: Join newsgroups and visit community forums to connect with your peers for suggestions on training resources and advice on your certification path and studies.

•

TechNet: Designed for IT professionals, this site includes how-to instructions, best practices, downloads, technical resources, newsgroups, and chats.

•

MSDN: Designed for developers, the Microsoft Developer Network (MSDN) features code samples, technical articles, downloads, newsgroups, and chats.

Top of pageTop of page

Skills 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 listed in the following table. The table lists Official Microsoft Learning Products to help you reach competency in the skills being tested in the exam.

KEY:The course provides a general introductory overview of this task. You will need to supplement the course with additional work. = The course provides a general introductory overview of this task. You will need to supplement the course with additional work.    The course includes some material to prepare you for this task. You will need to supplement the course with additional work. = The course includes some material to prepare you for this task. You will need to supplement the course with additional work.    The course includes material to prepare you for this task. = The course includes material to prepare you for this task.
Skills measured by Exam 70-441Course 2781Course 2782
Designing Database Testing and Code Management Procedures   

Design a unit test plan for a database.

•

Assess which components should be unit tested.

•

Design tests for query performance.

•

Design tests for data consistency.

•

Design tests for application security.

•

Design tests for system resources utilization.

•

Design tests to ensure code coverage.

The course includes some material to prepare you for this task. You will need to supplement the course with additional work.


Create a performance baseline and benchmarking strategy for a database.

•

Establish performance objectives and capacity planning.

•

Create a strategy for measuring performance changes.

•

Create a plan for responding to performance changes.

•

Create a plan for tracking benchmark statistics over time.

The course includes some material to prepare you for this task. You will need to supplement the course with additional work.


Create a plan for deploying a database.

•

Select a deployment technique.

•

Design scripts to deploy the database as part of application setup.

•

Design database change scripts to apply application patches.

•

Design scripts to upgrade database data and objects.

The course includes some material to prepare you for this task. You will need to supplement the course with additional work.


Control changes to source code.

•

Set file permissions.

•

Set and retrieve version information.

•

Detect differences between versions.

•

Encrypt source code.

•

Mark groups of objects, assign version numbers to them, and devise a method to track changes.

The course includes some material to prepare you for this task. You will need to supplement the course with additional work.


Designing an Application Solution for SQL Server 2005   

Select and design SQL Server services to support business needs.

•

Select the appropriate services to use to support business needs.

•

Design a SQL Web services solution.

•

Design a Notification Services solution to notify users.

•

Design a Service Broker solution for asynchronous database applications.

•

Design a Microsoft Distributed Transaction Coordinator (MS DTC) solution for distributed transactions.

•

Design a Reporting Services solution.

•

Design an Integration Services solution.

•

Design a SQL Server core service solution.

•

Design a SQL Server Agent solution.

•

Design a DatabaseMail solution.

The course includes some material to prepare you for this task. You will need to supplement the course with additional work.


Design a logical database.

•

Design a normalized database.

•

Optimize the database design by denormalizing.

•

Design data flow architecture.

•

Optimize queries by creating indexes.

•

Design table width.

•

Design index-to-table-size ratio.

The course includes material to prepare you for this task.


Design an application solution to support security.

•

Design and implement application security.

•

Design the database to enable auditing.

•

Design objects to manage user access.

•

Design data-level security that uses encryption.

The course includes some material to prepare you for this task. You will need to supplement the course with additional work.


Design an application solution that uses appropriate database technologies and techniques.

•

Design a solution for storage of XML data in the database.

•

Choose appropriate languages.

•

Design a solution for scalability.

•

Design interoperability with external systems.

•

Develop aggregation strategies.

The course includes some material to prepare you for this task. You will need to supplement the course with additional work.


Design an application solution that supports reporting.

•

Design a snapshot strategy.

•

Design the schema.

•

Design the data transformation.

•

Design indexes for reporting.

•

Choose programmatic interfaces.

•

Evaluate use of reporting services.

•

Decide which data access method to use.

The course includes some material to prepare you for this task. You will need to supplement the course with additional work.


Design data distribution.

•

Design a DatabaseMail solution for distributing data.

•

Design SQL Server Agent alerts.

•

Specify a Web services solution for distributing data.

•

Specify a Reporting Services solution for distributing data.

•

Specify a Notification Services solution for distributing data.



Designing Database Objects   

Design objects that define data.

•

Design user-defined data types.

•

Design tables that use advanced features.

•

Design indexes.

•

Specify indexed views to meet business requirements.


The course includes some material to prepare you for this task. You will need to supplement the course with additional work.

Design objects that retrieve data.

•

Design views.

•

Design user-defined functions.

•

Design stored procedures.


The course includes some material to prepare you for this task. You will need to supplement the course with additional work.

Design objects that extend the functionality of a server.

•

Design scalar user-defined functions to extend the functionality of the server.

•

Design CLR user-defined aggregates.

•

Design stored procedures to extend the functionality of the server.


The course includes material to prepare you for this task.

Design objects that perform actions.

•

Design DML triggers.

•

Design DDL triggers.

•

Design WMI triggers.

•

Design Service Broker applications.

•

Design stored procedures to perform actions.


The course includes some material to prepare you for this task. You will need to supplement the course with additional work.

Designing a Database   

Design attributes.

•

Decide whether to persist an attribute.

•

Specify domain integrity by creating attribute constraints.

•

Choose appropriate column data types and sizes.


The course includes some material to prepare you for this task. You will need to supplement the course with additional work.

Design entities.

•

Define entities.

•

Define entity integrity.

•

Normalize tables to reduce data redundancy.

•

Establish the appropriate level of denormalization.


The course includes some material to prepare you for this task. You will need to supplement the course with additional work.

Design entity relationships (ER).

•

Specify ER for referential integrity.

•

Specify foreign keys.

•

Create programmable objects to maintain referential integrity.


The course includes some material to prepare you for this task. You will need to supplement the course with additional work.

Design database security.

•

Define database access requirements.

•

Specify database object security permissions.

•

Define schemas to manage object ownership.

•

Specify database objects that will be used to maintain security.

•

Design an execution context strategy.


The course includes some material to prepare you for this task. You will need to supplement the course with additional work.

Developing Applications That Use SQL Server Support Services   

Develop applications that use Reporting Services.

•

Specify subscription models, testing reports, error handling, and server impact.

•

Design reports.

•

Specify data source configuration.

•

Optimize reports.



Develop applications for Notification Services.

•

Create Notification Services configuration and application files.

•

Configure Notification Services instances.

•

Define Notification Services events and event providers.

•

Configure the Notification Services generator.

•

Configure the Notification Services distributor.

•

Test the Notification Services application.

•

Create subscriptions.

•

Optimize Notification Services.

The course includes some material to prepare you for this task. You will need to supplement the course with additional work.


Develop packages for Integration Services.

•

Select an appropriate Integration Services technology or strategy.

•

Create Integration Services packages.

•

Test Integration Services packages.



Note This preparation guide is subject to change at any time without prior notice and at the sole discretion of Microsoft. Microsoft exams might include adaptive testing technology and simulation items. Microsoft does not identify the format in which exams are presented. Please use this preparation guide to prepare for the exam, regardless of its format.

•

Learn more and download samples


Top of pageTop of page