Microsoft Azure Database Administrator Associate DP-300 (DP-300) — Questions 526–574

A point-in-time restore or database copy requires the source database to be online and recoverable. Because db1 is in a failed state from a storage issue, the control-plane operation cannot read its backups, so the ARM deployment returns an error rather than creating db2.

The cmdlet sets a customer-managed key as the server's TDE protector, satisfying the requirement to move encryption key custody from Microsoft-managed to your own Azure Key Vault. The protector must reside in the same region as the server, and the key's permissions grant the server access for wrap and unwrap operations.

Query Performance Insight surfaces the top CPU, duration and DTU-consuming queries, letting you tune indexes or rewrite statements to cut DTU usage. Scaling the tier would mask the problem rather than reduce consumption, so identifying the offending queries comes first.

Query Store captures missing index recommendations from actual workload history, letting you add indexes that eliminate the large table scans. This directly targets the scan bottleneck and is the cheapest first action, unlike scaling the General Purpose tier or rewriting queries blindly.

Microsoft Entra ID authentication centralises identity in a directory that enforces multi-factor authentication, conditional access and passwordless methods, satisfying the stem's demand for the strongest security. Unlike SQL logins, credentials are not stored in the database, and tokens are issued per user, enabling auditing and revocation.

The serverless compute tier automatically scales compute resources based on workload demand and pauses the database during inactive periods, billing only for storage. This directly addresses unpredictable bursts by scaling up during activity and scaling down or pausing when idle, minimizing cost. It requires no manual intervention and is ideal for intermittent, unpredictable workloads.

TDE with customer-managed keys encrypts backups at rest under your Key Vault key, and the system-assigned managed identity must hold get, wrapKey, and unwrapKey so the instance can unwrap the key for backup encryption and decryption operations.

Query Store captures query execution statistics, including CPU time, duration, and execution count. The Top Resource Consuming Queries report in the Azure portal or via Query Store catalog views ranks queries by resource consumption, making it ideal for identifying which queries are driving high CPU usage. This directly addresses the need to pinpoint CPU-intensive queries.

TDE with a customer-managed key in Azure Key Vault allows your organization to control and revoke the encryption key. This satisfies the requirement for data encrypted at rest with organizational control. It is the standard method for achieving customer-managed encryption in Azure SQL Database, and it integrates with Key Vault for key management and revocation.

Schema-scoped EXECUTE permission on the Sales schema lets AppUser run stored procedures without SELECT rights on base tables, satisfying least privilege. Ownership chaining means the procedures access tables under the schema owner's permissions, so no direct table grants are needed.

sys.dm_exec_query_stats aggregates execution counts, CPU time and logical reads per cached plan, so ordering by total resource consumption reveals the heaviest queries. This satisfies the requirement to identify which queries drive the elevated DTU usage on the Azure SQL Database.

To enable Microsoft Entra ID authentication for Azure SQL Database, you must first assign a Microsoft Entra ID admin at the server level. This admin can then create contained database users mapped to Microsoft Entra identities. Once set, clients can authenticate using Microsoft Entra ID, and MFA can be enforced through Conditional Access policies. This is the foundational step for Entra ID authentication.

Data Migration Assistant (DMA) is the recommended tool to assess on-premises SQL Server databases for migration to Azure SQL Managed Instance. It identifies compatibility issues, unsupported features, and provides recommendations. For a database using cross-database queries and SQL Server Agent, DMA will verify that these features are supported in Azure SQL Managed Instance and highlight any necessary changes before migration, ensuring a smoother process.

Azure Monitor provides built-in metrics for Azure SQL Database, including cpu_percent. You can create an alert rule that triggers when the average cpu_percent exceeds 80% over a 10-minute window. This is the most direct and least administrative effort because it uses platform metrics and the Azure Monitor alerting framework, with no need to write custom queries or deploy additional components.

PAGEIOLATCH_SH waits occur when queries read data pages from storage. Adding appropriate indexes, such as covering indexes, can reduce the number of pages that must be read to satisfy a query, thereby decreasing physical IO and the associated waits. This is a targeted optimization that addresses the query workload itself. It is especially effective when queries perform scans that can be converted to seeks with the right indexes.

Correct. Azure Automation runbooks can be scheduled to run monthly, use the Az.Sql module to perform planned failover and failback, and log results to a table. Although not entirely self-contained, it is the only viable option.

A job step is the unit that holds the T-SQL to be executed by the elastic job agent. After creating the job, you add one or more job steps, each with a command and a target group. This directly meets the requirement without external schedulers, because the agent handles scheduling and execution against the target database.

Log write throughput is capped by the General Purpose tier, so throttling stems from excessive log generation. Batching transactions and reducing log writes lowers the log rate itself, resolving throttling without the cost of moving to Business Critical.

The ALTER AVAILABILITY GROUP ... FAILOVER command performs a manual failover without data loss when the target secondary is synchronized. It is the correct command to use on the secondary replica that you want to become the new primary, ensuring no data loss because the secondary is synchronous and up to date. This meets the requirement for a planned failover without data loss.

Intelligent Insights identifies regressions caused by query plan changes, and automatic tuning can apply the corrective plan or index fix without manual intervention. Implementing recommendations first restores performance directly, satisfying the requirement to resolve the degradation before deeper investigation.

Azure SQL Database serverless automatically scales compute based on workload demand and can pause, scaling compute to zero during inactivity, billing only for storage while paused. This satisfies both constraints: no manual intervention for variable e-commerce traffic, and cost savings through zero-compute scaling when the database is unused.

This is correct because Azure Automation provides a native scheduler for PowerShell runbooks, and Export-AzureRmSqlDatabase creates a BACPAC file (a transactionally consistent backup) in Azure Storage. The cmdlet orchestrates the export through the Azure SQL Database management plane, ensuring a consistent snapshot of the live database. Running nightly via schedule meets the backup requirement without manual intervention, making it the simplest and most reliable option for automated database export.

Server-level auditing in Azure SQL Database captures the identity, statement and timestamp of every data modification and can stream audit records directly to a Log Analytics workspace, satisfying the requirement to record who changed what and when for compliance analysis.

Geo-restore uses the latest geo-replicated backup, which is typically no older than one hour. You cannot choose an arbitrary point in time; you restore to the most recent available geo-replicated backup. This is a key limitation for disaster recovery when using geo-restore for Azure SQL Database, as opposed to point-in-time restore within the same region.

Azure RBAC roles such as Reader apply to the control plane, governing resource management rather than T-SQL connectivity. Data plane access requires a contained database user mapped to a Microsoft Entra ID login or an external provider, plus database-level permissions such as db_datareader.

Microsoft Defender for Cloud's advanced threat protection detects anomalous access patterns and brute-force attempts against Azure SQL Managed Instance, raising alerts for suspicious logins and potential SQL injection. This satisfies the defence-in-depth requirement by adding a detective control layer beyond authentication and network isolation, enabling rapid response to compromised credentials or exploitation attempts.

Azure SQL Analytics consumes the database's resource-usage telemetry, including storage consumption, and surfaces it in Azure Monitor with historical trending. This satisfies the requirement to track storage space usage over time rather than only viewing a current value.

This view captures wait statistics aggregated by query and plan, including wait categories and total wait time. It shows whether the query is waiting on CPU, I/O, locks, or other resources. Since the user needs to distinguish CPU pressure from resource waits, this is the correct source. It provides the necessary wait data to make that determination.

Azure Automation provides a serverless way to run PowerShell scripts on a schedule. The New-AzSqlDatabaseExport cmdlet initiates a BACPAC export to Azure Blob Storage. This requires minimal development effort, as the cmdlet handles the export, and Automation manages the schedule and execution. It is an Azure-native solution that directly meets the requirement.

Always Encrypted ensures that data is encrypted at the client and never revealed to the database engine. By storing the column encryption keys in Azure Key Vault and not granting DBAs access to the keys, DBAs cannot decrypt the data even if they have full database permissions. This satisfies the requirement to prevent DBAs from viewing or modifying sensitive data.

Incremental mode deploys resources without deleting existing ones, so re-running the same ARM template leaves unchanged resources intact and creates only missing ones. This satisfies the idempotency requirement, whereas Complete mode would delete resources absent from the template, breaking repeated deployments.

A failover group replicates databases to a secondary server that can be opened read-only, letting reporting workloads query the secondary without impacting the primary. This satisfies the requirement for an offload reporting target while retaining failover capability.

In the Business Critical tier, zone redundancy replicates compute and storage across multiple availability zones. During planned maintenance, Azure performs a rolling upgrade, and the built-in high availability architecture automatically fails over to a healthy replica with no data loss and minimal downtime. This is the correct configuration to meet the stated requirements.

Elastic Database Jobs run T-SQL against every database in the target group from a single job agent, satisfying the centralised, low-touch requirement across 100 databases in 10 pools. Per-database execution isolates failures, so one database erroring does not halt the others, and built-in retry logic plus job history logging meet the resilience and centralised logging constraints.

Server-level and database-level firewall rules filter inbound connections by source IP address at the Azure SQL gateway, permitting only the listed on-premises ranges while blocking all other public traffic. This directly satisfies the requirement to restrict the public endpoint to specific on-premises addresses.

sys.dm_exec_query_stats aggregates cumulative execution statistics per cached query plan, including total worker time, so ordering by CPU time over the last hour identifies the top CPU-consuming queries. It satisfies the need to rank queries by CPU consumption.

Dynamic Data Masking applies masking rules at query time, so non-privileged users receive only the last four digits of the credit card column while CreditCardViewer role members see full values. This satisfies the requirement without altering stored data or duplicating the column.

Performing a manual failover to a synchronous secondary ensures no data loss because the secondary is synchronized, and it moves the primary role away from the node being patched. This allows you to patch the former primary while the availability group remains online and the application continues to use the listener. It is the standard first step for patching with minimal downtime and no data loss.

Azure Monitor provides platform metrics for Azure SQL Database, including CPU percentage. You can create a metric alert that evaluates the CPU percentage metric over a 10-minute window and triggers when the average exceeds 90%. This is the standard and recommended way to set up such alerts, as it integrates with Azure Monitor and supports actions like email or webhook notifications.

PAGEIOLATCH_SH waits indicate sessions waiting on data pages read from storage, typically caused by scans. Adding appropriate indexes reduces table scans, cutting physical I/O and shortening those waits at their source rather than masking symptoms through scaling.

Auditing to a Log Analytics workspace centralises all database activity for compliance analysis, while Microsoft Sentinel ingests those same logs to provide real-time analytics rules and alerts on suspicious activity, satisfying both the centralised logging and real-time alerting constraints.

Creating a contained database user from an external provider maps the Microsoft Entra identity into the database, which must happen before any role membership or permission grant. The db_owner connection is required because CREATE USER demands ALTER ANY USER permission, which the nurse's account does not yet hold.

Azure SQL Database supports TDE with customer-managed keys stored in Azure Key Vault. By configuring the logical server's TDE settings to use a customer-managed key, you enable encryption at rest with your own key. Key rotation is supported by creating a new key version in Key Vault and updating the server configuration, with no downtime. This meets all requirements.

Microsoft Entra ID authentication supports service principals, which are non-interactive identities used by applications. This satisfies the scenario's need for a supported authentication method, since the service principal authenticates via token rather than a stored SQL password, integrating with Microsoft Entra ID directory-based identity management.

In the Full recovery model, the transaction log cannot be truncated until all transactions are committed and log records are backed up or replicated. Long-running transactions or replication delays hold log records, preventing truncation and causing growth. Resolving these issues allows the log to truncate normally. This is the correct approach because it addresses the root cause without compromising recovery capabilities.

The General Purpose tier uses remote storage with locally attached compute and maintains a built-in standby replica. During planned hardware patching, Azure performs automated failover to that standby, keeping the database online with minimal downtime, satisfying the availability requirement without manual intervention.

A failover group provides an auto-failover endpoint and a readable secondary, satisfying the automated failover and read-access requirements. The secondary instance in North Europe also supports backups, meeting the cross-region disaster recovery constraint. Geo-replication alone lacks the automatic failover that failover groups deliver.

Setting a Microsoft Entra ID admin on the logical server is the prerequisite that enables Entra authentication; only after this can you create contained database users and remove SQL logins, satisfying the mandate that no SQL authentication exists.

Prevents accidental key loss.