DP-300 question patterns

SQL Managed Instance.

It has near-complete instance-level compatibility. SQL Database lacks Agent and cross-database queries.

SQL Server on an Azure VM.

Only IaaS gives OS control and version choice.

Prefer SQL Database or Managed Instance over a VM.

Patching, backups and HA are automatic in PaaS.

An elastic pool.

Databases share a resource budget, so cost is lower than sizing each for peak.

Serverless vCore compute with auto-pause.

Compute is paused when idle; storage is still billed.

Azure Arc-enabled SQL Server.

It projects servers into Azure for inventory, security, patching and billing.

SQL database in Microsoft Fabric.

It mirrors automatically to OneLake in Delta format.

Table partitioning with a date partition function and `SWITCH`.

Switching is a metadata operation.

Elastic database tools with a shard map manager.

It routes requests by sharding key.

Log Replay Service, DMS online mode or the Managed Instance link.

They continuously apply log backups until cutover.

`CREATE DATABASE ... AS COPY OF`.

Online, transactionally consistent copy.

Managed Instance database copy and move.

It avoids backup and restore plumbing.

Enable Microsoft Entra-only authentication.

SQL logins are disabled at the server.

Managed identity with `CREATE USER ... FROM EXTERNAL PROVIDER`.

No credential to leak or rotate.

Always Encrypted.

Keys never reach the engine.

Always Encrypted with secure enclaves.

The enclave computes on plaintext inside a protected region.

Transparent data encryption.

It encrypts files at rest, not data in use.

TDE protector in Azure Key Vault.

The customer controls and can revoke the key.

Dynamic data masking.

It masks results without changing storage.

Security policy with a filter predicate.

The engine enforces it for every query.

Ledger tables with digests in immutable storage.

Digests outside the database detect tampering.

Private endpoint with private DNS.

Service endpoints aren't reachable from on-premises.

Server-level auditing to Log Analytics.

It covers every database on the logical server.

Change data capture.

Change tracking only records that a row changed.

Query Store, then force the older plan.

It keeps plans and runtime stats.

Database watcher with a Data Explorer store.

It replaced SQL insights.

Intelligent Insights diagnostics log.

It gives root cause; not on MI.

An Extended Events session with an event file target.

It replaced Profiler and has low overhead.

Query `sys.dm_exec_requests` and follow `blocking_session_id`.

The head blocker is the root of the chain.

Enable FORCE_LAST_GOOD_PLAN.

It reverts to the last good plan and validates.

Enable CREATE_INDEX and DROP_INDEX.

Not available on MI or VMs.

`ALTER INDEX ... REBUILD`.

Above 30% rebuild; 5-30% reorganize.

Resource pool, workload group and classifier.

Not available in SQL Database.

Raise compatibility level for intelligent query processing.

Feedback adjusts grants on later runs.

An elastic job, or Azure Automation or Logic Apps.

SQL Database has no Agent.

Elastic job with a server or pool target group.

Targets are re-enumerated each run.

Bicep template deployed in a pipeline.

It is declarative and idempotent.

`what-if` on the deployment.

Shows adds, changes and deletes without applying.

Metric alert, action group and an Automation runbook.

Automation changes the service objective.

Auto-failover group with the read-write listener.

Listener names stay the same.

Active geo-replication.

Per-database, up to four secondaries.

Always On availability group.

Replicates databases; WSFC plus listener.

Failover cluster instance with shared disks or Premium file share.

Whole instance fails over; one copy of data.

Log shipping.

Backs up, copies and restores logs on a schedule.

Point-in-time restore to a new database.

PaaS restores never overwrite.

Long-term retention policy with a yearly retention.

LTR holds full backups up to 10 years.

Restore with NORECOVERY until the last step.

RECOVERY ends the chain.

Managed Instance link (distributed AG).

Near-real-time replication to MI.

Planned failover drills and test restores with measured RTO and RPO.

Untested plans fail.