Microsoft 70-450

Free Questions in OTE format

About Microsoft 70-450 dump

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
  

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
  

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
  

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
  

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
  

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
  

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