DP-300 Practice Exam 56 — 20 Questions

Question 1

Your company plans to migrate multiple on-premises SQL Server databases to Azure SQL Managed Instance. You need to select the appropriate migration method that minimizes downtime and supports automated schema and data validation. The source databases are running SQL Server 2019 Enterprise Edition. Which tool should you recommend?

Accepted Answer

Use Azure Database Migration Service with online mode.. Azure Database Migration Service (DMS) with online mode is the correct choice because it supports minimal downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Managed Instance, and it provides built-in schema and data validation to ensure consistency before cutover. This aligns with the requirement for automated validation and minimal downtime.

Answer

Use the Log Replay Service to continuously replicate transaction logs.

Answer

Set up transactional replication from on-premises to Azure SQL Managed Instance.

Answer

Export the databases as BACPAC files and import them into Azure SQL Managed Instance.

Question 2

You are designing a security strategy for Azure SQL Database. You need to ensure that database access is secured using Microsoft Entra ID (formerly Azure Active Directory) authentication. Which THREE actions should you take? (Choose THREE.)

Accepted Answer

Create a Microsoft Entra ID administrator for the Azure SQL logical server.. Creating a Microsoft Entra ID administrator for the Azure SQL logical server is required to enable Entra ID authentication at the server level. This administrator is a user or group from Entra ID that has permissions to manage all databases on the server, including creating contained database users mapped to Entra ID principals. Without this step, Entra ID authentication cannot be configured for the server or its databases.

Answer

Disable SQL Server authentication after migrating to Entra ID.

Answer

Enable Transparent Data Encryption (TDE).

Question 3

You need to audit all successful and failed login attempts on an Azure SQL Database. Which feature should you enable?

Accepted Answer

SQL Server Audit with a server-level audit specification that includes SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP.. SQL Server Audit is the correct feature because it allows you to capture both successful and failed login attempts at the server level by defining a server audit specification that includes the SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP audit action groups. These groups specifically log authentication events, which is exactly what is needed to audit all login attempts. Microsoft Defender for SQL, Vulnerability Assessment, and regulatory compliance dashboards do not provide granular login event auditing.

Answer

Microsoft Defender for SQL threat detection.

Answer

SQL Vulnerability Assessment.

Answer

Enable Microsoft Defender for Cloud's regulatory compliance dashboard.

Question 4

Which THREE are features of Azure SQL Database Hyperscale?

Accepted Answer

Storage up to 100 TB. Azure SQL Database Hyperscale supports storage up to 100 TB by using a distributed architecture where data is stored across multiple page servers, each managing a subset of pages. This allows the database to scale beyond the 4 TB limit of other service tiers, making it suitable for very large databases.

Answer

Zone redundant compute by default

Answer

Unlimited log rate

Question 5

Your company is migrating several on-premises SQL Server databases to Azure. The databases range from 50 GB to 2 TB and have varying performance requirements. You need to decide which Azure SQL deployment options to use. The requirements include: 
- Minimal application changes. 
- Support for SQL Server Agent jobs. 
- Ability to scale storage independently from compute. 
- Native support for cross-database queries. 
Which THREE options meet these requirements? (Choose three.)

Accepted Answer

Azure SQL Database elastic pool. Azure SQL Managed Instance (E) provides full SQL Server engine compatibility, including SQL Server Agent, cross-database queries, and independent storage scaling, with minimal application changes. SQL Server on Azure Virtual Machine (D) offers full control over the SQL Server instance, supporting Agent jobs and cross-database queries, and allows independent scaling of compute and storage via VM resizing and managed disks. Azure SQL Database elastic pool (B) supports SQL Server Agent jobs (via elastic jobs), independent storage scaling per database, and cross-database queries using elastic query, while requiring minimal application changes.

Answer

Azure SQL Database (single database)

Answer

Azure Synapse Analytics (dedicated SQL pool)

Question 6

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database is 2 TB in size and has a high transaction rate. You need to minimize downtime during migration. The source server is running SQL Server 2016 Enterprise Edition. Which migration approach should you recommend?

Accepted Answer

Use Azure Database Migration Service with online mode.. Azure Database Migration Service (DMS) with online mode is the correct choice because it supports minimal-downtime migrations for large databases with high transaction rates. It uses continuous data synchronization via change tracking or log shipping to keep the target nearly in sync, allowing a brief cutover window. For a 2 TB database, this avoids the lengthy downtime required by offline methods.

Answer

Export the database as a BACPAC file and import it into the managed instance.

Answer

Perform a full backup and restore to the managed instance, then apply log backups.

Answer

Set up transactional replication from on-premises to the managed instance.

Question 7

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 8

Which TWO actions are required to implement transparent data encryption (TDE) with customer-managed keys for an Azure SQL Database?

Accepted Answer

Create an Azure Key Vault and a key.. Option A is correct because TDE with customer-managed keys requires an Azure Key Vault to store the key, which provides centralized key management and control. The key in the vault is used to protect the database encryption key (DEK), ensuring that the customer retains ownership and control over the encryption material.

Answer

Set the backup encryption level to 'Encrypted'.

Answer

Create a server certificate in the database.

Answer

Configure column encryption keys in the database.

Question 9

You are migrating an on-premises SQL Server database with 5 TB of data to Azure SQL Database. The database has multiple large tables and indexes. You need to minimize downtime during migration. Which service should you use?

Accepted Answer

Azure Database Migration Service with online migration mode. Azure Database Migration Service (DMS) with online migration mode is the correct choice because it minimizes downtime by continuously synchronizing ongoing changes from the on-premises SQL Server to Azure SQL Database while the initial bulk data copy is in progress. This allows you to cut over to the target with only a brief pause, which is critical for a 5 TB database where offline methods would cause extended unavailability.

Answer

Export to BACPAC and import using SqlPackage

Answer

Backup and restore using URL with log shipping

Answer

Transactional replication from on-premises to Azure SQL Database

Question 10

You are deploying Azure SQL Database for a new application that requires predictable performance and minimal latency. You need to choose a service tier that provides dedicated resources and allows scaling independently for compute and storage. Which service tier should you select?

Accepted Answer

Business Critical. The Business Critical service tier is designed for applications requiring predictable performance and minimal latency, as it provides dedicated resources (CPU and memory) and uses local SSD storage for the database. It also supports independent scaling of compute and storage, allowing you to adjust DTUs or vCores without affecting storage capacity, which aligns with the requirement for independent scaling.

Answer

General Purpose

Answer

Basic

Answer

Hyperscale

Question 11

Which THREE actions should you take when planning to implement Azure SQL Database with Microsoft Defender for SQL and Microsoft Sentinel integration?

Accepted Answer

Configure Microsoft Sentinel data connectors for Azure SQL Database. Option B is correct because Microsoft Sentinel requires a data connector to ingest Azure SQL Database security events. The 'Azure SQL Database' data connector in Sentinel must be configured to pull diagnostic logs and alerts from the SQL resource into the Log Analytics workspace used by Sentinel, enabling advanced threat detection and incident response.

Answer

Enable Advanced Threat Protection for each database individually

Answer

Create a diagnostic setting to stream metrics to Event Hub

Question 12

You are deploying Azure SQL Database for a critical application. The compliance policy requires that the database be configured for zone redundancy in the primary region and have automated failover to a secondary region with no data loss. What Azure SQL Database offering and feature should you choose?

Accepted Answer

Business Critical tier with zone redundancy and active geo-replication. The Business Critical tier is the only Azure SQL Database tier that supports both zone redundancy within a region and active geo-replication with synchronous data replication to a secondary region. Zone redundancy ensures high availability within the primary region by distributing replicas across availability zones, while active geo-replication provides automated failover to a secondary region with no data loss because it uses synchronous commit at the primary and asynchronous replication to the secondary, but with the ability to fail over without data loss if the secondary is fully synchronized. This combination meets the compliance requirement for zero data loss during a regional failover.

Answer

Basic tier with geo-replication

Answer

General Purpose tier with geo-replication

Answer

Hyperscale tier with zone redundancy

Question 13

You are designing a disaster recovery plan for an Azure SQL Database that uses the Business Critical tier. The database is deployed in the West US region. You need to ensure that if the entire West US region becomes unavailable, the database can be failed over to a secondary region with minimal data loss. What should you implement?

Accepted Answer

Configure active geo-replication to East US. Active geo-replication for Azure SQL Database Business Critical tier provides a continuous, asynchronous replication stream to a secondary region, ensuring minimal data loss (typically a few seconds) during a regional outage. Unlike auto-failover groups, active geo-replication allows you to manually initiate a failover to the secondary region with a Recovery Point Objective (RPO) of up to 5 seconds, which meets the requirement for minimal data loss. The Business Critical tier also supports readable secondaries, but the key here is the replication mechanism that prioritizes low RPO over automatic failover.

Answer

Configure an auto-failover group with a readable secondary in East US

Answer

Enable zone redundancy for the database

Answer

Configure geo-restore from the West US database

Question 14

You are monitoring an Azure SQL Database and notice a pattern of high CPU usage during business hours. You need to identify the queries consuming the most CPU over the last 24 hours. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats (Option C) is the correct DMV because it returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), execution count, and last execution time. By querying this view and ordering by total_worker_time descending, you can identify the queries that have consumed the most CPU over the last 24 hours, directly addressing the pattern of high CPU usage during business hours.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_sessions

Answer

sys.dm_os_performance_counters

Question 15

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?

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 16

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 17

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

Accepted Answer

Assign a Microsoft Entra ID user or group as the Azure SQL Database admin. Assigning a Microsoft Entra ID user or group as the Azure SQL Database admin (Option B) is required because this establishes the administrative identity that can manage authentication for the database. Without this step, Microsoft Entra ID identities cannot be granted access or used to authenticate. Setting the database's authentication mode to 'Azure AD Only Authentication' (Option C) is also required because it disables SQL authentication, ensuring that only Microsoft Entra ID identities can connect, which is a key requirement for enabling Microsoft Entra ID authentication.

Answer

Create contained database users mapped to Microsoft Entra ID identities

Answer

Deploy the database in Azure SQL Managed Instance

Answer

Register the application in Microsoft Entra ID

Question 18

You are designing a disaster recovery strategy for an Azure SQL Database that uses the Business Critical tier. The primary region is East US, and you need to ensure automatic failover with zero data loss in case of a regional outage. The secondary region must be West US. You also need to minimize latency for read-only workloads in the secondary region. What should you implement?

Accepted Answer

Create a failover group with the primary in East US and a readable secondary in West US.. A failover group with a readable secondary in the Business Critical tier provides automatic failover with zero data loss because it uses synchronous replication. The readable secondary in West US minimizes latency for read-only workloads by allowing direct connections to the secondary replica. This meets all requirements: automatic failover, zero data loss, and low-latency reads in the secondary region.

Answer

Create a failover group with the primary in East US and a non-readable secondary in West US using Basic tier.

Answer

Configure active geo-replication with a readable secondary in West US.

Answer

Configure active geo-replication with a readable secondary in West US using Standard tier.

Question 19

Your company requires that all Azure SQL Databases use Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault. The security policy mandates that the key must be rotated every 90 days. You need to implement this requirement with minimal administrative overhead. What should you do?

Accepted Answer

Use Azure Key Vault key rotation policy to automatically rotate the key every 90 days. Option D is correct because Azure Key Vault supports automatic key rotation policies that can be configured to rotate a customer-managed key (CMK) every 90 days without any manual intervention. When this key is used for TDE in Azure SQL Database, the database automatically uses the latest version of the key from Key Vault, so no additional steps are needed to update the database after rotation. This satisfies the security policy with minimal administrative overhead.

Answer

Manually rotate the key in Key Vault every 90 days and update the database

Answer

Use Azure SQL Database server-side TDE with a service-managed key

Answer

Store the key in the database and use a custom rotation script

Question 20

Refer to the exhibit. The exhibit shows a partial ARM template for deploying an Azure SQL Database server. You run the deployment but get an error stating the server cannot be created because of a missing firewall rule. The template has public network access disabled and no firewall rules defined. What is the most likely cause?

Accepted Answer

The Entra ID admin SID is invalid (all zeros).. The error occurs because the Entra ID admin SID is set to all zeros ('00000000-0000-0000-0000-000000000000'), which is an invalid GUID. Azure SQL Database requires a valid, non-zero SID (object ID) for the Entra ID admin to establish authentication. When public network access is disabled, the server can still be created without firewall rules, but the invalid SID prevents the deployment from succeeding.

Answer

The firewallRules array must include at least one rule when publicNetworkAccess is Disabled.

Answer

The tenantId is missing in the Entra ID admin configuration.

Answer

The Entra ID admin type should be 'Group' not 'User'.