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

The Azure Database Administrator implements and manages the operational aspects of cloud-native and hybrid data platform solutions built on Microsoft Azure data services and Microsoft SQL Server. The Azure Database Administrator uses a variety of methods and tools to perform day-to-day operations, including applying knowledge of using T-SQL for administrative management purposes.

Azure DBA responsible for management, availability, security, and performance monitoring and optimization of modern relational database solutions. Register Today


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

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

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