Pass the Microsoft Microsoft Certified: Azure Database Administrator Associate DP-300 Questions and answers with CertsForce

Last_Query_Plan_Stats allows you to enable or disable collection of the last query plan statistics (equivalent to an actual execution plan) in sys.dm_exec_query_plan_stats.

Lightweight profiling can be disabled at the database level using the LIGHTWEIGHT_QUERY_PROFILING database scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Basic Concept: This question tests task automation for database administration, including which Azure or SQL mechanism can run repeatable maintenance and configuration work.

Why the selected answer is Correct: The selected values fit this scenario because they apply the required Azure SQL configuration at the correct scope and sequence: You need to ensure that SOL Server Agent jobs running on SQL 1 can notify administrators when a failure occurs.

Why the alternate choices are Wrong: The alternate selections apply to different administrative stages or different scopes. In a hotspot or drag-drop task, order and scope are part of the answer, so a technically valid Azure feature can still be wrong if it is placed in the wrong step.

Box 1: Hash

Scenario:

Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible.

A hash distributed table can deliver the highest query performance for joins and aggregations on large tables.

Box 2: Round-robin

Scenario:

You plan to create a promotional table that will contain a promotion ID. The promotion ID will be associated to a specific product. The product will be identified by a product ID. The table will be approximately 5 GB.

A round-robin table is the most straightforward table to create and delivers fast performance when used as a staging table for loads. These are some scenarios where you should choose Round robin distribution:

When you cannot identify a single key to distribute your data.

If your data doesn’t frequently join with data from other tables.

When there are no obvious keys to join.

Basic Concept: This question tests choosing the correct Azure migration approach by matching the source platform, target service, downtime tolerance, and administrative effort.

Why the selected answer is Correct: The selected values fit this scenario because they apply the required Azure SQL configuration at the correct scope and sequence: You need to ensure that the migrated environment can be managed by using multiserver administration and supports master/target (MSX/TSX) jobs.

Why the alternate choices are Wrong: The alternate selections would either use a migration tier that does not match the downtime requirement, choose a network resource that does not support the migration path, or add an unnecessary deployment component.

Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location in the secondary region to protect against regional outages. However, that data is available to be read only if the customer or Microsoft initiates a failover from the primary to secondary region. When you enable read access to the secondary region, your data is available to be read if the primary region becomes unavailable. For read access to the secondary region, enable read-access geo-redundant storage (RA-GRS) or read-access geo-zone-redundant storage (RA-GZRS).

Dedicated SQL pool supports many, but not all, of the table features offered by other databases.

Surrogate keys are not supported. Implement it with an Identity column.

Basic Concept: This question tests high availability and disaster recovery design for Azure SQL, SQL Server on Azure VMs, and regional failure scenarios.

Why B is Correct: Owner is a security-related control, but its value depends on whether the requirement is identity, encryption, auditing, network isolation, or data exposure reduction. The scenario asks for: You need to ensure that User1 can delete LTR backups of DBl. That makes Owner the option that satisfies the required Azure SQL layer and operational outcome.

Why A is Wrong: SQL Server Contributor is a security-related control, but its value depends on whether the requirement is identity, encryption, auditing, network isolation, or data exposure reduction. It does not satisfy the required identity, encryption, firewall, auditing, or data-exposure boundary described in the scenario: You need to ensure that User1 can delete LTR backups of DBl.

Why C is Wrong: SQL Security Manager is a security-related control, but its value depends on whether the requirement is identity, encryption, auditing, network isolation, or data exposure reduction. It is not the right enforcement point for this case; the scenario needs the control that governs the data or identity path being tested.

Why D is Wrong: Contributor is a security-related control, but its value depends on whether the requirement is identity, encryption, auditing, network isolation, or data exposure reduction. It does not satisfy the required identity, encryption, firewall, auditing, or data-exposure boundary described in the scenario: You need to ensure that User1 can delete LTR backups of DBl.

Instead restore Database1 from Server1 to the Server2 by using the RESTORE Transact-SQL command and the REPLACE option.

Note: REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. This is an important safeguard.