DP-300: Azure Database Administrator Associate Study Guide
DP-300 (Azure Database Administrator Associate) validates your ability to plan and deploy Azure data platform resources, secure them, monitor and optimize performance, automate routine tasks, and design high availability and disaster recovery. It targets database administrators and infrastructure professionals who manage Azure SQL Database, Azure SQL Managed Instance, SQL Server on Azure VMs, and the open-source Azure Database services. You should know T-SQL, the Azure portal, PowerShell/CLI, and core SQL Server administration before sitting the exam.
Domain 1: Plan and Implement Data Platform Resources
- Azure SQL Managed Instance gives the highest compatibility with on-premises SQL Server because it supports instance-scoped features: SQL Server Agent, cross-database queries, linked servers, Service Broker, and CLR.
- Elastic pools let multiple Azure SQL databases share a common set of vCores/eDTUs, which lowers cost when databases have varying, non-overlapping usage peaks.
- The vCore purchasing model scales compute and storage independently and is the only model that supports Azure Hybrid Benefit (reuse of on-prem SQL Server licenses with Software Assurance).
- The DTU model bundles compute, storage, and I/O into fixed, pre-packaged units (Basic, Standard, Premium) and is simplest for predictable single-database workloads.
- Hyperscale supports databases up to 100 TB, fast backups/restores via snapshots, and rapid read scale-out using named replicas - it is the choice for very large databases.
- The Serverless compute tier (vCore only) auto-scales and auto-pauses during idle periods, billing per second of compute used - ideal for intermittent, unpredictable workloads with idle gaps.
- Business Critical uses locally attached SSD storage with an Always On availability group (3-4 replicas) and a built-in readable secondary; General Purpose uses remote Premium storage with a single compute node.
- Azure Database Migration Service (DMS) in online mode performs near-zero-downtime migrations by continuously syncing the source until you cut over.
- The Azure SQL Migration extension in Azure Data Studio handles modern migrations; Data Migration Assistant (DMA) assesses compatibility and feature parity before migration.
- Azure SQL Managed Instance must be deployed into a dedicated, delegated subnet inside a virtual network - it cannot share that subnet with other resources.
- Azure Database for PostgreSQL and MySQL Flexible Server offer Burstable, General Purpose, and Memory Optimized compute tiers, plus zone-redundant HA.
- On a SQL Server on Azure VM, place tempdb on the local ephemeral SSD (D: drive) for fast, non-persistent I/O, and put data/log files on Premium SSD or Ultra Disk managed disks.
- In an elastic pool you can cap any single database with a per-database min/max vCore (or eDTU) setting to prevent one database from starving the others.
- Zone-redundant configuration spreads replicas across availability zones in one region to survive a datacenter (zone) failure at no extra compute cost on supported tiers.
Domain 2: Implement a Secure Environment
- Transparent Data Encryption (TDE) encrypts data at rest - data files, log files, and backups - with no application code changes; it is enabled by default on new Azure SQL databases.
- TDE with customer-managed keys (Bring Your Own Key) stores the TDE protector in Azure Key Vault, giving you control over key rotation and revocation.
- Always Encrypted keeps sensitive columns encrypted end-to-end including during query processing; encryption/decryption happen only on the client and the server never sees plaintext.
- Dynamic Data Masking obscures sensitive values for non-privileged users at query time (for example an email masking function) without changing the stored data.
- SQL Data Discovery and Classification automatically finds and labels sensitive columns (SSNs, credit cards) so you can track your sensitive-data footprint.
- Microsoft Defender for SQL provides advanced threat protection and surfaces SQL injection alerts when application queries contain potentially malicious SQL.
- SQL Vulnerability Assessment scans against security best-practice rules and produces a baseline plus remediation guidance; results are stored in an Azure Storage account.
- Set a Microsoft Entra ID admin on the logical server, then enable Microsoft Entra-only authentication to disable SQL logins entirely for the strongest identity posture.
- Restrict network access by combining a virtual network service endpoint for Microsoft.Sql with a virtual network rule on the server.
- Setting 'Deny public network access' to Yes blocks all internet-based connections so only private/VNet paths remain.
- Azure Private Link with a private endpoint assigns the database a private IP inside your VNet, removing exposure to the public internet entirely.
- Server-level IP firewall rules (configurable in the portal, T-SQL, or PowerShell) allow specific client IP ranges; database-level rules are more granular and evaluated first.
- Enabling 'Allow Azure services and resources to access this server' opens connectivity to any Azure service (firewall rule 0.0.0.0) - convenient but broad, so use cautiously.
- Set the minimum TLS version to 1.2 on the logical server to reject connections using older, weaker TLS protocols, and route audit logs to a Log Analytics workspace or Storage account.
Domain 3: Monitor, Configure, and Optimize Resources
- Query Store continuously captures query plans, runtime statistics, and wait stats over time, letting you pinpoint when a regression started and force a previously good plan.
- Query Store plan forcing fixes plan-regression issues (for example after a statistics update chose a worse plan) without code changes.
- Key Query Store settings include STALE_QUERY_THRESHOLD_DAYS (retention) and DATA_FLUSH_INTERVAL_SECONDS (how often in-memory data is persisted).
- Automatic tuning in Azure SQL Database can apply FORCE PLAN (revert to last known good plan) and CREATE INDEX (add helpful indexes) automatically without manual approval.
- The PAGEIOLATCH_SH wait type means queries are waiting on data pages being read from disk into the buffer pool, indicating memory pressure or slow I/O.
- sys.dm_db_resource_stats shows recent CPU, data I/O, and log I/O percentage at ~15-second granularity for the current Azure SQL database.
- sys.dm_db_missing_index_details and sys.dm_db_missing_index_group_stats reveal indexes the optimizer wishes existed and their estimated benefit.
- sys.dm_exec_query_stats (often joined with sys.dm_exec_sql_text) ranks queries by aggregate cost, for example ORDER BY total_worker_time DESC for top CPU consumers.
- sys.dm_exec_requests with sys.dm_exec_sql_text identifies queries running right now along with their current wait type and blocking session.
- sys.indexes plus sys.dm_db_index_usage_stats expose unused or rarely used indexes that are candidates for removal.
- Use ALTER INDEX ... REORGANIZE for light (online, low-impact) defragmentation; use REBUILD for heavy fragmentation, which is more resource intensive.
- Intelligent Insights uses machine learning to analyze telemetry and provide root-cause analysis of performance degradation with actionable recommendations.
- Query Performance Insight in the portal surfaces the top resource-consuming and longest-running queries for an Azure SQL database without writing DMV queries.
- Add OPTION (RECOMPILE) to a query to defeat parameter-sniffing issues by forcing a fresh, parameter-specific plan on each execution at a small CPU cost.
Domain 4: Configure and Manage Automation of Tasks
- Elastic Jobs is the Azure-native way to run T-SQL on a schedule across many Azure SQL databases spanning different logical servers and pools; it requires a job database and an elastic job agent.
- Elastic Jobs target groups can include individual databases, elastic pools, or whole servers - add the elastic pool as a target group member to hit every database in it.
- SQL Server Agent is built into Azure SQL Managed Instance and supports T-SQL, OS command/PowerShell (CmdExec-style), SSIS package, and transactional replication job steps; unsupported items include Merge replication, Queue Reader, Analysis Services, and alerts.
- Azure SQL Database (single/pooled) has no SQL Server Agent; use Elastic Jobs or Azure Automation for scheduling instead.
- Azure Automation PowerShell runbooks with a schedule handle time-based operations such as scaling a database (Set-AzSqlDatabase) up before a peak window and back down after.
- Use Set-AzSqlDatabase in a runbook to change vCore count, service tier, or compute size on a schedule (for example 8 vCores normally, 16 every Friday evening).
- Use New-AzSqlDatabaseExport in a scheduled runbook to export a database to a BACPAC in Azure Storage on a recurring basis.
- Authenticate Automation runbooks with a managed identity (preferred) or a Run As account so they can call Azure resource cmdlets securely.
- Configure retry count and retry interval on a job step so transient failures retry automatically rather than failing the whole job.
- Azure Monitor metric alerts trigger on resource metrics like DTU percentage (for example > 90% for 5 minutes) and fire an action group.
- Azure Monitor log search (log query) alerts run a Kusto query against Log Analytics on a schedule to detect patterns metrics cannot express.
- An action group can chain multiple actions, such as an email notification plus triggering an Automation runbook to auto-remediate.
- Automate schema and configuration deployments with ARM templates or Bicep for infrastructure, and DACPAC deployments via SqlPackage in Azure DevOps/CI-CD pipelines for database objects.
- Index and statistics maintenance on many databases is commonly automated with Elastic Jobs running T-SQL maintenance scripts on a schedule.
Domain 5: Plan and Configure HA and DR Environment
- Auto-failover groups provide automatic cross-region failover and a single read-write listener endpoint, so applications connect once and Azure redirects to the current primary after failover.
- Auto-failover groups support both single databases and elastic pools, and the secondary server must be in a different Azure region from the primary.
- The GracePeriodWithDataLossHours parameter controls how long Azure waits before performing an automatic failover that could incur data loss.
- Active geo-replication supports up to four readable secondary replicas (same or different regions); failover must be initiated manually or via custom automation.
- With active geo-replication, each secondary lives on its own logical server with a unique name, so reporting apps connect directly to the secondary server's name for read-only queries.
- Point-in-time restore (PITR) uses automated full, differential, and log backups to restore to any second within the short-term retention window (configurable 1-35 days; default 7).
- PITR is the right recovery for accidental data changes/deletes: restore to a point just before the incident, which creates a brand-new database alongside the original.
- A deleted Azure SQL database can be recovered from the logical server's 'Deleted databases' list within the retention window without a separate backup.
- Long-term retention (LTR) keeps full backups for up to 10 years with weekly/monthly/yearly retention rules to satisfy audit and compliance mandates.
- Backup storage redundancy options are Locally redundant (LRS), Zone redundant (ZRS), and Geo redundant (GRS); choose GRS for cross-region backup durability.
- Azure SQL Database automated backups run continuously: full backups weekly, differentials every 12-24 hours, and transaction log backups every 5-10 minutes.
- Zone-redundant configuration protects against a single availability-zone failure within a region; for full regional DR you also need geo-replication or auto-failover groups.
- On Azure SQL Managed Instance, combine a zone-redundant Business Critical configuration with an auto-failover group to a secondary instance in another region for HA plus DR.
- SQL Server on Azure VM uses Always On Availability Groups with synchronous replicas for HA, just like on-premises SQL Server.
DP-300 exam tips
- Learn the decision tree for choosing a deployment option: SQL Database (PaaS, lowest management), Managed Instance (instance features/migration), or SQL on VM (full control). Many questions hinge on the unique feature that only one of them offers.
- Memorize the difference between active geo-replication (up to 4 readable secondaries, manual failover, per-server endpoints) and auto-failover groups (automatic failover, single read-write listener, supports pools).
- Know which DMV answers which question: sys.dm_db_resource_stats for resource usage, sys.dm_exec_requests for what is running now, sys.dm_exec_query_stats for historical top consumers, and the missing/unused index DMVs.
- For security scenarios, distinguish encryption at rest (TDE) from encryption in use (Always Encrypted) from masking (Dynamic Data Masking) - the wrong layer is a common trap answer.
- Watch for the automation boundary: SQL Server Agent exists on Managed Instance and SQL on VM but NOT on Azure SQL Database, where you must use Elastic Jobs or Azure Automation instead.
Study guide FAQ
How much does the DP-300 exam cost and how is it scored?
Pricing varies by region (commonly around USD 165), and you must score at least 700 on a 1000-point scale to pass. The exam runs about 120 minutes and includes roughly 40-60 questions in multiple formats, including multiple choice, drag-and-drop, and case studies.
Do I need deep T-SQL coding skills to pass DP-300?
You need solid working T-SQL and SQL Server administration knowledge, but the exam is administration-focused rather than development-focused. Expect to read and reason about DMV queries, ALTER INDEX statements, and configuration commands rather than write large stored procedures from scratch.
Does DP-300 cover the open-source Azure database services?
Yes. While Azure SQL Database, Managed Instance, and SQL on Azure VM are the core, the exam also touches Azure Database for PostgreSQL and MySQL Flexible Server, including their compute tiers (Burstable, General Purpose, Memory Optimized) and high-availability options.
What is the most efficient way to prepare for the exam?
Combine Microsoft Learn's DP-300 learning paths with hands-on practice in a real Azure subscription: deploy a database, configure TDE and a private endpoint, force a plan in Query Store, schedule an Elastic Job, and set up an auto-failover group. Then drill practice questions and review the official skills-measured outline before your test date.