DP-300 Practice Exam 57 — 20 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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.

Question 13

Refer to the exhibit. Which action should you take to improve performance?

Accepted Answer

Force plan 2 using the query store.. The exhibit shows two plans for the same query. Plan 2 has lower CPU, reads, and duration. Plan 1 is being forced, causing poor performance. The best action is to force plan 2.

Answer

Enable automatic tuning for the query.

Answer

Increase the database's DTU/DTU level.

Answer

Drop and recreate the query's indexes.

Question 14

You manage an Azure SQL Database that supports a critical financial application. The database is in the General Purpose tier and uses active geo-replication for disaster recovery. You need to automate the process of failing over to the secondary region in case of a regional outage, but only after confirming that the primary is unreachable for more than 5 minutes. Additionally, you need to send an alert to the operations team when the failover occurs. The solution should use Azure services and minimize manual steps. What should you implement?

Accepted Answer

Use Azure Logic Apps with a timer trigger to check the database status every minute and initiate failover if unreachable.. Azure Logic Apps can automate the failover process by periodically checking the primary database's connectivity. If it remains unreachable for more than 5 minutes, it can initiate a geo-replication failover and send alerts to the operations team. This meets all requirements using Azure services with minimal manual steps. Option B is incorrect because SQL Agent is not available in Azure SQL Database as a PaaS service. Option C is incorrect because Azure Site Recovery is designed for virtual machines, not for Azure SQL Database geo-replication. Option D is incorrect because the 'Deadlock count' metric is unrelated to database connectivity; it measures deadlocks, not reachability, and the described threshold does not correctly detect unreachability.

Answer

Schedule a SQL Agent job on the secondary to run a script that checks connectivity and fails over.

Answer

Configure Azure Site Recovery for the SQL database.

Answer

Configure an Azure Monitor metric alert on the 'Deadlock count' metric (or custom metric) with a threshold of 0 for 5 minutes, then use an action group to trigger an Azure Automation runbook that runs the failover PowerShell cmdlet.

Question 15

A company plans to deploy Azure SQL Database in a new region. They require that the database be accessible only from a specific set of Azure virtual machines in the same region. What should the DBA configure?

Accepted Answer

Configure a VNet service endpoint and a firewall rule for the VNet subnet. A VNet service endpoint extends your virtual network private address space and the identity of your VNet to the Azure SQL Database service over a direct connection. By combining a service endpoint on the subnet with a firewall rule that allows traffic only from that specific VNet/subnet, you restrict access exclusively to Azure VMs in that subnet, meeting the requirement without exposing the database to the public internet.

Answer

Use a private endpoint only

Answer

Configure a point-to-site VPN

Answer

Set the firewall to allow Azure services and resources to access the server

Question 16

You are reviewing the audit configuration of an Azure SQL Database using the above JSON from Azure Resource Manager. Based on the exhibit, which of the following is true?

Accepted Answer

Audit logs are sent to Azure Monitor. The JSON shows 'isAzureMonitorTargetEnabled' is true, indicating audit logs are sent to Azure Monitor. Option B is incorrect because 'state' is 'Enabled', meaning auditing is enabled. Option A is incorrect because 'isStorageSecondaryKeyInUse' is false. Option C is incorrect because 'retentionDays' is 90, not 30.

Answer

Audit logs are written using the secondary storage key

Answer

Auditing is currently disabled

Answer

Audit logs are retained for 30 days

Question 17

You are the database administrator for a company that uses Azure SQL Managed Instance. The security team has detected unusual query activity from a specific client IP address. You need to immediately block all connections from that IP address while maintaining connectivity for all other users. Which solution should you implement?

Accepted Answer

Add a firewall rule at the managed instance level that denies the specific IP address.. Azure SQL Managed Instance supports instance-level firewall rules that can explicitly deny a specific client IP address while allowing all other traffic. Unlike Azure SQL Database, which uses server-level firewall rules, Managed Instance firewall rules are configured directly on the instance and can include both allow and deny entries, enabling granular access control without affecting other users.

Answer

Create a private endpoint for the managed instance and disable public network access.

Answer

Add a server-level firewall rule at the logical server level to deny the IP address.

Answer

Modify the network security group (NSG) associated with the managed instance's subnet to deny the IP address.

Question 18

You need to ensure that all queries accessing a specific column containing credit card numbers are encrypted both at rest and in transit between the application and Azure SQL Database. Which THREE technologies should you implement? (Choose three.)

Accepted Answer

Enforce TLS 1.2 or higher for client connections. Enforcing TLS 1.2 or higher for client connections encrypts data in transit between the application and Azure SQL Database. TLS ensures that all queries and their results, including the credit card column, are protected from interception during network transmission.

Answer

Row-Level Security

Answer

Dynamic Data Masking

Question 19

Which TWO options are valid services for scheduling automated tasks for Azure SQL Database? (Choose two.)

Accepted Answer

Azure Automation. Azure Automation is correct because it provides a cloud-based automation and configuration service that supports scheduling PowerShell or Python runbooks to execute tasks against Azure SQL Database, such as running T-SQL scripts or performing maintenance operations. It integrates natively with Azure SQL via the Az module and can be triggered on a recurring schedule, making it a valid service for scheduling automated tasks.

Answer

SQL Server Agent

Answer

Azure Functions

Answer

Azure Logic Apps

Question 20

Refer to the exhibit. You have an Azure SQL Managed Instance configured with the failover group properties shown. The primary region experiences an outage. After 30 minutes, the failover group has not failed over. You need to force failover immediately. What should you do?

Accepted Answer

Use the Switch-AzSqlDatabaseFailoverGroup cmdlet with -AllowDataLoss.. When the failover group has an automatic failover policy and the primary region experiences an outage, the automatic failover will only occur after the grace period (typically 1 hour) expires. Since only 30 minutes have passed, the failover has not yet triggered automatically. To force an immediate failover, you can use the Switch-AzSqlDatabaseFailoverGroup cmdlet with the -AllowDataLoss parameter, which initiates a forced failover that may result in data loss. Option A is incorrect because changing the failover policy to Manual does not force a failover; it only changes the policy for future failovers. Option B is incorrect because setting secondaryType to Standby is not relevant to initiating a failover. Option D is incorrect because waiting for the grace period to expire would eventually result in automatic failover, but the requirement is to force failover immediately.

Answer

Change the failover policy to Manual and then fail over.

Answer

Set the secondaryType to Standby.

Answer

Wait for the grace period to expire.