DP-300 Plan and implement data platform resources Practice Test 9 — 15 Questions

Question 1

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.)

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

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 2

You are reviewing an ARM template snippet for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' in resource group 'rg1'. The source database 'db1' is in the same server 'server1' and is currently active. What will be the result of deploying this template?

Accepted Answer

It will create a new empty database with the specified service objective.. The ARM template snippet specifies `createMode: Default` and a `requestedServiceObjectiveName`, but does not include a `sourceDatabaseId` or `restorePointInTime` property. With `createMode: Default`, Azure SQL Database creates a new empty database using the specified service objective, regardless of the presence of a `sourceDatabaseId` in the template (which is ignored when `createMode` is not set to `Copy`, `PointInTimeRestore`, or `Secondary`). Therefore, the deployment succeeds and creates a new empty database named 'db2' with the defined performance tier.

Answer

The deployment will fail because sourceDatabaseId is specified but not used.

Answer

It will create a copy of db1 in the same server.

Answer

It will perform a point-in-time restore of db1 to the current time.

Question 3

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' from 'db1'. The source database 'db1' is currently in a failed state due to a storage issue. What will be the result of deploying this template?

Accepted Answer

The deployment will fail because db1 is not in a recoverable state.. The ARM template creates a new database by copying from a source database. Azure SQL Database requires the source database to be in an online and healthy state to perform a copy operation. Since db1 is in a failed state due to a storage issue, it is not accessible for copying, so the deployment will fail.

Answer

It will create an empty database because the source is not accessible.

Answer

It will delete db1 and create db2 as a replacement.

Answer

It will create a new database by recovering db1 to its last known good state.

Question 4

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON. The source database 'db1' was created on 2025-03-01 and has a current backup retention of 7 days. The restore point is 2025-03-15T10:00:00Z. What will be the result of deploying this template?

Accepted Answer

The deployment will succeed and create a new database restored to the specified point in time.. The ARM template specifies a restore point of 2025-03-15T10:00:00Z. Azure SQL Database point-in-time restore (PITR) uses a sliding window of the configured backup retention period (7 days) backward from the current date. Assuming the current date is 2025-03-22, the retention window covers from 2025-03-15 to 2025-03-22. The restore point falls exactly at the start of this window, so it is valid. The deployment will succeed and create a new database restored to that exact timestamp.

Answer

The deployment will fail because the restore point is older than 7 days.

Answer

The deployment will succeed but the restore point will be ignored and the latest backup will be used.

Answer

The deployment will fail because the source database is not geo-replicated.

Question 5

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 6

Your team is migrating an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses Service Broker for cross-database messaging. The compliance requirement mandates that the migration must be performed with minimal downtime and that the target must support the Service Broker feature. What migration strategy should you recommend?

Accepted Answer

Use Azure Database Migration Service (DMS) with online migration to Azure SQL Managed Instance. Azure SQL Managed Instance fully supports Service Broker, and the Azure Database Migration Service (DMS) with online migration mode enables minimal downtime by continuously replicating changes from the source SQL Server to the target Managed Instance until a cutover. This satisfies both the Service Broker feature requirement and the compliance mandate for minimal downtime.

Answer

Perform a native backup and restore to Azure SQL Managed Instance

Answer

Use the Data Migration Assistant (DMA) to migrate to Azure SQL Database

Answer

Use Azure Database Migration Service (DMS) with offline migration to Azure SQL Database

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

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. 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 9

You are planning to migrate several on-premises SQL Server databases to Azure SQL Managed Instance. The databases use SQL Server Agent jobs, cross-database queries, and CLR assemblies. Which migration tool should you use to assess compatibility?

Accepted Answer

Data Migration Assistant (DMA). The Data Migration Assistant (DMA) is specifically designed to assess compatibility of on-premises SQL Server databases with Azure SQL Managed Instance, including features like SQL Server Agent jobs, cross-database queries, and CLR assemblies. DMA performs a detailed feature parity check and identifies blocking issues, such as unsupported CLR permissions or cross-database dependencies, before migration. It provides a comprehensive report with remediation guidance, making it the ideal tool for this pre-migration assessment phase.

Answer

Azure Database Migration Service (DMS)

Answer

Azure Data Studio with SQL Server migration extension

Answer

SQL Server Management Studio (SSMS)

Question 10

Your organization is implementing Microsoft Purview for data governance. You need to scan an Azure SQL Database to automatically classify sensitive data. The security team requires that the scan use a managed identity and that the data classification be stored in Microsoft Purview. What should you configure on the Azure SQL Database?

Accepted Answer

Assign the Microsoft Purview managed identity the db_datareader role and configure the firewall to allow Purview IP ranges. Microsoft Purview uses its own managed identity to authenticate to data sources when scanning. To scan an Azure SQL Database, you must grant Purview's managed identity the `db_datareader` role in the database to allow read access for classification, and you must configure the Azure SQL firewall to allow inbound connections from the Purview IP ranges (or enable 'Allow Azure services and resources to access this server'). This satisfies the security team's requirement to use a managed identity and store classification in Purview.

Answer

Enable system-assigned managed identity on the database and grant it permissions to access Purview

Answer

Use SQL authentication with a dedicated login and firewall rule

Answer

Create a user-assigned managed identity and assign it to the database

Question 11

You are deploying an Azure SQL Managed Instance for a financial application. The compliance requirements dictate that all data at rest must be encrypted using TDE with a customer-managed key stored in Azure Key Vault. Additionally, the key must be accessible only from the managed instance's virtual network. How should you configure the Key Vault firewall and managed instance?

Accepted Answer

Enable the Key Vault firewall, select 'Allow trusted Microsoft services', and configure the managed instance with a system-assigned managed identity. The correct configuration. Enabling the Key Vault firewall and selecting 'Allow trusted Microsoft services' ensures that only trusted Azure services, including Azure SQL Managed Instance with a system-assigned managed identity, can access the key. This satisfies the requirement that the key be accessible only from the managed instance's virtual network, as the managed instance is a trusted service within that network. Option B is incorrect because disabling the Key Vault firewall would allow public network access to the vault, violating the network restriction. Option C is incorrect because Azure SQL Managed Instance does not have a public IP address; it is deployed inside a virtual network and cannot be added as a public IP firewall rule. Option D is incorrect because disabling the firewall again allows public access, and using a user-assigned managed identity is unnecessary; a system-assigned managed identity is the recommended approach when using 'Allow trusted Microsoft services'.

Answer

Disable the Key Vault firewall and assign the managed instance a service principal

Answer

Enable the Key Vault firewall and add the managed instance's public IP address to the firewall rules

Answer

Disable the Key Vault firewall and use a user-assigned managed identity for the managed instance

Question 12

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. 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 13

Which TWO conditions must be met to use Azure SQL Database serverless compute tier?

Accepted Answer

The database must be a single database, not part of an elastic pool. Azure SQL Database serverless compute tier is designed only for single databases, not for databases in elastic pools. The serverless tier auto-scales compute resources based on workload demand and pauses during idle periods, which is incompatible with the shared resource model of elastic pools where multiple databases share a fixed set of resources.

Answer

The database must use the DTU purchasing model

Answer

The database must be in the Hyperscale service tier

Answer

The database must be in the General Purpose service tier

Question 14

You are a database administrator for a global e-commerce company. The company runs a critical transactional workload on an Azure SQL Managed Instance (MI) in the West US region. The database is approximately 1 TB in size and uses the Business Critical tier with zone redundancy enabled. The application requires read latency for reporting queries to be under 10 ms. The company is expanding to Europe and needs to deploy a secondary copy of the database in the North Europe region to serve read-only reporting traffic for European users. The secondary must be kept in sync with the primary with a recovery point objective (RPO) of less than 5 seconds. Additionally, the solution must support automatic failover if the primary region becomes unavailable. You need to design the disaster recovery and read scaling solution. Which approach should you take?

Accepted Answer

Create an auto-failover group with the primary in West US and add a readable secondary in North Europe, using synchronous commit. Azure SQL Managed Instance supports auto-failover groups with a readable secondary in a paired region. By configuring synchronous commit within the failover group, you achieve an RPO of less than 5 seconds (typically 0-2 seconds) while the secondary in North Europe can serve read-only traffic with low latency. The auto-failover group also provides automatic failover if the primary region becomes unavailable, meeting both the read scaling and disaster recovery requirements.

Answer

Create a geo-replica of the MI in North Europe and manually failover when needed

Answer

Deploy a separate managed instance in North Europe and use transactional replication to keep it in sync

Answer

Create an auto-failover group with the primary in West US and secondary in North Europe, but do not make the secondary readable

Question 15

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