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.
- Design SQL Server instances.
- Design physical database and object placement.
- Design a migration, consolidation, and upgrade strategy.
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
This objective may include but is not limited to: instance configuration, surface area configuration, CPU affinity, memory allocation, max degree of parallelism (MAXDOP), collation
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
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
- Design instance-level security configurations
- Design database, schema, and object security parameters
- Design a security policy and an audit plan
- Design an encryption strategy
This objective may include but is not limited to: choosing authentication type, logon triggers, regulatory requirements
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
This objective may include but is not limited to: users, roles, certificate and key management, Service broker, Common Language Runtime (CLR), ownership chains
This objective may include but is not limited to: Policy-Based Management Framework, security functions, sp_helprotect, catalog views, extended events, notifications
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
- Design database mirroring
- Design a high-availability solution that is based on replication
- Design a high-availability solution that is based on log shipping
- Select high-availability technologies based on business requirements
This objective may include but is not limited to: cluster resource group, cluster setup considerations, number of nodes, service accounts
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
This objective may include but is not limited to: different replication types, topologies, recover from replication failure, synchronization, health monitoring
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
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
- Design a recovery strategy
- Design a recovery test plan
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
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
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
- Design a monitoring solution at the instance level
- Design a solution to monitor performance and concurrency
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
This objective may include but is not limited to: instance, database and object monitoring, data collection, event notifications, dynamic management objects, analyze results
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
- Design a solution to govern resources
- Design policies by using Policy-Based Management
- Design a data compression strategy
- Design a management automation strategy
This objective may include but is not limited to: rebuild for page-level compression, index and heap maintenance, partition management, statistics
This objective may include but is not limited to: Resource Governor (CPU, memory, number of requests per second; resource pools, resource groups), query governor
This objective may include but is not limited to: designing policies and conditions
This objective may include but is not limited to: row vs. page level, update frequency, compression ratio, compressing partitions, specific indexes
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
- Design a strategy to use linked servers
- Design a replication strategy for data distribution
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
This objective may include but is not limited to: security, providers, distributed transactions
This objective may include but is not limited to: selecting replication types, conflict resolution, health monitoring, horizontal and vertical partitioning