Take this exam by 31 May 2015 and get a free Second Shot. Sign in to see if you are already eligible.

Exam 70-450

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

$150.00 USD*

Not in the United States?

* Pricing does not include any promotional offers or reduced pricing for Microsoft IT Academy program members, Microsoft Certified Trainers, Microsoft Partner Network program members, and students. Price is subject to change without notice. Price does not include applicable taxes. Please confirm exact pricing with the exam provider before registering to take an exam.
Microsoft SQL Server 2012 logo
  • Published: 12 November 2008
  • Languages: English
  • Audiences: IT professionals
  • Technology: Microsoft SQL Server 2008
  • Credit towards certification: MCITP: Database Administrator 2008

Convince your boss email

Copy the following content into an email message to your boss and update [the bracketed text] with the appropriate content.

Dear [your boss's name]:

I'd like to expand and prove my technical skills by earning a Microsoft Certification. My next step is taking Exam 70-450. The registration fee is $150.00 USD.

When I pass this certification, you'll have proof of my ability to work with Microsoft SQL Server 2008. Microsoft has training and online resources to help me prepare for the exam. I believe I'll be more effective and productive after achieving this valuable technical certification.

Let me know if you have any questions. In the meantime, you can find out more about this exam at https://www.microsoft.com/learning/en-hk/exam-70-450.aspx. As you can see, the exam validates skills that are crucial to our team's success.

Thank you for your support,

[your name]

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 in the exam. The higher the percentage, the more questions you are likely to see on that content area in the exam.

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

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

  • 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

  • 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, catalogue views, extended events, notifications

  • Design an encryption strategy

    • Transparent Data Encryption, encrypting protected data, certificate and key management, filestream

  • 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, synchronisation, 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, reinitialising, consistency check on secondary instance

  • Select high-availability technologies based on business requirements

    • Failover clustering, database mirroring, log shipping, replication

  • 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

  • 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, analyse 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, analyse results

  • Design a solution to monitor performance and concurrency

    • Dedicated Administrator Connection (DAC), locking, blocking, deadlocks, dynamic management objects, index utilisation, tracing, analyse

  • 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

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

Buy a practice test for Exam 70-450 from:

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

We recommend that you review this exam preparation guide in its entirety and familiarise 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 of the topics listed in the "Skills measured" section.