The DP-300 exam asks you to choose the right Azure SQL deployment model for each scenario. Memorizing feature names is not enough — you need to understand when each option reaches its limits. The exam covers five operational domains: deploying a data platform, securing the environment, monitoring and optimizing, automating tasks, and designing HA/DR. This post walks through key decision points using real-world scenarios.
Three Deployment Models
Imagine choosing between an apartment where all maintenance is handled by building management versus one where you repair the plumbing yourself. The two options change your entire daily life. Azure SQL works the same way.
is a fully managed PaaS offering. Microsoft handles OS patching, backups, and high availability. It operates at the single-database level. SQL Server Agent, cross-database queries, and CLR are not supported.
is also PaaS but provides a full SQL Server instance. It is the right choice for legacy migrations that need SQL Server Agent, cross-database queries, or Linked Servers. Broader scope means higher cost.
is IaaS. The DBA controls the full OS and SQL Server installation. Choose this when you need all SQL Server ecosystem features — like SSRS or SSIS — or complete OS-level control.
Encryption Choices: TDE or Always Encrypted?
If you deposit money in a safe but the vault company's staff can still open it and see the contents, can you really call that secure? That question draws the exact line between TDE and Always Encrypted.
encrypts data at rest at the disk level. The SQL Server engine and DBAs can still see the plaintext. It protects against ransomware and physical disk theft, and is enabled by default on both Azure SQL Database and Managed Instance.
is client-side encryption. Because encryption keys reside in the client application or Azure Key Vault, the SQL Server engine itself cannot access the plaintext. When a requirement states that even DBAs must not read sensitive columns — such as social security numbers or credit card numbers — Always Encrypted is the answer.
In exam questions: 'encrypt data at rest' alone points to TDE. 'DBAs cannot read the column' or 'security team controls keys separately' points to Always Encrypted.
High Availability: Auto-Failover Group vs Active Geo-Replication
A commercial aircraft does not fly with a single engine. If one fails, the other keeps the plane in the air. Azure SQL high availability follows the same principle, but your choice depends on the level of automation you need.
creates read-only secondary replicas in a different Azure region. If a failure occurs, you must trigger the failover manually. It is supported only by Azure SQL Database.
adds an automatic failover layer on top of Active Geo-Replication. When the primary region goes down, Azure automatically switches traffic to the secondary. It provides a single read/write listener endpoint, so the application connection string does not change. Both Azure SQL Database and Managed Instance support it.
When RPO and RTO requirements are strict and automation is needed, choose Auto-Failover Group. When minimizing cost with manual control suffices, choose Active Geo-Replication.
Performance Tuning: DTU vs vCore
When buying a car, do you look only at horsepower, or do you also consider fuel efficiency and torque? Choosing the right Azure SQL service tier requires the same multi-dimensional thinking.
The model bundles CPU, memory, and I/O into a single number. Setup is simple and suits predictable workloads, but you cannot tune individual resources independently. Common for small applications or early migration phases.
The model lets you specify CPU cores, memory, and storage independently. Azure Hybrid Benefit allows applying existing SQL Server licenses to reduce costs significantly. Enterprise environments typically prefer this model.
is a set of query optimization features introduced in SQL Server 2019, including Adaptive Joins, Memory Grant Feedback, and Deferred Variable Deferral. No code changes required — just raise the database compatibility level to 150 or higher.
!DTU versus vCore
All Three Models at a Glance
| Criteria | Azure SQL Database | Azure SQL Managed Instance | SQL Server on Azure VM | |:--|:--|:--|:--| | Service Type | PaaS | PaaS | IaaS | | OS Control | Not available | Not available | Full control | | SQL Server Agent | Not available | Available | Available | | Cross-DB Queries | Not available | Available | Available | | SSRS / SSIS | Not available | Limited | Full support | | Backup Management | Automatic (Microsoft) | Automatic (Microsoft) | Self-managed |
If you need legacy SQL Server features, choose Managed Instance. For full OS control, choose SQL Server on VM. For minimal management overhead on a new application, choose Azure SQL Database.
Study Roadmap: Domain-by-Domain Preparation
A librarian organizes books by genre first, then by author and publication year. Studying for DP-300 works best the same way — start big picture, drill into specific features.
Deployment models: Define the boundary between Azure SQL Database, Managed Instance, and SQL Server on Azure VM. → Azure SQL Database, Managed Instance, and SQL on VM, Azure SQL Service Tiers and Compute Models, Migrating from SQL Server to Azure SQL Authentication and authorization: Understand Microsoft Entra ID integration, server logins vs database users, and RBAC. → Microsoft Entra ID and SQL Authentication Data protection: Know when TDE applies versus Always Encrypted, and what Dynamic Data Masking can and cannot hide. → TDE and Always Encrypted, SQL Audit, Dynamic Data Masking, and Row-Level Security Monitoring and tuning: Practice with Query Store, Intelligent Query Processing, and key DMVs. → Query Store, Azure Monitor, and SQL Insights, Automatic Tuning, Index Maintenance, and MAXDOP Automation: Distinguish SQL Server Agent (Managed Instance only) from Elastic Jobs (Azure SQL Database). → SQL Server Agent and Elastic Jobs HA/DR: Compare PITR, LTR, Active Geo-Replication, and Auto-Failover Group by retention period and automation level. → Failover Group, Geo-Replication, and PITR/LTR
Common Traps: PITR vs LTR
Taking a daily photo of your warehouse inventory and keeping a year's worth of photos serve different purposes. Backup strategy in DP-300 works the same way.
is the built-in automatic backup feature of Azure SQL Database and Managed Instance. Default retention is 7 days, configurable up to 35. Use it to restore from accidental data deletion at a specific moment in time.
stores backups in Azure Blob Storage for up to 10 years. Designed for legal and compliance requirements that exceed the 35-day PITR limit. Any scenario mentioning 'retention beyond 35 days' or 'compliance archiving' points to LTR.
SQL Server Agent job automation is available only on Managed Instance. For Azure SQL Database, use to schedule automation tasks.
Exam Key Takeaways
'Legacy migration requiring T-SQL cross-database queries' -- Azure SQL Managed Instance 'Full control over OS and SQL Server installation' -- SQL Server on Azure VM 'Run a single database with minimum management overhead' -- Azure SQL Database 'Column-level encryption that DBAs cannot decrypt' -- Always Encrypted 'Transparent at-rest encryption, enabled by default' -- Transparent Data Encryption (TDE) 'Automatic failover with a single connection endpoint' -- Auto-Failover Group 'Read-only cross-region replica, manual failover' -- Active Geo-Replication 'Backup retention beyond 35 days, compliance requirement' -- Long-Term Retention (LTR) 'Restore to a specific point after accidental deletion, up to 35 days' -- Point-in-Time Restore (PITR) 'Use existing SQL Server licenses, tune resources independently' -- vCore model 'Schedule automated tasks within Azure SQL Database' -- Elastic Jobs
TDE = transparent disk-level encryption | Always Encrypted = client-held key column encryption | Auto-Failover Group = automatic switch + single endpoint
Ready to test where you stand? Try the DP-300 practice exam now.