DP-300 Practice Questions

To restore a specific database on an Azure SQL Managed Instance (SQL MI) using Transact-SQL, the correct command syntax is RESTORE DATABASE. Unlike on-premises SQL Server, Azure SQL Managed Instance does not support accessing backups directly from local physical disks, network shares (UNC paths like \\NAS01...), or tape devices via the DISK or TAPE parameters for a native restore operation.

Instead, SQL Managed Instance requires that the native backup file (.bak) be stored in Azure Blob Storage. Therefore, the command must use the FROM URL clause to specify the location of the backup file. The URL parameter points to the blob storage container where the backup resides.

Microsoft Learn. (2024). Restore a database to Azure SQL Managed Instance with T-SQL. Retrieved from https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-sample-database-quickstart?view=azuresql

Microsoft Learn. (2023). RESTORE Statements - FROM URL (Transact-SQL). Retrieved from https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-from-url-transact-sql?view=sql-server-ver16

Microsoft Learn. (2024). Native backup and restore in SQL Managed Instance. Retrieved from https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/migration-native-backup-restore-import?view=azuresql

In Azure Databricks notebooks, a default language is set upon creation. To execute code in a different language within a specific cell, you must use a "magic command" at the beginning of the cell. These commands are prefixed with a percent sign (%) followed by the name of the language. For the scenario described, even though the notebook's default language is R, you would use %scala to run Scala code or %sql to run SQL queries in individual cells, effectively switching the language context for that specific cell.

A. \\[]: This syntax is not a valid command or switch for changing languages within an Azure Databricks notebook.

C. \\[]: This is a duplicate of option A and is also incorrect for the same reason.

D. @: The @ symbol is not used as a language switch in Azure Databricks. It has different meanings in other contexts, such as decorators in Python.

1. Microsoft Learn

Azure Databricks Documentation. "Databricks notebooks." Under the section "Mix languages

" it states

"To override the default language in a cell

you can click the language button or type the language magic command % at the beginning of a cell." This explicitly confirms the use of the % symbol.

2. Microsoft Learn

Azure Databricks Documentation. "Magic commands." This document provides a comprehensive list of magic commands available in Databricks notebooks

all of which are prefixed with %. It lists %python

%r

%scala

and %sql as the primary language magic commands.

For Box 1, the failure occurred 2 days ago, which falls within the configured 14-day Point-in-time restore (PITR) window. PITR is the only method that minimizes data loss by allowing recovery to a specific timestamp. For Box 2, Azure SQL Database LTR stores a backup only once even if it satisfies multiple retention policies (Weekly, Monthly, and Yearly). After 52 weeks, all 52 weekly backups are still within their 52-week retention period. The 12 monthly backups and the 1 yearly backup coincide with these existing weekly backups and do not create additional duplicate copies. Thus, physically and logically, there are 52 unique backup copies.

Microsoft Learn - Long-term retention - Azure SQL Database: "If a specific backup is kept for multiple purposes (for example, a backup is both a weekly and a monthly backup), it is stored only once but effectively counts towards the retention of both."

Source: learn.microsoft.com/en-us/azure/azure-sql/database/long-term-retention-overview

Microsoft Learn - Automated backups - Azure SQL Database: Confirms PITR allows restoration to any second within the retention period to minimize data loss.

Source: learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview

The dynamic management function (DMF) sys.dm_db_index_operational_stats is the correct choice for identifying low-level operations performed on an index. It provides detailed metrics on I/O, locking, latching, and access method activity for each partition of an index. Specifically, it tracks the number of inserts, updates, deletes, and page splits, as well as lock wait times. Since the user reports slow queries, analyzing these operational statistics helps identify if the performance degradation is caused by high contention, blocking, or heavy modification overhead on index1. While it is technically a function requiring parameters, Microsoft documentation frequently categorizes it under the broad "Dynamic Management View" (DMV) umbrella for administrative tasks.

A. sys.dm_exec_query_plan_stats: This DMV returns the last known actual execution plan for a cached query. It analyzes individual query logic rather than aggregating operational activity across a specific index.

B. Sys.dm_db_index_physical_stats: This DMV identifies index fragmentation and size. While fragmentation can cause slowness, it does not track the specific operations (inserts/updates/seeks) being performed on the index.

D. Sys.dm_db_index_useage_stats: This option is incorrect due to a spelling error ("useage"). Furthermore, the valid view (usage_stats) tracks high-level seek/scan counts rather than granular operational contention.

Microsoft Official Documentation: sys.dm_db_index_operational_stats (Transact-SQL). This documentation specifies that the view returns low-level I/O, locking, latching, and access method activity.

Microsoft Official Documentation: Monitor performance of Microsoft Azure SQL Database and Azure SQL Managed Instance using dynamic management views. Section: "Index-related DMVs".

Microsoft Learn (DP-300 Training): Optimize query performance in Azure SQL. It highlights using operational stats to identify hot spots and locking issues in database structures.

University Courseware (CS 186, UC Berkeley - Database Systems): Discussion on "Access Methods and Index Monitoring" identifies the distinction between physical statistics (fragmentation) and operational metrics (I/O and locking).

The question refers to the Litware, Inc. case study, where SalesSQLDb1 is explicitly defined as a database residing within an elastic pool (SalesPool).

1. Collect metrics from: To effectively monitor a database in an elastic pool, you must collect metrics from both the database and the elastic pool. Monitoring the database alone is insufficient because performance issues may stem from the pool reaching its aggregate resource limits (eDTU/vCore or storage caps), even if the individual database is within its limits ("noisy neighbor" problem). Conversely, monitoring only the pool hides which specific database is consuming resources. The logical server itself does not provide the necessary granular resource metrics for this scenario.
2. Stream metrics to: The technical requirements for Litware typically state that metrics must be analyzed using Azure built-in functionality (identifying trends, querying logs). Azure Log Analytics (part of Azure Monitor) is the correct service for ingesting and querying telemetry data using Kusto Query Language (KQL). Azure Storage is primarily for long-term archiving (compliance), and Azure Event Hubs is for streaming to third-party systems.

Microsoft Learn - Monitor Azure SQL Database with Azure Monitor:

"You can use metrics to monitor database and elastic pool resource consumption and health... Detect a gradual increase in resource consumption, and proactively scale up the database or elastic pool."

Reference: Monitor Azure SQL Database with Azure Monitor metrics and alerts (Section: "Azure SQL Database metrics")

Microsoft Learn - Diagnostic Settings:

"You can enable and configure the streaming export of diagnostic telemetry to one of several destinations, including Log Analytics, Event Hubs, and Azure Storage."

"The suggested default is to route resource logs to Azure Monitor Logs [Log Analytics] so you can query them with other log data."

Reference: Diagnostic settings in Azure Monitor

Microsoft Learn - Elastic Pools:

"To monitor the performance of a database in Azure SQL Database... start by monitoring the CPU and IO resources used by your workload relative to the level of database performance... For elastic pools, it shows the amount of storage space used... by all databases."

Reference: Management and monitoring of elastic pools

Box 1: When creating a SQL Server credential to authenticate with Azure Blob Storage using a Shared Access Signature (SAS), the IDENTITY argument must be set exactly to the string 'SHARED ACCESS SIGNATURE'. This specific string instructs the SQL engine to interpret the content of the SECRET field as a SAS token.

Box 2: Azure SQL Managed Instance (SQL MI) manages automated backups internally to ensure point-in-time restore capabilities. To prevent user actions from breaking the log chain required for these automated backups, users are restricted from taking standard full backups. A user-initiated full backup on a Managed Instance is only permitted if the COPY_ONLY option is specified. Without this option, the command will fail.

Microsoft Learn. (2024). SQL Server Backup to URL. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver16#credential (See section: "Create a Credential")

Microsoft Learn. (2024). User-initiated backups - Azure SQL Managed Instance. Retrieved from https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/backup-user-initiated?view=azuresql (See section: "Backup limitations")

To manually add a database to an Always On availability group, the database on the secondary replica must be restored into a state that allows the availability group to start synchronizing it. The RESTORE WITH NORECOVERY option leaves the database in a RESTORING state. This specific state is required so that subsequent transaction log backups can be applied and, more importantly, so that the availability group can take over synchronization from the primary replica once the database is joined. The database remains inaccessible until it is synchronized and brought online by the availability group.

A. Restore with Recovery: This option finalizes the database recovery process, brings the database online, and makes it accessible. A database in this state cannot be joined to an availability group as a secondary because it cannot receive and apply log records from the primary.

C. Restore with Standby: This option brings the database to a read-only state and allows for additional transaction log restores. While used in scenarios like log shipping to allow for reporting, it is not the standard or required state for preparing a database to join an availability group. The NORECOVERY option is the correct and direct method.

1. Microsoft Docs

Manually prepare a secondary database for an availability group (SQL Server)

Last Updated: 02/17/2023.

Section: "To prepare a secondary database"

Paragraph/Step 3: "On the instance of SQL Server that hosts the secondary replica

restore the full database backup of the primary database

using RESTORE WITH NORECOVERY... If you need to restore any further backups

they must also be restored using the WITH NORECOVERY option." This directly confirms that NORECOVERY is the required option.

2. Microsoft Docs

RESTORE Statements - Arguments (Transact-SQL)

Last Updated: 03/04/2024.

Section: "{ RECOVERY | NORECOVERY | STANDBY = standbyfilename }"

Paragraph on NORECOVERY: "Instructs RESTORE to not roll back any uncommitted transactions. The NORECOVERY option allows a subsequent RESTORE LOG operation to be applied to the database. The database is not usable in this state." This explains the technical state that NORECOVERY creates

which is essential for AG seeding.

3. Microsoft Docs

Start Data Movement on an Always On Secondary Database (SQL Server)

Last Updated: 02/17/2023.

Section: "Prerequisites"

Paragraph: "Your secondary databases must be in the RESTORING state. To put a secondary database into the RESTORING state

restore the database backups using the WITH NORECOVERY Transact-SQL option." This reinforces that the RESTORING state

achieved via WITH NORECOVERY

is a prerequisite.

To enable customer-managed key (CMK) encryption on an existing Azure Data Factory, the factory must be completely empty. This is a one-time setup requirement. All existing resources, which include linked services, pipelines, datasets, and data flows, must be removed before you can configure the data factory to use a key from Azure Key Vault. Therefore, the first action required is to remove the existing linked service from df1.

A. Disable purge protection on vault1.

This is incorrect. To use a key from Azure Key Vault for CMK encryption in Data Factory, both soft delete and purge protection must be enabled on the key vault.

C. Create a self-hosted integration runtime.

This is incorrect. A self-hosted integration runtime is a compute infrastructure for data movement and is not a prerequisite for configuring factory-level encryption with a customer-managed key.

D. Disable soft delete on vault1.

This is incorrect. Soft delete is a mandatory prerequisite and must be enabled on the Azure Key Vault to protect against accidental key deletion and ensure data recoverability.

1. Microsoft Learn. (2023). Encrypt Azure Data Factory with customer-managed keys.

Section: Important

Content: "To enable customer-managed key

your Azure Data Factory V2 must be empty. You must remove all resources (for example

linked services

pipelines

and data flows) before you enable the key." This directly supports the correct answer (B).

2. Microsoft Learn. (2023). Encrypt Azure Data Factory with customer-managed keys.

Section: Prerequisites

Content: "The key vault must have soft delete and purge protection enabled." This statement confirms that options A and D are incorrect as they suggest disabling these features.

To enable compression on an existing index, you must rebuild it. The ALTER INDEX statement with the REBUILD option is used for this purpose. The WITH (DATA_COMPRESSION = PAGE) clause explicitly specifies the desired compression level. Including PARTITION = ALL ensures that the compression setting is applied to all partitions of the index, effectively covering the entire dataset referenced by the clustered index. This command modifies the physical storage format to reduce I/O and storage space while maintaining the index structure.

Microsoft Learn. (2025). ALTER INDEX (Transact-SQL). Retrieved from https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

Microsoft Learn. (2025). Enable compression on a table or index. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/enable-compression-on-a-table-or-index

The configuration must follow the dependency chain of the Resource Governor architecture. First, create a resource pool to define the specific physical limits (CPU and memory) for App1. Second, create a workload group and map it to that resource pool; this group serves as the container for App1's queries. Third, create a user-defined classifier function that identifies sessions originating from "App1" (e.g., using APP_NAME()) and returns the name of the workload group created in the previous step. Finally, modify Resource Governor to register this new classifier function (ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = ...) ) and apply the configuration (ALTER RESOURCE GOVERNOR RECONFIGURE).

Microsoft Learn. (2023). Resource Governor. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor

Microsoft Learn. (2023). Create a Resource Pool. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/create-a-resource-pool

Microsoft Learn. (2023). Create and Test a Classifier User-Defined Function. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/create-and-test-a-classifier-user-defined-function

Microsoft Learn. (2023). Enable Resource Governor. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/enable-resource-governor