Microsoft certification exam 70-450 test and examination of the relevant outline content requirements:

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

  • 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 percent)

  • 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: Windowsservice accounts, filestream, proxy, credentials, instance-level permissions, certificate and key management, endpoint security, using SSL certificates, TCP ports
  • Design database, schema, and object security paramaters
    • 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 percent)

  • 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 percent)

  • 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 percent)

  • 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 percent)

  • 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 percent)

  • 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