DP-300 Practice Test 35 — 15 Questions

Question 1

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses a SQL Server Agent job that runs a PowerShell script to process files. You need to ensure the job continues to run after migration with minimal changes. What should you do?

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

Accepted Answer

Migrate the database to Azure SQL Managed Instance and recreate the job using SQL Server Agent.. Azure SQL Managed Instance supports SQL Server Agent, including jobs that run PowerShell scripts via CmdExec or PowerShell job steps. By migrating the database and recreating the job with the same script, you preserve the existing automation with minimal changes. This is the only option that keeps the job execution environment identical to the on-premises setup.

Answer

Migrate the database to Azure SQL Managed Instance and configure an Azure Automation runbook to run the script.

Answer

Migrate the database to Azure SQL Managed Instance and use Elastic Jobs to run the PowerShell script.

Answer

Migrate the database to Azure SQL Database and use Elastic Jobs to run the PowerShell script.

Question 2

You are planning high availability for an Azure SQL Database that runs an e-commerce application. The database uses the Business Critical service tier. Which TWO features are automatically enabled to provide high availability within a single region? (Choose two.)

Accepted Answer

Automatic failover to a secondary replica if the primary becomes unavailable.. Option A is correct because in the Business Critical tier Azure SQL Database automatically provisions a primary replica plus secondary replicas and performs automatic failover to a secondary if the primary becomes unavailable, with no user action required. Option B is correct because Business Critical uses Always On availability group technology to maintain multiple synchronous replicas (one primary and two secondary) within the same region, ensuring data redundancy and fast failover. Option C is incorrect because auto-failover groups are a user-configured feature for cross-region disaster recovery, not an automatic single-region HA capability. Option D is incorrect because active geo-replication is a manually configured cross-region replication feature, not automatically enabled for single-region HA. Option E is incorrect because read scale-out using a readable secondary is an optional configuration for offloading read workloads, not an automatic HA mechanism.

Answer

Auto-failover groups with a secondary in another region.

Answer

Active geo-replication to a paired region.

Answer

Read scale-out with a readable secondary replica.

Question 3

Which TWO of the following are best practices for managing firewall rules for Azure SQL Database?

Accepted Answer

Use Azure Private Link to connect from Azure VNets instead of opening firewall rules to IP ranges.. Option C is correct because Azure Private Link (Private Endpoint) gives Azure SQL Database a private IP inside your VNet, so clients connect over the Microsoft backbone without any public IP firewall rules, which is the recommended way to restrict access from Azure VNets. Option D is correct because Azure Activity Logs record control-plane operations such as creating, updating, or deleting firewall rules, providing the audit trail needed to detect unauthorized or accidental rule changes. Options A and B are not best practices: IP-based rules for all clients, especially Azure services, expose the logical server to the public internet, and broad ranges like 0.0.0.0/0 defeat the purpose of a firewall. Option E is not recommended because the 'Allow Azure Services' rule (0.0.0.0) permits connections from any Azure tenant, not just your own resources, so it should be avoided in favor of Private Link or specific IP rules.

Answer

Use IP-based firewall rules for all client connections, including Azure services.

Answer

Create firewall rules with broad IP ranges (e.g., 0.0.0.0/0) to simplify management.

Answer

Enable the 'Allow Azure Services' firewall rule to allow connections from Azure services.

Question 4

Which THREE actions are required to configure Microsoft Entra ID authentication for an Azure SQL Database? (Choose three.)

Accepted Answer

Set a Microsoft Entra ID administrator for the Azure SQL Database server.. Option B is correct because every Azure SQL logical server must have a Microsoft Entra ID administrator provisioned (via the server's Microsoft Entra ID admin setting) before Entra authentication can be used; this admin acts as the security principal authorized to manage Entra logins and users on the server. Option C is correct because, after the Entra admin is set, you must create contained database users (for example, CREATE USER [name] FROM EXTERNAL PROVIDER) in each target database and grant them permissions, since Entra principals are not automatically mapped to database-level access. Option D is correct because the Entra identity used to authenticate must belong to the same tenant as the Azure SQL server (or be a supported guest/B2B identity), as cross-tenant authentication is not supported for Azure SQL Database. Option A is incorrect because no special firewall rule for the 'Microsoft Entra ID service' is required; Entra authentication uses the existing SQL firewall rules for client connectivity, not a service-specific rule. Option E is incorrect because SQL authentication does not need to be enabled as a fallback — Entra-only authentication is fully supported and SQL auth can even be disabled.

Answer

Configure a firewall rule to allow connections from the Microsoft Entra ID service.

Answer

Ensure that SQL authentication is enabled as a fallback.

Question 5

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You want to identify the top queries by total CPU time over the last hour. Which DMV should you query?

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats exposes cumulative per-plan statistics including total_worker_time, which represents total CPU time consumed by a query plan. Aggregating and ordering by total_worker_time identifies the top CPU-consuming queries. It is the standard DMV for query-level CPU analysis in Azure SQL Database.

Answer

sys.dm_exec_requests

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_db_resource_stats

Question 6

Which TWO actions are required to enable Microsoft Entra ID authentication for Azure SQL Database?

Accepted Answer

Set an Microsoft Entra ID administrator for the logical server. Setting a Microsoft Entra ID administrator for the logical server (Option A) is required because it establishes the Entra ID tenant as an identity provider for the Azure SQL Database logical server, enabling token-based authentication. Creating a contained database user mapped to an Entra ID identity (Option D) is required because Azure SQL Database uses contained database users for authentication, where the user is authenticated directly against the database without requiring a login in the master database. These two actions together allow Entra ID users to authenticate to the database using their cloud identities.

Answer

Create a login in the master database for each Entra ID user

Answer

Assign a system-assigned managed identity to the logical server

Answer

Enable 'contained database authentication' on the server

Question 7

You need to automatically send an email notification when an Azure SQL Database reaches 80% storage usage. What should you configure?

Accepted Answer

Azure Monitor alert with action group. Azure Monitor alerts with action groups are the native mechanism for triggering notifications based on platform metrics such as storage percentage on an Azure SQL Database. You create an alert rule on the 'storage' metric with a threshold of 80%, and attach an action group that sends email (or SMS, webhook, etc.). This is the standard, supported approach for metric-based notifications on Azure SQL Database.

Answer

Change Data Capture (CDC) with Logic Apps

Answer

Elastic Database Job with sp_send_dbmail

Answer

SQL Agent Mail

Question 8

You are the DBA for an Azure SQL Database that stores sensitive customer data. The security team requires that database administrators be able to manage the database but not see the sensitive data in plaintext. You need to implement a solution that meets this requirement with minimal application changes. What should you do?

Accepted Answer

Implement Always Encrypted with column encryption keys stored in Azure Key Vault.. Always Encrypted is designed to protect sensitive data from high-privileged users like DBAs. By storing column encryption keys in Azure Key Vault and configuring the client driver, data remains encrypted on the server and is only decrypted by authorized applications. This meets the requirement with minimal application changes because the encryption is handled by the client driver without altering the database schema significantly.

Answer

Implement Transparent Data Encryption (TDE) with a customer-managed key.

Answer

Implement Row-Level Security (RLS) on the sensitive tables.

Answer

Implement Dynamic Data Masking on the sensitive columns.

Question 9

You are configuring security for an Azure SQL Database. You need to ensure that only members of a specific Microsoft Entra ID group can connect to the database as contained database users with db_owner permissions. What should you do?

Accepted Answer

Create a contained database user for the Microsoft Entra ID group and add it to the db_owner role.. Contained database users in Azure SQL Database can be mapped to Microsoft Entra ID groups, allowing group members to authenticate and inherit permissions. Adding the group to the db_owner role grants the necessary database-level permissions. This method centralizes management in Entra ID and follows least privilege by scoping permissions to the database.

Answer

Create a SQL login for each member of the Microsoft Entra ID group and grant them db_owner permissions.

Answer

Configure the Microsoft Entra ID group as the server admin for the logical server.

Answer

Enable Microsoft Entra ID authentication on the server and assign the group to the db_owner role using Azure RBAC.

Question 10

You have an Azure SQL Database that is experiencing performance issues. You suspect that a recent deployment introduced a regression in a stored procedure. You need to identify the query plan change and the specific query that is performing poorly. What should you use?

Accepted Answer

Query Store. Query Store is the correct tool because it retains historical query plans and runtime statistics, enabling you to detect plan changes and performance regressions. It can show when a stored procedure's plan changed and how its performance degraded. Other tools either lack historical data (DMVs), are unavailable (Profiler), or lack query-level detail (Azure Monitor metrics).

Answer

Azure Monitor metrics

Answer

SQL Server Profiler

Answer

Dynamic management views (DMVs)

Question 11

You are planning to deploy a new Azure SQL Database for an internal HR application. The application is used only during business hours on weekdays and can tolerate a brief outage if the database needs to be scaled. To minimize cost, you need the database to automatically scale compute resources based on workload demand and allow the database to be paused when not in use. Which purchasing model and service tier should you choose?

Accepted Answer

vCore-based purchasing model with the General Purpose service tier and serverless compute. The vCore-based General Purpose tier with serverless compute is designed for intermittent, unpredictable workloads. It automatically scales compute within a configured range and pauses the database when inactive, reducing cost. The other options use provisioned compute or DTU-based models that do not support auto-scaling or auto-pausing, so they cannot meet the cost-saving requirements.

Answer

DTU-based purchasing model with the Standard service tier

Answer

vCore-based purchasing model with the Business Critical service tier and provisioned compute

Answer

DTU-based purchasing model with the Basic service tier

Question 12

You need to deploy an Azure SQL Database that automatically scales compute resources based on workload demand, with minimal administrative overhead. The database should scale between a minimum and maximum number of vCores. Which purchasing model and service tier should you use?

Accepted Answer

vCore-based purchasing model with the Serverless compute tier.. The vCore-based Serverless compute tier is designed for automatic scaling and minimal administration. It scales compute based on workload and can pause during inactivity, making it ideal for applications with unpredictable or intermittent usage. It allows setting a minimum and maximum vCore range, providing control over cost and performance.

Answer

DTU-based purchasing model with the Basic service tier.

Answer

vCore-based purchasing model with the Provisioned compute tier.

Answer

DTU-based purchasing model with the Standard service tier.

Question 13

You administer an Azure SQL Database named SalesDB in the East US region. The business requires that SalesDB remains available even if an entire Azure availability zone within East US fails. You need to configure the database so that replicas are automatically distributed across multiple availability zones with no application connection string changes. What should you do?

Accepted Answer

Enable zone redundancy for the database.. Zone redundancy in Azure SQL Database spreads replicas across availability zones within a region, providing automatic failover if a zone becomes unavailable. It requires no application changes because the same server hostname is used. Auto-failover groups and active geo-replication are cross-region features, and read-scale out is for read offloading, so none of those meet the intra-region zone failure requirement.

Answer

Configure an auto-failover group to a secondary region.

Answer

Enable read-scale out and point the application to the read-only replica.

Answer

Create an active geo-replication secondary in the same region.

Question 14

You are responsible for an Azure SQL Managed Instance that hosts a database with a table named Orders. The table has a clustered index on OrderID and a nonclustered index on CustomerID. You notice that a frequently executed query that filters on CustomerID and returns a small number of rows is performing a clustered index scan. You need to improve the query performance. What should you do?

Accepted Answer

Create a covering nonclustered index on CustomerID that includes the columns required by the query.. When a query filters on a nonclustered index key but requires additional columns not in the index, the optimizer may choose a clustered index scan to avoid key lookups. Making the nonclustered index covering by including the required columns allows an index seek that returns all needed data without touching the base table. This is the most direct and reliable way to improve performance for this frequent query.

Answer

Update statistics on the CustomerID column.

Answer

Force the query to use the existing nonclustered index with a query hint.

Answer

Rebuild the nonclustered index on CustomerID.

Question 15

You are a database administrator for a healthcare company that uses Azure SQL Managed Instance. The company requires that a T-SQL script run every night to perform index maintenance on a specific database. You need to configure an automated solution that uses SQL Server Agent. Which of the following must you do to enable SQL Server Agent jobs on the managed instance?

Accepted Answer

Create a SQL Server Agent job using T-SQL or SQL Server Management Studio.. SQL Server Agent is fully supported on Azure SQL Managed Instance and is always running. You create jobs using T-SQL or SSMS, and the agent executes them on schedule. No additional configuration or external services are needed. The other options either misstate the agent's availability or suggest unnecessary steps that are not applicable to managed instances.

Answer

Configure Azure Automation to run the T-SQL script and invoke it from a SQL Server Agent job.

Answer

Enable the 'Agent XPs' advanced option using sp_configure.

Answer

Enable the SQL Server Agent service on the managed instance.