Home > Articles > Microsoft > MCITP

  • Print
  • + Share This
Exam Objectives

Exam Objectives

This exam is divided into seven major portions. Each of these main objectives map to roles database administrators typically fulfill on a daily basis. The percentages listed alongside each heading indicate the relative weight of each major topic area on the exam.

Designing a SQL Server Instance and a Database Solution (14%)

  • Design for CPU, memory, and storage capacity requirements.
  • This objective may include but is not limited to: RAID, calculating table size, IO throughput, transaction per second, data compression, non-uniform memory access (NUMA), tempdb capacity

  • Design SQL Server instances.
  • This objective may include but is not limited to: instance configuration, surface area configuration, CPU affinity, memory allocation, max degree of parallelism (MAXDOP), collation

  • Design physical database and object placement.
  • This objective may include but is not limited to: 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.
  • This objective may include but is not limited to: multi-instance considerations, SQL Server version upgrade, instance and database collation, server-level and instance-level objects, service pack application

Designing a Database Server Security Solution (15%)

  • Design instance authentication
  • This objective may include but is not limited to: choosing authentication type, logon triggers, regulatory requirements

  • Design instance-level security configurations
  • This objective may include but is not limited to: 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
  • This objective may include but is not limited to: users, roles, certificate and key management, Service broker, Common Language Runtime (CLR), ownership chains

  • Design a security policy and an audit plan
  • This objective may include but is not limited to: Policy-Based Management Framework, security functions, sp_helprotect, catalog views, extended events, notifications

  • Design an encryption strategy
  • This objective may include but is not limited to: Transparent Data Encryption, encrypting protected data, certificate and key management, filestream

Designing a Database Solution for High Availability (15%)

  • Design a failover clustering solution
  • This objective may include but is not limited to: cluster resource group, cluster setup considerations, number of nodes, service accounts

  • Design database mirroring
  • This objective may include but is not limited to: whether to use a witness server, Windows Server considerations, suspend vs. stop, automatic or manual failover, automatic page repair, database snapshots for reporting, managing instance-level objects

  • Design a high-availability solution that is based on replication
  • This objective may include but is not limited to: different replication types, topologies, recover from replication failure, synchronization, health monitoring

  • Design a high-availability solution that is based on log shipping
  • This objective may include but is not limited to: 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
  • This objective may include but is not limited to: failover clustering, database mirroring, log shipping, replication

Designing a Backup and Recovery Solution (20%)

  • Design a backup strategy
  • This objective may include but is not limited to: recovery model, compression, choosing backup types, scheduling, backup media, file and filegroups backup, verifying backups, key management, mirrored backups, cluster considerations

  • Design a recovery strategy
  • This objective may include but is not limited to: page, file, filegroup, partial and online restores, orphan users, instance rebuild, encryption considerations, handling media failures, transaction logs, point in time and mark recovery, filestreams

  • Design a recovery test plan
  • This objective may include but is not limited to: log shipping, replication, hardware considerations, scheduling a database restore test, handling high availability failures

Designing a Monitoring Strategy (13%)

  • Design a monitoring solution at the operating system level
  • This objective may include but is not limited to: system monitor counters, event logs, dynamic management views and functions, Windows Management Instrumentation (WMI), remote monitoring, analyze results

  • Design a monitoring solution at the instance level
  • This objective may include but is not limited to: instance, database and object monitoring, data collection, event notifications, dynamic management objects, analyze results

  • Design a solution to monitor performance and concurrency
  • This objective may include but is not limited to: Dedicated Administrator Connection (DAC), locking, blocking, deadlocks, dynamic management objects, index utilization, tracing, analyze

Designing a Strategy to Maintain and Manage Databases (14%)

  • Design a maintenance strategy for database servers
  • This objective may include but is not limited to: rebuild for page-level compression, index and heap maintenance, partition management, statistics

  • Design a solution to govern resources
  • This objective may include but is not limited to: Resource Governor (CPU, memory, number of requests per second; resource pools, resource groups), query governor

  • Design policies by using Policy-Based Management
  • This objective may include but is not limited to: designing policies and conditions

  • Design a data compression strategy
  • This objective may include but is not limited to: row vs. page level, update frequency, compression ratio, compressing partitions, specific indexes

  • Design a management automation strategy
  • This objective may include but is not limited to: SQL Server PowerShell, Windows Management Instrumentation (WMI), SQL Server Agent, event notifications, DDL triggers

Designing a Strategy for Data Distribution (9%)

  • Administer SQL Server Integration Services (SSIS) packages
  • This objective may include but is not limited to: 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
  • This objective may include but is not limited to: security, providers, distributed transactions

  • Design a replication strategy for data distribution
  • This objective may include but is not limited to: selecting replication types, conflict resolution, health monitoring, horizontal and vertical partitioning

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.