DP-300 Practice Test 26 — 15 Questions

Question 1

You are tuning an Azure SQL Database workload. Which TWO actions can help reduce PAGELATCH_EX contention?

Accepted Answer

Add multiple data files to the filegroup. Adding multiple data files to the filegroup helps reduce PAGELATCH_EX contention by distributing page allocation activity across multiple files. When a single data file is used, all concurrent insert operations compete for the same allocation page (e.g., PFS, GAM, SGAM), causing exclusive latches. With multiple files, each file has its own set of allocation structures, allowing parallel allocation operations to proceed with less contention.

Answer

Increase MAXDOP

Answer

Use a columnstore index

Answer

Configure a readable secondary replica

Question 2

Your company is deploying a new Azure SQL Database that will store Personally Identifiable Information (PII). The security team requires that all queries against the database are audited and that any access to sensitive columns is logged with the actual data values. Which combination of features should you enable?

Accepted Answer

Enable SQL Server Audit on the database and create a database audit specification that captures SELECT and UPDATE on the sensitive columns.. SQL Server Audit (available in Azure SQL Database) allows you to create a database audit specification that captures SELECT and UPDATE operations on specific columns, including the actual data values accessed. This meets the security requirement to log access to sensitive columns with the actual data, whereas other options either lack column-level detail or do not capture the data values.

Answer

Enable Transparent Data Encryption (TDE) and configure a server-level audit specification for schema changes.

Answer

Enable Dynamic Data Masking on the sensitive columns and use Azure SQL Database Auditing.

Answer

Enable Azure SQL Database Auditing and configure the diagnostic settings to send logs to a Log Analytics workspace.

Question 3

A company has an Azure SQL Managed Instance that is experiencing high CPU usage. The DBA observes that a specific query is causing high compile time due to parameter sniffing. The query is executed frequently with varying parameter values. Which approach should the DBA use to reduce CPU usage without changing the T-SQL code?

Accepted Answer

Enable forced parameterization for the database. Forced parameterization (Option D) converts literal values in the query to parameters, allowing SQL Server to reuse cached execution plans across different parameter values. This reduces CPU usage by eliminating frequent recompilations caused by parameter sniffing, without requiring any changes to the T-SQL code itself.

Answer

Create a plan guide to force a specific plan

Answer

Use optimize for ad hoc workloads

Answer

Add query store and force the last good plan

Question 4

Your company has a strict policy that all Azure SQL Databases must have Microsoft Defender for SQL enabled. You need to enforce this policy across all subscriptions using a scalable, automated approach. What should you do?

Accepted Answer

Create an Azure Policy initiative that includes the 'Configure Microsoft Defender for SQL to be enabled' policy and assign it to the root management group.. Azure Policy provides a scalable, automated, and continuous enforcement mechanism across all subscriptions. By creating an initiative that includes the 'Configure Microsoft Defender for SQL to be enabled' policy and assigning it to the root management group, you ensure that any new or existing subscription inherits the policy, and non-compliant resources are automatically remediated or flagged. This approach aligns with the requirement for a strict, company-wide policy without manual intervention.

Answer

Use Azure Blueprints to deploy a predefined ARM template that enables Defender for SQL.

Answer

Create a script that runs periodically to check and enable Defender for SQL on all databases.

Answer

Assign the 'SQL Security Manager' role to a central team to manually enable Defender for SQL.

Question 5

You are designing a new Azure SQL Database deployment for a mission-critical application. The application requires 99.99% availability SLA, automated failover within 30 seconds, and read-scale capability for reporting. Which tier and configuration should you choose?

Accepted Answer

Business Critical with zone redundancy and a failover group. Business Critical with zone redundancy and a failover group meets all requirements: 99.99% SLA (supported by zone-redundant Business Critical), automated failover within 30 seconds (failover groups provide automatic, fast failover across regions), and read-scale capability (Business Critical includes a readable secondary replica).

Answer

General Purpose serverless with geo-replication

Answer

General Purpose with active geo-replication and read-scale

Answer

Hyperscale with zone redundancy enabled

Question 6

Your company uses Azure SQL Database with Microsoft Entra ID authentication. You need to ensure that only users with specific Azure AD roles can access the database. What should you configure?

Accepted Answer

Create contained database users mapped to Azure AD identities and grant appropriate permissions.. Azure SQL Database supports contained database users mapped to Azure AD identities, which allows you to grant granular permissions (e.g., db_datareader, db_owner) to specific Azure AD users or groups. This ensures that only those with the appropriate Azure AD roles (via group membership or direct assignment) can access the database, without relying on SQL Server logins or firewall rules.

Answer

Configure an Azure SQL Database firewall rule to allow only Azure AD IP addresses.

Answer

Assign the SQL Server Contributor role to Azure AD users.

Answer

Create SQL authenticated users with strong passwords.

Question 7

A developer reports that they cannot connect to an Azure SQL Database using Azure AD authentication. The developer is a member of an Azure AD group that has been granted db_datareader role in the database. The connection string uses Active Directory Password authentication. What is the most likely issue?

Accepted Answer

The Azure AD group has not been created as a database user.. The most likely issue is that the Azure AD group has not been created as a database user in the SQL Database. Even though the group has been granted the db_datareader role at the Azure AD level, Azure SQL Database requires that the group be explicitly mapped to a database user via the CREATE USER [group name] FROM EXTERNAL PROVIDER statement. Without this mapping, the group's membership does not translate into database-level permissions, and the developer's authentication will fail.

Answer

The server firewall is blocking the connection.

Answer

The developer's IP address is not allowed.

Answer

Multi-factor authentication is required but not configured.

Question 8

Which TWO actions should you take to implement a secure environment for Azure SQL Database that meets the principle of least privilege?

Accepted Answer

Assign database roles instead of individual permissions.. Assigning database roles instead of individual permissions (Option A) aligns with the principle of least privilege by grouping necessary permissions into predefined roles, reducing the risk of over-privileging and simplifying permission management. Contained database users with Azure AD authentication (Option C) eliminate the dependency on server-level logins, allowing authentication at the database level and enabling fine-grained access control without granting server-wide privileges.

Answer

Enable all database features for maximum functionality.

Answer

Use server-level logins for all users.

Answer

Grant the db_owner role to all application users.

Question 9

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database, and you need to ensure that resource usage is isolated and predictable. You also need to manage performance at the tenant level. Which Azure SQL Database offering should you choose?

Accepted Answer

Azure SQL Database elastic pools with per-database min/max DTU or vCore settings.. Azure SQL Database elastic pools with per-database min/max DTU or vCore settings are the correct choice because they provide resource isolation and predictable performance at the tenant level. Elastic pools allow you to allocate a shared pool of resources across multiple databases while setting per-database minimum and maximum limits, ensuring that no single tenant can consume excessive resources and that each tenant gets a guaranteed baseline. This directly addresses the multi-tenant SaaS requirement for isolated and predictable resource usage.

Answer

Azure SQL Database serverless compute tier.

Answer

Azure SQL Database single databases with DTU-based tier.

Answer

Azure SQL Managed Instance with multiple databases.

Question 10

Which TWO methods can be used to automate index maintenance in Azure SQL Database?

Accepted Answer

Azure Automation runbooks executing T-SQL scripts. Azure Automation runbooks can execute T-SQL scripts against Azure SQL Database using the Invoke-SqlCmd cmdlet or similar, enabling scheduled index maintenance tasks such as rebuilding or reorganizing indexes. This method is fully supported in Azure SQL Database, which lacks SQL Server Agent, and allows for flexible, cloud-native automation.

Answer

SQL Server Agent jobs

Answer

Azure Data Factory pipelines

Answer

Automatic tuning

Question 11

You are the database administrator for a company that uses Azure SQL Database. The company has a strict security policy requiring that all database connections be encrypted and that the server's firewall only allows connections from a list of approved IP addresses. You have configured the server-level firewall rules accordingly and enabled the 'Force encryption' setting on the server. However, after deployment, you notice that an application running on an Azure virtual machine is able to connect to the database even though its public IP address is not in the approved list. The virtual machine is in the same region as the database. What is the most likely cause?

Accepted Answer

The 'Allow Azure services and resources to access this server' setting is enabled, which allows all Azure IPs to connect.. The 'Allow Azure services and resources to access this server' setting, when enabled, adds a firewall rule that permits connections from all Azure IP addresses, including those not in the approved list. This overrides the specific IP-based firewall rules, allowing the Azure VM (even with a public IP not on the approved list) to connect. The setting is designed to allow Azure services like Azure App Service or Azure VMs to bypass IP restrictions, which violates the strict security policy in this scenario.

Answer

The virtual machine is using a private IP address that is automatically allowed.

Answer

The virtual machine is using a service endpoint that bypasses the firewall.

Answer

The 'Force encryption' setting is misconfigured; it should be disabled to allow non-encrypted connections.

Question 12

A company runs SQL Server 2019 on Azure Virtual Machines in an availability set. They need to achieve high availability for a critical database with automatic failover and no shared storage. The solution must minimize downtime during planned maintenance. What should they implement?

Accepted Answer

Create an Always On Availability Group with an availability group listener. An Always On Availability Group (AG) with a listener provides high availability with automatic failover at the database level without requiring shared storage. This solution meets the requirement for automatic failover and minimizes downtime during planned maintenance by allowing manual failover to a synchronized secondary replica with minimal disruption.

Answer

Configure Log Shipping to a secondary VM

Answer

Deploy a Failover Cluster Instance using Azure Shared Disks

Answer

Use Database Mirroring with automatic failover

Question 13

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. You need to implement a solution that allows applications to connect using their own managed identities without storing credentials in connection strings. What should you configure?

Accepted Answer

Enable Microsoft Entra ID authentication and assign managed identities to the applications.. Microsoft Entra ID authentication allows Azure SQL Database to trust tokens issued by Entra ID for managed identities. By assigning a managed identity to each application, the application can acquire an access token from Azure Managed Identity endpoints and present it to the database without ever storing credentials in connection strings. This eliminates the need for passwords or connection string secrets.

Answer

Use Always Encrypted with column master key in Azure Key Vault.

Answer

Configure firewall rules to allow application IP addresses.

Answer

Enable SQL Server authentication and create a login for each application.

Question 14

You need to create a new Azure SQL Database for a development environment. The development team requires the ability to restore the database to any point in time within the last 7 days. Which backup policy configuration should you choose?

Accepted Answer

Use the default backup retention of 7 days. The default backup retention for Azure SQL Database is 7 days, which meets the requirement to restore to any point in time within the last 7 days without additional configuration. Point-in-time restore (PITR) is enabled by default with this retention period, allowing automatic backups and restores within that window. No further backup policy changes are needed for this development environment.

Answer

Configure long-term retention (LTR) for 90 days

Answer

Configure point-in-time restore for 35 days

Answer

Configure point-in-time restore for 1 day

Question 15

You are a database administrator for a global SaaS provider. You have multiple Azure SQL Databases in a single logical server. You need to implement a disaster recovery strategy that provides automatic failover across Azure regions with the lowest possible RPO and RTO for critical databases. The solution must not require any application code changes. What should you implement?

Accepted Answer

Configure an auto-failover group across two Azure regions with a grace period of 1 hour and include all critical databases in the group.. Auto-failover groups provide automatic failover across Azure regions with the lowest possible RPO (typically 5 seconds) and RTO (around 1 minute) for critical databases, without requiring any application code changes. By setting the grace period to 1 hour, you allow the system to tolerate transient outages before triggering failover, which balances availability and data loss tolerance. Including all critical databases in the group ensures consistent failover behavior and prevents split-brain scenarios.

Answer

Use Azure SQL Database backup and restore across regions with a recovery point objective (RPO) of 1 hour.

Answer

Configure an auto-failover group across two Azure regions with a grace period of 0 and include only the most critical database.

Answer

Use active geo-replication for each critical database and configure a custom health check to trigger failover.