Free DP-300 sample questions

Correct answer: Use the Query Store system stored procedure `sp_query_store_clear_message_queues` on the secondary before the failover.

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.

Correct answers: Set a Microsoft Entra ID group as the server administrator.; Enable the 'Azure Active Directory authentication only' setting on the SQL Server resource in the Azure portal.

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.

Correct answer: Elastic Jobs.

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.

Correct answer: An Always On availability group with two replicas in synchronous-commit mode.

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.

Correct answer: Data Migration Assistant (DMA).

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.

Correct answer: A view that uses a CASE statement to show the Salary or a masked value, combined with granting SELECT on the view instead of the table.

While DDM and RLS are powerful security features, they don't natively support conditional unmasking based on the data in another column for the same user. DDM applies the mask to all non-privileged users, and RLS filters entire rows. The most direct and flexible way to implement this specific logic (unmask for your own department, mask for others) is to create a security view. The view's logic would contain a CASE statement that checks if the employee's department matches the analyst's department (e.g., using `USER_NAME()` or session context) and returns either the actual Salary or a masked value. The analyst is then granted permission only to the view.

Correct answer: `sys.dm_exec_requests` and `sys.dm_exec_sql_text`

The `sys.dm_exec_requests` DMV provides information about each request currently executing in SQL Server, including the `sql_handle` for the executing batch. To get the actual text of the SQL statement, you must pass this `sql_handle` to the `sys.dm_exec_sql_text` dynamic management function. Joining these two allows you to see the SQL text for a specific session ID. `sys.dm_exec_sessions` provides session-level information but not the currently executing statement text. `sys.dm_tran_locks` shows lock information but not the query text.

Correct answer: True

This is true. In an Azure environment, the availability group listener's IP address needs a mechanism to float between the VMs hosting the replicas. An Azure Load Balancer is used for this purpose. It is configured with a health probe to detect which node is the primary replica and directs traffic to that node's IP address accordingly.

Correct answer: uniqueString()

The `uniqueString()` function is designed for this purpose. It creates a deterministic 13-character hash string based on one or more seed values you provide, such as the resource group ID. Because it's deterministic, if you run the same template with the same seed values again, it will generate the same unique string, which is essential for idempotent deployments. The `guid()` function generates a new random GUID on every run, which would cause the deployment to fail on subsequent runs as it would try to create a new resource with a different name.

Correct answer: Increase the number of vCores for the primary compute replica.

In Azure SQL Database, including the Hyperscale tier, the maximum transaction log generation rate is directly tied to the number of vCores allocated to the compute replica. To increase the log rate limit beyond 100 MBps, the number of vCores must be increased. Adding read replicas helps with read scaling but does not affect the write or log generation capacity of the primary replica. Increasing max database size is irrelevant to the log rate limit. Switching to the DTU model is not an option for Hyperscale.