Microsoft Learning:

Exam 70-441:

PRO: Designing Database Solutions by Using Microsoft SQL Server 2005

Published:January 30, 2006
Language(s):English, French, German, Japanese, Chinese (Simplified)
Audience(s):Developers
Technology:Microsoft SQL Server 2005
Type:Proctored Exam
Preparing for an Exam
This exam will be retired on June 30, 2011.
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
Credit Toward CertificationExam 70-441: PRO: Designing Database Solutions by Using Microsoft SQL Server 2005: counts as credit toward the following certification(s):
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.
Skills Being MeasuredThis exam measures your ability to accomplish the technical tasks listed below.The percentages indicate the relative weight of each major topic area on the exam.The higher the percentage, the more questions you are likely to see on that content area on the exam.

The information after “This objective may include but is not limited to” is intended to further define or scope the objective by describing the types of skills and topics that may be tested for the objective. However, it is not an exhaustive list of skills and topics that could be included on the exam for a given skill area. You may be tested on other skills and topics related to the objective that are not explicitly listed here.
Designing Database Testing and Code Management Procedures (12%)
  • 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.
  • 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.
  • 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.
  • 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.
Designing an Application Solution for SQL Server 2005 (27%)
  • 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.
  • 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.
  • 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.
  • 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.
  • Design an application solution that supports reporting.
    • Decide which data access method to use.
    • Design a snapshot strategy.
    • Design the schema.
    • Design the data transformation.
    • Design indexes for reporting.
    • Choose programmatic interfaces.
    • Evaluate use of reporting services.
  • 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 (22%)
  • 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.
  • Design objects that retrieve data.
    • Design views.
    • Design user-defined functions.
    • Design stored procedures.
  • 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.
  • Design objects that perform actions.
    • Design DML triggers.
    • Design DDL triggers.
    • Design WMI triggers.
    • Design Service Broker applications.
    • Design stored procedures to perform actions.
Designing a Database (21%)
  • Design attributes.
    • Decide whether to persist an attribute.
    • Specify domain integrity by creating attribute constraints.
    • Choose appropriate column data types and sizes.
  • Design entities.
    • Define entities.
    • Define entity integrity.
    • Normalize tables to reduce data redundancy.
    • Establish the appropriate level of denormalization.
  • Design entity relationships (ER).
    • Specify ER for referential integrity.
    • Specify foreign keys.
    • Create programmable objects to maintain referential integrity.
  • 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.
Developing Applications That Use SQL Server Support Services (18%)
  • 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.
  • Develop packages for Integration Services.
    • Select an appropriate Integration Services technology or strategy.
    • Create Integration Services packages.
    • Test Integration Services packages.
Preparation Tools and ResourcesTo 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" tab.
Learning Plans and Classroom Training
Microsoft E-Learning
Microsoft Press Books There are no Microsoft Press books currently available.
Practice Tests
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.
Have Questions? For advice about training and certification, connect with peers: For questions about a specific certification, chat with a Microsoft Certified Professional (MCP): To find out about recommended blogs, Web sites, and upcoming Live Meetings on popular topics, visit our community site:
Exam index

How To Get It

Schedule your exam through the following exam provider: