CertifyDeck
Start for free
All cheat sheets
DP-300 · CHEAT SHEET

Microsoft Certified: Azure Database Administrator Associate

Key concepts grouped by official exam weight.

Download as PDF (free, no sign-up)

1. Plan and implement data platform resources

15–20%

Plan and deploy Azure SQL solutions

  • Azure SQL Database is PaaS for cloud-born applications, with elastic pools for multitenant SaaS; Azure SQL Managed Instance offers near-complete SQL Server compatibility (SQL Server Agent, CLR, Service Broker, Database Mail) for migrations with few changes; SQL Server on Azure VMs gives full OS and engine control, needed for features such as FILESTREAM.
  • Azure SQL resources are deployed repeatably with Bicep or ARM templates (az deployment group create or New-AzResourceGroupDeployment) and with commands such as az sql db create. Incremental mode is the default and reapplies all properties; the what-if operation previews changes without deploying, and @secure() keeps passwords out of deployment history.
  • SQL Server enabled by Azure Arc brings Azure management to instances in data centers, edge sites and other clouds: an inventory you can query with Azure Resource Graph, best practices and migration assessments, Microsoft Defender for Cloud, Microsoft Entra ID authentication, pay-as-you-go billing and Extended Security Updates. It connects with outbound HTTPS traffic.
  • SQL database in Microsoft Fabric is a transactional database built on the Azure SQL Database engine that automatically mirrors its data into OneLake and exposes a read-only SQL analytics endpoint. It supports only Microsoft Entra ID and does not support CDC, TDE with customer keys or Always Encrypted; it fits AI apps, operational data stores, reverse ETL and translytical apps.
  • Partitioning splits a table or index horizontally: the partition function defines boundaries (RANGE LEFT or RIGHT) and the scheme maps partitions to filegroups. It eases per-partition maintenance, partition switching and partition elimination in queries. In Azure SQL Database everything sits on PRIMARY, and a unique clustered index must include the partitioning column.

Configure resources for scale and performance

  • A single database is scaled with T-SQL, the portal, Set-AzSqlDatabase or az sql db update; it stays online, but connections drop at the end, so applications need retry logic. Business Critical, Premium and Hyperscale offer read scale-out through ApplicationIntent=ReadOnly, and elastic pools share resources among databases with unpredictable spikes.
  • SQL Managed Instance scales vCores, storage and service tier after creation. In General Purpose, operations reattach remote storage; in Business Critical almost all of them seed data, which slows down under heavy load. Next-gen General Purpose supports flexible memory, instance pools share one VM, and tempdb accepts extra data files to reduce contention.
  • On SQL Server on Azure Virtual Machines, data disks use ReadOnly host caching and log disks use None; tempdb goes on the local D: SSD and data disks are formatted with a 64-KB allocation unit. A storage pool's column count is fixed at creation, so adding disks only adds capacity. On M-series VMs, Write Accelerator improves transaction log write latency.
  • A partition function defines the boundaries and whether each boundary value belongs to the left (RANGE LEFT, the default) or right (RANGE RIGHT) partition; a partition scheme maps partitions to filegroups. SPLIT RANGE and MERGE RANGE add or remove boundaries, SWITCH moves a whole partition between tables, and in Azure SQL Database every partition is on PRIMARY.
  • Row and page compression shrink rowstore tables and indexes at the cost of extra CPU; they are set per table, index or partition, and nonclustered indexes don't inherit the table's setting. sp_estimate_data_compression_savings estimates savings by sampling into tempdb. Columnstore indexes also support archival compression, which is slower to query.

Plan and implement a migration strategy

  • Before migrating, assess the number and size of databases, the acceptable downtime and the features in use. SQL Server enabled by Azure Arc automatically produces a readiness assessment with target and sizing recommendations. An Azure VM fits when you need operating system access or FileStream; Azure SQL Database doesn't support SQL Server Agent jobs (use elastic jobs instead).
  • An online migration keeps the source active and replicates changes until cutover; an offline migration requires downtime. To Azure SQL Managed Instance, the Managed Instance link is the lowest-downtime online option and allows reading the target, and Log Replay Service in continuous mode also minimizes downtime. To Azure SQL Database, DMS is offline and transactional replication provides the online path.
  • In an online migration the source stays active while changes replicate, and cutover happens at the end: with Log Replay Service in continuous mode you upload the tail-log backup and run Complete-AzSqlInstanceDatabaseLogReplay; with the Managed Instance link you stop the workload, check the lag and perform a planned failover. To Azure SQL Database you use transactional replication with a push subscription.
  • An offline migration stops the application during the copy. To Azure SQL Managed Instance you natively restore a .bak from Azure Blob Storage with a SAS credential, or use Log Replay Service in autocomplete mode, the only method that restores differential backups. To Azure SQL Database, a BACPAC imported with SqlPackage runs faster if you scale up the target and disable automatic statistics.
  • To SQL Server on Azure VMs you can use backup and restore, detach and attach from a URL, log shipping, or convert the machine to a VHD to move the whole instance. Azure Database Migration Service needs a self-hosted integration runtime when backups are on an on-premises share. SQL Server enabled by Azure Arc migrates to Managed Instance from the portal.

2. Implement a secure environment

20–25%

Configure database authentication and authorization

  • Microsoft Entra authentication is enabled by setting a Microsoft Entra admin on the logical server or managed instance; it is a single identity (user, group or application) and is the first one able to create other Entra principals. It doesn't integrate with Azure RBAC, and SQL Managed Instance needs the Directory Readers role on its identity.
  • On SQL Server 2022 or later on Azure VMs, Microsoft Entra authentication is enabled through the SQL IaaS Agent extension with a managed identity that can query Microsoft Graph; once enabled it can't be disabled. SQL Managed Instance doesn't support FROM WINDOWS logins, but offers Windows Authentication for Microsoft Entra principals through Kerberos.
  • Server-level principals are logins (SQL, Windows or Microsoft Entra) and server roles; database-level principals are users, database roles and application roles. Azure SQL Database provides fixed server roles prefixed with ##MS_ that are managed from master, and their permissions reach each database when the login has a user there or belongs to ##MS_DatabaseConnector##.
  • For a Microsoft Entra identity you can create a contained user with CREATE USER ... FROM EXTERNAL PROVIDER or a login-based user with CREATE USER ... FROM LOGIN; the two aren't linked even if they share a name. Entra users must be created by Entra identities, and when a service principal's display name isn't unique you use WITH OBJECT_ID with an alias.
  • In SQL Server Management Studio, the Permissions or Securables page lets you grant or deny permissions on the Explicit tab and view on the Effective tab the result combined with role memberships, where a deny wins. The With Grant box is read-only, so WITH GRANT OPTION is applied with T-SQL; column permissions are set from Column Permissions.

Implement security for data at rest and data in transit

  • Transparent data encryption (TDE) encrypts data, log files and backups at rest at page level with a database encryption key protected by the TDE protector. In Azure SQL Database it's on by default with a service-managed key and supports a customer-managed key in Azure Key Vault; in SQL Server you create a master key, a certificate and the encryption key, and back up the certificate.
  • Always Encrypted encrypts columns in the client driver with column encryption keys protected by a column master key stored outside the database. Deterministic encryption supports equality lookups, grouping and indexes; randomized is more secure, and secure enclaves are used for LIKE, ranges or sorting. Column-level encryption with a symmetric key is done in T-SQL with EncryptByKey.
  • In Azure SQL Database, server-level IP firewall rules are stored in master and grant access to all databases; database-level rules are stored in each database, are managed only with T-SQL and require a first server-level rule. The firewall checks the requested database's rules first and then the server rules; the 0.0.0.0 rule admits all Azure services.
  • Always Encrypted encrypts columns inside the client driver with column encryption keys protected by a column master key kept outside the database (for example in Azure Key Vault), so the engine never sees keys or plaintext. Deterministic encryption supports equality lookups, GROUP BY and indexes; randomized encryption without an enclave does not. Values must always be sent as parameters.
  • Always Encrypted with secure enclaves processes encrypted data inside a protected memory region of the engine, enabling in-place encryption with ALTER TABLE and LIKE, range and sort queries on randomized columns protected by enclave-enabled keys (ENCLAVE_COMPUTATIONS). Replicas and geo-secondaries must also support enclaves.

Implement compliance controls for sensitive data

  • Data Discovery & Classification finds columns with sensitive data, recommends classifications and stores labels and information types as column metadata, which auditing records in data_sensitivity_information. It is managed in the portal, with T-SQL (ADD SENSITIVITY CLASSIFICATION, sys.sensitivity_classifications), PowerShell or the REST API; each column holds a single classification.
  • In Azure SQL Database, server auditing covers all current and future databases and can coexist with database audits; it writes to Azure Storage, Log Analytics or Event Hubs. SQL Server and SQL Managed Instance use SQL Server Audit: a server audit with its target (file, TO URL or EXTERNAL_MONITOR) plus server or database audit specifications.
  • Change tracking is synchronous and lightweight: it records which rows changed (primary key only) and is queried with CHANGETABLE and the version functions, ideal for synchronization. Change data capture reads the transaction log asynchronously and stores values in change tables for ETL; in Azure SQL Database a scheduler runs it instead of SQL Server Agent.
  • Dynamic data masking hides values in query results without changing stored data, using functions such as default(), email(), random(), partial() and datetime(). Administrators and db_owner see real data; others are granted UNMASK at database, schema, table or column level. It doesn't stop inference through ad hoc queries or updates to the column.
  • Ledger provides cryptographic tamper evidence: updatable ledger tables keep earlier versions in a history table and append-only tables accept only inserts. Digests are stored in immutable storage or Azure Confidential Ledger and verified with sp_verify_database_ledger_from_digest_storage. A ledger table can't be reverted to a regular table.

3. Monitor, configure, and optimize database resources

20–25%

Monitor resource activity and performance

  • A baseline is a set of metrics collected over time that defines normal performance; without it you can't tell a real problem from usual activity. For SQL Server on virtual machines, you combine Performance Monitor counters (or InfluxDB, Collectd and Grafana on Linux) with wait statistics, and Azure Monitor adds alerts with dynamic thresholds.
  • Azure Monitor collects platform metrics automatically, but resource logs need a diagnostic setting. In Azure SQL Database, sys.dm_db_resource_stats gives recent granular data and sys.resource_stats (in master) the history; Managed Instance uses sys.server_resource_stats. Database watcher centralizes estate monitoring, and on virtual machines you use DMVs and SQL Server counters.
  • In Azure SQL Database, metrics such as CPU percentage, Data IO percentage, Log IO percentage and Workers percentage measure consumption toward the user workload limit, and DTU percentage equals the highest of CPU, data IO and log IO. sys.dm_db_resource_stats provides 15-second data for the last hour, and sys.resource_stats in master provides 5-minute data for historical analysis.
  • Azure Monitor metric alert rules evaluate aggregated values over a lookback period, notify through action groups and can be scoped to a database, an elastic pool or a whole resource group or subscription. Diagnostic settings stream metrics and resource logs to Log Analytics, Event Hubs or Azure Storage, and are configured separately for elastic pools, managed instances and each database.
  • Database watcher is a managed monitoring solution for Azure SQL Database and Azure SQL Managed Instance that collects data from more than 70 system views into an Azure Data Explorer or Microsoft Fabric Real-Time Analytics data store, with estate and resource dashboards in the portal. It preferably authenticates with Microsoft Entra through its managed identity and must be started after configuration.

Monitor and optimize query performance

  • Query Store is configured with ALTER DATABASE SET QUERY_STORE: OPERATION_MODE, MAX_STORAGE_SIZE_MB, SIZE_BASED_CLEANUP_MODE, CLEANUP_POLICY, INTERVAL_LENGTH_MINUTES and QUERY_CAPTURE_MODE (ALL, AUTO, CUSTOM or NONE). When it exceeds its size it switches to read-only, visible in sys.database_query_store_options. It is on by default in Azure SQL Database and Managed Instance, and cannot be turned off in Azure SQL Database.
  • Query Store keeps the history of queries, plans, runtime statistics and waits, so you can find plan regressions and force the previous plan with sp_query_store_force_plan without touching the application. In SSMS the Regressed Queries, Top Resource Consuming Queries and Query Wait Statistics views support the analysis, and Query Store hints apply query hints without code changes.
  • Blocking happens when one session holds a lock and another requests an incompatible lock on the same resource. To resolve it, find the head blocker through blocking_session_id in sys.dm_exec_requests, review its most recent statement with sys.dm_exec_input_buffer and fix the cause, such as transactions left open after a timeout, which SET XACT_ABORT ON or a ROLLBACK prevents.
  • DMVs support performance diagnosis: sys.dm_db_resource_stats and sys.dm_elastic_pool_resource_stats show recent consumption, sys.dm_exec_requests and sys.dm_os_waiting_tasks show current waits, and sys.dm_exec_query_stats shows statistics for cached plans. Wait types guide the diagnosis: PAGEIOLATCH points to data I/O, WRITELOG to log I/O and RESOURCE_SEMAPHORE to memory grant waits.
  • Missing index suggestions appear in execution plans and in DMVs such as sys.dm_db_missing_index_details, but they are pre-execution estimates, are cleared by restarts and failovers and only propose nonclustered indexes. Before creating them, combine them with existing indexes, put equality columns before inequality columns and validate the change with Query Store; filtered indexes serve well-defined subsets.

Configure database solutions for optimal performance

  • Measure rowstore fragmentation and page density with sys.dm_db_index_physical_stats before acting. Reorganize is less resource intensive, always online and defragments only the leaf level; rebuild removes fragmentation at all levels and updates index statistics with a full scan. In Azure SQL, prefer reorganize or online rebuilds, and make large rebuilds resumable so a failover does not restart them.
  • Keep AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS on, and add manual updates with UPDATE STATISTICS or sp_updatestats after bulk loads or on ascending keys. sys.dm_db_stats_properties shows the last update and modification counter. PERSIST_SAMPLE_PERCENT keeps a chosen sampling rate, NORECOMPUTE disables automatic updates, and asynchronous updates let queries compile with existing statistics.
  • DBCC CHECKDB runs allocation, table and catalog checks using an internal database snapshot. PHYSICAL_ONLY is a low-overhead option for frequent production runs, with periodic full checks. Restoring from a good backup is preferred over REPAIR options. Azure SQL Database manages integrity itself and does not allow customer repairs; Hyperscale uses DBCC CHECKTABLE WITH TABLOCK per table.
  • Automatic tuning offers FORCE_LAST_GOOD_PLAN, CREATE_INDEX and DROP_INDEX in Azure SQL Database, but only FORCE_LAST_GOOD_PLAN in Azure SQL Managed Instance, configured with T-SQL. Configure it on the logical server and let databases inherit it. Automatically applied actions are validated and reverted if performance regresses, and the feature depends on Query Store being enabled and writable.
  • Instance options set with sp_configure tune performance: cost threshold for parallelism keeps cheap queries serial, optimize for ad hoc workloads caches plan stubs for single-use batches, and max and min server memory bound the buffer pool. On Azure VMs, use read-only host caching for data disks, none for log disks, and place tempdb on the local SSD.

4. Configure and manage automation of tasks

15–20%

Create and manage SQL Server Agent jobs

  • SQL Server Agent schedules can be time based (one time or recurring) or event based (when the Agent starts or when the CPU is idle) and can be shared by several jobs, so editing one affects every attached job. Create them with sp_add_schedule and attach them with sp_attach_schedule in msdb. Azure SQL Managed Instance does not support CPU idle schedules, and a disabled schedule must be re-enabled explicitly.
  • SQL Server Agent alerts respond to an error number, a severity, a performance counter or a WMI event and are created with sp_add_alert; sp_add_notification links them to an operator. To send email, the Agent must use Database Mail, and sp_update_job sets when a job notifies (1 success, 2 failure, 3 always). SQL Managed Instance has no alerts or pager, and its mail profile must be named AzureManagedInstance_dbmail_profile.
  • To diagnose SQL Server Agent jobs you query msdb: sp_help_jobhistory filters by status (0 failed, 1 succeeded, 3 canceled) and sysjobactivity shows current activity. Transact-SQL steps run in the job owner's context; @database_user_name applies only when the owner is sysadmin. In sp_add_jobstep, @retry_attempts, @on_fail_action and @flags control retries, the flow after a failure and how step output is logged.

Automate deployment of database resources

  • Bicep and ARM templates declaratively describe logical servers and databases (Microsoft.Sql/servers and servers/databases). Incremental mode, the default, leaves resources outside the template untouched but resets omitted properties; complete mode deletes whatever isn't listed. Passwords are passed with @secure() or with getSecret from Key Vault, never in outputs or parameters files.
  • The Az.Sql module automates Azure SQL: New-AzSqlServer creates the logical server with PSCredential credentials, New-AzSqlServerFirewallRule adds firewall rules (-AllowAllAzureIPs goes in a separate call), New-AzSqlDatabase creates databases with options such as -ComputeModel or -ElasticPoolName, and Set-AzSqlDatabase changes the service level or moves the database into an elastic pool.
  • The Azure CLI creates and configures Azure SQL with az sql server create, az sql server firewall-rule create and az sql db create or update. Parameters such as --compute-model, --auto-pause-delay (-1 disables it), --backup-storage-redundancy, --zone-redundant or --ledger-on, which can't be changed later, define the database; --no-wait doesn't wait for the operation to finish.
  • Deployment errors can be validation, preflight or deployment errors; the first two appear in the activity log but not in the deployment history. Failed operations are listed with az deployment operation group list or Get-AzResourceGroupDeploymentOperation, and each deployment has a correlation ID. Codes such as ParentResourceNotFound or DeploymentFailedCleanUp point to the cause.

Create and manage database tasks in Azure

  • Elastic jobs run T-SQL on a schedule or once across many databases in Azure SQL Database. They consist of an agent, a job database, jobs with steps, and target groups whose servers and elastic pools are enumerated at run time, with optional exclusions. They authenticate with a user-assigned managed identity or with database-scoped credentials, and their scripts must be idempotent.
  • Azure Automation runs PowerShell, PowerShell Workflow, Python or graphical runbooks to automate database tasks with the Az cmdlets, started manually, by other systems or on a schedule. Runbooks authenticate with the Automation account's managed identity, because Run As accounts were retired. The Azure sandbox minimizes costs, while a Hybrid Runbook Worker suits local resources or long-running work.
  • Automated tasks in Azure are monitored with Azure Monitor: the elastic job agent exposes the Failed Elastic jobs metric, and Azure Automation exposes the Total Jobs metric with Runbook Name and Status dimensions. Action groups send email or SMS, alert processing rules can suppress notifications, and diagnostic settings forward job logs to Log Analytics.
  • For elastic jobs, jobs.job_executions shows the lifecycle of each execution (Failed, TimedOut, Skipped and so on), and a NULL step_id marks the parent execution. In Azure Automation, job statuses and output streams explain failures; fair share stops long jobs in the sandbox, and with a firewall on Azure SQL you need a Hybrid Runbook Worker with a service endpoint.

5. Plan and configure a high availability and disaster recovery (HA/DR) environment

20–25%

Plan an HA/DR strategy for database solutions

  • RTO is the time until the application recovers and RPO is the data loss that can be tolerated. In Azure SQL Database, zone redundancy gives zero RPO for zonal failures; failover groups and active geo-replication are asynchronous with a low RTO, and geo-restore takes minutes or hours. On VMs, availability groups and FCIs provide high availability, while log shipping provides disaster recovery.
  • In a hybrid deployment, part of the HA/DR solution runs in Azure and part on-premises. A hybrid availability group needs a multi-subnet cluster and a VPN, and asynchronous commit is recommended because of latency; log shipping also requires a VPN. The Managed Instance link replicates in near real time from SQL Server to a managed instance.
  • Azure SQL Database offers zone redundancy to survive a zone outage with zero RPO, and active geo-replication, failover groups or geo-restore for regional disasters. SQL Managed Instance uses failover groups that move all user databases. On VMs, if storage is geo-replicated, a database's data and log files must sit on the same disk.
  • A disaster recovery drill simulates the outage, recovers and validates the application. With failover groups you use a planned failover, with no data loss, that can run in production; with geo-restore you work on a copy. High availability is tested with Invoke-AzSqlDatabaseFailover, and a planned availability group failover needs synchronous commit and synchronized replicas.

Plan and perform backup and restore of a database

  • A backup strategy starts from the RPO and RTO. In SQL Server, point-in-time restore needs the full recovery model and log backups. In Azure SQL Database you choose backup storage redundancy (LRS, ZRS, GRS or GZRS), which affects only future backups. For SQL VMs you can choose between Automated Backup, Azure Backup or manual backups.
  • Native tools (SSMS, T-SQL and PowerShell) take full, differential, log and copy-only backups. A copy-only backup doesn't change the differential base or the log chain. BACKUP supports CHECKSUM, COMPRESSION and ENCRYPTION, which needs a master key and a certificate in master. Managed Instance only allows COPY_ONLY backups to URL, and not with service-managed TDE.
  • With native tools you restore the full backup, then the most recent differential and then the log backups in sequence, using NORECOVERY until the last one, which is restored WITH RECOVERY. Before restoring a database in the full recovery model, take a tail-log backup. MOVE relocates files and REPLACE overwrites an existing database.
  • In Azure SQL Database, point-in-time restore creates a new database on the same server and never overwrites the original; it also recovers deleted databases. In SQL Managed Instance it can restore to another instance or subscription in the same region. In SQL Server you restore a full backup and log backups with the same STOPAT clause, under the full recovery model.
  • Long-term retention (LTR) copies automatic full backups to separate storage according to the W, M, Y and WeekOfYear parameters. Policy changes affect only future backups, and enabling it copies the latest full backup. It is configured with the portal, the CLI (az sql db ltr-policy set) or PowerShell, and an LTR backup is always restored as a new database.

Configure HA/DR for database solutions

  • Active geo-replication creates readable secondaries per database on another logical server and replicates the log asynchronously. It is configured with the portal, ALTER DATABASE ... ADD SECONDARY ON SERVER in master, PowerShell or az sql db replica. A planned failover loses no data; a forced one can. Lag is measured with replication_lag_sec, and sp_wait_for_database_copy_sync protects critical transactions.
  • On Azure VMs, a multi-subnet availability group needs no load balancer; in a single subnet you need a VNN listener with an Azure Load Balancer (Floating IP and ProbePort) or a DNN listener. On SQL Managed Instance, Business Critical uses Always On technology internally, and the Managed Instance link builds a distributed availability group from SQL Server without replicating logins or jobs.
  • A failover group replicates databases to another region and provides read-write and read-only listeners that stay the same after failover. The customer managed policy is recommended; forced failover (AllowDataLoss) accepts data loss. On SQL Managed Instance it covers all user databases, requires a shared DNS zone and doesn't replicate master or msdb.
  • An FCI on Azure VMs is a single SQL Server instance on a WSFC cluster with shared storage: Azure shared disks, premium file shares, Storage Spaces Direct or Elastic SAN, each with its own limits on zones, FILESTREAM and MSDTC. With shared disks a disk witness is preferred; in multiple subnets no load balancer or DNN is needed.
  • Log shipping sends transaction log backups from a primary database to one or more secondaries through SQL Server Agent backup, copy and restore jobs. It requires the full or bulk-logged recovery model, never fails over automatically, supports a restore delay, and the monitor server must be added when the configuration is created.
Practice this certification