DP-300 Azure SQL Database Administrator Practice Questions

Microsoft's best practice for SQL Server on Azure VMs is to place TempDB files on the local temporary disk (typically the D: drive). This drive is a non-persistent, high-performance SSD that is directly attached to the host node. It offers very low latency and high throughput, which is ideal for the volatile nature of TempDB workloads. Because the data on this drive is lost during events like VM resizing or host migration, it's unsuitable for user database files, but perfect for TempDB, which is recreated every time the SQL Server service starts.

The sql_statement_recompile event is specifically designed to fire whenever a statement-level recompilation occurs. By creating an Extended Events session that captures this event, you can identify which queries are recompiling and investigate the reason (e.g., statistics changes, schema changes, temp table usage). This is the most direct way to diagnose excessive recompilation issues. The other events are useful for general query monitoring but do not specifically target recompilations.

Azure SQL Auditing is the feature designed to track and log database events to an audit log. You can configure it at the server or database level to write to Azure Storage, Log Analytics, or Event Hubs. Importantly, you can create detailed audit specifications that filter events based on criteria such as the action type (SELECT, UPDATE, etc.) and the database principal (user or role) performing the action. This allows you to precisely target the activities of the Auditors role. CDC tracks data changes but not SELECT statements. Defender for SQL is for threat detection, not general activity auditing.

Query Store's plan forcing capability is the ideal solution for this scenario, known as plan regression. By identifying a historically better-performing plan within Query Store, a DBA can use sys.sp_query_store_force_plan (or the corresponding UI option in SSMS) to instruct the query optimizer to always use that specific plan for the query. This provides an immediate and stable fix for the performance issue. Clearing the cache is temporary and doesn't guarantee a good plan will be generated next time. Rebuilding indexes might not be related to the plan choice.

The diagram shows a hybrid architecture. The Failover Cluster Instance (FCI) provides high availability within the primary region (West US 2) by failing over between availability zones. However, for disaster recovery to a completely different region (East US 2), a Distributed Availability Group is used. It acts as an 'availability group of availability groups,' allowing the FCI (which acts as a single AG) to replicate data asynchronously to a standalone instance in the DR region. This component provides the cross-region DR capability.

The correct answer is to use sp_query_store_clear_message_queues. When a failover occurs in a geo-replication or failover group setup, the Query Store data is replicated to the secondary. However, performance statistics (runtime stats) are not, leading to a potential mismatch. Running this procedure on the secondary just before a planned failover clears any queued-up, in-flight statistics, preventing the carry-over of potentially misleading performance data from the primary. This allows the new primary to generate fresh, relevant statistics post-failover, mitigating issues like PSP optimization problems. Failing back is disruptive. Automatic Tuning might eventually fix it but is not the immediate, targeted solution. Clearing the entire Query Store is a drastic measure that loses all historical performance data.

Setting a Microsoft Entra ID admin is a prerequisite for enabling Microsoft Entra-only authentication.

This is the specific action that disables SQL authentication logins (sa and others) and enforces that all connections must use Microsoft Entra authentication. This feature is available for Azure SQL Database, Managed Instance, and Synapse, and can be configured for SQL Server on Azure VM through specific setup.

Elastic Jobs are specifically designed for running T-SQL scripts across a group of Azure SQL Databases, including all databases in an elastic pool. It provides native capabilities for defining target groups, creating job steps with T-SQL, scheduling, and monitoring execution, making it the most suitable and efficient tool for this scenario. Azure Automation is a more general-purpose automation service and would require more complex scripting to iterate through all databases. SQL Server Agent is not available in Azure SQL Database.

An Always On availability group with synchronous-commit mode ensures an RPO of zero by requiring the transaction to be hardened on the secondary before committing on the primary. This configuration also provides automatic failover capabilities for a low RTO and allows the secondary replica to be configured for read-access, meeting the reporting requirement. An FCI provides HA at the instance level but doesn't inherently provide a readable secondary. Asynchronous-commit mode would not guarantee an RPO of zero. Log shipping has a higher RPO and RTO.

Data Migration Assistant (DMA) is the correct tool for this task. Beyond its primary function of identifying compatibility issues, DMA can run a performance data collection assessment. This assessment gathers detailed information about the source SQL Server's workload, which is then used to recommend an appropriate Azure SQL Database service tier and size. It collects the necessary metrics like CPU, memory, IOPS, and query statistics. DMS is for executing the migration, not for assessment. The SSMS Performance Dashboard is for real-time monitoring, not for long-term data collection for baselining.