Microsoft Azure Database Administrator Associate DP-300 (DP-300) — Questions 1–75

Deterministic encryption produces the same ciphertext for identical plaintext, enabling equality searches on CreditLimit through the Always Encrypted-enabled driver. Randomised encryption would block exact-match queries, and application code changes stay minimal since the driver handles encryption transparently.

Log write latency directly measures the time taken to write transaction log records to durable storage, which is the precise bottleneck causing slow queries during peak hours in the General Purpose tier. Examining this metric in Azure Monitor isolates whether log throughput, not CPU or data I/O, constrains performance.

Auto-failover groups provide automated geo-replication across Azure regions, enabling a secondary server to take over if the primary fails. This satisfies the disaster recovery requirement by delivering cross-region failover for Azure SQL Database, including read-write listener endpoints that redirect connections without manual intervention.

High worker time with low logical reads indicates CPU consumption rather than I/O. Inefficient plans, such as scans, bad cardinality estimates or missing indexes causing repeated CPU-heavy operations, burn worker time without many page reads, and no blocking or deadlocks were reported.

An Always On availability group with a synchronous secondary replica allows you to perform a manual failover to the secondary before restarting the primary VM. The database remains online on the secondary, and after the primary VM restarts, you can fail back. This provides minimal downtime during the planned OS update, meeting the requirement.

An auto-failover group with automatic failover policy enables Azure to automatically fail over to the secondary region if the primary region becomes unavailable. This provides a low RTO because failover is automatic and typically completes within 30 seconds. The RPO is also low because the secondary is continuously synchronized. This is the correct action to meet the RPO and RTO requirements.

To identify the blocking chain and the T-SQL statements involved, you should query sys.dm_exec_requests to get the blocking_session_id, join it with sys.dm_exec_sql_text to retrieve the SQL text using the sql_handle, and optionally join with sys.dm_exec_sessions for session details. This combination provides the blocking session, the blocked session, and the statements they are executing, giving a complete view of the blocking scenario.

The avg_log_write_percent column measures log write throughput as a percentage of the database's provisioned limit. At 95%, the database is approaching its log write IOPS ceiling, risking throttling. Scaling up the service tier or SKU raises that limit, while optimising log-heavy operations reduces demand, directly addressing the sustained near-saturation constraint.

Zone-redundant configuration replicates the database across multiple availability zones within the region, so a single zone failure leaves a replica serving traffic. This directly satisfies the stem's requirement for continuity during one availability zone outage.

ASR's default crash-consistent snapshots capture the disk state without flushing in-memory writes or quiescing the database, so a recovered SQL Server may need crash recovery and appear inconsistent. Enabling application-consistent snapshots, via VSS, produces a transactionally consistent recovery point.

Always Encrypted with the column master key in Azure Key Vault encrypts SSNs at rest and in transit, and only client applications with key access can decrypt them. Azure SQL Database never sees plaintext, satisfying the authorised-client constraint.

READ COMMITTED SNAPSHOT uses row versioning in tempdb, so readers don't take shared locks on data pages. This removes the shared-versus-exclusive lock conflicts that cause deadlocks during concurrent flash-sale transactions, satisfying the requirement to minimise deadlock occurrences.

Elastic job target groups can include an elastic pool as a target. When the job runs, the agent enumerates all databases in the pool at that time, so databases added later are automatically included. This satisfies the requirement to run on all current and future databases without manual updates.

Azure SQL Database allows you to change the backup storage redundancy setting for a database to geo-redundant storage, which replicates backups to a paired region. This change can be made without taking the database offline and directly meets the compliance requirement. It is the supported method to alter where backups are stored.

Creating contained database users mapped to Microsoft Entra ID groups lets role membership flow from group assignment, so permissions are granted automatically as users join or leave groups. This satisfies the requirement for automatic role assignment without per-user manual grants.

Zone redundancy is the Azure SQL Database feature that automatically places compute and storage replicas across multiple availability zones inside the region. Enabling it requires no connection string changes because the same logical server endpoint is used. It protects against a single availability zone outage, which is exactly the requirement here.

Elastic Database Jobs run T-SQL against a target group of databases, so a statistics-update script can execute across many Azure SQL databases from one job definition. This satisfies the stem's requirement for cross-database administrative scripting.

The query filters on CustomerID and returns a small number of rows, but the optimizer chooses a clustered index scan, likely because the nonclustered index on CustomerID does not cover the query and key lookups would be expensive. A covering index includes all columns referenced by the query (in the key or INCLUDE clause), eliminating key lookups and enabling an index seek. This reduces IO and CPU, improving performance for this frequent query.

A failover group with a non-readable secondary in East US satisfies every constraint: geo-replication delivers an RPO well under 5 minutes, automatic failover meets the 1-hour RTO without manual intervention, and setting the secondary to non-readable prevents any read traffic from reaching it, protecting primary performance.

RESOURCE_SEMAPHORE waits occur when queries cannot obtain a memory grant because concurrent queries have consumed the available workspace memory. At 100 DTUs the General Purpose tier caps memory, so peak-hour concurrency triggers the intermittent slowdowns.

On Azure SQL Managed Instance, SQL Server Agent is available and you can create jobs using T-SQL stored procedures in the msdb database or via SQL Server Management Studio. The agent runs automatically, so you simply define the job, steps, and schedule. This is the correct method to automate T-SQL scripts on a schedule.

Disabling auto-pause keeps the serverless database continuously active, eliminating the cold-start resume latency that delays the first query. This directly satisfies the requirement to improve first-query performance after idle periods, though it forfeits the cost savings auto-pause provides during inactivity.

Azure DevOps Pipelines can integrate with source control to automatically build and deploy a DACPAC (Data-tier Application Package) to Azure SQL Database. The DACPAC contains the schema and can be deployed using SqlPackage.exe. This supports version control, continuous integration, and continuous deployment, making it ideal for repeatable multi-environment deployments. It aligns with DevOps practices and automates the entire process.

PAGEIOLATCH_SH waits occur when sessions stall reading pages from storage into the buffer pool, so the first suspect is storage throughput. Checking database-level IOPS or throughput limits identifies whether the tier is throttling reads during the degradation windows.

Microsoft Entra ID authentication requires a Microsoft Entra administrator assigned at the server level, since that identity governs directory-based logins for every database on the server. Without this administrator, contained database users cannot be created or authenticated.

Streaming 'AllMetrics' captures the platform metrics Azure SQL Database emits natively — CPU percentage, data IO and log IO — without enabling expensive SQL Insights or query store overhead. Diagnostic settings route these directly to the Log Analytics workspace, satisfying the long-term analysis and cost-effectiveness constraints. Basic and Instance metrics tiers are excluded, keeping ingestion charges minimal.

Adaptive joins let the optimiser defer the hash-versus-nested-loops decision until after the first input is scanned, choosing based on actual row counts. This corrects poor join choices caused by inaccurate cardinality estimates at compile time.

High total_logical_reads means the query is pulling many 8 KB pages from the buffer pool, indicating it scans more data than necessary. This points to missing or ineffective indexes forcing large scans, satisfying the stem's constraint of diagnosing poor query performance.

PITR retention governs the point-in-time restore window, so 7 days meets the 2-hour PITR requirement for the last week. LTR policies separately retain yearly backups for 5 years, satisfying the stem's long-term retention constraint without extending PITR.

Contained database users live inside each database, so a tenant admin granted db_owner there cannot reach other databases on the same logical server. Microsoft Entra authentication supplies the identity, and no server-level login is created, preserving tenant isolation.

Azure Monitor metric alerts evaluate platform metrics such as DTU consumption against thresholds over a defined window, satisfying the 80% for 10 minutes condition. DTU percentage is emitted automatically as a metric, so no diagnostic logging or query is needed, unlike Log Analytics-based alerts.

Azure SQL Database long-term retention (LTR) backup policy extends automated backups to up to 10 years, satisfying the requirement for retention beyond the default 7–35 days. LTR stores full backups in Azure Blob storage as read-only copies, independent of the standard retention window, with no manual intervention needed.

Updating statistics gives the query optimiser accurate cardinality estimates, so it can choose index seeks or better join orders instead of scanning large tables. This directly addresses the stem's table-scan symptom while remaining within the existing General Purpose tier, satisfying the no-added-cost constraint.

Server-level firewall rules apply to the logical server and therefore to every database it hosts, so the addresses are managed in one place rather than per database. Defining a separate rule per application restricts each to its own ranges. Because the firewall denies traffic that does not match a rule, other addresses cannot reach the database.

Zone redundancy in the General Purpose service tier replicates the database across multiple availability zones within the same region. If one zone fails, the database remains available with automatic failover to another zone. This meets the requirement for high availability within a region with minimal downtime and no application changes.

Zone redundancy for Business Critical Azure SQL Database automatically distributes replicas across multiple availability zones, so if one zone fails, the database remains online with no manual intervention. It requires no application changes and is enabled with a single setting, meeting the minimal-effort requirement. This is the correct approach for zone-level high availability.

Dynamic data masking hides the column values from users without the UNMASK permission while leaving the data intact. Granting UNMASK to the payroll user lets that application see real values, and ad hoc users without UNMASK see masked output, satisfying both requirements without altering stored data.

AutoPauseDelayInMinutes sets the inactivity period before serverless compute auto-pauses, so the intermittently used development database stops incurring compute charges while remaining available on demand. This directly satisfies the cost-effectiveness requirement without deleting or reprovisioning the database.

A nonclustered index stores the WHERE-clause key values in a separate B-tree structure, letting the engine seek directly to matching rows instead of scanning every page. This sharply reduces the pages touched per execution, directly lowering logical reads — the metric the stem asks you to minimise.

Sustained log write percent above 90% signals the transaction log is saturating its provisioned throughput, a resource-bound bottleneck that scaling up the service tier or compute size directly relieves. This metric isolates log I/O pressure rather than general CPU load.

To achieve an RTO of less than 30 seconds for a zonal failure, zone redundancy is required. Since West US 2 does not support availability zones, migrating to a supported region and enabling zone redundancy is the only way to meet the requirement with minimal administrative effort. This provides automatic failover within the region.

TDE with customer-managed keys allows you to use your own key stored in Azure Key Vault. You can configure automatic key rotation by setting up a key rotation policy in Key Vault and enabling the TDE protector to automatically use the latest key version. This meets both encryption and key management requirements.

Contained database users mapped to Microsoft Entra ID service principals let each application authenticate independently, with no shared server-level login. Storing each client secret in Azure Key Vault enables per-application rotation without affecting others, and Key Vault's rotation policies satisfy the automatic credential rotation requirement.

Disabling dropIndex prevents automatic tuning from dropping indexes while leaving other tuning options active. This satisfies the requirement to stop the specific action causing slow queries while still benefiting from automatic tuning for index creation and plan regression correction.

Service endpoint secures traffic to Azure SQL from your VNet without a public IP.

By default, users without UNMASK see masked data. To ensure the Reporting role sees masked values, you must confirm that the role does not possess UNMASK, either directly or through role membership. If the role or its members had UNMASK, they would see the unmasked data, defeating the purpose. Thus, verifying and, if necessary, revoking UNMASK from the Reporting role is required.

Azure SQL Managed Instance requires a dedicated subnet delegated to Microsoft.Sql/managedInstances. The delegation allows Azure to inject instance resources into the subnet. An NSG is required to control traffic, and specific ports must be open for management and connectivity. This configuration enables communication with other Azure resources and on-premises systems via VPN.

Automatic failover only triggers once the grace period elapses, and 3600 seconds equals 60 minutes. The 30-minute outage falls short of that threshold, so Azure SQL Database withholds failover to avoid potential data loss, satisfying the stem's grace-period constraint.

TDE with BYOK requires the logical server to access the key. The server's system-assigned managed identity must be granted the Key Vault Crypto Service Encryption User role, and the vault must have purge protection enabled to prevent accidental key deletion. This is the prerequisite before configuring the key as the TDE protector.

Logging to an Azure Storage account supports retention policies of arbitrary length, unlike Log Analytics, which caps retention at two years. Setting the retention policy to 2,555 days (7 years) on the storage account therefore satisfies the compliance requirement while capturing all DDL schema changes through database-level auditing.

The Business Critical service tier in Azure SQL Managed Instance uses locally attached SSD storage and includes a built-in secondary replica for high availability. This provides the lowest I/O latency and highest availability, making it suitable for I/O-intensive workloads. It also supports all the required features such as cross-database queries, SQL Server Agent, and Service Broker.

GitHub Actions orchestrates pipeline jobs, and the Azure SQL Database deployment action connects to the server and applies schema changes via SqlPackage. This satisfies the CI/CD automation constraint by executing DACPAC or SQL script deployment against Azure SQL Database on each pipeline run.

Planned failover via Azure CLI or PowerShell performs a controlled switchover with zero data loss, letting you schedule it inside the maintenance window. It gracefully drains the primary before promoting the secondary, unlike an unplanned forced failover.

Azure SQL Database enforces TLS 1.2 by default, but the logical server setting lets you explicitly require a minimum TLS version. Configuring this at the server level applies to all databases on that server and requires no application changes or client certificate management, satisfying the requirement with minimal effort.

Elastic Database jobs in Azure SQL Database allow you to run T-SQL scripts against a target group of databases on a schedule. They are a built-in feature of Azure SQL Database and do not require an external server or agent. This makes them ideal for automating administrative tasks like index maintenance and statistics updates across one or many databases.

SQL Agent Jobs are the native scheduling mechanism in Azure SQL Managed Instance, supporting recurring T-SQL steps such as executing a stored procedure hourly. Unlike Azure Automation or Elastic Jobs, it runs in-instance with no external orchestrator, satisfying the hourly cleanup requirement directly.

Exporting the TDE certificate and private key to a .pfx, restoring it to the managed instance, then restoring the backup preserves encryption because the instance holds the same protector. This satisfies the stem's requirement to migrate while preserving TDE.

In the General Purpose service tier, the maximum log write throughput scales with the number of vCores. Increasing from 4 to 8 vCores doubles the log write throughput limit, directly addressing the bottleneck without changing the service tier. This is the correct action because the scenario specifies that the service tier must remain the same, and scaling vCores is the only way to increase log throughput within General Purpose.

The high log write percentage indicates that the database is approaching the log write throughput limit for its current service tier and compute size. Scaling up increases the log write rate limit, directly addressing the bottleneck. Since CPU and data IO are low, the workload is log-write-bound, so a higher tier or more vCores will provide more log throughput and improve report performance.

Configuring the logical server's Minimum TLS version to 1.2 rejects any client handshake negotiating TLS 1.0 or 1.1, directly satisfying the requirement that all connections use TLS 1.2 or higher. This server-level setting enforces the constraint across every database on that server.

Azure Defender for SQL provides threat detection and anomalous access alerts, while SQL Auditing writes query activity to a storage, Log Analytics, or Event Hub target. Together they satisfy both the detection-and-alert and query-logging compliance requirements.

A security policy with a predicate function that filters on the tenant ID column against the current database principal ID enforces row-level security directly in the database engine. Because the filter is applied to every query automatically, tenants cannot see or infer other tenants' rows, and no application changes are required. This matches the least-effort requirement while meeting the strict isolation goal.

For a managed identity to access Azure SQL Database, you must create a contained database user that represents the identity and then assign permissions. The user is created with the FROM EXTERNAL PROVIDER clause, which maps the identity to a database principal. Without this step, the managed identity can obtain a token but will not have a database user, so authentication will fail at the database level.

Embedding the storage account access key in the ARM template exposes a full-privilege credential in plaintext, readable by anyone with access to the template, deployment history or source control. This grants unrestricted access to the audit storage account, undermining the audit trail's integrity.

In-memory OLTP stores hot tables and their indexes in memory-optimised structures, so transactional changes are applied without conventional page-latch logging. This directly reduces the volume of log records generated per transaction, lowering WRITELOG waits caused by log-write throughput saturation on the target Azure SQL Database.

This enforces encrypted connections.

SQL Server on Azure Virtual Machine provides full SQL Server engine, including native SQL Server Agent, cross-database queries, and independent scaling of compute (via VM size) and storage (via managed disks). Minimal application changes needed. Correct.

Locally redundant backup storage replicates backups three times within a single region, providing durability within that region at a lower cost than geo-redundant or zone-redundant options. This meets the requirement for cost-effectiveness and single-region durability. It is the default and most economical choice for backup storage redundancy.

Elastic Database Jobs run T-SQL on a defined target group, so scheduling index rebuilds across a set of databases is a supported maintenance scenario. This satisfies the stem by automating recurring index maintenance without per-database scripting.

Writing audit logs to a storage account that has a time-based immutability policy enforces write-once, read-many retention for the specified period, satisfying the seven-year requirement. Auditing captures the caller identity for SELECT statements against Ledger, and the immutability policy prevents deletion or alteration during the retention window.

Auto-failover groups support Hyperscale service tier databases and provide a read-write listener endpoint that remains constant across failovers. This allows the application to continue using the same connection string after the secondary is promoted. The failover group also manages the replication and supports automatic failover, minimizing data loss and administrative effort. This is the correct solution for the scenario.

To use customer-managed keys for TDE, you must first create an Azure Key Vault, generate or import a key, and then grant the logical server's managed identity permissions to access that key. This establishes the trust relationship needed before you can configure the database to use the key for encryption. Without this step, the server cannot retrieve the key to encrypt or decrypt the database.

A system-assigned managed identity gives the Azure SQL logical server an identity in Microsoft Entra ID, which Key Vault access policies and key wrapping require. Without it, the server cannot authenticate to the vault to unwrap the customer-managed TDE protector.

Using Microsoft Entra authentication with a managed identity eliminates the need for credentials in code. The App Service's system-assigned managed identity is used to authenticate to the database. Creating a contained database user for that identity and granting db_datareader role provides read-only access with minimum permissions, meeting all requirements.