Plan and Implement Data Platform Resources (15-20%)

• Deploy resources by using manual methods
• deploy database offerings on selected platforms
• configure customized deployment templates
• apply patches and updates for hybrid and IaaS deployment
• Recommend an appropriate database offering based on specific requirements
• evaluate requirements for the deployment
• evaluate the functional benefits/impact of possible database offerings
• evaluate the scalability of the possible database offering
• evaluate the HA/DR of the possible database offering
• evaluate the security aspects of the possible database offering
• Configure resources for scale and performance
• configure Azure SQL Database for scale and performance
• configure Azure SQL managed instances for scale and performance
• configure SQL Server in Azure VMs for scale and performance
• calculate resource requirements
• evaluate database partitioning techniques, such as database sharding
• set up SQL Data Sync
• Evaluate a strategy for moving to Azure
• evaluate requirements for the migration
• evaluate offline or online migration strategies
• evaluate requirements for the upgrade
• evaluate offline or online upgrade strategies
• Implement a migration or upgrade strategy for moving to Azure
• implement an online migration strategy
• implement an offline migration strategy
• implement an online upgrade strategy
• implement an offline upgrade strategy

Implement a Secure Environment (15-20%)

• Configure database authentication by using platform and database tools
• configure Azure AD authentication
• create users from Azure AD identities
• configure security principals
• Configure database authorization by using platform and database tools
• configure database and object-level permissions using graphical tools
• apply principle of least privilege for all securables
• Implement security for data at rest
• implement Transparent Data Encryption (TDE)
• implement object-level encryption
• implement Dynamic Data Masking
• implement Azure Key Vault and disk encryption for Azure VMs
• Implement security for data in transit
• configure server and database-level firewall rules
• implement Always Encrypted
• Implement compliance controls for sensitive data
• apply a data classification strategy
• configure server and database audits
• implement data change tracking
• perform a vulnerability assessment

Monitor and Optimize Operational Resources (15-20%)

• Monitor activity and performance
• prepare an operational performance baseline
• determine sources for performance metrics
• interpret performance metrics
• configure and monitor activity and performance at the infrastructure, server, service, and
• database levels
• Implement performance-related maintenance tasks
• implement index maintenance tasks
• implement statistics maintenance tasks
• configure database auto-tuning
• manage storage capacity
• Identify performance-related issues
• configure Query Store to collect performance data
• identify sessions that cause blocking
• assess growth/fragmentation of databases and logs
• assess performance-related database configuration parameters
• Configure resources for optimal performance
• configure storage and infrastructure resources
• configure server and service account settings for performance
• configure Resource Governor for performance
• Configure a user database for optimal performance
• implement database-scoped configuration
• configure compute resources for scaling
• configure Intelligent Query Processing (IQP)

Optimize Query Performance (5-10%)

• Review query plans
• determine the appropriate type of execution plan
• identify problem areas in execution plans
• extract query plans from the Query Store
• Evaluate performance improvements
• determine the appropriate Dynamic Management Views (DMVs) to gather query
• performance information
• identify performance issues using DMVs
• identify and implement index changes for queries
• recommend query construct modifications based on resource usage
• assess the use of hints for query performance
• Review database table and index design
• identify data quality issues with duplication of data
• identify normal form of database tables
• assess index design for performance
• validate data types defined for columns
• recommend table and index storage including filegroups
• evaluate table partitioning strategy
• evaluate the use of compression for tables and indexes

Perform Automation of Tasks (10-15%)

• Create scheduled tasks
• manage schedules for regular maintenance jobs
• configure multi-server automation
• configure notifications for task success/failure/non-completion
• Evaluate and implement an alert and notification strategy
• create event notifications based on metrics
• create event notifications for Azure resources
• create alerts for server configuration changes
• create tasks that respond to event notifications
• Manage and automate tasks in Azure
• perform automated deployment methods for resources
• implement policies by using automated evaluation modes

Plan and Implement a High Availability and Disaster Recovery (HADR) Environment (15-20%)

• Recommend an HADR strategy for a data platform solution
• recommend HADR strategy based on RPO/RTO requirements
• evaluate HADR for hybrid deployments
• evaluate Azure-specific HADR solutions
• identify resources for HADR solutions
• Test an HADR strategy by using platform, OS, and database tools
• test HA by using failover
• test DR by using failover or restore
• Perform backup and restore a database by using database tools
• perform a database backup with options
• perform a database restore with options
• perform a database restore to a point in time
• configure long-term backup retention
• Configure HA/DR by using OS, platform, and database tools
• configure replication
• create an Always On Availability Group
• configure auto-failover groups
• integrate a database into an Availability Group
• configure quorum options for a Windows Server Failover Cluster
• configure an Availability Group listener

Perform Administration by Using T-SQL (10-15%)

• Examine system health
• evaluate database health using DMVs
• evaluate server health using DMVs
• perform database consistency checks by using DBCC
• Monitor database configuration by using T-SQL
• assess proper database autogrowth configuration
• report on database free space
• review database configuration options
• Perform backup and restore a database by using T-SQL
• prepare databases for Always On Availability Groups
• perform transaction log backup
• perform restore of user databases
• perform database backups with options
• Manage authentication by using T-SQL
• manage certificates
• manage security principals
• Manage authorization by using T-SQL
• configure permissions for users to access database objects
• configure permissions by using custom roles