Administering Relational Databases on Microsoft Azure @ Azure DBA : DP 300

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 SQL DB and database-level firewall rules
• implement Always Encrypted
• configure Azure Data Gateway

Implement compliance controls for sensitive data

• apply a data classification strategy
• configure server and database audits
• implement data change tracking
• perform vulnerability assessment

Monitor activity and performance

• prepare an operational performance baseline
• determine sources for performance metrics
• interpret performance metrics
• assess database performance by using Azure SQL Database Intelligent Performance
• 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
  
  • Azure SQL agent jobs, Azure automation, SQL server agent jobs
• 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
  • including AutoClose, AutoShrink, AutoGrowth

Configure resources for optimal performance

• configure storage and infrastructure resources
  • optimize IOPS, throughput, and latency
  • optimize tempdb performance
  • optimize data and log files for performance
• 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)

Review query plans

• determine the appropriate type of execution plan
• live Query Statistics, Actual Execution Plan, Estimated Execution Plan, Showplan
• 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
• 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

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

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 DR by using platform and database tools

• configure replication
• configure Azure Site Recovery for a database offering

Configure HA using platform, OS and database tools

• create an Availability Group
• integrate a database into an Availability Group
• configure quorum options for a Windows Server Failover Cluster
• configure an Availability Group listener

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