Microsoft Azure Database Administrator Associate DP-300 (DP-300) — Questions 76–150

Zone redundancy for Azure SQL Database General Purpose tier spreads the storage and compute replicas across multiple availability zones within the same region. This provides high availability if a single datacenter (availability zone) fails, and the failover is automatic without application changes. It meets the requirement of surviving a datacenter failure within the region while keeping the application connection string unchanged.

Auto-failover groups provide automatic failover for one or more databases, with an RPO of about 5 seconds and an RTO of about 1 hour. They also allow read-write and read-only listener endpoints, simplifying connection management. This meets the RPO and RTO requirements with minimal administrative effort because failover is automatic and configured at the group level.

Correct: The grace period adds to RTO.

Auto-failover groups replicate the Business Critical database to a secondary server in another region and permit promotion during a regional outage. This satisfies the minimal-data-loss constraint, since replication is continuous and failover preserves committed transactions.

PAGELATCH_EX waits on tempdb allocation pages (such as PFS, GAM, SGAM) occur when many concurrent sessions allocate space in tempdb. Adding more tempdb data files distributes the allocation load across multiple files, reducing contention on allocation pages. This is a standard remedy for tempdb allocation bottlenecks. Since the service tier cannot change, adding files is the appropriate action to alleviate the contention without scaling up.

A DTU-based elastic pool with 200 DTUs (eDTUs) is the correct choice because it gives 50 small-to-medium databases a shared pool of compute and storage resources, letting them absorb bursts by using spare capacity from quiet databases. The DTU purchasing model bundles compute, storage, and backup into a simple, cost-effective unit; 200 DTUs typically sustains around 200 MB/s of I/O and enough CPU/GPU resources (approximately 4 vCore-equivalent) for variable workloads. Because you pay only for the pool's aggregate DTUs, not per-database DTUs, this is the most economical way to serve 50 databases with fluctuating demand.

Set-AzSqlDatabase modifies properties of an existing Azure SQL Database, including the requested service objective and edition via parameters such as -RequestedServiceObjectiveName. Using it with the managed identity's authenticated Az context changes the tier without recreating the database, which is exactly what the nightly scaling runbook requires.

Azure Database Migration Service online migration keeps the source SQL Server 2019 database available whilst continuously replicating changes to Azure SQL Managed Instance, cutting cutover downtime to a brief final sync. SQL Managed Instance supports Service Broker, satisfying the compliance constraint that the target retain cross-database messaging.

The secondary must be synchronized to ensure no data loss.

Auto-pausing after inactivity and resuming at a minimum 0.5 vCores are exclusive to the serverless compute tier. Provisioned and hyperscale tiers never auto-pause, so the database must be serverless for the configured behaviour to take effect.

Azure Monitor metric alerts evaluate platform metrics such as CPU percentage on a near-real-time basis and fire an action group that invokes the Azure Automation runbook via a webhook. This satisfies the stem's requirement to monitor CPU usage and trigger scaling automatically, unlike log-based alerts which add query latency.

Correct. The DMVs only contain data for successful logins, so the query (if it were valid) would return information about active sessions, which are successful logins.

Index Spool (Lazy Spool) indicates the optimiser repeatedly rewinds intermediate results because no suitable index supports the joins and predicates. Creating indexes on the join and filter columns gives the engine seek access, eliminating the spool and reducing the millions of rows scanned.

FORCE_LAST_GOOD_PLAN is an automatic tuning feature in Azure SQL Database that monitors for plan regressions and automatically forces the last known good plan when a regression is detected. It is specifically designed for the scenario where a query becomes slower due to a plan change, which matches the reported symptom.

In Azure SQL Database, you can create contained database users that map to Microsoft Entra ID identities, including groups. By creating a user for the Entra ID group and adding it to the db_owner role, all members of that group inherit db_owner permissions. This is the recommended approach for managing permissions for Entra ID groups without server-level logins.

Auto-failover groups for Azure SQL Managed Instance support a readable secondary in a paired region and allow manual or automatic failover. They provide a listener endpoint and built-in replication, minimizing administrative overhead. This is the native DR feature for managed instances and meets the requirement for a readable secondary and manual failover capability.

The failover group listener provides a stable read-write DNS endpoint that redirects clients to the current primary after failover. Applications keep one connection string, satisfying the no-change requirement, while the listener abstracts the underlying instance swap between regions.

The Serverless compute tier automatically pauses the database after a configured idle period and resumes it when a new connection arrives. This directly matches the requirement to minimize cost for a database that is used only during business hours. General Purpose with Serverless also allows a minimum and maximum vCore range so that compute scales with demand while idle time is not billed.

Dynamic Data Masking applies a masking function to the column so non-admin users see obfuscated values at query time, while privileged users retain full data. This satisfies the requirement to automate masking for non-admin users without altering stored data or application code.

Query Store automatically captures query plans and runtime statistics, and its Regressed Queries view compares plan performance over time to highlight queries whose plan changed and became slower. Because the feature is built into the database, no external monitoring tool is required, and it directly answers which queries regressed after the statistics update.

Auto-failover group with a readable secondary uses asynchronous replication but provides automatic failover. You can configure a grace period to minimize data loss, making it the best option for minimal data loss and automatic recovery.

TDE with customer-managed keys allows you to use your own key stored in Azure Key Vault, giving you full control over key lifecycle, including rotation and revocation. It encrypts data at rest and is the standard method for meeting regulatory requirements for independent key management. This approach also integrates with Azure Key Vault for centralized key management.

Scaling up raises the provisioned compute ceiling, so the same workload consumes a smaller proportion of available DTUs. This directly satisfies the stem's constraint of sustained consumption above 90% during peak hours, where the database is genuinely resource-bound rather than suffering from inefficient queries or missing indexes.

Elastic Jobs are a built-in Azure SQL Database feature designed to automate T-SQL execution across one or more databases on a schedule. You configure a job with a step containing the index rebuild script, set a schedule for 2:00 AM, and the service handles execution without needing a VM or external orchestrator. This directly meets the requirement with minimal overhead.

Runbook can automate planned failover without data loss.

Automatic plan correction detects regressions where a previously good plan is replaced by a worse one, then forces the last known good plan using FORCE_LAST_GOOD_PLAN. This directly satisfies the requirement to automatically force a better-performing plan, which plain automatic tuning options such as CREATE INDEX or DROP INDEX do not provide.

Azure AD-only authentication disables SQL authentication at the logical server level, so only Microsoft Entra ID principals can connect. This enforces the policy that no SQL authentication users are created, meeting the stem's authentication constraint.

Automatic failover on the readWriteEndpoint triggers only after the configured grace period elapses without primary availability. With 60 minutes set, the group waits that duration before promoting West Europe, so the outage must persist the full hour.

The storage_percent metric in Azure Monitor for Azure SQL Database reports the percentage of storage space used relative to the maximum data size. Setting an alert on this metric at 90% directly meets the requirement. This metric is specific to Azure SQL Database and is the correct signal to monitor storage utilization.

The vCore model with General Purpose service tier supports the serverless compute tier, which automatically scales compute based on workload demand and can pause the database during inactive periods, billing only for storage. This directly addresses unpredictable usage and cost optimization, while accepting a brief reconnection delay when resuming from a paused state, exactly as the scenario permits.

Query Store captures execution plans and runtime statistics, letting you identify the regressed plan causing high CPU and force a superior one via a plan guide. This directly addresses the stem's constraint: statistics and indexes are already refreshed, so the remaining lever is plan choice, not stale metadata.

Minimal logging reduces log space for large imports under full recovery model.

Serverless with auto-pause suspends compute after inactivity and resumes on connection, so SalesDB only incurs compute cost during the 8 AM to 6 PM window. Storage charges persist, but idle overnight and weekend compute is eliminated.

The Business Critical tier uses local SSD storage and has a significantly higher maximum log rate compared to General Purpose. This directly addresses the log write wait issue by providing faster log throughput. While it increases cost, it is the correct action when the log rate is the bottleneck and needs to be raised beyond the General Purpose limit.

An auto-failover group provides a read-write listener endpoint that automatically redirects connections to the secondary server after a failover. It supports automatic failover based on a policy, ensuring that the application can reconnect without manual intervention. This directly satisfies the requirement for continued availability during a regional outage with automatic redirection. It also allows for readable secondaries and can include multiple databases.

Query Store persists execution plans, runtime statistics and wait categories per query, letting you rank statements by CPU, duration or logical reads. This directly identifies the queries driving high DTU consumption on the Azure SQL Database.

Automatic tuning detects plan regressions by comparing query performance over time and forces the last known good plan, restoring throughput without manual intervention. This directly addresses the regression-correction benefit, distinct from index creation or parameter forcing.

A planned failover in the failover group completes synchronisation before switching roles, guaranteeing zero data loss — the stem's explicit requirement. Because the secondary is already synchronised via the failover group, the portal operation drains remaining transactions, promotes West US to primary, and demotes East US, satisfying the maintenance scenario without data loss.

Elastic Database Jobs are a native Azure SQL Database feature that can run T-SQL scripts against one or many databases on a schedule. They require no on-premises infrastructure and minimal setup: create a job agent, a job, a step, and a schedule. This directly satisfies the requirement to run a T-SQL script daily with low overhead.

An elastic pool shares provisioned eDTUs or vCores across many single-tenant databases, so each tenant keeps its own database for isolation while aggregate resources absorb unpredictable per-tenant load spikes. This satisfies the predictable-performance and cost-efficiency constraints, since you pay for pooled capacity rather than peak provisioning per database.

Azure SQL Database auditing can be enabled at the server or database level and configured to write to Azure Storage, Log Analytics, or Event Hubs. Setting retention to 90 days in the storage account meets the requirement. This is the simplest way to audit all user activity and retain logs for the specified period.

Always Encrypted is designed to protect sensitive data from high-privileged users like DBAs. The client driver encrypts and decrypts data, so the database engine never sees plaintext. Storing the column master key in Azure Key Vault meets the key storage requirement. The application requires minimal changes: it needs to use a supported client driver with Always Encrypted enabled and connection string adjustments.

FORCE LAST GOOD PLAN automatically reverts a query to its previous good execution plan when a regression is detected, and it is applied without approval once automatic tuning is enabled. It satisfies the stem's constraint of recommendations applied automatically.

SQL Agent is built into Azure SQL Managed Instance and runs T-SQL jobs on a schedule, so a nightly job at 2 AM executes the stored procedure natively with no external orchestrator or added latency.

To use a customer-managed key for TDE, you store the key in Azure Key Vault and set it as the TDE protector for the logical server. Key Vault supports rotation policies that automatically generate new key versions, and Azure SQL Database automatically uses the latest version if configured to do so, ensuring continuous access.

Database-level firewall rules apply only to the specified database, not the whole logical server. A server-level rule would grant the developers access to every database on that server, breaching the isolation requirement. Scoping the rule to 192.168.1.0/24 at database level satisfies the constraint that other databases remain unreachable.

Always Encrypted with the column master key in Azure Key Vault keeps keys outside the database engine, so encryption and decryption occur in the application driver. This satisfies the requirement that the application, not the database, manages encryption.

Elastic Database Jobs run T-SQL across every database in a pool from a job agent, using a target group rather than per-database connections. This satisfies the scheduled, pool-wide execution requirement, and job step output can be written to a logging table.

`sys.dm_db_file_space_usage` returns page counts per database file, split into allocated, unallocated and mixed-extent categories, letting you track space consumed within each data and log file. This directly satisfies the requirement to monitor storage space usage on the managed instance, since it exposes per-file consumption without relying on instance-level or host-level metrics.

Queries With High Variation highlights queries that have multiple execution plans with significantly different performance metrics. This directly addresses the scenario where a stored procedure sometimes runs fast and sometimes slow. From this report, you can drill into the query and force the better-performing plan, resolving the inconsistent performance.

The Business Critical service tier uses local SSD storage, which provides lower I/O latency compared to the remote storage used in General Purpose. This directly reduces PAGEIOLATCH_SH waits by improving I/O performance. Therefore, switching to Business Critical is the most effective action to address these waits.

Bicep is a declarative DSL that transpiles to ARM templates, letting you define the database, firewall rules, auditing, and threat detection policies once and redeploy identically across dev, test, and prod. Parameter files handle per-environment differences.

Conditional Access evaluates sign-in conditions and enforces authentication strength, so a policy targeting users outside the corporate network can require MFA. This satisfies the constraint of enforcing MFA specifically for external access, which SQL firewall rules alone cannot do.

ALTER PARTITION FUNCTION ... SPLIT adds the next month's boundary, while MERGE collapses the oldest one, keeping the monthly range current. Elastic Database Jobs run this T-SQL on a schedule without external compute, satisfying the automation requirement for Azure SQL Database, where SQL Agent is unavailable.

Replacing the 10-year policy with a 7-year monthly retention schedule satisfies the compliance requirement while eliminating three years of unnecessary stored backups. Long-term retention policies are configurable per database, so the change directly reduces storage consumption without affecting the mandated retention window.

avg_data_io_percent reports the percentage of the data-file IOPS limit consumed, directly exposing data disk saturation. Monitoring it identifies a disk IO bottleneck caused by read or write activity against data files, satisfying the stem's requirement in Azure SQL Database.

Azure SQL Managed Instance supports native BACKUP DATABASE TO URL, which writes backups directly to Azure Blob Storage. SQL Server Agent is available on Managed Instance, so scheduling a job with a T-SQL step is a native, low-code solution. This meets the requirement without external orchestration.

An action group specifies what happens when an alert fires, such as sending an email, SMS, or triggering a runbook. To respond to high CPU usage, you must associate an action group with the alert rule. Without an action group, the alert would only be visible in the Azure portal but would not perform any automated response.

SQL Server Audit with a server-level specification capturing SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP records both successful and failed login attempts. Server-level scope is required because login events occur at the server, not database, level in Azure SQL Database.

VNet service endpoints restrict connectivity to the specified subnet, while firewall rules permit the named public IP addresses. Together they satisfy the requirement that only that virtual network and those public IPs can reach the Azure SQL Database.

The command alters the database's service objective, moving it to the S2 performance level within its existing elastic pool or standalone tier. This immediately changes the compute and DTU allocation applied to OrdersDB, taking effect without recreating the database.

Azure SQL Managed Instance includes SQL Server Agent, which is fully supported. You can create a job with a T-SQL step that runs BACKUP DATABASE TO URL, and schedule it to run daily at midnight. This uses the built-in capabilities of the managed instance and does not require additional Azure services, minimizing cost and administrative effort.

Upgrading to Business Critical adds synchronous replicas within the region for local HA, but geo-replication remains asynchronous with ~1 hour RPO. It does not achieve sub-minute geo RPO.

WRITELOG waits occur when sessions wait for transaction log writes to complete. A high proportion indicates the log's I/O subsystem cannot keep pace with commit throughput, making slow log storage the direct cause rather than data file or CPU contention.

Fewer databases to recover reduces failover time.

sys.dm_exec_query_stats aggregates cumulative execution statistics per cached query plan, exposing total_worker_time as the CPU metric. Querying it ordered by total_worker_time DESC yields the top CPU-consuming queries, satisfying the requirement to rank queries by total CPU time over the last hour.

A CanNotDelete resource lock prevents deletion of the database and its parent resources regardless of RBAC permissions, directly satisfying the requirement to stop accidental deletion. It blocks delete operations while still permitting reads and writes.

Long-term retention writes backups to an Azure Storage account using a SAS token embedded in the ARM template. If that token has expired or is malformed, the storage account rejects access, producing exactly the accessibility failure described.

Storing the schema as a SQL project in Azure Repos Git provides version control. A build pipeline that generates a DACPAC artifact ensures that the schema is packaged for deployment. This, combined with a release pipeline, enables automated deployment and rollback to any previous version, fulfilling the requirements.

Blob auditing writes audit logs to the storage account using its access key; a null key means the database cannot authenticate to that account. Auditing therefore fails to record events, regardless of the subscription ID placeholder shown in the exhibit.

Hyperscale's architecture separates compute from a log-based storage layer that scales to 100 TB, far exceeding the 4 TB limit of General Purpose and Business Critical. This satisfies the large-storage benefit the question asks about.

Azure SQL Database automatically retains backups for point-in-time restore (PITR) for a default of 7 days, but you can configure it up to 35 days. Setting the PITR retention policy to 30 days ensures you can restore to any point within the last 30 days, directly meeting that requirement. This is a necessary configuration for the specified recovery window.

Setting the TDE protector to a new key version re-wraps the database encryption key with the new key material while the instance stays online. This is the supported rotation path for customer-managed keys and maintains continuous encryption, satisfying the annual rotation requirement.