Microsoft Azure Database Administrator Associate
Last reviewed: May 2026
A scannable reference of architectural patterns the DP-300 exam tests. Read top-to-bottom, or jump to a section.
A mission-critical SQL database requires 99.995% SLA, zone-redundant HA, and read scale-out capabilities.
Deploy Azure SQL Database using the Business Critical service tier with zone redundancy enabled.
Why: Business Critical provides the highest SLA, uses local SSDs for low latency, and includes built-in readable secondary replicas at no extra cost. General Purpose has a lower SLA and no built-in read replicas.
A database has unpredictable, intermittent usage patterns with long idle periods. Cost optimization is critical.
Deploy Azure SQL Database using the General Purpose tier with the Serverless compute model.
Why: Serverless automatically scales compute based on demand and can auto-pause during inactivity, charging only for storage. This is more cost-effective than Provisioned compute for non-continuous workloads.
Migrating an on-premises SQL Server that relies heavily on features like SQL Server Agent, cross-database queries, and Service Broker.
Migrate to Azure SQL Managed Instance.
Why: Managed Instance offers near 100% compatibility with on-premises SQL Server, preserving instance-level features not available in Azure SQL Database.
An application requires OS-level access, file system access (e.g., for Filestream), or features not supported by PaaS offerings like CLR with EXTERNAL_ACCESS.
Deploy SQL Server on an Azure Virtual Machine (IaaS).
Why: IaaS provides full control over the operating system and SQL Server instance, offering maximum compatibility with on-premises configurations at the cost of increased management overhead.
A database is expected to grow beyond 4 TB, up to 100 TB, and requires rapid storage scaling and fast restores.
Deploy Azure SQL Database using the Hyperscale service tier.
Why: Hyperscale is designed for Very Large Databases (VLDBs), offering up to 100 TB of storage that scales automatically. It uses a unique architecture with page servers for fast, constant-time database restores regardless of size.
A SaaS application hosts many small databases with varying, unpredictable usage patterns. Need to optimize costs while providing shared resources.
Group the databases into an Azure SQL Database elastic pool.
Why: Elastic pools allow multiple databases to share a set of resources (eDTUs or vCores) at a set price, which is more cost-effective than provisioning individual databases when usage is not constant across all tenants.
Deploying an Azure SQL Managed Instance into a virtual network.
Create a dedicated subnet with a minimum size of /27 (32 addresses) and delegate it to Microsoft.Sql/managedInstances.
Why: Managed Instance requires a dedicated, empty subnet with sufficient IP addresses for its internal components and future scaling. A /27 is the minimum supported size.
Migrating a large, mission-critical on-premises SQL Server database to Azure with minimal downtime.
Use the Azure Database Migration Service (DMS) in online migration mode.
Why: DMS online migration performs an initial load and then uses continuous data synchronization (log shipping) to keep the target in sync, allowing for a very short cutover window.
Configuring storage for a SQL Server on Azure VM hosting a data warehouse workload with large sequential reads.
Use Premium SSDs. Configure Read-only host caching for data files and None for log files.
Why: Read-only caching is optimal for the large sequential reads common in data warehouses. Log files must have caching disabled to ensure write durability and prevent data loss.
A security policy requires all database connections to be encrypted and to validate the server certificate.
Set the minimum TLS version to 1.2 on the server. In client connection strings, use `Encrypt=Strict`.
Why: Setting minimum TLS on the server prevents insecure protocol negotiation. `Encrypt=Strict` (TDS 8.0+) enforces encryption and full certificate validation, preventing man-in-the-middle attacks.
Sensitive data in specific columns (e.g., SSN) must be encrypted, but the application needs to perform equality lookups and joins on the encrypted data.
Use Always Encrypted with deterministic encryption for the searchable columns.
Why: Deterministic encryption generates the same ciphertext for a given plaintext value, allowing for equality comparisons. Randomized encryption provides stronger protection but does not allow these operations.
Data-at-rest encryption is required, but the organization must maintain full control over the encryption keys.
Enable Transparent Data Encryption (TDE) with customer-managed keys (BYOK) stored in Azure Key Vault.
Why: This configuration allows the organization to manage the key lifecycle (rotation, revocation) in their own Key Vault, providing control and meeting compliance requirements for key ownership.
An Azure SQL Database must only be accessible from a specific Azure Virtual Network, with public internet access completely blocked.
Configure a Private Endpoint for the SQL Server and set "Deny public network access" to Yes.
Why: A Private Endpoint gives the SQL database a private IP within your VNet. Disabling public access ensures it is the only way to connect, providing complete network isolation.
A multi-tenant application must ensure users can only see their own data within a shared table.
Implement Row-Level Security (RLS) by creating a security predicate (inline table-valued function) and a security policy that applies it to the table.
Why: RLS transparently filters rows based on user context (e.g., USER_NAME() or SESSION_CONTEXT), enforcing data isolation at the database engine level without application changes.
Database administrators need to manage the database but should not be able to view sensitive data in certain columns.
Implement Dynamic Data Masking (DDM) on the sensitive columns. Do not grant the UNMASK permission to DBAs.
Why: DDM obfuscates data in query results for non-privileged users without changing the stored data. This allows DBAs to perform their duties while preventing them from seeing the actual sensitive information.
A security policy mandates disabling SQL authentication to enforce centralized identity management and MFA for an Azure SQL DB or Managed Instance.
Set an Azure AD admin for the server and enable the "Azure AD-only authentication" property.
Why: This setting completely disables the SQL authentication endpoint, forcing all connections to use Azure AD. This is a critical step for enforcing modern authentication policies.
Need to detect and receive alerts for anomalous database activities, including potential SQL injection, unusual access patterns, and brute-force attacks.
Enable Microsoft Defender for SQL (formerly Advanced Threat Protection).
Why: Defender for SQL analyzes database logs for suspicious activities and generates security alerts, providing a crucial layer of threat detection beyond basic access controls.
Audit logs for an Azure SQL Database must be retained for several years and be searchable for compliance and security investigations.
Configure Azure SQL Auditing to send logs to a Log Analytics workspace with the required data retention configured.
Why: Log Analytics provides long-term retention and powerful KQL-based querying capabilities, making it superior to Blob Storage for searchable, long-term audit data.
Provide temporary, time-bound, and approval-gated access to a database for a DevOps team for troubleshooting.
Use Azure AD Privileged Identity Management (PIM) to manage eligibility for an Azure AD group that has database access.
Why: PIM provides just-in-time (JIT) access that is auditable, requires justification, and automatically expires, adhering to the principle of least privilege.
A system requires a verifiable, tamper-evident history of all data modifications to meet strict regulatory compliance.
Use Azure SQL Database ledger feature.
Why: Ledger tables use blockchain concepts to cryptographically link data changes, creating an immutable history that can be independently verified. This is stronger than temporal tables, which are not tamper-evident.
A database is experiencing performance degradation. Need to identify top resource-consuming queries, track plan changes, and find performance regressions.
Enable and utilize Query Store.
Why: Query Store is the built-in "flight data recorder" for query performance. It automatically captures query history, plans, and wait stats, making it the primary tool for diagnosing performance issues over time.
A query performs well sometimes but poorly at other times due to parameter sniffing issues, where an execution plan is optimized for a non-representative parameter value.
Use Query Store to identify the different plans and force the consistently good execution plan.
Why: Plan forcing in Query Store provides a quick and effective way to stabilize performance for problematic queries without code changes. It overrides the optimizer's choice with a known-good plan.
To improve query performance without code changes by leveraging features like batch mode on rowstore, memory grant feedback, and table variable deferred compilation.
Set the database compatibility level to 150 (for SQL 2019 features) or higher.
Why: The Intelligent Query Processing (IQP) feature set is enabled by the database compatibility level. Level 150+ activates a broad range of "no-code-change" performance enhancements in the query processor.
The operations team needs to be notified when key performance metrics, such as CPU percentage or deadlocks, exceed a defined threshold.
Use Azure Monitor to create metric alerts (for CPU) and log alerts (for deadlocks) that trigger an Action Group.
Why: Azure Monitor is the centralized platform for monitoring and alerting on Azure resources. Action Groups provide flexible notification channels (email, SMS, webhook, etc.).
Improve write performance by identifying and removing indexes that are not being used by any read queries.
Query the `sys.dm_db_index_usage_stats` DMV.
Why: This DMV tracks index usage (seeks, scans, lookups) versus updates. Indexes with high updates but zero or very low usage are prime candidates for removal, reducing maintenance overhead.
Need to capture detailed information about intermittent blocking issues, including the statements and sessions involved in the blocking chain.
Configure an Extended Events session that captures the `blocked_process_report` event.
Why: This event provides a detailed XML report of blocking chains when the `blocked process threshold` is exceeded, offering deep diagnostic information not available in DMVs.
A database needs its index strategy to adapt automatically to changing workload patterns without manual intervention.
Enable the CREATE_INDEX option in Azure SQL Database Automatic tuning.
Why: This feature allows Azure to analyze the workload, identify missing indexes with high impact, create them, and validate their performance benefit, automating a key DBA task.
Offload read-heavy reporting workloads from the primary OLTP database in a Business Critical or Premium tier.
Modify the application's read-only connection strings to include `ApplicationIntent=ReadOnly`.
Why: These tiers include a free, built-in readable secondary replica. The `ApplicationIntent` property in the connection string automatically routes read-only connections to this replica, isolating read workloads.
A large fact table in a data warehouse is frequently used for aggregation queries (SUM, COUNT, AVG) that are performing slowly.
Create a clustered columnstore index on the fact table.
Why: Columnstore indexes store data in a columnar format, providing very high data compression and enabling batch mode execution, which dramatically accelerates aggregation and scan-heavy analytical queries.
A database experiences significant blocking contention between read queries (reports) and write queries (transactions).
Enable Read Committed Snapshot Isolation (RCSI) on the database.
Why: RCSI uses row versioning, allowing readers to see the last committed version of data without taking shared locks, thereby eliminating blocks from writers. Writers do not block readers.
An application using a Serverless database experiences initial slow connection times after a period of inactivity.
Decrease the auto-pause delay or configure a minimum vCore value greater than zero.
Why: The delay is caused by the database resuming from a paused state (cold start). Setting a min vCore value prevents the database from pausing completely, eliminating resume latency at the cost of some continuous compute billing.
Implement a CI/CD pipeline for automated, version-controlled, and repeatable database schema deployments.
Use a SQL Database Project (e.g., in Visual Studio) to generate a DACPAC file. Use Azure DevOps pipelines to deploy the DACPAC.
Why: This is the standard Infrastructure as Code (IaC) pattern for SQL schema. The DACPAC is a declarative model of the schema, and the deployment tools handle generating the differential script, ensuring consistency.
An Azure SQL Database needs to be scaled up or down automatically based on a schedule or metric thresholds (e.g., high CPU).
Use an Azure Automation runbook (PowerShell) triggered by a schedule or an Azure Monitor alert.
Why: Azure SQL Database (Provisioned tier) does not have built-in autoscaling. Azure Automation is the standard tool for orchestrating this type of operational task using scripts and schedules.
A maintenance script (e.g., index rebuild) needs to be executed against hundreds of Azure SQL databases.
Use Elastic Jobs.
Why: Elastic Jobs is a PaaS service designed specifically for running T-SQL jobs across a target group of databases, managing credentials, scheduling, and logging centrally.
Ensure that all newly created Azure SQL Servers in a subscription have a specific feature, like TDE or Auditing, enabled by default.
Create an Azure Policy with a `DeployIfNotExists` or `Modify` effect.
Why: Azure Policy provides governance at scale. The `DeployIfNotExists` effect will automatically configure the missing setting during resource creation, enforcing compliance without manual intervention.
Schedule a recurring T-SQL script or maintenance task on an Azure SQL Managed Instance.
Use the built-in SQL Server Agent.
Why: Managed Instance includes the full SQL Server Agent, providing the same familiar job scheduling capabilities as on-premises SQL Server without needing an external automation service.
Control when Azure performs planned maintenance on an Azure SQL Database or Managed Instance to minimize impact on business operations.
Configure a Maintenance Window for the resource.
Why: This feature allows you to select a predefined time slot (e.g., weekends) for Azure to apply updates, giving you predictability over service-impacting maintenance.
An application requires automatic failover to a secondary region for disaster recovery, without needing connection string changes.
Configure an Auto-Failover Group between the primary and secondary databases/instances.
Why: Failover groups provide read-write and read-only listener endpoints. These endpoints automatically redirect traffic to the current primary/secondary server after a failover, making the process transparent to the application.
Database backups must be retained for many years (e.g., 7-10 years) to meet legal or regulatory compliance requirements.
Configure a Long-Term Backup Retention (LTR) policy.
Why: Standard Point-in-Time Restore (PITR) backups are kept for a maximum of 35 days. LTR stores full backups in separate Azure Blob Storage for up to 10 years, specifically for compliance needs.
A database must remain available during a datacenter (Availability Zone) failure within a single Azure region.
Enable the zone-redundant configuration for a Business Critical or Premium tier database.
Why: Zone redundancy deploys synchronous secondary replicas in different physical datacenters within the same region, providing automatic failover with RPO near-zero for zone-level outages.
An Azure SQL Managed Instance requires a disaster recovery solution in a paired Azure region with automatic failover capability.
Configure an Auto-Failover Group for the Managed Instance.
Why: This is the canonical DR pattern for Managed Instance, providing asynchronous replication, listener endpoints for transparent application failover, and an automated failover option.
Need to be able to restore a database to any specific second from the past month.
Configure the short-term backup retention (PITR) period to 30-35 days.
Why: Azure SQL automatically takes full, differential, and frequent transaction log backups. The PITR retention setting (1-35 days) determines how long these backups are kept, defining the window for point-in-time restores.
Configuring a Windows Server Failover Cluster for a SQL Server Always On Availability Group on Azure VMs.
Use a Cloud Witness as the quorum witness.
Why: A Cloud Witness uses Azure Blob Storage and is the recommended, most resilient option for clusters in Azure. It avoids the need for a third VM for a file share witness or complex shared disk configurations.
Implementing a SQL Server Failover Cluster Instance (FCI) on Azure VMs which requires shared storage.
Use Azure Shared Disks (attaching a managed disk to multiple VMs).
Why: Azure Shared Disks is the native Azure solution for providing block storage that can be accessed by multiple VMs, which is a prerequisite for a traditional FCI.
The disaster recovery process for a failover group needs to be tested without impacting the production primary database.
Initiate a planned (manual) failover during a low-impact maintenance window, validate application connectivity, and then fail back.
Why: A planned failover ensures no data loss and is the most thorough way to validate the entire DR process, including DNS propagation and application reconnection. It is a brief, controlled production event.
