Azure DP-300 cheat sheet

Azure SQL Database offers two purchasing models: DTU (Basic, Standard, Premium) bundles compute, IO and storage; vCore (General Purpose, Business Critical, Hyperscale) lets you independently size compute, memory and storage and is required for Azure Hybrid Benefit.

Tier selection rule: General Purpose uses remote premium storage (higher latency, lower cost); Business Critical uses local SSD with in-memory OLTP and a free readable secondary; Hyperscale scales storage to 100 TB with fast restore from snapshot backups.

Serverless compute (General Purpose only) auto-scales vCores between a min and max, and auto-pauses after an inactivity delay configurable from 1 hour to 7 days, billing only for storage while paused.

Elastic pools share a fixed eDTU/vCore budget across many databases; they are the correct choice when individual databases have spiky, non-overlapping peak workloads.

Azure SQL Managed Instance deploys into a dedicated delegated subnet (minimum /27, /26 recommended) with a network security group and route table; it provides near-100% SQL Server surface area, including SQL Agent and cross-database queries.

Azure SQL Database does not support SQL Server Agent, cross-database queries, CLR, or the Service Broker - those workloads require Managed Instance or SQL Server on an Azure VM.

SQL Server on Azure VM is an IaaS lift-and-shift option: place data, log and tempdb on separate striped Premium SSD v2 or Ultra Disk volumes and avoid the OS disk for database files.

Database collation is fixed at creation time and cannot be altered afterwards, so validate collation in the deployment template before provisioning.

Reserved capacity is a 1-year or 3-year prepurchase of vCore compute that lowers cost substantially; it does not cover storage, backup storage, or serverless compute.

Authentication options: SQL logins and passwords, Azure AD (Microsoft Entra ID) logins and contained database users; setting the server to Azure AD-only authentication disables all SQL logins.

Network access is controlled by server-level and database-level firewall rules (IP ranges plus the Allow Azure Services 0.0.0.0 rule) or by a private endpoint; disabling public network access forces private-only connectivity.

Transparent Data Encryption protects data and backups at rest; with a customer-managed key the Azure Key Vault must have soft delete and purge protection enabled or the key operation is rejected.

Always Encrypted is client-side column encryption: the driver holds the column master key (Key Vault or Windows certificate store); deterministic encryption allows equality lookups and joins, randomized allows none.

Dynamic data masking obscures values in query results only - data is stored in plaintext and users with the UNMASK permission see unmasked values, so masking is not an encryption control.

Row-level security is implemented as an inline table-valued function predicate bound by a security policy; users with CONTROL permission on the table are not filtered by the predicate.

Auditing can be enabled at server scope and at database scope simultaneously, writing to a storage account, Log Analytics workspace, or Event Hub.

Microsoft Defender for SQL adds threat detection alerts and vulnerability assessment; it must be enabled per server or per Managed Instance, not automatically inherited by every database.

For application access, prefer contained database users mapped to an Azure AD identity, or a managed identity, and grant membership in fixed database roles rather than the server admin role.

Query Store records query text, execution plans, runtime statistics and wait categories; it must be enabled on a database before Automatic Tuning can create or force plans.

Automatic tuning options are FORCE PLAN, CREATE INDEX and DROP INDEX, and each can be set to Inherit, ON or OFF at the server level and overridden at the database level in Azure SQL Database and Managed Instance.

sys.dm_db_resource_stats reports CPU, data IO, log IO and memory as a percentage of the service tier limit every 15 seconds for roughly the last hour; sys.resource_stats reports hourly aggregates for the last 14 days.

Missing index suggestions from sys.dm_db_missing_index_details are advisory estimates only, do not include included columns from all queries, and are cleared by restarts and plan eviction.

High RESOURCE_SEMAPHORE waits indicate memory grant pressure (often oversized sorts/hashes or parameter sniffing), while PAGEIOLATCH waits indicate storage IO bottlenecks and CXPACKET/CXCONSUMER indicates parallelism skew.

Database scoped configuration (MAXDOP, legacy cardinality estimation, parameter sniffing, optimized plan forcing) is set per database with ALTER DATABASE SCOPED CONFIGURATION on Azure SQL Database; on Managed Instance and SQL on VM it can also be set at instance level.

Resumable online index rebuild lets a rebuild be paused and resumed, preserving progress, and reduces log growth; online rebuild requires Business Critical, Managed Instance, or SQL Server Enterprise/Developer.

Query Performance Insight surfaces the top CPU, duration and data IO queries from Query Store, and its data retention matches the Query Store retention interval.

Azure SQL Database has no SQL Server Agent; recurring T-SQL work must run through Elastic Database Jobs, Azure Automation, Logic Apps, or Azure Functions.

An elastic job agent requires its own Azure SQL Database as the job database and uses a target group whose members are either an explicit database list or an entire server/elastic pool.

SQL Server Agent is available on Azure SQL Managed Instance and on SQL Server on Azure VM; on Managed Instance, Agent jobs can also target Azure SQL Database members via linked servers only indirectly, so use Elastic Jobs for cross-service scheduling.

Schema and data deployment automation uses sqlpackage with dacpac/bacpac files, or SqlPackage/DACPAC tasks inside Azure DevOps pipelines and GitHub Actions.

Infrastructure provisioning is automated with ARM templates, Bicep, or Terraform; Azure Policy enforces allowed SKUs, tiers, and regions, and resource locks prevent accidental deletion.

Alerting is automated with Azure Monitor alert rules that invoke action groups, which can send email/SMS/push, call a webhook, run an Azure Function, or trigger a Logic App.

Serverless Azure SQL Database provides automatic compute scaling and auto-pause, which replaces manual scheduling of start/stop for dev/test databases.

RPO is the maximum tolerable data loss measured in time; RTO is the maximum tolerable downtime - the chosen DR topology must satisfy both, and cost rises as either shrinks toward zero.

Azure SQL Database automated backups consist of a weekly full backup, differential backups roughly every 12–24 hours, and transaction log backups every 5–10 minutes, driving point-in-time restore RPO to about 5–10 minutes.

Point-in-time restore retention is 7 days by default and configurable from 1 to 35 days; long-term retention adds weekly, monthly, or yearly copies kept up to 10 years.

Active geo-replication supports up to four readable secondary databases in any region with asynchronous replication and requires manual failover initiation.

Auto-failover groups extend geo-replication with read-write and read-only listener endpoints, a configurable automatic failover grace period, and grouped failover for multiple databases or an elastic pool.

Geo-restore creates a new database from the geo-redundant backup copy in any region after a regional outage, with an RPO of approximately 1 hour and no guaranteed RTO.

Zone redundancy places replicas across three availability zones in one region and raises the availability SLA to 99.995%; it is available in the Business Critical and Premium tiers and in General Purpose/Hyperscale in supported regions.

Backup storage redundancy (LRS, ZRS, GRS, RA-GRS) is chosen at creation; changing it later is possible but restricts which restore options remain available for the interim period.

SQL Server on Azure VM achieves HA with Always On availability groups across VMs in an availability set or across zones, and DR with a distributed availability group or Azure Site Recovery.