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
|
