PRO: Designing Database Solutions by Using Microsoft SQL Server 2005

Exam News

Exam 70-441: PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 became available January 30, 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 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 Certification

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

• Microsoft Certified IT Professional: Database Developer

Instructor-led Courses for This Exam

• Course 2781: Designing Microsoft SQL Server 2005 Server-Side Solutions

• Course 2782: Designing Microsoft SQL Server 2005 Databases

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 listed below.

Skills measured by exam 70-441

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.

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

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.

• 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.

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.

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

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

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.