Plan and Implement Data Platform Resources

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

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 securable

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

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

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

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

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

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