DP-300 Practice Test 13 — 15 Questions

Question 1

You have an Azure SQL Managed Instance. You need to automate the execution of a stored procedure every hour to clean up historical data. What is the most appropriate solution?

Accepted Answer

SQL Agent Job. SQL Agent Jobs are a built-in feature of Azure SQL Managed Instance, providing native scheduling for T-SQL jobs like executing a stored procedure every hour. Option B is less appropriate because Azure Logic Apps is an external service that adds complexity and latency. Option C is designed for executing tasks across multiple databases, not a single instance. Option D is external and less integrated compared to the native SQL Agent Job.

Answer

Azure Logic Apps with SQL connector

Answer

Elastic Database Job

Answer

Azure Automation runbook with T-SQL

Question 2

You manage an Azure SQL Managed Instance. You need to monitor storage space usage. Which TWO dynamic management views can you use?

Accepted Answer

sys.dm_db_file_space_usage. Options C and D are correct. sys.dm_db_file_space_usage provides data file space usage, and sys.dm_db_log_space_usage provides transaction log space usage. Option A is incorrect because sys.dm_db_partition_stats shows row counts and partition-level information, not storage space. Option B is incorrect because sys.dm_exec_query_stats is for query performance metrics. Option E is incorrect because sys.dm_os_performance_counters includes various performance counters but does not directly show per-database space usage.

Answer

sys.dm_db_partition_stats

Answer

sys.dm_exec_query_stats

Answer

sys.dm_os_performance_counters

Question 3

Your company runs a global e-commerce application using Azure SQL Database in the West Europe region. You need to implement a solution that provides automatic failover and allows the secondary region to be used for read-only queries during normal operations. The secondary must be in a different region. Which configuration meets these requirements?

Accepted Answer

Create an auto-failover group with a secondary in West US and use the secondary as readable.. An auto-failover group in Azure SQL Database provides automatic failover to a secondary region and allows the secondary database to be used for read-only queries when configured as readable. It also provides a read-write and read-only listener endpoint, so applications can connect without changing connection strings after failover. This meets both the automatic failover and read-only query requirements.

Answer

Enable read scale-out on the primary database.

Answer

Configure active geo-replication to a secondary server in West US and implement application-level failover logic.

Answer

Configure geo-restore with a recovery point objective of 1 hour.

Question 4

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that 'Query performance degradation' was detected. After reviewing the details, you find that a specific query now has a higher duration and is using a different execution plan. What is the recommended first step to troubleshoot?

Accepted Answer

Query the Query Store to compare the previous and current plans. Query Store captures historical execution plans, runtime statistics, and plan changes per query, so it is the correct first step to compare the previous and current plans and identify what regressed (e.g., a plan change, parameter sniffing, or missing index). Intelligent Insights flags the degradation, but Query Store provides the forensic detail needed to diagnose it.

Answer

Restart the database to clear the plan cache

Answer

Increase the service tier of the database

Answer

Run the Database Engine Tuning Advisor

Question 5

A company is planning to migrate their on-premises SQL Server databases to Azure SQL Managed Instance. They have a database that uses SQL Server Agent jobs with proxies and also uses cross-database queries extensively. What is the main consideration for this migration?

Accepted Answer

Migrate to Azure SQL Managed Instance as it supports SQL Agent and cross-database queries within the same instance.. Azure SQL Managed Instance is the correct target because it provides full support for SQL Server Agent, including proxies, and enables cross-database queries within the same instance. Unlike Azure SQL Database, Managed Instance maintains instance-level scope, allowing queries that reference other databases in the same instance without requiring external data sources or elastic queries.

Answer

Migrate to SQL Server on Azure Virtual Machines for full control.

Answer

Migrate to Azure SQL Database elastic query to handle cross-database queries.

Answer

Migrate to Azure SQL Database instead to reduce costs.

Question 6

The database 'mydb' is experiencing performance issues during peak hours. Based on the exhibit, what is the most likely cause?

Accepted Answer

The service tier S3 is not sufficient for the workload.. Azure SQL Database service tier S3 (Standard tier, 100 DTUs) is a relatively low-performance tier. If the database experiences performance issues during peak hours, the most likely cause is that the service tier cannot handle the workload's concurrency and throughput demands. Upgrading to a higher tier (e.g., Premium or vCore-based) would provide more DTUs/vCores and IOPS.

Answer

Zone redundancy is disabled, causing failover delays.

Answer

Read scale is disabled, increasing load on primary.

Answer

The database is auto-pausing frequently due to autoPauseDelay.

Question 7

You are monitoring an Azure SQL Database using Intelligent Insights. The built-in intelligence detects a performance issue and suggests a specific index to create. The database is running the Business Critical service tier. You want to automatically implement this recommendation without manual intervention. What should you configure?

Accepted Answer

Enable automatic tuning for 'CREATE INDEX' in the Azure portal.. Azure SQL Database's automatic tuning feature can automatically apply index recommendations generated by the built-in intelligence engine. Enabling the 'CREATE INDEX' automatic tuning option allows Azure to create recommended indexes without manual intervention, directly addressing the requirement. This option is supported on Business Critical tier and integrates with Intelligent Insights and Query Store to validate the recommendation before applying it.

Answer

Enable automatic tuning for 'FORCE LAST GOOD PLAN' in the Azure portal.

Answer

Set up Query Store to capture the recommended index execution.

Answer

Configure Azure Advisor to email you the recommendation.

Question 8

You need to automate the deployment of database schema changes across multiple Azure SQL Databases in a development environment. Which Azure service is designed for this purpose?

Accepted Answer

Azure DevOps using database projects and release pipelines.. Azure DevOps with database projects and release pipelines provides a CI/CD solution specifically designed for deploying schema changes to Azure SQL Databases. Option B is incorrect because SQL Agent jobs are used for scheduled administrative tasks within a single database, not for multi-database schema deployment automation. Option C is incorrect because Azure Automation Account with PowerShell runbooks is suitable for general automation tasks but lacks native CI/CD capabilities for database schema updates. Option D is incorrect because Azure Data Factory with copy activity focuses on data movement and transformation, not on executing schema changes.

Answer

SQL Agent jobs on each database.

Answer

Azure Automation Account with PowerShell runbooks.

Answer

Azure Data Factory with a copy activity.

Question 9

You are responsible for a SQL Server 2019 instance on an Azure VM. The VM is part of a failover cluster instance (FCI) using Azure shared disks. During a recent failover test, the cluster took 15 minutes to bring the database online. You need to reduce the failover time to under 5 minutes. What should you do?

Accepted Answer

Remove unnecessary databases from the FCI and distribute them to other instances.. Removing unnecessary databases from the FCI reduces the number of databases that need to be recovered during failover, thereby decreasing the overall failover time. Option A is correct. Option B is incorrect because upgrading the VM does not directly reduce the number of databases to recover. Option C is incorrect because increasing disk size does not improve failover time. Option D is incorrect because instant file initialization helps with file growth, not failover recovery.

Answer

Upgrade the VM to a larger size with more CPU and memory.

Answer

Increase the size of the Azure shared disks.

Answer

Enable instant file initialization on all SQL Server instances.

Question 10

Refer to the exhibit. You run these commands in an Azure SQL Database. What is the result?

Accepted Answer

A Microsoft Entra ID user is created and granted read access to the database.. The commands create a user in an Azure SQL Database mapped to a Microsoft Entra ID (formerly Azure AD) identity. The CREATE USER statement with FROM EXTERNAL PROVIDER creates a user that corresponds to an Entra ID user or group. The ALTER ROLE statement then adds this user to the db_datareader database role, granting read access to all tables and views. Therefore, option D correctly describes the outcome: a Microsoft Entra ID user is created and granted read access.

Answer

The user is created but not granted any permissions.

Answer

The commands fail because Entra ID users cannot be created in Azure SQL Database.

Answer

A SQL Server authentication user is created and granted read access.

Question 11

You are preparing a disaster recovery runbook. You plan to use the PowerShell command shown in the exhibit to restore a database to a different region. What must be true for this command to succeed?

Accepted Answer

The source database must have geo-redundant backup storage configured.. The Restore-AzSqlDatabase cmdlet with the -FromGeoBackup parameter requires the source database to have geo-redundant backup storage (also known as geo-redundant storage) enabled. This allows restoration from geographically replicated backups. Option B is incorrect because point-in-time restore is not required for a geo-restore; geo-restore uses the geo-redundant backup independently. Option C is incorrect because the source database does not need to be online at the time of restoration; geo-backups are stored in Azure storage and are available even if the source database is offline. Option D is incorrect because the purpose of geo-restore is to restore to a different region; therefore, the target server can be in a different region than the source.

Answer

The source database must have point-in-time restore enabled.

Answer

The source database must be online at the time of restoration.

Answer

The target server must be in the same region as the source server.

Question 12

You need to design a disaster recovery solution for an Azure SQL Database that uses the General Purpose service tier. The solution must have an RTO of 1 hour and an RPO of 15 minutes. Which TWO options can achieve these requirements?

Accepted Answer

Auto-failover group with a secondary in a different region.. Option C (auto-failover group with a secondary in a different region) is correct because failover groups provide a readable/writable secondary in another region with an RPO of about 5 seconds and an RTO typically under 1 hour, satisfying both the 15-minute RPO and 1-hour RTO. Option D (active geo-replication to a secondary server in a paired region) is also correct because it continuously replicates transactions asynchronously with an RPO of roughly 5 seconds and supports a manual/forced failover that can complete well within 1 hour, meeting the stated targets. Option A (PITR using geo-redundant backup storage) is not appropriate because PITR restores to a point in time from backups with an RPO of up to 10 minutes but an RTO that can exceed 1 hour depending on database size, and it is a restore operation rather than a fast failover. Option B (LTR backups) is incorrect because LTR is designed for long-term compliance retention (up to 10 years) and does not provide the low RTO/RPO needed for disaster recovery. Option E (zone redundancy on the primary database) is incorrect because zone redundancy only protects against a single-zone datacenter failure within the same region and does not address a regional disaster or provide cross-region failover.

Answer

Point-in-time restore (PITR) using geo-redundant backup storage.

Answer

Long-term retention (LTR) backups.

Answer

Zone redundancy on the primary database.

Question 13

Drag and drop the steps to configure an Azure SQL Managed Instance link for disaster recovery in the correct order.

Accepted Answer

Create a secondary managed instance, then create the link, then configure the secondary as readable, then monitor replication, then initiate failover when needed.. The link requires a secondary instance first, then establishing the link, configuring as readable, monitoring, and failing over when needed.

Answer

Create the link, then create a secondary managed instance, then configure as readable, then monitor, then failover.

Answer

Create a secondary managed instance, then create the link, then monitor, then failover, then configure as readable.

Answer

Create a secondary managed instance, then configure as readable, then create the link, then monitor, then failover.

Question 14

You are a database administrator for a healthcare organization. You need to deploy a new Azure SQL Database that stores protected health information (PHI). The database must be encrypted at rest using a customer-managed key in Azure Key Vault. Additionally, you need to ensure that backups are also encrypted with the same key. Which configuration should you use?

Accepted Answer

Enable Transparent Data Encryption (TDE) using a customer-managed key from Azure Key Vault. Transparent Data Encryption (TDE) with a customer-managed key from Azure Key Vault is the correct choice because it encrypts the database at rest, including data files and log files, using a key that you control and manage in Azure Key Vault. This meets the requirement for encrypting protected health information (PHI) at rest with a customer-managed key, and TDE automatically ensures that backups are encrypted with the same database encryption key (DEK), which is protected by the customer-managed key.

Answer

Use column-level encryption with a certificate

Answer

Enable Transparent Data Encryption with a service-managed key

Answer

Enable Always Encrypted with keys stored in Azure Key Vault

Question 15

You observe that the average of Maximum DTU consumption over the last hour is consistently above 90%. What should you do next?

Accepted Answer

Scale up the database to a higher service tier or increase DTU.. A sustained average of Maximum DTU consumption above 90% over an hour indicates the database is consistently resource-bound and approaching its tier ceiling. The correct next step is to scale up to a higher service tier or add DTUs so the workload has headroom and latency does not degrade. Query Store and index maintenance are optimization steps, but the immediate signal is capacity saturation.

Answer

Enable Query Store to analyze top queries.

Answer

Rebuild all indexes in the database.

Answer

Do nothing; it's normal for DTU to be high.