Preparation Guide for Exam 70-443

PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005

Updated: April 23, 2008

Exam news

This Microsoft Certified Professional (PRO) exam, Exam 70-443: PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005, became available in February 2006.

Top of page

Audience profile

Candidates for this exam are professional database administrators who optimize and maintain database solutions. They have three or more years dedicated to database work, which may include two or more of the following phases in the product lifecycle: design, development, deployment, optimization, maintenance, or support. The typical work environment is an enterprise or a midsize organization. Candidates should be experienced in using Microsoft SQL Server 2005.

Candidates for this exam should be an expert in the following areas:

•	Defining high-availability solutions
•	Automating administrative tasks
•	Defining security solutions
•	Monitoring and troubleshooting the database server
•	Designing and executing deployments
•	Defining the infrastructure (such as storage, hardware, and number of servers or instances)

Top of page

Credit toward certification

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

•	Microsoft Certified IT Professional: Database Administrator

Top 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 training	Microsoft E-Learning	Microsoft Press books	Practice tests
Course 2786: Designing a Microsoft SQL Server 2005 Infrastructure (two days) Course 2787: Designing Security for Microsoft SQL Server 2005 (two days) Course 2788: Designing High Availability Database Solutions Using Microsoft SQL Server 2005 (three days)	Collection 2786: Designing a Microsoft SQL Server 2005 Infrastructure (14-hour series) Collection 2787: Designing Security for Microsoft SQL Server 2005 (12-hour series) Collection 2788: Designing High Availability Database Solutions Using Microsoft SQL Server 2005 (16-hour series)	MCITP Self-Paced Training Kit (Exam 70-443): Designing a Database Server Infrastructure Using Microsoft SQL Server 2005 Inside Microsoft SQL Server 2005: The Storage Engine Inside Microsoft SQL Server 2005: T-SQL Querying Inside Microsoft SQL Server 2005: T-SQL Programming Inside Microsoft SQL Server 2005: Query Tuning and Optimization Microsoft SQL Server 2005: Database Essentials Step by Step Microsoft SQL Server 2005 Administrator's Pocket Consultant Microsoft SQL Server 2005 Administrator's Companion	MeasureUp (Measureup.com) Self Test Software (Selftestsoftware.com)

Classroom training

Microsoft E-Learning

Microsoft Press books

Practice tests

Course 2786: Designing a Microsoft SQL Server 2005 Infrastructure (two days)

Course 2787: Designing Security for Microsoft SQL Server 2005 (two days)

Course 2788: Designing High Availability Database Solutions Using Microsoft SQL Server 2005 (three days)

Collection 2786: Designing a Microsoft SQL Server 2005 Infrastructure (14-hour series)

Collection 2787: Designing Security for Microsoft SQL Server 2005 (12-hour series)

Collection 2788: Designing High Availability Database Solutions Using Microsoft SQL Server 2005 (16-hour series)

MCITP Self-Paced Training Kit (Exam 70-443): Designing a Database Server Infrastructure Using Microsoft SQL Server 2005
Inside Microsoft SQL Server 2005: The Storage Engine
Inside Microsoft SQL Server 2005: T-SQL Querying
Inside Microsoft SQL Server 2005: T-SQL Programming
Inside Microsoft SQL Server 2005: Query Tuning and Optimization
Microsoft SQL Server 2005: Database Essentials Step by Step
Microsoft SQL Server 2005 Administrator's Pocket Consultant
Microsoft SQL Server 2005 Administrator's Companion

MeasureUp (Measureup.com)

Self Test Software (Selftestsoftware.com)

Microsoft online resources

•	Learning Plan: Get started with a step-by-step study guide that is based on recommended resources for this exam.
•	Microsoft Learning Community: Join newsgroups and visit community forums to connect with peers for suggestions on training resources and advice on your certification path and studies.
•	Microsoft SQL Server 2005 – Learning Portal: Find special offers and information on training and certification.
•	Product information: Visit the SQL Server 2005 Web site for detailed product information.
•	Microsoft Learning Community: Join newsgroups and visit community forums to connect with peers for suggestions on training resources and advice on your certification path and studies.
•	TechNet: Designed for IT professionals, this site features 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 page

Skills measured

This certification exam measures your ability to design a database server infrastructure by using SQL Server 2005. Before taking the exam, you should be proficient in the job skills listed in the following table. The table shows the official Microsoft Learning products that may 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 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.

Skills measured by Exam 70-443

Course 2786

Course 2787

Course 2788

Designing a Database Server Infrastructure

Design for capacity requirements.

•	Analyze storage requirements.
•	Analyze network requirements.
•	Analyze CPU requirements.
•	Analyze the current configuration.
•	Analyze memory requirements.
•	Forecast and incorporate anticipated growth requirements into the capacity requirements.

Specify software versions and hardware configurations.

•	Choose a version and edition of the operating system.
•	Choose a version of SQL Server 2005.
•	Choose a CPU type.
•	Choose memory options.
•	Choose a type of storage.

Design physical storage.

•	Design transaction log storage.
•	Design backup file storage.
•	Decide where to install the operating system.
•	Decide where to place SQL Server service executables.
•	Specify the number and placement of files to create for each database.

Design instances.

•	Decide how many databases to create.
•	Decide on the placement of system databases for each instance.
•	Decide on the physical storage for the tempdb database for each instance.
•	Decide on the number of instances.
•	Decide on the naming of instances.
•	Decide how many physical servers are needed for instances.
•	Establish service requirements.
•	Specify instance configurations.

Design a database consolidation strategy.

•	Gather information to analyze the dispersed environment.
•	Identify potential consolidation problems.
•	Create a specification to consolidate SQL Server databases.
•	Design a database migration plan for the consolidated environment.
•	Test existing applications against the consolidated environment.

Designing Security for a Database Server Solution

Analyze business requirements.

•	Gather business and regulatory requirements.
•	Decide how requirements will impact choices at various security levels.
•	Evaluate costs and benefits of security choices.
•	Decide on appropriate security recommendations.
•	Inform business decision makers about security recommendations and their impact.
•	Incorporate feedback from business decision makers into a design.

Integrate database security with enterprise-level authentication systems.

•	Decide which authentication system to use.
•	Design Active Directory organizational units (OUs) to implement server-level security policies.
•	Ascertain the impact of authentication on a high-availability solution.
•	Establish the consumption of enterprise authentication.
•	Ascertain the impact of enterprise authentication on service up-time requirements.

Develop Microsoft Windows server-level security policies.

•	Develop a password policy.
•	Develop an encryption policy.
•	Specify server accounts and server account rights.
•	Specify the interaction of the database server with antivirus software.
•	Specify the set of running services and disable unused services.
•	Specify the interaction of the database server with server-level firewalls.
•	Specify a physically secure environment for the database server.

Modify the security design based on the impact of network security policies.

Analyze the risk of attacks to the server environment and specify mitigations.

Design SQL Server service-level security.

•	Specify logins.
•	Select SQL Server server roles for logins.
•	Specify a SQL Server service authentication mode.
•	Design a secure HTTP endpoint strategy.
•	Design a secure job role strategy for the SQL Server Agent Service.
•	Specify a policy for .NET assemblies.

Design database-level security.

•	Specify database users.
•	Design schema containers for database objects.
•	Specify database roles.
•	Define encryption policies.
•	Design DDL triggers.

Design object-level security.

•	Design a permissions strategy.
•	Analyze existing permissions.
•	Design an execution context.
•	Design column-level encryption.
•	Design security for CLR objects in the database.

Designing a Physical Database

Modify an existing database design based on performance and business requirements.

•	Ensure that a database is normalized.
•	Allow selected denormalization for performance purposes.
•	Ensure that the database is documented and diagrammed.

Design tables.

•	Decide if partitioning is appropriate.
•	Specify primary and foreign keys.
•	Specify column data types and constraints.
•	Decide whether to persist computed columns.
•	Specify physical location of tables, including file groups and a partitioning scheme.

Design file groups.

•	Design file groups for performance.
•	Design file groups for recoverability.
•	Design file groups for partitioning.

Design index usage.

•	Design indexes for faster data access.
•	Design indexes to improve data modification.
•	Specify physical placement of indexes.

Design views.

•	Analyze business requirements.
•	Choose the type of view.
•	Specify row and column filtering.

Create database conventions and standards.

•	Define database object-naming conventions.
•	Define consistent synonyms.
•	Define database coding standards.
•	Document database conventions and standards.

Create database change-control procedures.

•	Establish where to store database source code.
•	Isolate development and test environments from the production environment.
•	Define procedures for moving from development to test.
•	Define procedures for promoting from test to production.
•	Define procedures for rolling back a deployment.
•	Document the database change-control procedures.

Designing a Database Solution for High Availability

Select high-availability technologies based on business requirements.

•	Analyze availability requirements.
•	Analyze potential availability barriers.
•	Analyze environmental issues.
•	Analyze potential problems related to processes and staff.
•	Identify potential single points of failure.
•	Decide how quickly the database solution must failover.
•	Choose automatic or manual failback.
•	Analyze costs versus benefits of various solutions.
•	Combine high-availability technologies to improve availability.

Develop a strategy for migration to a highly available environment.

•	Analyze the current environment.
•	Ascertain migration options.
•	Choose a migration option.

Design a highly available database storage solution.

•	Design the RAID solutions for your environment.
•	Design a SAN solution.

Design a database-clustering solution.

•	Design a Microsoft Cluster Service (MSCS) implementation.
•	Design the cluster configuration of the SQL Server service.

Design database mirroring.

•	Design server roles for database mirroring.
•	Design the initialization of database mirroring.
•	Design a test strategy for planned and unplanned role changes.

Design a high-availability solution that is based on replication.

•	Specify an appropriate replication solution.
•	Choose servers for peer-to-peer replication.
•	Establish a strategy for resolving data conflicts.
•	Design an application failover strategy.
•	Design a strategy to reconnect client applications.

Design log shipping.

•	Specify the primary server and secondary server.
•	Switch server roles.
•	Design an application failover strategy.
•	Design a strategy to reconnect client applications.

Designing a Data Recovery Solution for a Database

Specify data recovery technologies based on business requirements.

•	Analyze how much data the organization can afford to lose.
•	Analyze alternative techniques to save redundant copies of critical business data.
•	Analyze how long the database system or database can be unavailable.

Design backup strategies.

•	Specify the number and location of devices to be used for backup.
•	Specify what data to back up.
•	Specify the frequency of backup.
•	Choose a backup technique.
•	Specify the type of backup.
•	Choose a recovery model.

Create a disaster recovery plan.

•	Document the sequence of possible events.
•	Create a disaster decision tree that includes restore strategies.
•	Establish recovery success criteria.
•	Validate restore strategies.

Designing a Strategy for Data Archiving

Select archiving techniques based on business requirements.

•	Gather requirements that affect archiving.
•	Ascertain data movement requirements for archiving.

Design the format of archival data.

Specify what data to archive.

•	Specify the level of granularity of an archive.
•	Specify how long to keep the archives.

Plan for data archival and access.

•	Specify the destination for archival data.
•	Specify the frequency of archiving.
•	Decide if replication is appropriate.
•	Establish how to access archived data.

Design the topology of replication for archiving data.

•	Specify the publications and articles to be published.
•	Specify the distributor of the publication.
•	Specify the subscriber of the publication.

Design the type of replication for archiving data.

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 page

Preparation Guide for Exam 70-443

PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005

Related Links

On This Page

Exam news

Audience profile

Credit toward certification

Preparation tools and resources

Microsoft online resources

Skills measured