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/elastic pools 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
- assess database performance by using Intelligent Insights for Azure SQL Database and Managed Instance
- 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
- automate database maintenance tasks
- 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
- automate backups
- automate performance tuning and patching
- 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 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