DP-300 Practice Test 5 — 10 Questions

Question 1

You need to automate the deployment of schema changes to multiple Azure SQL Databases in different regions. The solution must support rollback and version control. Which technology should you use?

Accepted Answer

Use Azure DevOps with a database project and release pipelines.. Azure DevOps with a database project and release pipelines is the correct choice because it provides source control for schema changes, automated deployment across multiple environments, and built-in rollback capabilities through pipeline versioning and deployment history. This approach aligns with infrastructure-as-code principles, enabling consistent, repeatable, and auditable schema deployments to Azure SQL Databases in different regions.

Answer

Use Azure Data Factory to run stored procedures for schema changes.

Answer

Use SQL Server Agent jobs to run deployment scripts on schedule.

Answer

Use Azure Automation with PowerShell scripts to execute T-SQL scripts.

Question 2

You administer an Azure SQL Database that uses the General Purpose tier. Users report that queries are slow during peak hours. You need to identify if the slow performance is due to log write latency. Which metric should you examine in Azure Monitor?

Accepted Answer

Log write latency. The correct metric to examine is Log write latency (option D), as it directly measures the time taken to write to the transaction log, which can indicate slow performance due to log write latency. Option A (Log IO percent) measures the percentage of log throughput used, not latency. Option B (Transaction log usage) measures log file space used, not performance. Option C (Average IO latency) includes both data and log I/O, so it is not specific to log writes.

Answer

Log IO percent

Answer

Transaction log usage

Answer

Average IO latency

Question 3

You manage an Azure SQL Database that is part of a business-critical application. You need to ensure that network traffic between the application hosted on Azure VMs and the database is encrypted and does not traverse the public internet. What should you configure?

Accepted Answer

Create a private endpoint for Azure SQL Database in the same virtual network as the application VMs.. Creating a private endpoint for Azure SQL Database places the database service on a private IP address within the same virtual network as the application VMs. This ensures that all traffic between the VMs and the database stays entirely within the Microsoft Azure backbone network, never traversing the public internet, while also providing encryption in transit via TLS by default.

Answer

Use TLS 1.2 for all connections to the database.

Answer

Configure server-level firewall rules to allow only the application VM IP addresses.

Answer

Enable forced tunneling on the application VMs to route all traffic through the on-premises network.

Question 4

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

Accepted Answer

Increase the log rate limit by scaling up the service level objective. In Azure SQL Database Hyperscale, log rate is governed by the service level objective, and when the log generation rate exceeds the limit the workload is throttled. Scaling up to a higher service level objective raises the log rate limit, directly relieving the throttling. This is the documented remedy for log rate throttling in Hyperscale.

Answer

Increase max degree of parallelism

Answer

Reduce backup retention period

Answer

Add more compute replicas

Question 5

Which TWO of the following are supported high availability features in Azure SQL Managed Instance?

Accepted Answer

Zone-redundant deployment for Business Critical tier. Option A is correct because Azure SQL Managed Instance in the Business Critical service tier supports zone-redundant deployment, which places the primary and secondary replicas across different availability zones to protect against datacenter-level failures. Option C is correct because SQL Managed Instance includes a built-in Always On Availability Groups mechanism that automatically provisions and manages replicas for high availability, with the Business Critical tier using local SSD and multiple secondary replicas. Database mirroring (B) is a deprecated SQL Server feature and is not offered as a supported HA option in Azure SQL Managed Instance. Log shipping to a secondary instance (D) is not a built-in HA feature of SQL Managed Instance, though it can be configured manually for disaster recovery scenarios. Windows Server Failover Clustering (E) is an underlying infrastructure concept used by SQL Server on-premises and Azure VMs, but it is not exposed or supported as a user-configurable HA feature in Azure SQL Managed Instance.

Answer

Database mirroring

Answer

Log shipping to a secondary instance

Answer

Windows Server Failover Clustering

Question 6

You are reviewing a PowerShell script that configures auditing for an Azure SQL Database. The script sets an audit rule with the specified parameters. After running the script, you notice that SELECT operations are not being audited. What is the most likely cause?

Accepted Answer

The AuditActionGroup specified does not capture SELECT operations.. The script likely specifies an AuditActionGroup that does not include the group responsible for capturing SELECT operations. In Azure SQL Database auditing, SELECT operations are captured by the SUCCESSFUL_SCHEMA_OBJECT_ACCESS_GROUP or similar action groups. If the configured AuditActionGroup omits this group, SELECT statements will not be logged, even though other operations may be audited correctly.

Answer

The retention days are set too low, causing logs to be overwritten.

Answer

The storage endpoint is incorrectly formatted.

Answer

The storage account access key is invalid.

Question 7

Which TWO of the following are benefits of using Azure SQL Database failover groups compared to active geo-replication alone?

Accepted Answer

Failover of multiple databases in a single group.. Failover groups extend active geo-replication by allowing you to manage failover for a group of databases as a single unit. This simplifies the failover process when you have multiple databases that must be failed over together to maintain application consistency. Option B is correct because failover groups support the coordinated failover of multiple databases, which active geo-replication alone does not.

Answer

Support for manual failover.

Answer

Readable secondary replicas for read-scale workloads.

Answer

Synchronous data replication to the secondary.

Question 8

Your organization uses Azure SQL Managed Instance and needs to implement a defense-in-depth strategy. Which THREE security controls should you implement? (Choose three.)

Accepted Answer

Enable advanced threat protection using Microsoft Defender for Cloud.. Option A is correct because enabling advanced threat protection via Microsoft Defender for Cloud provides detection and alerting for anomalous activities and potential vulnerabilities on Azure SQL Managed Instance, which is a core detective control in a defense-in-depth strategy. Option B is correct because server-level auditing captures database events and writes them to an audit log, providing the monitoring and traceability required for a layered security approach. Option D is correct because configuring network security groups (NSGs) on the subnet restricts inbound traffic to the managed instance, enforcing network-level segmentation and reducing the attack surface, which is a fundamental preventive control. Option C is not a security control; columnstore indexes are a performance optimization for analytical workloads and do not contribute to defense-in-depth. Option E is not the best fit because application roles manage permissions within a database but do not address the broader layered security controls expected in a defense-in-depth strategy for Azure SQL Managed Instance.

Answer

Create columnstore indexes on large tables to improve query performance.

Answer

Create application roles in each database to manage permissions.

Question 9

You need to automate the creation of a new Azure SQL Database whenever a new customer signs up. The solution should use infrastructure as code and integrate with your CI/CD pipeline. What should you use?

Accepted Answer

Create an ARM template that defines the database and deploy it from your CI/CD pipeline.. B is correct because ARM (Azure Resource Manager) templates are the recommended infrastructure-as-code approach for defining and deploying Azure SQL Databases in a repeatable, declarative manner. Integrating ARM template deployment into a CI/CD pipeline ensures consistent, version-controlled database creation as part of automated workflows, aligning with DevOps best practices.

Answer

Create an Azure Automation runbook that calls New-AzureRmSqlDatabase and trigger it from your CI/CD pipeline.

Answer

Set up an Elastic Database Job that runs a CREATE DATABASE statement.

Answer

Configure a SQL Server Agent job on the logical server to run a CREATE DATABASE statement.

Question 10

Your company has a compliance requirement to keep database backups for 10 years. You are using Azure SQL Database. Which backup retention feature should you use?

Accepted Answer

Long-term retention (LTR) policy. Long-term retention (LTR) policy is the correct feature for keeping database backups for 10 years, as it supports retention of full backups for up to 10 years. Point-in-time restore (PITR) is limited to a maximum of 35 days and cannot meet the 10-year requirement. Geo-redundant storage (GRS) provides geographic redundancy for backups but does not extend the retention period. Automated backups are retained for a maximum of 35 days by default and cannot be configured for 10-year retention.

Answer

Geo-redundant storage (GRS)

Answer

Automated backups

Answer

Point-in-time restore (PITR)