• Published:
    November 12, 2008
  • Languages:
  • Audiences:
    IT professionals
  • Technology:
    Microsoft SQL Server 2008
  • Credit toward certification:
    MCITP: Database Administrator 2008

Pro: Designing, optimizing, and maintaining a database administrative solution using Microsoft SQL Server 2008

This exam has been retired

For currently available options, please see the Microsoft Certification exam list.

Skills measured

This 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. View video tutorials about the variety of question types on Microsoft exams.

Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.

Do you have feedback about the relevance of the skills measured on this exam? Please send Microsoft your comments. All feedback will be reviewed and incorporated as appropriate while still maintaining the validity and reliability of the certification process. Note that Microsoft will not respond directly to your feedback. We appreciate your input in ensuring the quality of the Microsoft Certification program.

If you have concerns about specific questions on this exam, please submit an exam challenge.

If you have other questions or feedback about Microsoft Certification exams or about the certification program, registration, or promotions, please contact your Regional Service Center.

Design a SQL Server instance and a database solution (14%)
  • Design for CPU, memory, and storage capacity requirements
    • RAID, calculating table size, IO throughput, transaction per second, data compression, non-uniform memory access (NUMA), tempdb capacity
  • Design SQL Server instances
    • Instance configuration, surface area configuration, CPU affinity, memory allocation, max degree of parallelism (MAXDOP), collation
  • Design physical database and object placement
    • Heap and index placement, filestream, data and log files, filegroups, partition placement, large object placement, full text catalog
  • Design a migration, consolidation, and upgrade strategy
    • Multi-instance considerations, SQL Server version upgrade, instance and database collation, server-level and instance-level objects, service pack application

Preparation resources

Design a database server security solution (15%)
  • Design instance authentication
    • Choosing authentication type, logon triggers, regulatory requirements
  • Design instance-level security configurations
    • Windows service accounts, filestream, proxy, credentials, instance-level permissions, certificate and key management, endpoint security, using SSL certificates, TCP ports
  • Design database, schema, and object security parameters
    • Users, roles, certificate and key management, Service broker, Common Language Runtime (CLR), ownership chains
  • Design a security policy and an audit plan
    • Policy-Based Management Framework, security functions, sp_helprotect, catalog views, extended events, notifications
  • Design an encryption strategy
    • Transparent Data Encryption, encrypting protected data, certificate and key management, filestream

Preparation resources

Design a database solution for high availability (15%)
  • Design a failover clustering solution
    • Cluster resource group, cluster setup considerations, number of nodes, service accounts
  • Design database mirroring
    • Whether to use a witness server, Windows Server considerations, suspend versus stop, automatic or manual failover, automatic page repair, database snapshots for reporting, manage instance-level objects
  • Design a high-availability solution that is based on replication
    • Different replication types, topologies, recover from replication failure, synchronization, health monitoring
  • Design a high-availability solution that is based on log shipping
    • Manage instance-level objects, changing roles, reporting secondary instance for reporting, monitor server, reinitializing, consistency check on secondary instance
  • Select high-availability technologies based on business requirements
    • Failover clustering, database mirroring, log shipping, replication

Preparation resources

Design a backup and recovery solution (20%)
  • Design a backup strategy
    • Recovery model, compression, choose backup types, scheduling, backup media, file and filegroups backup, verify backups, key management, mirrored backups, cluster considerations
  • Design a recovery strategy
    • Page, file, filegroup, partial and online restores, orphan users, instance rebuild, encryption considerations, handle media failures, transaction logs, point in time and mark recovery, filestreams
  • Design a recovery test plan
    • Log shipping, replication, hardware considerations, schedule a database restore test, handle high-availability failures

Preparation resources

Design a monitoring strategy (13%)
  • Design a monitoring solution at the operating system level
    • System monitor counters, event logs, dynamic management views and functions, Windows Management Instrumentation (WMI), remote monitoring, analyze results
  • Install SQL Server and related services
    • Test connectivity; enable and disable features; install SQL Server database engine and SSIS (not SSRS and SSAS); configure an operating system disk
  • Design a monitoring solution at the instance level
    • Instance, database, and object monitoring, data collection, event notifications, dynamic management objects, analyze results
  • Design a solution to monitor performance and concurrency
    • Dedicated Administrator Connection (DAC), locking, blocking, deadlocks, dynamic management objects, index utilization, tracing, analyze

Preparation resources

Design a strategy to maintain and manage databases (14%)
  • Design a maintenance strategy for database servers
    • Rebuild for page-level compression, index and heap maintenance, partition management, statistics
  • Design a solution to govern resources
    • Resource Governor (CPU, memory, number of requests per second, resource pools, resource groups), query governor
  • Design policies by using Policy-Based Management
    • Designing policies and conditions
  • Design a data compression strategy
    • Row versus page level, update frequency, compression ratio, compressing partitions, specific indexes
  • Design a management automation strategy
    • SQL Server PowerShell, Windows Management Instrumentation (WMI), SQL Server Agent, event notifications, DDL triggers

Preparation resources

Design a strategy for data distribution (9%)
  • Administer SQL Server Integration Services (SSIS) packages
    • Design security for accessing packages, troubleshoot and restart package, schedule package execution, deploy packages to same or different instances
  • Design a strategy to use linked servers
    • Security, providers, distributed transactions
  • Design a replication strategy for data distribution
    • Select replication types, conflict resolution, health monitoring, horizontal and vertical partitioning

Preparation resources

Who should take this exam?

Candidates for this exam are senior database administrators who design and maintain database administrative solutions across multiple server instances and services.

More information about exams

Preparing for an exam

We recommend that you review this exam preparation guide in its entirety and familiarize yourself with the resources on this website before you schedule your exam. See the Microsoft Certification exam overview for information about registration, videos of typical exam question formats, and other preparation resources. For information on exam policies and scoring, see the Microsoft Certification exam policies and FAQs.


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. To help you prepare for this exam, Microsoft recommends that you have hands-on experience with the product and that you use the specified training resources. These training resources do not necessarily cover all topics listed in the "Skills measured" section.