DP-300 Practice Exam 53 — 20 Questions

Question 1

You are setting up Azure SQL Database and need to ensure that only specific Azure services (e.g., Azure Data Factory) can access the database. What should you configure?

Accepted Answer

Add a virtual network rule for the subnet where the service is deployed.. A virtual network rule allows you to restrict access to your Azure SQL Database to traffic originating from a specific virtual network subnet. When Azure services like Azure Data Factory are deployed in a virtual network, you can create a virtual network rule that references the subnet of that service, ensuring only traffic from that subnet can connect. This provides a more secure and granular access control compared to IP-based firewall rules, as it leverages the Azure backbone network and avoids exposing the database to public IP ranges.

Answer

Create a private endpoint for the database.

Answer

Add firewall rules for the public IP addresses of the Azure services.

Answer

Add a firewall rule with start and end IP set to 0.0.0.0.

Question 2

You have an Azure SQL Database that uses the General Purpose service tier. You notice that the log write throughput is consistently near the limit. What should you do to improve log write performance?

Accepted Answer

Migrate to the Business Critical service tier.. The General Purpose service tier in Azure SQL Database has a maximum log write throughput of 1.5 MB/s for the most common configurations. The Business Critical tier uses local SSD storage and a higher log I/O limit (up to 100 MB/s), which directly addresses log write throughput bottlenecks. Migrating to Business Critical is the correct action because it provides significantly higher log write throughput and lower latency for transaction log writes.

Answer

Enable accelerated database recovery.

Answer

Migrate to the Hyperscale service tier.

Answer

Increase the DTU purchase model to a higher tier.

Question 3

Refer to the exhibit. You run the Azure CLI command to check the configuration of an Azure SQL Database named db1. The output shows zoneRedundant is true and replicationRole is Primary. Which statement is true about this database?

Accepted Answer

The database is zone-redundant and is the primary database in its replication relationship.. The output shows zoneRedundant is true and replicationRole is Primary. Zone redundancy means the database has replicas in different availability zones within the same region, and replicationRole being Primary indicates this database is the primary in its replication relationship. Therefore, option B is correct. Option A is incorrect because active geo-replication involves a secondary in a different region, not just zone redundancy. Option C is incorrect because Hyperscale databases have a different architecture; zoneRedundant may be supported but the output does not specify Hyperscale. Option D is incorrect because a readable secondary replica is not indicated; zone redundancy does not necessarily imply a readable secondary.

Answer

The database is configured with active geo-replication to a secondary region.

Answer

The database is a Hyperscale database with zone redundancy enabled.

Answer

The database has a readable secondary replica in a different Azure availability zone.

Question 4

You are configuring a new Azure SQL Database. The company policy requires that all connections use Microsoft Entra authentication and that no SQL authentication accounts exist. What should you do to prevent creation of SQL authenticated logins?

Accepted Answer

Set the 'DisallowSqlAuthentication' property to 'True' on the logical server.. Setting the 'DisallowSqlAuthentication' property to 'True' on the logical server enforces that only Microsoft Entra authentication can be used to connect to the Azure SQL Database. This property prevents the creation of any SQL authenticated logins, including the SQL admin account, and ensures compliance with the policy that no SQL authentication accounts exist.

Answer

Set the 'public_network_access' to 'Disabled'.

Answer

Remove the SQL admin login after creating the database.

Answer

Create an Azure Policy to audit SQL authentication usage.

Question 5

Match each Azure SQL Database high availability feature to its description.

Accepted Answer

Zone-redundant availability: Protects against datacenter failures by replicating databases across three availability zones within a region.. These features provide different levels of availability and disaster recovery for Azure SQL Database.

Answer

Zone-redundant availability: Automatically fails over databases across different Azure regions.

Answer

Active geo-replication: Creates four replicas in the same datacenter to provide high availability.

Question 6

You have an Azure SQL Database that is accessed by a web application. The application uses a service principal (Microsoft Entra application) to connect. You need to rotate the client secret for the service principal without downtime. What should you do?

Accepted Answer

Store the client secret in Azure Key Vault, configure the application to retrieve the secret from Key Vault, and implement a secret rotation policy.. It enables zero-downtime secret rotation by decoupling the application from a single secret value. By storing the client secret in Azure Key Vault and configuring the application to retrieve it dynamically, you can rotate the secret in Key Vault without changing the application's configuration or restarting it. The application always fetches the latest version of the secret, so rotation is transparent and does not interrupt connectivity.

Answer

Create a new client secret, update the application configuration, and then delete the old secret.

Answer

Switch to using a client certificate instead of a client secret.

Answer

Use a system-assigned managed identity for the web application.

Question 7

You are deploying an Azure SQL Database for a new application that requires consistent performance and low latency. The database will be accessed by users across multiple Azure regions. Which deployment option should you use?

Accepted Answer

Single database with Active Geo-Replication. Active Geo-Replication for a single database enables continuous data synchronization to readable secondary replicas in different Azure regions, providing low-latency read access for globally distributed users and supporting manual failover for write availability. This meets the requirement for consistent performance and low latency across multiple regions without the orchestration overhead of failover groups.

Answer

Managed Instance with failover groups

Answer

Single database in one region

Answer

Elastic pool in one region

Question 8

You manage a critical SQL Server on Azure Virtual Machine running SQL Server 2019. The VM is in a single availability set. You need to ensure automatic failover in case of a zone-level failure. What should you implement?

Accepted Answer

Deploy the VM in an availability zone and configure an Always On availability group with a secondary replica in another zone.. Deploying the VM in an availability zone and configuring an Always On availability group with a secondary replica in another zone ensures automatic failover if the primary zone fails. Option B is incorrect because an availability set only protects against failures within a datacenter, not zone-level failures. Option C is incorrect because geo-replication is designed for regional failure recovery, not zonal failover. Option D is incorrect because automated backups with long-term retention do not provide failover capabilities.

Answer

Place the VM in an availability set.

Answer

Enable geo-replication to another Azure region.

Answer

Configure automated backups with long-term retention.

Question 9

You are migrating an on-premises SQL Server 2012 database to Azure SQL Managed Instance. The database is 5 TB and uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. What is the best approach to migrate while preserving TDE?

Accepted Answer

Back up the certificate and private key to a .pfx file, restore the .pfx to the target managed instance, then restore the database backup.. TDE in SQL Server relies on a certificate (or asymmetric key) that must be present in the target instance to decrypt the database backup. By backing up the certificate and private key to a .pfx file and restoring it to Azure SQL Managed Instance, you ensure the target has the necessary encryption keys to read the backup. Azure SQL Managed Instance supports restoring TDE-protected backups only if the corresponding certificate is first restored into the master database.

Answer

Use Azure Data Studio to import the certificate directly from the local machine store during migration.

Answer

Disable TDE on the source database, migrate the backup, then enable TDE on the target.

Answer

Create a master key in the target managed instance and then restore the database; the certificate will be imported automatically.

Question 10

Which TWO of the following are valid methods to automate backups for Azure SQL Managed Instance? (Select exactly two.)

Accepted Answer

Schedule T-SQL BACKUP DATABASE TO URL statements via SQL Agent jobs.. Options B and D are correct. Azure SQL Managed Instance supports automated backups through the service's built-in point-in-time restore (PITR) and long-term retention (LTR) policies (option D). Additionally, you can schedule manual copy-only backups to Azure Blob Storage using T-SQL BACKUP DATABASE TO URL statements via SQL Agent jobs (option B). Option A is incorrect because Azure Backup does not natively support Azure SQL Managed Instance. Option C is incorrect because Azure Site Recovery is a disaster recovery solution, not a backup service. Option E is incorrect because Azure VM backup is designed for IaaS virtual machines, not for PaaS managed instances.

Answer

Use Azure Backup to schedule full backups.

Answer

Use Azure Site Recovery to replicate the instance.

Answer

Use Azure VM backup by installing the backup extension.

Question 11

Your organization requires that all changes to sensitive data in an Azure SQL Database be logged for compliance. You need to capture who changed what data and when, and store the logs in a Log Analytics workspace for analysis. What should you configure?

Accepted Answer

Configure server-level auditing to send logs to a Log Analytics workspace.. Server-level auditing in Azure SQL Database can be configured to send audit logs directly to a Log Analytics workspace, capturing detailed information about data changes including who made the change, what was changed, and when. This meets the compliance requirement for logging sensitive data changes and enables analysis using Log Analytics queries.

Answer

Enable change tracking on the database.

Answer

Enable Microsoft Defender for Cloud on the server.

Answer

Enable Transparent Data Encryption (TDE) with customer-managed keys.

Question 12

Your organization uses Azure SQL Database and wants to automatically detect and alert on potential SQL injection attacks. Which Azure service should you enable?

Accepted Answer

Microsoft Defender for SQL. Microsoft Defender for SQL (option C) is the correct answer because it provides advanced SQL security capabilities, including a dedicated SQL injection detection engine that analyzes database activity patterns and alerts on suspicious queries. Unlike other options, Defender for SQL specifically monitors for SQL injection attempts by evaluating query anomalies and known attack signatures, making it the appropriate service for automatic detection and alerting.

Answer

Azure SQL Database Vulnerability Assessment

Answer

Azure SQL Auditing

Answer

Microsoft Sentinel

Question 13

Your company has an Azure SQL Database with a failover group configured to a secondary region. The primary region experiences a temporary network issue. The failover group is set to automatic failover with a grace period of 1 hour. What will happen?

Accepted Answer

Automatic failover will start after 1 hour if the primary is still unreachable.. With automatic failover configured and a grace period of 1 hour, Azure SQL Database will wait for the grace period to expire before initiating failover. If the primary region remains unreachable after 1 hour, automatic failover occurs. Option A is incorrect because failover does not happen immediately; it waits for the grace period. Option B is incorrect because manual failover is possible but not required; automatic failover will proceed after the grace period. Option D is incorrect because the secondary database is not deleted or re-created; it is already provisioned and will become the primary upon failover.

Answer

Automatic failover will happen immediately.

Answer

You must manually initiate failover.

Answer

The secondary database will be deleted and re-created.

Question 14

You are designing an Azure SQL Database solution for a SaaS application that will host multiple tenants. Each tenant has a dedicated database. You need to ensure that tenants are isolated and resource usage is optimized. Which two features should you use? (Choose TWO.)

Accepted Answer

DTU-based purchasing model. (DTU-based purchasing model) is correct because it provides a simple, pre-configured bundle of compute, storage, and I/O resources, which is ideal for SaaS environments with multiple dedicated databases where predictable performance and isolation are needed. Option C (Elastic pool) is correct because it allows multiple databases to share a fixed pool of DTU resources, optimizing cost and resource utilization across tenants while maintaining logical isolation.

Answer

Serverless compute tier

Answer

Hyperscale service tier

Answer

vCore-based purchasing model

Question 15

You are monitoring an Azure SQL Database using Query Performance Insight. You see a query with high duration and high CPU usage. The query plan shows a clustered index scan. What is the most likely cause and recommendation?

Accepted Answer

Missing nonclustered index; create an index on the predicates.. Query Performance Insight shows a query with high duration and CPU usage, and the query plan reveals a clustered index scan. A clustered index scan reads all rows in the table, which is inefficient when only a subset of rows is needed. The most likely cause is a missing nonclustered index on the columns used in the WHERE clause (predicates), which would allow a seek operation instead of a full scan, reducing both CPU and duration.

Answer

Fragmented clustered index; rebuild the clustered index.

Answer

Insufficient memory; increase the service tier.

Answer

Parameter sniffing; add OPTION (RECOMPILE).

Question 16

You are planning a disaster recovery strategy for an Azure SQL Database that supports a critical application. The database is 500 GB in size and you need to recover it within 1 hour (RTO) with a maximum data loss of 5 minutes (RPO). Which Azure SQL Database feature should you use?

Accepted Answer

Active geo-replication to a secondary server in a paired region.. Active geo-replication provides asynchronous replication with an RPO of a few seconds to a few minutes, and failover can be initiated manually within minutes, meeting the RTO and RPO. Option A is wrong because point-in-time restore does not provide a separate secondary region. Option B is wrong because long-term retention is for archival, not DR. Option C is wrong because failover groups use geo-replication but the RPO is still asynchronous; the question asks for the feature, and geo-replication is the core.

Answer

Long-term retention (LTR) backups.

Answer

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

Answer

Auto-failover group with a secondary in a different region.

Question 17

Which TWO actions are valid for implementing column-level encryption in Azure SQL Database using Always Encrypted? (Choose two.)

Accepted Answer

Use randomized encryption for columns that will not be searched.. Always Encrypted supports two encryption types: deterministic and randomized. Randomized encryption is valid for columns that will not be searched because it encrypts the same plaintext into different ciphertexts each time, preventing pattern-based attacks. This makes it suitable for sensitive data like credit card numbers or personal identifiers that only need to be decrypted for use, not queried with equality predicates.

Answer

Store the column encryption key in the database.

Answer

Use a hash of the column value for encryption.

Answer

Encrypt an entire row by specifying a row-level encryption key.

Question 18

You administer a large Azure SQL Database that is used for a SaaS application. The database has a table with over 1 billion rows that is frequently queried by customer ID. The table currently has a clustered index on an identity column and a nonclustered index on customer ID. Queries that filter by customer ID are experiencing high IO and long execution times. You analyze the execution plan and see that the nonclustered index is used, but there are many key lookups. You need to optimize the query performance while minimizing storage overhead. What should you do?

Accepted Answer

Add all queried columns as included columns to the nonclustered index. Adding all queried columns as included columns to the existing nonclustered index on customer ID creates a covering index. This eliminates the need for key lookups, reducing IO and improving query performance for point lookups by customer ID. The storage overhead is minimal since included columns are stored only at the leaf level. Option A is wrong because a clustered columnstore index is designed for analytical workloads and can degrade point lookup performance. Option B is wrong because a filtered index on frequent values still may not cover all columns, leading to key lookups. Option C is wrong because partitioning does not eliminate key lookups and can add complexity without performance benefit for point queries.

Answer

Create a clustered columnstore index on the table

Answer

Create a filtered index on customer ID for frequent values

Answer

Partition the table by customer ID

Question 19

You are troubleshooting a performance issue in an Azure SQL Database. You need to identify the queries that are consuming the most CPU over the last hour. Which two methods can you use? (Choose two.)

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats (Option A) returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), which can be filtered by creation_time or last_execution_time to focus on the last hour. Query Store's top resource consuming queries report (Option E) provides a built-in, graphical view of queries ranked by CPU, duration, or other metrics over a configurable time window, making it ideal for identifying high-CPU queries in the last hour.

Answer

sys.dm_os_wait_stats

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_exec_requests

Question 20

You are reviewing an ARM template snippet that configures a long-term retention (LTR) policy for an Azure SQL Database. Based on the exhibit, how long will weekly backups be retained?

Accepted Answer

4 weeks. [CORRECT]. In the ARM template snippet, the weeklyRetention property is set to 'P4W' (ISO 8601 duration format), which means 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks.

Answer

4 days. [wrong]

Answer

4 months. [wrong]

Answer

4 years. [wrong]