DP-300 Practice Test 18 — 15 Questions

Question 1

Match each Azure SQL Database service tier to its description.

Accepted Answer

General Purpose: Balanced performance for most cloud workloads with compute and storage separation.. These are the main service tiers in Azure SQL Database, each designed for different performance and scalability needs.

Answer

General Purpose: High I/O and low latency with multiple read-only replicas.

Answer

Business Critical: Scalable storage up to 100 TB with auto-scaling compute.

Answer

Hyperscale: Balanced performance for small to medium workloads with compute and storage separation.

Question 2

You need to audit all schema changes (DDL) on an Azure SQL Database for compliance. The audit logs must be retained for 7 years. What should you do?

Accepted Answer

Enable auditing on the database, log to a storage account, and set the retention policy to 7 years.. Azure SQL Database auditing can be configured at the database level to log all database events, including DDL changes, to a storage account. The retention policy can be set to 7 years directly in the audit settings, ensuring compliance with long-term retention requirements. This is the native, supported method for auditing schema changes on Azure SQL Database.

Answer

Create an extended events session to capture DDL events and save to a file.

Answer

Enable change tracking on the database and query the change tracking tables.

Answer

Enable SQL Server Audit at the server level and specify a file destination.

Question 3

You have an Azure SQL Database that is experiencing performance degradation. You suspect that parameter sniffing is causing suboptimal execution plans. What should you do to mitigate this issue without changing application code?

Accepted Answer

Enable forced parameterization for the database.. Forced parameterization (option D) is the correct mitigation because it instructs the SQL Server query optimizer to treat all literal values in queries as parameters, which reduces the likelihood of parameter sniffing causing suboptimal plans for repeated queries. This is done at the database level without requiring any changes to application code, making it ideal for scenarios where code modification is not feasible.

Answer

Add the OPTION (RECOMPILE) query hint to all queries.

Answer

Clear the plan cache using DBCC FREEPROCCACHE.

Answer

Enable the 'Optimize for Ad Hoc Workloads' setting.

Question 4

Which TWO actions should you perform to enable automatic tuning for an Azure SQL Database?

Accepted Answer

Enable automatic tuning at the Azure SQL Database server level. Automatic tuning in Azure SQL Database requires enabling it at both the server level (to set the inheritance policy) and the database level (to apply tuning options like FORCE_LAST_GOOD_PLAN or CREATE_INDEX). Option B is correct because enabling at the server level allows you to define a default tuning policy that can be inherited by all databases, which is a prerequisite for per-database tuning. Option C is correct because you must also explicitly enable automatic tuning at the database level to override or confirm the inherited settings for that specific database.

Answer

Set the database compatibility level to 150

Answer

Enable Query Store

Answer

Configure performance recommendations

Question 5

You are configuring Azure SQL Database firewall rules. You need to allow a team of developers to connect from their office IP range (192.168.1.0/24) to a specific database. The developers should not be able to access other databases on the same logical server. What should you do?

Accepted Answer

Add a database-level firewall rule for the IP range 192.168.1.0/24.. Database-level firewall rules in Azure SQL Database allow you to restrict access to a specific database on a logical server, rather than the entire server. By adding a rule for the IP range 192.168.1.0/24 at the database level, the developers can connect only to that database, and they will be blocked from accessing other databases on the same server. This is the correct approach because server-level rules would grant access to all databases, which violates the requirement.

Answer

Create a private endpoint for the database.

Answer

Add a server-level firewall rule for the IP range 192.168.1.0/24.

Answer

Configure a virtual network service endpoint for the server.

Question 6

Your company uses Azure SQL Database with Microsoft Defender for Cloud. You receive an alert indicating a potential SQL injection attack. The alert shows that the attack originated from the IP address of your company's VPN gateway. You have verified that no legitimate users are using the VPN at that time. What should you do to immediately stop the attack while preserving legitimate access?

Accepted Answer

Remove the firewall rule that allows the VPN gateway IP address. The attack is originating from the VPN gateway's IP address, and the firewall rule allowing that IP is the only entry point for the attacker. By removing that specific firewall rule, you immediately block the malicious traffic while preserving legitimate access through other firewall rules (e.g., for on-premises or other VPN ranges). This directly stops the SQL injection attack at the network layer without affecting other users or services.

Answer

Disable the public endpoint on the SQL Database

Answer

Change the SQL Database server admin password

Answer

Add the attacker's IP address to a block list in Microsoft Defender for Cloud

Question 7

You are planning a disaster recovery strategy for an Azure SQL Database that is used by a non-critical application. The database is in the Standard tier. You need to minimize cost while ensuring the database can be restored to a different region within 12 hours of a regional disaster. What should you use?

Accepted Answer

Use geo-restore from geo-redundant backups.. Geo-redundant backup storage (RA-GRS) is automatically enabled for databases in the Standard tier, allowing geo-restore to any region within 12 hours. Option A is wrong because active geo-replication is not available in Standard tier. Option C is wrong because failover groups require Premium or Business Critical. Option D is wrong because cross-region replication is a setting for backup storage, not a separate feature.

Answer

Enable cross-region replication on the database.

Answer

Configure active geo-replication to a secondary region.

Answer

Create a failover group with the secondary in a paired region.

Question 8

Your company has an Azure SQL Managed Instance deployed in the East US region. The database hosts a financial application that requires a Recovery Time Objective (RTO) of 15 minutes and a Recovery Point Objective (RPO) of 15 seconds. The solution must use a secondary region in West US. The budget allows for a single secondary instance but must minimize compute costs during normal operations. You have configured a failover group between the primary and a secondary managed instance in West US. During a recent disaster recovery drill, you observed that the failover took 20 minutes to complete, exceeding the RTO. You need to improve the failover time to meet the 15-minute RTO without increasing costs. What should you do?

Accepted Answer

Configure the failover group to use manual failover instead of automatic; this reduces failover time because you can control the exact moment.. Configuring manual failover allows the database administrator to initiate failover immediately without waiting for a grace period. This reduces the overall failover time, as the 20-minute failover observed likely included time waiting for automatic failover to trigger after the grace period. With manual failover, the switch can be performed in less than 15 minutes. Option B is incorrect because setting the grace period to 1 hour would actually increase the time before automatic failover occurs, and enabling read-scale failover does not reduce failover time. Option C increases cost and does not directly affect failover time. Option A does not significantly reduce failover time.

Answer

Ensure the secondary managed instance is in a different virtual network and configure cross-region VNet peering for faster synchronization.

Answer

Create a failover group with automatic failover policy and set the grace period to 1 hour. Also, enable read-scale failover for the secondary.

Answer

Scale up the primary managed instance to a higher service tier (e.g., from General Purpose to Business Critical) to improve performance.

Question 9

A company has an Azure SQL Database that experiences periodic performance degradation. The database uses the General Purpose service tier. You need to identify the most common performance bottlenecks. You enable the Query Store and collect data for a week. Which Query Store view should you query to find queries that have the highest total resource consumption over time?

Accepted Answer

sys.query_store_query_stats. A is correct because sys.query_store_query_stats aggregates runtime statistics per query across all plans and time intervals, making it the ideal view to identify queries with the highest total resource consumption (e.g., CPU, I/O, duration) over the collected week. Query Store captures historical execution data, and this view provides the cumulative metrics needed to pinpoint the most resource-intensive queries for performance bottleneck analysis.

Answer

sys.query_store_plan

Answer

sys.dm_exec_query_stats

Answer

sys.dm_db_resource_stats

Question 10

You have an Azure SQL Managed Instance configured with an auto-failover group between two regions. You need to ensure that client applications can automatically connect to the secondary instance after a failover without changing connection strings. What should you configure?

Accepted Answer

Use the failover group listener endpoint in the connection string.. The failover group listener provides a read-write listener endpoint that automatically points to the current primary. Option B is wrong because a Traffic Manager profile would require manual intervention. Option C is wrong because an Application Gateway does not provide automatic failover redirection. Option D is wrong because a load balancer does not handle failover group redirection.

Answer

Set up a load balancer with health probes.

Answer

Deploy an Application Gateway with backend pools for each region.

Answer

Configure a Traffic Manager profile with endpoint monitoring.

Question 11

A company has multiple Azure SQL Databases and wants to automate the deployment of schema changes using a CI/CD pipeline. They need to ensure that changes are automatically rolled back if any step fails. Which Azure service should they use?

Accepted Answer

Azure DevOps with Azure SQL Database deployment task. Azure SQL Database elastic jobs are designed for automating administrative tasks, not CI/CD. Azure Automation with PowerShell runbooks can orchestrate rollbacks but requires custom scripting. Azure DevOps with Azure SQL Deployment task supports transactional deployments with built-in rollback on failure. Azure Machine Learning is irrelevant.

Answer

Azure Automation with PowerShell runbooks

Answer

Azure SQL Database elastic jobs

Answer

Azure Machine Learning

Question 12

You are monitoring an Azure SQL Database that is running a mission-critical workload. You notice that the DTU consumption is consistently above 90% during peak hours. You need to recommend a solution to reduce the DTU consumption. What should you recommend?

Accepted Answer

Scale up to a higher service tier or increase DTUs. When DTU consumption consistently exceeds 90%, the database is resource-constrained, leading to performance degradation. Scaling up to a higher service tier or increasing DTUs directly provides more CPU, memory, and I/O resources, alleviating the bottleneck and reducing DTU utilization percentage. This is the standard corrective action for sustained high DTU usage in Azure SQL Database.

Answer

Scale down to a lower service tier

Answer

Enable geo-replication

Answer

Enable read scale-out

Question 13

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database. You need to ensure that tenant data is isolated and that performance is predictable. Cost efficiency is important. Which deployment model should you use?

Accepted Answer

Use an elastic pool with one database per tenant. An elastic pool allows you to provision a shared set of resources (eDTUs or vCores) that is distributed across multiple databases, each representing a tenant. This provides logical isolation of tenant data (each tenant has its own database) while pooling resources to handle variable workloads cost-effectively. The elastic pool model is specifically designed for SaaS multi-tenant scenarios where predictable performance is achieved through resource governance, and cost efficiency comes from sharing resources among databases that do not all peak simultaneously.

Answer

Deploy a single Azure SQL Database per tenant

Answer

Use Azure SQL Managed Instance with multiple databases

Answer

Use a single large database with row-level security

Question 14

Your company is planning to migrate on-premises SQL Server databases to Azure SQL Managed Instance. The security team requires that all database connections be encrypted and that the server's identity be verified using a certificate from a trusted public certificate authority (CA). What should you configure?

Accepted Answer

Set the 'Force Encryption' property to True for the managed instance.. Setting 'Force Encryption' to True on the managed instance enforces TLS encryption for all client connections and ensures the server presents a certificate from a trusted public CA to verify its identity. This meets both requirements: encryption of data in transit and server identity verification via a trusted certificate chain.

Answer

Install a custom CA-signed certificate on the managed instance.

Answer

Enable Transparent Data Encryption (TDE) with a server certificate.

Answer

Configure Always Encrypted with column master key in Key Vault.

Question 15

You are a database administrator for a SaaS company. You manage an Azure SQL Database that uses the Hyperscale service tier. The database supports a multi-tenant application with unpredictable workloads. You notice that the database's page server is experiencing high IOPS utilization, causing query performance degradation. You need to improve performance by optimizing the page server usage. What should you do?

Accepted Answer

Increase the number of secondary replicas to distribute read workload.. In Azure SQL Database Hyperscale, increasing the number of secondary replicas can help distribute read workloads. Secondary replicas have their own buffer pool caches, which can reduce page server IOPS utilization by serving read requests from cache rather than repeatedly fetching pages from the page server. Option B is incorrect because Accelerated Database Recovery (ADR) reduces undo log space requirements but does not affect page server IOPS. Option C is incorrect because changing to Business Critical tier moves away from the Hyperscale architecture and does not directly address page server performance. Option D is incorrect because increasing MAXDOP may increase parallelism and potentially lead to higher IOPS, not reduce it.

Answer

Enable accelerated database recovery (ADR) to reduce log IO.

Answer

Change the service tier to Business Critical to get faster IO.

Answer

Increase the MAXDOP setting for the database.