DP-300 scenario questions — in the actual exam and in interviews that borrow its style — aren’t testing whether you can define a feature. They’re testing whether you can read a situation (a business constraint, a symptom, a set of competing requirements) and pick the right Azure SQL capability to address it. That’s a genuinely different skill than reciting what Always Encrypted does, and it’s the skill this list is built around.
Split into Beginner (1–30), Intermediate (31–70), and Advanced (71–100), every question is framed as a real situation, the way DP-300 itself and most serious interviews actually ask them — and every answer explains the reasoning, not just the final choice, since in both the exam and a real interview, the “why” is what’s actually being evaluated.
One framing worth carrying through the whole list: almost every scenario question has a “tempting but wrong” answer sitting right next to the correct one — usually the more expensive, more complex, or more familiar-from-on-prem option. Good scenario-answering means noticing that trap and explaining specifically why the simpler or cheaper option actually satisfies the stated requirement.
Beginner Level
1. A team wants a single Azure SQL database for a new internal app with unpredictable, low, and intermittent usage. What service tier would you recommend, and why? Serverless compute tier — it auto-scales based on actual load and can auto-pause during genuine inactivity, billing per-second rather than for a fixed reserved capacity that would sit mostly idle for this kind of intermittent workload.
2. A company needs to migrate an on-prem SQL Server database that relies heavily on SQL Server Agent jobs and linked servers. Which Azure SQL target should you recommend? Azure SQL Managed Instance — its near-full SQL Server compatibility, including native SQL Agent and linked server support, avoids the significant re-engineering that Azure SQL Database would require for these specific dependencies.
3. A database needs to be encrypted at rest with no application changes required. What Azure SQL feature satisfies this? Transparent Data Encryption (TDE) — it’s enabled by default on every new Azure SQL database, encrypting data at the page level without requiring any application-side changes at all.
4. An application team reports they cannot connect to a newly created Azure SQL Database at all. What’s the first thing to check? Firewall rules — by default, no IP address is allowed to connect to a new Azure SQL server, so a missing firewall rule for the application’s IP is the most common first-time connectivity blocker.
5. A business wants their database to automatically recover to a specific point in time if a bad data change occurs. What feature covers this? Point-in-Time Restore (PITR) — Azure SQL Database’s automatic backup chain (full, differential, log) supports restoring to any specific moment within the configured retention window, without any manual backup job configuration needed.
6. A company wants to reuse their existing on-prem SQL Server licenses to reduce Azure SQL compute costs. What should you recommend? Azure Hybrid Benefit, available on the vCore purchasing model — it lets existing licenses (with active Software Assurance) reduce the compute cost of a vCore-based Azure SQL database or Managed Instance.
7. A reporting application needs read-only access to a database without impacting the primary’s write performance. What feature addresses this on a Business Critical tier database? Read scale-out — it routes read-only traffic (via ApplicationIntent=ReadOnly) to an already-existing HA secondary replica, offloading reporting load from the primary at no additional cost.
8. A database administrator needs to see which queries are consuming the most resources over the last 24 hours, without writing any KQL or T-SQL. What tool should they use? Query Performance Insight — a portal-based visual view built on Query Store data, showing top resource-consuming queries over a chosen time window with no query-writing required.
9. A company needs their Azure SQL database’s authentication centrally managed with MFA support. What authentication method should you recommend? Microsoft Entra ID (Azure AD) authentication — it ties database access to the organization’s central identity provider, supporting MFA and centralized offboarding, unlike SQL Authentication’s standalone username/password model.
10. A team wants to prevent accidental data loss if someone runs an UPDATE without a WHERE clause. What’s the relevant built-in protection, and does it need to be configured? Point-in-Time Restore already covers this by default, since automatic backups are always running — no separate configuration is needed beyond making sure the retention window comfortably covers how quickly such a mistake would realistically be noticed.
11. A database needs to support 50 small, low-traffic tenant databases cost-effectively. What Azure SQL construct fits this best? An elastic pool — sharing a pooled resource allocation across many databases with unpredictable, non-overlapping usage is generally more cost-effective than provisioning each tenant database separately at its own peak capacity.
12. A company’s compliance team requires audit logs of every login and permission change, retained for 3 years. What Azure SQL feature, and what configuration detail, satisfies this? Azure SQL auditing, routed to a Log Analytics workspace or Storage Account with retention configured to meet the 3-year requirement — since the platform’s short default retention wouldn’t satisfy a multi-year compliance need on its own.
13. An application experiences connection failures, and the blocked_by_firewall metric is elevated. What’s the most likely cause? The connecting IP address isn’t included in an allowed firewall rule — this metric specifically tracks connections rejected at the firewall level, distinguishing it from other possible connection failure causes like authentication or resource limits.
14. A team wants to know if a specific database resource is currently healthy without checking broader Azure service status. What should they check? Resource Health — it reports the specific database resource’s own Available/Degraded/Unavailable status with a health history, distinct from Service Health, which covers broader regional or platform-wide incidents.
15. A junior DBA asks why Automatic Tuning’s CREATE INDEX recommendation isn’t being applied automatically. What’s the most likely explanation? That specific tuning option is likely set to “recommend only” mode rather than automatic apply — a deliberate configuration choice giving a human the final approval before the index is actually created, rather than a malfunction.
16. A database needs geo-redundant backup storage for disaster recovery purposes. What setting controls this? Backup storage redundancy — configuring it to GRS (geo-redundant storage) or RA-GRS specifically enables Geo-Restore into a different region, versus LRS or ZRS which don’t provide that cross-region backup protection.
17. A company wants a stable connection string that doesn’t need to change if a failover occurs. What Azure SQL feature provides this? A failover group’s listener endpoint — it always points to whichever database is currently primary, so the application’s connection string doesn’t need modification during a failover event.
18. An application connects fine via SSMS but fails intermittently from the application server. What’s a reasonable first hypothesis? A difference in connection-level settings, retry logic, or connection pooling behavior between the application and a manual SSMS session — worth comparing what the application is actually sending (via captured traffic or Extended Events) rather than assuming the database itself is the issue just because a manual test succeeds.
19. A database’s storage is nearing 90% of its configured maximum size. What’s the immediate next step before deciding on a fix? Check the storage_percent trend and identify what’s actually driving the growth (recent bulk load, index rebuild temporary space, unmanaged log growth) before deciding between increasing max size or addressing the underlying growth driver.
20. A team needs to migrate a database with some deprecated T-SQL syntax to Azure SQL Database. What tool should be run first, before any migration begins? The Data Migration Assistant (DMA) — it identifies compatibility issues and deprecated features early, during the assessment phase, so blockers are known and can be planned for well before the actual migration.
21. A company needs a database that can scale to very large size (50+ TB) with fast backup/restore regardless of size. What service tier fits? Hyperscale — its distributed storage architecture makes backup and restore operations near-constant-time regardless of database size, unlike General Purpose or Business Critical, where restore time scales with data volume.
22. A developer asks why their query, which used to run fast, suddenly runs slow, with no code changes made. What’s the first tool to check? Query Store — it can show whether the query’s execution plan changed recently and whether that change correlates with when the slowdown began, turning a vague “it got slower” into a specific, provable, timestamped fact.
23. A company needs to restrict which rows a multi-tenant application’s users can see, enforced at the database level rather than trusted to application code. What feature addresses this? Row-Level Security (RLS) — it applies a security predicate at the engine level, so even a bug in an application’s WHERE clause can’t leak cross-tenant data, since the database itself filters the rows.
24. A team wants to reduce accidental exposure of sensitive columns (like SSNs) in query results for support staff, without changing the stored data. What feature fits? Dynamic Data Masking — it obscures sensitive data in query results for non-privileged users without altering the actual stored data, though it’s a presentation-layer control, not a substitute for genuine access control or encryption.
25. A database administrator needs to check what’s currently executing on a database right now, live. What DMV should they query? sys.dm_exec_requests — it shows currently executing requests in real time, including status and wait information, the standard first stop for “what’s happening right now.”
26. A company’s database needs Always On-style high availability with fast, sub-30-second local failover. What service tier should you recommend? Business Critical — its architecture with multiple synchronized local replicas enables fast failover, versus General Purpose’s remote-storage-reattachment model, which is generally slower.
27. An application needs to know if a database resource is impacted by a broader Azure outage versus its own specific issue. What should they check alongside Resource Health? Service Health — it reports broader regional or service-wide incidents, complementing Resource Health’s resource-specific status, since a database can show as Available on Resource Health while still being affected by a broader connectivity issue Service Health might explain.
28. A team wants Azure to automatically suggest cost savings across their Azure SQL fleet. What tool should they check regularly? Azure Advisor — its cost recommendations category flags databases with sustained low utilization and suggests downsizing or reserved capacity, based on actual observed usage patterns.
29. A company needs a quick way to see whether a specific database currently has TDE enabled. What would you check? The portal’s Transparent Data Encryption blade for that database, or query sys.dm_database_encryption_keys directly — worth noting it’s on by default for new databases, so this check is usually confirming status rather than needing to enable it from scratch.
30. A stakeholder asks what the difference is between RTO and RPO in plain terms, for a project kickoff document. How would you explain it simply? RTO is how long you can be down before it seriously hurts the business; RPO is how much data you can afford to lose. Both numbers together define what kind of DR architecture is actually appropriate — a five-minute RTO and a four-hour RTO lead to very different, very differently priced designs.
Intermediate Level
31. A database is consistently hitting sessions_percent near 100% while CPU stays around 30%. What’s the likely root cause, and what would you check? This points to a connection management problem, not a resource bottleneck — I’d check for a connection leak in the application (connections opened but never properly closed or pooled) rather than assuming a scale-up would help, since scaling up doesn’t raise session limits proportionally to CPU and wouldn’t fix a leak.
32. A company wants near-zero-downtime migration of a 2TB production database from on-prem SQL Server to Azure SQL Managed Instance. What approach and tool should you recommend? Azure Database Migration Service (DMS) in online mode — it performs an initial full copy while the source stays live, then continuously syncs ongoing changes, letting the actual cutover downtime shrink to just the final connection switch rather than the full data movement window.
33. A query’s estimated execution plan shows a seek, but the query still performs poorly. What should you investigate next? Compare actual versus estimated row counts at each operator in the actual execution plan — a seek being chosen doesn’t guarantee low cost if the seek predicate returns a large number of rows, or if it’s executed many times as the inner side of a loop join.
34. A regulated financial services company wants Automatic Tuning enabled but needs every schema change to go through a formal review process. How would you configure it? Set CREATE INDEX and DROP INDEX to “recommend only” mode rather than full automatic apply, routing recommendations into the existing change-approval workflow — preserving the analytical value of Automatic Tuning while keeping schema changes under proper governance.
35. A database using Always Encrypted needs to support range queries (like BETWEEN) on an encrypted column. What configuration is required? Always Encrypted with secure enclaves — standard Always Encrypted can’t support range comparisons since the engine never sees plaintext at all, while enclave-based computation allows richer operations by decrypting inside a hardware-protected, attested enclave on the server.
36. A company’s DR plan requires failing over to a secondary region within 30 seconds with near-zero data loss. What Azure SQL configuration best satisfies this? A failover group with automatic failover policy, on Business Critical tier for fast underlying HA architecture — though it’s worth validating actual geo-replication lag under real production load, since near-zero RPO for a forced cross-region failover specifically depends on how caught-up the secondary genuinely is at the moment of failure.
37. A team notices LOG_RATE_GOVERNOR waits spiking during a nightly batch job. What does this wait type indicate, and what are the possible fixes? It indicates the workload is being throttled against the service tier’s transaction log write throughput cap — an Azure-specific constraint. Fixes include batching the load into smaller transactions to reduce log generation rate, or scaling to a tier with a higher log throughput limit if the sustained need is genuinely expected to persist.
38. A company wants to migrate SSIS packages as part of a database migration, with minimal rework. What Azure service should you recommend? Azure-SSIS Integration Runtime within Azure Data Factory — it’s specifically designed to lift-and-shift existing SSIS packages with minimal changes, rather than requiring a full re-platform onto native Data Factory pipelines for packages that are otherwise sound.
39. A database shows healthy CPU and DTU metrics, but users still report slowness. What are two categories of issue this doesn’t rule out? Session/worker limit exhaustion (sessions_percent/workers_percent, a separate hard cap independent of CPU) and blocking (a blocked session waits rather than consumes CPU, so it doesn’t show up as elevated resource utilization) — both explain user-perceived slowness without appearing in overall resource metrics.
40. A company needs to know whether their reserved capacity purchase for a specific database is still cost-effective a year later. What would you check? Actual sustained utilization over a recent representative period against the originally committed vCore amount — if the workload has genuinely shrunk or grown since the reservation was purchased, the original commitment may no longer be optimally sized, worth revisiting rather than assuming a reservation is a permanent, no-longer-reviewable decision.
41. A stored procedure runs fast for most customers but extremely slowly for a specific large customer. What’s the likely cause, and how would you confirm it? Parameter sniffing — the cached plan was likely compiled for a smaller, more typical customer and is poorly suited to the large customer’s very different row volume; confirming it involves comparing the cached plan against a plan generated with OPTION (RECOMPILE) for the large customer’s parameter specifically.
42. A company wants to reduce Log Analytics ingestion costs without losing the ability to diagnose production incidents. What are two concrete levers? Selectively enabling only the diagnostic categories genuinely needed at full fidelity (rather than everything by default), and using resource-specific tables instead of legacy AzureDiagnostics mode, which is both cheaper to store and cheaper to query.
43. A newly migrated database performs noticeably worse than it did on-prem, even though the service tier seems appropriately sized. What’s a reasonable first diagnostic step? Check Query Store’s wait statistics for the migrated database — Azure-specific waits like LOG_RATE_GOVERNOR don’t exist on-prem and point to a genuinely different kind of constraint that the on-prem tuning playbook wouldn’t have addressed, since it never had to.
44. A company wants their elastic pool sized correctly, not just “big enough to be safe.” What data should inform that sizing decision? Aggregate peak resource consumption across all member databases over a representative period, ideally captured via Azure Migrate or historical sys.dm_db_resource_stats data if migrating existing databases, rather than provisioning a pool generously without basing size on actual measured demand.
45. A team needs to grant a new application’s service account exactly the access it needs, nothing more. What’s the recommended approach over granting db_owner? Create a custom database role scoped to the specific tables/stored procedures the application actually touches, and grant only the necessary SELECT/INSERT/UPDATE/EXECUTE permissions on those specific objects — db_owner is broader than almost any application service account genuinely needs.
46. A database administrator wants to validate that Automatic Tuning’s FORCE LAST GOOD PLAN action actually improved performance, not just changed the plan. What should they check? Compare the forced plan’s actual runtime statistics (via Query Store) against both the regressed plan’s performance and, ideally, the query’s performance from before the regression began — confirming genuine, measured improvement rather than assuming a different plan automatically means a better one.
47. A company’s backup retention is set to the maximum 35 days, but a compliance audit requires 7 years of retained backups. What’s missing from their configuration? Long-Term Retention (LTR) — standard retention tops out at 35 days regardless of setting, and LTR is a separate, explicitly configured feature specifically designed for multi-year compliance-driven backup retention.
48. A team wants to validate a proposed index change before applying it to a large production database, without the cost of a full separate environment. What approach fits? Database Copy to a lower-tier, cost-controlled test environment, replaying captured real production query patterns from Query Store against it and comparing before/after plans — a middle ground between skipping validation entirely and standing up a full duplicate production-scale environment.
49. A database’s Query Store appears to be missing recent data, even though queries were definitely executing. What’s a likely, easily-overlooked explanation? Query Store may have hit its configured maximum size and switched to read-only mode, meaning it’s no longer capturing new data — checking sys.database_query_store_options for the actual operational state is the direct way to confirm this before assuming a genuine data loss bug.
50. A company wants to migrate a database with heavy cross-database transaction usage to Azure SQL Database, which doesn’t support this the same way. What are the options? Reconsider whether Managed Instance (which does support cross-database transactions) is the more appropriate target given this dependency, or, if Azure SQL Database is a hard requirement, evaluate whether the coupling is genuinely necessary or incidental — sometimes the real fix is consolidating what should have been one database, or accepting eventual consistency for the specific cases that can tolerate it.
51. A team notices a query’s actual row count is dramatically higher than its estimated row count at a specific operator. What does this typically indicate, and what’s the likely fix? A cardinality estimation problem, often caused by stale statistics or a non-sargable predicate — checking statistics freshness first is a low-effort step before assuming something more complex like parameter sniffing or a fundamental query rewrite is needed.
52. A company’s application needs Managed Identity-based authentication to Azure SQL Database instead of a stored password. What’s the benefit, and what’s required to set it up? Managed Identity removes an entire class of credential-leak risk since no password or connection secret is ever stored in code or config — setup requires enabling the Azure resource’s managed identity, creating a corresponding Azure AD-based database user for that identity, and granting appropriate permissions to it.
53. A database shows a rising connection_failed trend, but firewall rules and credentials both check out fine. What else should be investigated? Session or worker limit exhaustion (sessions_percent/workers_percent), which produces connection failures for a completely different reason than firewall or authentication issues — worth checking directly rather than continuing to investigate the ruled-out causes further.
54. A team wants to validate their documented RTO for a failover group is actually achievable, not just a theoretical number from Microsoft’s general documentation. What should they do? Perform an actual planned failover test and measure the real elapsed time for their specific database and data volume — a documented RTO that’s never been tested against real data is a hope, not a validated number.
55. A company needs their Azure SQL Database’s collation to match their on-prem source exactly during migration. Why does this matter, and what should be done? Collation differences can silently change string comparison, sorting, and case-sensitivity behavior, potentially breaking application logic that worked correctly under the source’s collation — the target database’s collation should be explicitly set to match the source at creation time, not left at default.
56. A database administrator is asked to explain why a scale-up operation didn’t seem to improve performance. What are two possible explanations worth checking? First, confirm the scale operation actually completed and the new resource ceiling is genuinely active (via dtu_limit/cpu_limit metrics), since a still-pending or failed scale wouldn’t show any improvement. Second, consider whether the actual bottleneck was never the resource dimension that was scaled — a log I/O or session-limit problem wouldn’t necessarily improve from a compute scale-up.
57. A company wants to know if their elastic pool is suffering from a noisy-neighbor problem. What data would confirm this? Compare each member database’s individual resource consumption against the pool’s total capacity and each database’s configured min/max bounds — a noisy-neighbor pattern shows one or a few databases consistently consuming a disproportionate share, distinguishable from genuine pool-wide capacity exhaustion where most databases show elevated pressure simultaneously.
58. A team needs their monitoring setup to alert specifically on Automatic Tuning actions themselves, not just general performance metrics. Why would this be valuable, and how would they implement it? Visibility into schema and plan changes as they happen — via scheduled queries against sys.dm_db_tuning_recommendations or Activity Log — is valuable because unattended automated changes to database structure deserve the same visibility a manual change would get, especially for catching a pattern like repeated reverts of the same recommendation, which suggests something worth manual investigation.
59. A company’s application intermittently times out, and sys.dm_exec_requests shows the query’s wait type as LCK_M_X at the time. What does this tell you, and what’s the next diagnostic step? It indicates the session was blocked waiting for a lock, not genuinely slow on its own — the next step is tracing the blocking chain via blocking_session_id to find the actual head blocker, rather than assuming the timed-out query itself needs tuning.
60. A team is deciding between 1-year and 3-year reserved capacity for a database whose workload has been stable for 6 months but is expected to grow. What would you recommend? A 1-year term — the workload’s stability is only recently established and expected future growth introduces real uncertainty about whether the current sizing will still be appropriate over a 3-year horizon; a shorter commitment better matches that uncertainty, even at a somewhat smaller discount.
61. A company migrating to Azure SQL Database discovers their application relies on a linked server for cross-database reporting. What are the options? Evaluate whether Elastic Query can serve the same cross-database query pattern for Azure SQL Database, or reconsider Managed Instance as the target if linked server support is genuinely required with minimal re-engineering — treating this as a real architectural decision rather than assuming a direct feature-for-feature replacement always exists.
62. A database’s tempdb_log_size metric shows sustained pressure, but the team can’t add tempdb files the way they would on-prem. What’s the appropriate response? Since file configuration isn’t directly controllable in Azure SQL Database, the diagnostic goal shifts to identifying which specific query is driving excessive tempdb usage (via Query Store, looking for sort/hash spills or heavy temp table use) and reducing that workload’s footprint, rather than trying to add capacity to tempdb itself.
63. A team wants to confirm whether Dynamic Data Masking is providing genuine security, or just cosmetic protection, for a specific use case. How would you frame that assessment? DDM only affects what’s shown in query results to users without the UNMASK permission — it doesn’t stop someone with sufficient privileges or a clever workaround query from inferring or extracting real data, so it should be framed as reducing casual/accidental exposure, not as a substitute for genuine access control or encryption for a use case involving a real security boundary.
64. A company’s application team insists a database change should be rolled back, but the DBA team believes the issue is actually unrelated resource contention from a concurrent batch job. How would you investigate to settle this? Check sys.dm_db_resource_stats for the incident window to confirm or rule out genuine resource pressure, and cross-reference Query Store’s plan history for the affected query to see if a plan actually changed around the time of the reported change — objective, timestamped data settles the disagreement more reliably than either team’s initial assumption.
65. A team is deciding whether a specific database genuinely needs Business Critical tier or if General Purpose would suffice. What questions would you ask to make that decision? What’s the actual required failover time (RTO), and does the workload have a genuine need for the built-in read-scale-out secondary — if General Purpose’s HA model already satisfies the real RTO requirement and there’s no read-scale-out need, Business Critical’s extra cost may not be buying anything the business actually requires.
66. A company wants to confirm their Always Encrypted implementation will survive a regional failover. What specifically needs to be validated? That the column master key (typically in Azure Key Vault) is genuinely accessible from the secondary region — not just assumed to be, since a technically successful database failover that the application can’t decrypt data against afterward isn’t a real recovery.
67. A database shows a burst of Automatic Tuning recommendations appearing all at once, unexpectedly. What’s a reasonable first hypothesis before treating it as a malfunction? A recent significant change in workload — a bulk data load, a major schema change, or a new application feature going live — is a plausible and common trigger for a legitimate burst of new Query Store-driven recommendations reacting to a genuine underlying change, worth confirming before assuming something’s wrong with the tuning system itself.
68. A team needs to decide whether a specific compliance requirement allows using Azure’s standard paired-region geo-replication for DR. What should they check first? Confirm explicitly with the compliance team which specific regions satisfy the data residency requirement — some mandates prohibit replication outside a specific boundary entirely, which could rule out the standard paired-region default and require a different, still-compliant regional DR approach.
69. A company’s cost review finds GRS backup storage applied uniformly across their entire fleet, including many low-criticality dev databases. What would you recommend? Tier backup storage redundancy by actual database criticality — GRS roughly doubles backup storage cost, and applying it broadly to databases that don’t genuinely need cross-region DR protection is a common, easily-corrected source of unnecessary cost.
70. A team is troubleshooting a database that suddenly shows as suspect. What’s the appropriate first action? Check the error log (via diagnostic logs routed to Log Analytics, filtered to Errors) immediately for the specific cause, rather than attempting any corrective action first — the appropriate fix depends entirely on the actual reported cause, which can range from transient and self-resolving to something requiring an actual restore.
Advanced Level
71. Design a migration and DR strategy for a 5TB, 24/7 financial trading database requiring under 2 minutes of downtime and near-zero data loss for the migration cutover itself. Use DMS in online mode with the full data copy and continuous sync happening well ahead of the actual cutover date, monitoring replication lag under real production load until it’s consistently near zero — the 2-minute budget covers only stopping writes on the source, confirming final sync, and switching the connection endpoint, since trying to fit multi-terabyte data movement into a 2-minute window would never work; only the final switch needs to be that tightly time-boxed.
72. A company’s Business Critical database shows healthy synchronous replication, but a forced cross-region failover during a real regional outage lost several seconds of transactions. Explain why this happened despite Business Critical’s strong HA guarantees. Business Critical’s synchronous replication applies specifically to its local, in-region HA replicas — the cross-region geo-replication link (used for the failover group’s DR capability) is asynchronous by design, since synchronous replication across genuinely distant regions would impose unacceptable latency on every primary write. A forced cross-region failover specifically doesn’t wait for that asynchronous link to catch up, which is exactly why some data loss is possible there even though local Business Critical failover itself is near-zero-loss.
73. A large enterprise wants a single governance framework enforcing consistent diagnostic settings, tagging, and Automatic Tuning policy across 500+ databases provisioned by different teams. Outline your approach. Azure Policy to enforce diagnostic settings and tagging at creation time regardless of which team provisions a database, ARM/Bicep/Terraform templates as the standard provisioning path so teams start from a consistent baseline, and a tiered Automatic Tuning default (full automatic for low-risk databases, recommend-only for regulated/critical ones) applied at the server level with documented override criteria — paired with a recurring governance review to catch drift, since a one-time policy setup alone doesn’t guarantee ongoing consistency as the fleet grows.
74. Explain a scenario where Automatic Tuning’s FORCE LAST GOOD PLAN would mask, rather than solve, a genuine underlying problem. If a query’s performance is gradually degrading due to real data growth — where no plan, old or new, actually scales well at the new size — FORCE LAST GOOD PLAN might keep reverting to whatever the “least bad” historical plan was, creating a false sense the situation is handled when the real need is a schema or indexing change. Repeated forcing on the same query over time is the signal this is happening, worth escalating to manual investigation rather than trusting the automated mitigation indefinitely.
75. A company needs Always Encrypted with secure enclaves but is concerned about the security guarantee if attestation is misconfigured. Explain the residual risk and how you’d address it. Enclave computation’s entire security guarantee depends on the attestation service correctly verifying the enclave’s integrity before decryption occurs inside it — a misconfigured or bypassed attestation step undermines that guarantee even though the encryption mechanism itself is sound. I’d treat attestation configuration as its own security-critical review item, not assume “enclaves are enabled” alone is sufficient evidence the protection is actually working as intended.
76. A globally distributed application needs low-latency reads in three regions plus DR, using Azure SQL Database’s geo-replication (max 4 secondaries). Design the topology. Three readable secondaries positioned in the regions with genuine local read demand, each serving ApplicationIntent=ReadOnly traffic locally, with one of them (or a fourth, dedicated secondary) explicitly designated as the failover group’s DR target — being deliberate that read-latency secondaries and the DR-designated secondary serve overlapping but distinct purposes, rather than assuming any secondary automatically covers both needs equally well.
77. Explain how Adaptive Join’s runtime plan-branch decision can still be “wrong” for a specific execution, and what that reveals about the underlying query. Adaptive Join defers the Nested Loops versus Hash Match choice until runtime based on actual row counts crossing a threshold — if it still picks the less-efficient branch for a specific execution, the actual row count for that case was genuinely on the “wrong” side of the threshold, revealing that the query’s row-count variability is wide enough that even a runtime-adaptive decision doesn’t have one universally correct answer, not that the adaptive mechanism itself failed.
78. A regulated company needs customer-managed TDE keys with a documented key rotation process that never risks an outage. Design the approach. Configure auto-rotation to the latest Key Vault key version, ensure the new key version exists and is available before the old one’s expiration with proactive alerting on expiration dates, and maintain at least one prior key version accessible for a defined grace period specifically to avoid a scenario where a delayed rotation or Key Vault access issue makes the database briefly inaccessible.
79. Explain the trade-off between Hyperscale and General Purpose for a database expected to grow from 500GB to 20TB over two years, considering both current and future cost. General Purpose’s storage and especially backup/restore time scale unfavorably as data grows large, becoming both a cost and an operational RTO risk well before 20TB. Hyperscale’s architecture keeps backup/restore near-constant-time regardless of size — the total cost of ownership modeled over the full two-year growth trajectory, not just today’s smaller size, is what should actually drive this decision, since choosing based on current size alone risks a costly re-platforming migration later.
80. A company’s Query Store shows a query with many distinct plans, none clearly superior. What does this suggest, and what would you avoid doing reflexively? This often indicates a genuinely ambiguous optimization scenario — either the query’s correct plan legitimately varies by parameter value, or a more fundamental issue (missing statistics, a schema design problem) is causing the optimizer to struggle regardless of plan choice. I’d avoid reflexively forcing any single plan and instead investigate why the plans vary so much before deciding forcing is even the right tool.
81. Design an incident response process for a suspected ransomware event on an Azure SQL Database, using its backup and access control capabilities. Contain first — revoke or suspend compromised credentials and restrict network access before any restoration, since restoring too early risks restoring into a still-compromised environment or destroying evidence. Identify the last known-good point using audit logs and Query Store data, restore into an isolated new database (never directly over the live one) for validation, and only promote to production once confirmed clean — treating the restored target as untrusted until proven otherwise.
82. A company wants to know whether their DR secondary, which has run as a read-only replica for two years without ever becoming primary, is genuinely trustworthy for a real failover. What’s the risk, and how would you address it? A secondary that’s never actually served as primary (only briefly tested via planned failover and immediate failback, if tested at all) may have an undiscovered gap — a configuration difference, an access rule that exists on the primary but was never validated as applying equivalently to the secondary’s primary role. I’d include a genuine, sustained period running as primary during periodic DR testing, not just a quick test-and-revert, specifically to surface any such gap during a test rather than a real incident.
83. Explain how you’d design fleet-wide backup validation for 500+ databases without the impractical cost of testing every single restore individually. A rotating, statistically sampled validation program — automatically restoring a meaningful, risk-weighted subset (more frequent for the most critical databases) into an isolated test environment on a recurring schedule, running automated integrity checks against each restored copy, and centrally logging results so a failed validation triggers investigation — giving genuine ongoing confidence without exhaustive per-database testing every cycle.
84. A team’s Automatic Tuning applied an index that helped one query but is measurably hurting write throughput on a high-volume table overall. Explain the trade-off and your resolution approach. The automated system evaluates each recommendation’s estimated benefit against its own estimated cost, but doesn’t necessarily weigh cross-cutting impact across a table’s full workload the way a DBA reviewing the whole picture would — I’d manually review and likely replace it with a narrower, more targeted covering index, or remove it if the aggregate cost genuinely outweighs the benefit, treating this as exactly the kind of judgment call recommend-only mode or active periodic review is meant to catch.
85. Explain why “the database is healthy according to Resource Health” doesn’t fully rule out a genuine application-facing outage, with a specific example. Resource Health reflects the platform-level status of the database resource itself, not the full path an application needs to reach it — an expired Azure AD token, a DNS resolution issue with a Private Endpoint, or a network path problem between the app and Azure would all leave the database showing Available while the application genuinely can’t function, meaning troubleshooting needs to work outward from the database rather than stopping once Resource Health looks fine.
86. A company needs to evaluate whether Azure Hybrid Benefit is being fully utilized across their vCore fleet. Design an audit approach. Compare actual license entitlement (SQL Server licenses with active Software Assurance the organization already owns) against what’s currently applied across the fleet’s vCore databases — this is often one of the highest-value, lowest-risk cost reviews specifically because it’s not asking anyone to accept less capacity, just correctly applying an entitlement already owned and likely already paid for.
87. Explain the security implication of granting a monitoring service account VIEW SERVER STATE, and how you’d mitigate it. That permission can expose query text and session details across the entire server, not just the databases the monitoring tool is meant to watch — potentially leaking sensitive data indirectly (literal parameter values in captured plan XML) to an account that only needed aggregate performance metrics. I’d scope monitoring permissions as narrowly as the tool actually requires and treat the monitoring pipeline itself as part of the security surface, not an exception to normal least-privilege review.
88. A company’s DR runbook has never specified who has authority to declare a forced failover during an ambiguous, partial regional outage. Explain the risk and how you’d fix it. Without pre-defined criteria and clear ownership, that decision gets made under real incident pressure by whoever happens to be present, which is exactly the wrong time to be establishing judgment criteria for the first time — I’d define explicit signals that justify a forced failover (specific Resource Health/Service Health patterns, a defined maximum wait time before defaulting to failover) and explicit decision authority, established calmly in advance, not improvised live.
89. Explain how you’d design a cost-optimized Log Analytics setup for a 24/7 high-throughput OLTP fleet without losing genuine incident diagnosability. Selectively enable full-fidelity diagnostic categories only where genuinely needed (Errors, Deadlocks, Blocks, targeted QueryStoreRuntimeStatistics), use resource-specific tables rather than legacy mode, apply Basic Logs tier to high-volume rarely-queried categories, set table-level retention deliberately rather than a blanket policy, and consider a commitment tier once daily ingestion crosses roughly 100GB — reviewed quarterly since ingestion patterns and actual query needs both drift over time.
90. A team wants to validate that a proposed index consolidation (merging several overlapping indexes into one wider covering index) won’t regress any currently-fine query. Design the validation approach. Capture the current plans for every query Query Store shows relying on each index being consolidated, apply the proposed change in a test environment against representative data, and directly compare each affected query’s before/after plan and runtime statistics — specifically checking none regress to a less efficient plan due to losing a narrower index’s more precise selectivity, before rolling the consolidation out broadly.
91. Explain the relationship between Query Store’s AUTO capture mode and a scenario where a genuinely important but infrequent, individually-cheap query never appears in Query Store data. AUTO mode filters based on execution count and resource consumption thresholds to reduce overhead, meaning a genuinely infrequent, individually cheap query can fall below that threshold and simply not get tracked — expected behavior, not a bug. If that specific query matters for an investigation, CUSTOM capture mode with adjusted thresholds, or temporarily switching to ALL mode, would be the appropriate, deliberate fix rather than assuming Query Store itself is malfunctioning.
92. A company’s multi-tenant SaaS application uses Row-Level Security for tenant isolation, but also uses Always Encrypted on the tenant identifier column. Explain the conflict and the fix. An RLS predicate function cannot directly compare against an Always Encrypted column’s plaintext value inside its predicate logic, since the engine never has plaintext for that column to evaluate against — the tenant-isolation logic needs a separate, non-encrypted identifier column for the RLS predicate to filter on, rather than trying to filter directly on the encrypted tenant identifier.
93. Explain how you’d design a chaos-engineering-style test for a failover group beyond a standard clean planned failover test. Test a genuinely forced failover to validate actual data-loss behavior under an unclean switch, test application behavior when the listener endpoint’s DNS change propagates slowly (not every client resolves equally fast), and test failback under real load rather than only during a quiet maintenance window — since a real incident is unlikely to conveniently occur during low traffic, and these adversarial conditions surface gaps a clean, well-behaved planned test wouldn’t.
94. A company’s cost optimization proposal would reduce a critical database’s resilience (dropping from Business Critical to General Purpose) to save a meaningful amount monthly. How would you handle presenting this trade-off? Quantify both sides explicitly — the real monthly savings and the specific, concrete risk being accepted (a slower failover time, reduced read-scale-out capacity) — and get that trade-off consciously approved by whoever owns the business risk, rather than a DBA unilaterally deciding the savings justify it; cost optimization that silently trades away resilience tends to surface as a far more expensive incident later.
95. Explain how you’d diagnose a case where CPU utilization looks normal in Azure Monitor, but sys.dm_exec_query_stats shows a specific query with very high total CPU time. This apparent contradiction usually means the high-CPU query isn’t running frequently or concurrently enough to move the aggregate CPU% metric meaningfully at the granularity Azure Monitor reports — a query that’s individually expensive but runs once a day won’t show as sustained pressure in an hourly view, even though it’s clearly worth tuning on its own merits; aggregate metrics and per-query cost data answer different questions and shouldn’t be expected to always corroborate each other.
96. A company needs to prove, for an audit, that their documented 45-second RTO for a specific database is real, not just Microsoft’s general marketing claim. What evidence would satisfy this? A recent, measured test result — the actual elapsed time from a genuine planned (or, ideally, at some point forced) failover test performed against that specific database’s real data volume — documented with methodology and date, since a stale test result (over a year old, or from before significant data growth) no longer represents a valid current claim and would need re-testing before being cited again.
97. Explain a scenario where restoring directly over a live, existing database — rather than restoring to a new database — is actually the correct choice. When the entire database is confirmed compromised or corrupted beyond selective repair (genuine wholesale corruption or a confirmed complete ransomware encryption event) and there’s no legitimate current data worth preserving, restoring directly over it after appropriate containment steps is faster and simpler than standing up a new database and manually cutting every consumer over — the general “restore to new” default exists specifically to protect partial, still-valid current data, and that reasoning doesn’t apply when there isn’t any.
98. A team wants to design monitoring specifically for the health of a geo-replication relationship itself, distinct from general database health. What would you include? Sustained elevated replication lag alerting (not momentary spikes, since some variance is expected under normal load), the geo-replication link’s actual synchronized/healthy state via relevant metrics, and proactive Resource Health/Service Health monitoring for the secondary region specifically — treating “is my DR relationship actually healthy” as its own distinct concern, since it wouldn’t necessarily show up in the primary database’s own health metrics looking fine.
99. Explain how you’d handle a scenario where a database’s automatic index creation from Automatic Tuning conflicts with a deliberate, hand-designed indexing strategy on a high-throughput table. Set CREATE INDEX to recommend-only (or off) specifically on tables under an intentional, already-reasoned-through indexing strategy — since a DBA’s deliberate trade-off (accepting some queries run slower to minimize write overhead) can be undermined by an automated system proposing an index that technically helps one deprioritized query but works against the overall strategy — rather than assuming every table should default to the same fleet-wide automated policy.
100. Walk through a complete, realistic DP-300-style scenario: a company is migrating a 3TB, mission-critical, highly regulated OLTP database to Azure with a 5-minute downtime budget, needs cross-region DR with sub-minute RTO, Always Encrypted for sensitive columns, and strict cost control. Design the end-to-end solution. Target Azure SQL Managed Instance or Azure SQL Database on Business Critical tier depending on specific compatibility needs surfaced by DMA. Migrate via DMS in online mode, with the bulk data copy and sync happening well ahead of cutover so the 5-minute budget only covers final sync confirmation and connection switch. Configure Always Encrypted with secure enclaves if range queries on protected columns are needed, ensuring the column master key in Key Vault has appropriate redundancy validated in the DR region specifically. Set up a failover group with automatic failover policy for the sub-minute RTO requirement, validated with real planned failover testing rather than trusted on documentation alone, and be explicit that a forced cross-region failover carries some non-zero RPO given asynchronous cross-region replication, regardless of Business Critical’s strong in-region guarantees. For cost control, apply Azure Hybrid Benefit if eligible, evaluate reserved capacity once post-migration steady-state usage is established (not before, since sizing assumptions aren’t yet validated), and set Automatic Tuning to recommend-only mode given the regulated environment’s change-control requirements. Every piece of this ties back to a specific stated requirement in the scenario — which is exactly the discipline DP-300 scenario questions, and real architecture decisions, are actually testing.
A Closing Thought
Every advanced answer above comes back to the same discipline: read the actual requirement stated in the scenario, resist the tempting-but-generic answer, and justify the choice against the specific constraint given — RTO, RPO, compliance, cost, compatibility — rather than defaulting to whichever option is most familiar or most impressive-sounding.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
