DP-300 Practice Exam 56 — 20 Questions

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create an auto-failover group with a secondary in West US and use the secondary as readable.. An auto-failover group with a readable secondary in a different region provides automatic failover and allows read-only queries on the secondary during normal operations. Option C is incorrect because active geo-replication requires application-level failover logic, which is not automatic, and thus does not meet the requirement for automatic failover. Option A is incorrect because read scale-out is limited to the same region. Option D is incorrect because geo-restore is a backup/restore mechanism, not a continuous replication solution.

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 2

Your company uses Azure SQL Managed Instance for a critical OLTP workload. You need to automate index maintenance for all databases in the instance without downtime. The solution must minimize performance impact during business hours. Which approach should you use?

Accepted Answer

Deploy Ola Hallengren’s IndexOptimize stored procedure in each database and schedule it via SQL Server Agent.. Azure SQL Managed Instance fully supports SQL Server Agent, which can schedule Ola Hallengren's IndexOptimize stored procedure for index maintenance across all databases. This approach allows centralized management, scheduling during off-peak hours, and granular control over parallelism and throttling to minimize performance impact. Option A is incorrect because Elastic Database Jobs are not available on Azure SQL Managed Instance; they are designed for Azure SQL Database. Option B is incorrect because Azure Logic Apps are not suited for executing T-SQL scripts across multiple databases with performance-sensitive throttling. Option C is incorrect because Azure Automation Runbooks would require complex orchestration and lack native scheduling and throttling capabilities compared to SQL Agent.

Answer

Use Elastic Database Jobs to run index maintenance scripts on all databases during off-peak hours, with parallel execution throttled.

Answer

Use Azure Logic Apps with a SQL connector to run index maintenance on each database, with retry policies.

Answer

Create an Azure Automation Runbook that connects to each database and runs index maintenance sequentially.

Question 3

Refer to the exhibit. You execute this PowerShell script to automate database configuration. The script runs without errors, but the database remains in the 'Standard' edition with S2 performance level. What is the most likely reason?

Accepted Answer

The database is being moved into an elastic pool, which overrides the edition and SLO. When the -ElasticPoolName parameter is specified, the database is moved into the elastic pool, and the -Edition and -RequestedServiceObjectiveName parameters are ignored because the elastic pool's service tier and performance level determine the database's settings. The script runs without errors, but the database remains at Standard S2 because the pool's tier overrides the specified edition and SLO. Option A is incorrect because the command succeeded, indicating the pool exists. Option B is incorrect because the parameter is correctly spelled. Option D is incorrect because the script succeeded without privilege errors.

Answer

The elastic pool does not exist

Answer

The -RequestedServiceObjectiveName parameter is misspelled

Answer

The script requires administrative privileges that the user does not have

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 execution plans and runtime statistics, allowing you to compare the previous plan with the current plan to identify plan regression. Option A is wrong because restarting the database is disruptive and doesn't address the root cause. Option B is wrong because increasing the service tier may not fix plan regression and is not the first step. Option D is wrong because Database Engine Tuning Advisor focuses on index and query design, not on comparing plan changes.

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

You need to configure monitoring for an Azure SQL Database to meet the following requirements: 
- Alert when average DTU consumption exceeds 90% for 10 minutes.
- Track failed logins.
- Analyze query performance over the last 30 days.
Which THREE Azure services or features should you use? (Choose three.)

Accepted Answer

SQL auditing. (SQL auditing) is correct because it logs failed login attempts. Option C (Query Store) is correct as it retains query performance data for historical analysis (e.g., 30 days). Option D (Azure Monitor metric alerts) is correct because it can monitor metrics like DTU and trigger alerts when thresholds are exceeded. Option B (Automatic tuning) is wrong because it optimizes query execution plans, not monitoring. Option E (Azure SQL Assessment) is wrong; it provides best-practice recommendations, not monitoring.

Answer

Automatic tuning

Answer

Azure SQL Assessment

Question 6

Which THREE factors should you consider when configuring automatic tuning for an Azure SQL Database? (Choose three.)

Accepted Answer

Whether to allow automatic index dropping.. The correct answers are B, D, and E. Automatic tuning for Azure SQL Database includes the options 'CREATE INDEX' (option E), 'DROP INDEX' (option B), and 'FORCE LAST GOOD PLAN' (option D). Option A (automatically creating plan guides) is not part of automatic tuning; plan guides are a manual feature. Option C (automatic statistics update) is a separate maintenance operation and not an automatic tuning option. Therefore, when configuring automatic tuning, you should consider enabling or disabling index creation, index dropping, and forcing the last good plan to address query plan regressions.

Answer

Whether to enable automatic plan guide creation.

Answer

Whether to enable automatic statistics update.

Question 7

You are reviewing an ARM template for Azure SQL Database. The exhibit shows a resource definition for Transparent Data Encryption (TDE). You need to ensure that the database uses customer-managed keys (CMK) stored in Azure Key Vault instead of service-managed keys. What additional configuration is required?

Accepted Answer

Add a resource of type 'Microsoft.Sql/servers/encryptionProtector' and reference the key vault key.. To use customer-managed keys (CMK) for Transparent Data Encryption (TDE) in Azure SQL Database, you must configure an encryption protector that points to the key in Azure Key Vault. This is done by adding a resource of type 'Microsoft.Sql/servers/encryptionProtector' to the ARM template, which sets the server-level encryption protector to use the specified key vault key. Option D correctly identifies this required resource, as TDE with CMK is managed at the server level, not directly on the database resource.

Answer

Modify the database resource to include a 'keyVaultUri' property.

Answer

Enable Always Encrypted on the database.

Answer

Set the TDE state to 'Disabled' and then re-enable with a customer key.

Question 8

You are designing an automation solution to deploy Azure SQL Database schema changes using CI/CD pipelines. The solution must support rollback if a deployment fails and must integrate with Azure DevOps. Which two components should you include in your pipeline?

Accepted Answer

Azure SQL Database project with DACPAC deployment.. Using an Azure SQL Database project with DACPAC (data-tier application package) allows version-controlled, repeatable schema deployments and supports rollback by redeploying a previous DACPAC. Option E is correct because the Azure SQL Database deployment task in Azure Pipelines can deploy DACPACs or execute SQL scripts as part of a CI/CD pipeline, enabling rollback through previous pipeline runs. Option A is incorrect: Azure Automation runbooks are designed for operational tasks like incident remediation, not for CI/CD schema deployment. Option B is incorrect: Azure Data Factory is an ETL service, not for schema change management. Option C is incorrect: although PowerShell with Invoke-SqlCmd can run scripts, it lacks built-in rollback support and is not a CI/CD component; also, Azure SQL Managed Instance is a different deployment option, but the question is about Azure SQL Database.

Answer

Azure Automation runbook to execute T-SQL scripts.

Answer

Azure Data Factory pipeline to copy schema changes.

Answer

A PowerShell script that uses Invoke-SqlCmd to deploy to Azure SQL Managed Instance.

Question 9

Your organization is implementing Microsoft Purview for data governance. You need to scan an Azure SQL Database to automatically classify sensitive data. The security team requires that the scan use a managed identity and that the data classification be stored in Microsoft Purview. What should you configure on the Azure SQL Database?

Accepted Answer

Assign the Microsoft Purview managed identity the db_datareader role and configure the firewall to allow Purview IP ranges. Microsoft Purview uses its own managed identity to authenticate to data sources when scanning. To scan an Azure SQL Database, you must grant Purview's managed identity the `db_datareader` role in the database to allow read access for classification, and you must configure the Azure SQL firewall to allow inbound connections from the Purview IP ranges (or enable 'Allow Azure services and resources to access this server'). This satisfies the security team's requirement to use a managed identity and store classification in Purview.

Answer

Enable system-assigned managed identity on the database and grant it permissions to access Purview

Answer

Use SQL authentication with a dedicated login and firewall rule

Answer

Create a user-assigned managed identity and assign it to the database

Question 10

Your company runs a mission-critical database on Azure SQL Managed Instance in the East US region. To comply with a new regulatory requirement, you must ensure that the database can be recovered within 15 minutes in the event of a regional disaster, with a maximum data loss of 10 seconds. You also need to minimize compute costs during normal operations. What should you configure?

Accepted Answer

Deploy the primary instance as Business Critical with a zone-redundant secondary replica, and configure a failover group to a secondary instance in a paired region using Business Critical with a non-readable secondary.. It combines synchronous data redundancy within the primary region via zone-redundant Business Critical, which provides automatic failover within the region for high availability, with an asynchronous geo-failover group to a paired region using a non-readable secondary replica. The Business Critical tier offers synchronous replication within the region, meeting the 10-second RPO, and the failover group enables geo-failover within 15 minutes (RTO). Using a non-readable secondary (no read scale-out) minimizes compute costs during normal operations. Option A is wrong because General Purpose uses asynchronous replication, which cannot achieve a 10-second RPO. Option B is wrong because zone-redundancy alone does not protect against a full regional disaster. Option D is wrong because pairing a Business Critical primary with a General Purpose secondary would result in asynchronous replication between regions, risking higher RPO, and a readable secondary adds unnecessary cost.

Answer

Use a General Purpose instance with a failover group to a secondary instance in a paired region.

Answer

Configure a zone-redundant Business Critical instance in East US only, without a geo-secondary.

Answer

Deploy the primary instance as Business Critical with a readable secondary replica, and configure a failover group to a secondary instance in a paired region using General Purpose.

Question 11

Your company plans to use Azure SQL Managed Instance for a mission-critical application. You need to ensure that all connections to the database are encrypted and that the server's identity is verified. Which configuration should you enforce?

Accepted Answer

Set 'Force Encryption' = ON and 'Trust Server Certificate' = OFF. Setting 'Force Encryption' = ON ensures that all connections to Azure SQL Managed Instance use TLS encryption, while setting 'Trust Server Certificate' = OFF forces the client to validate the server's certificate against a trusted certificate authority (CA). This combination guarantees both data-in-transit encryption and server identity verification, meeting the requirement for a mission-critical application.

Answer

Set 'Force Encryption' = OFF and 'Trust Server Certificate' = OFF

Answer

Set 'Force Encryption' = ON and 'Trust Server Certificate' = ON

Answer

Set 'Force Encryption' = OFF and 'Trust Server Certificate' = ON

Question 12

Your company uses Azure SQL Database and needs to protect sensitive columns (e.g., credit card numbers) from being accessed by unauthorized users. You implement Always Encrypted. However, some queries that perform pattern matching on the encrypted column are failing because the column cannot be searched. What should you do to allow pattern matching while maintaining security?

Accepted Answer

Enable Always Encrypted with secure enclaves and use a column master key that supports enclave computations.. Always Encrypted with secure enclaves allows computations, including pattern matching (LIKE, equality, comparisons), on encrypted columns by using a trusted execution environment (e.g., Intel SGX). The column master key must support enclave computations (enclave-enabled key) to permit the SQL Server engine to offload operations to the enclave. This preserves encryption at rest and in transit while enabling rich query patterns.

Answer

Implement row-level security (RLS) to filter rows based on user identity.

Answer

Change the encryption type from randomized to deterministic encryption.

Answer

Use Dynamic Data Masking (DDM) to mask the column for unauthorized users instead of encryption.

Question 13

You are a database administrator for an Azure SQL Database. You need to allow a user to restore a database from a backup to a new database, but the user should not have permission to delete the original database. What is the minimum permission required?

Accepted Answer

CREATE DATABASE permission on the logical server. The minimum permission required to restore a database from a backup to a new database in Azure SQL Database is the CREATE DATABASE permission on the logical server. This is because restoring a backup to a new database is essentially creating a new database, and the user does not need any additional permissions on the original database to perform this action. The db_backupoperator and db_owner roles grant excessive permissions on the original database, while the SQL Server Contributor role is an Azure RBAC role that provides broader management capabilities beyond what is needed.

Answer

db_backupoperator database role

Answer

db_owner database role

Answer

SQL Server Contributor role

Question 14

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 15

You have an Azure SQL Database that runs a critical workload. You need to automate the monitoring of performance anomalies and receive notifications when certain thresholds are exceeded. Which TWO actions should you implement? (Choose two.)

Accepted Answer

Enable SQL Insights (preview) for intelligent performance monitoring.. Options D and E are correct. Enabling SQL Insights (preview) provides intelligent performance monitoring with built-in anomaly detection, and creating an Azure Monitor alert rule on DTU/CPU and storage metrics enables automated notifications when thresholds are exceeded. Option A is incorrect because Power BI is a reporting tool, not designed for real-time alerting on performance anomalies. Option B is incorrect because Query Store tracks query performance but does not natively support email notifications. Option C is incorrect because SQL Agent alerts are for job scheduling events, not for monitoring performance counters like DTU or CPU.

Answer

Create a Power BI report that refreshes every minute.

Answer

Enable Query Store and set up email notifications for high query duration.

Answer

Configure a SQL Agent alert on performance counters.

Question 16

You manage a critical Azure SQL Database that supports a financial application. The database is configured with the Business Critical service tier (8 vCores) and uses Always On availability groups for high availability. Recently, the application team noticed that some transactions are experiencing high latency during writes. You investigate and find that the log write latency is averaging 10 ms, and there are occasional HADR_SYNC_COMMIT waits. The application requires durable writes with synchronous commit. You need to reduce write latency without compromising data durability. What should you do?

Accepted Answer

Increase the number of vCores to 16. Increasing the number of vCores (Option D) provides additional CPU and memory resources for the log writer, which can process transactions faster, thereby reducing log write latency. In the Business Critical tier, the log write path is CPU-intensive, and scaling up directly improves throughput. Option A is incorrect because Premium SSD v2 is not applicable to Azure SQL Database; storage is managed by the service. Option B is incorrect because disabling automatic tuning does not impact log write performance and may negatively affect overall query performance. Option C is incorrect because the number of secondary replicas is fixed at three in Business Critical, and reducing them is not possible; further, synchronous commit requires at least one secondary to ensure durability.

Answer

Switch to Premium SSD v2 storage

Answer

Disable automatic tuning to reduce overhead

Answer

Reduce the number of secondary replicas to minimize synchronization overhead

Question 17

Which TWO of the following are valid methods to migrate an on-premises SQL Server database to Azure SQL Database?

Accepted Answer

Export the database as a BACPAC file and import into Azure SQL Database. A BACPAC file is a logical backup that contains the database schema and data in a compressed format. Azure SQL Database supports importing BACPAC files directly via the Azure portal, PowerShell, or the SqlPackage utility, making this a straightforward and fully supported migration method.

Answer

Configure transactional replication to replicate changes to Azure SQL Database

Answer

Use log shipping to continuously copy transaction logs

Answer

Restore a native SQL Server backup to Azure SQL Database

Question 18

You are a database administrator for a financial services company. The company has multiple Azure SQL Managed Instances in different regions for disaster recovery. Each Managed Instance hosts several databases. You need to automate the process of backing up all databases and copying the backup files to a central Azure Blob Storage account for long-term retention. The backup must be taken daily at 10 PM local time for each region. The solution must be resilient to regional outages and must not use native backup retention more than 7 days. Additionally, you must ensure that backup files are encrypted at rest and in transit. What should you do?

Accepted Answer

Create an Azure Automation account in each region with a PowerShell runbook that connects to the local Managed Instance, performs a full database backup to a local blob container, and then copies the backup file to the central storage account using AzCopy. Schedule the runbook to run daily at 10 PM local time.. Azure Automation accounts in each region with PowerShell runbooks can be scheduled to run daily at 10 PM local time. The runbook connects to the local Managed Instance, performs a full database backup to a local blob container (using BACKUP TO URL or similar), and then uses AzCopy to copy the backup file to the central storage account. This approach is resilient to regional outages because each region has its own automation account. Option A is incorrect because managing SQL Agent jobs manually across instances is less automated and less resilient; also, backing up directly to a central storage account may be affected by network issues. Option C is incorrect because Elastic Database Jobs are not available for Azure SQL Managed Instance. Option D is incorrect because Azure Backup for SQL Server is designed for SQL Server on Azure VMs, not for Managed Instance.

Answer

Configure a SQL Agent job on each Managed Instance to perform backups to the central storage account directly using BACKUP TO URL.

Answer

Use Elastic Database Jobs to schedule backups across all databases in all Managed Instances.

Answer

Enable Azure Backup for SQL Server in Azure Backup vault and configure backup policies for each Managed Instance.

Question 19

You need to run a complex T-SQL script on an Azure SQL Database every hour. The script performs data transformations that must be logged for auditing. Which native Azure service should you use?

Accepted Answer

Elastic Database Jobs with a T-SQL job step.. Elastic Database Jobs (option B) is the correct native Azure service for running complex T-SQL scripts on Azure SQL Database on a schedule with logging. It is designed for scheduled execution of T-SQL scripts across one or more databases and provides job logging and history. Option A is incorrect because SQL Agent is not available in Azure SQL Database single databases (only in SQL Server on VMs or Managed Instances). Option C is incorrect because Azure Data Factory is primarily an ETL service, not optimized for simple scheduled script execution, though it can be used, it is not the native simple solution. Option D is incorrect because Azure Automation Runbooks execute PowerShell, not T-SQL directly; while Invoke-SqlCmd can be used, it is not the native T-SQL scheduling service.

Answer

SQL Agent job on the Azure SQL Database.

Answer

Azure Data Factory with a stored procedure activity.

Answer

Azure Automation Account with a PowerShell runbook that uses Invoke-SqlCmd.

Question 20

You are a database administrator for a healthcare company. You have an Azure SQL Database that stores patient records. The database is currently accessible from the public internet via firewall rules. You need to implement a secure environment that meets the following requirements:
- All traffic to the database must be private and not traverse the internet.
- The database must be accessible from an Azure Virtual Machine in a specific VNet.
- The solution must minimize management overhead and cost.
- You need to ensure that the database can be failed over to a secondary region in case of an outage.

What should you do?

Accepted Answer

Create a private endpoint in the VNet, disable public network access, and configure a failover group with a private endpoint in the secondary region.. It meets all requirements: a private endpoint ensures all traffic to the database stays within the Microsoft backbone network and never traverses the internet. Disabling public network access enforces that no public traffic can reach the database, fulfilling the private traffic requirement. Configuring a failover group with a private endpoint in the secondary region provides geo-replication and automatic failover while maintaining private connectivity, with minimal management overhead and no additional cost for VPNs. Option A is incorrect because restricting the firewall to the VM's public IP still allows traffic over the public internet, violating the private traffic requirement. Option B is incorrect because a point-to-site VPN adds complexity and cost, and still uses a VPN connection that may traverse the internet, and does not provide the same seamless private connectivity as a private endpoint. Option D is incorrect because a VNet service endpoint does not make traffic private; it still uses the public endpoint of the database, and keeping public access enabled fails the requirement to disable public internet access.

Answer

Restrict firewall rules to only the VM's public IP and enable active geo-replication.

Answer

Configure a point-to-site VPN from the VM to the database and set up geo-replication.

Answer

Create a VNet service endpoint and a failover group. Keep public access enabled for failover.