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

Question 1

You are managing an Azure SQL Managed Instance that hosts multiple databases for a financial application. You need to implement a security solution that meets compliance requirements by auditing all database activity and sending the audit logs to a centralized Log Analytics workspace for analysis. The solution must also support real-time alerts on suspicious activities. What should you configure?

Accepted Answer

Enable auditing to a Log Analytics workspace and integrate with Microsoft Sentinel.. Option C is correct because auditing to a Log Analytics workspace allows centralized collection of audit logs, which can then be integrated with Microsoft Sentinel for real-time analytics, threat detection, and automated alerting on suspicious activities. This meets both the compliance requirement for auditing and the operational need for real-time alerts.

Answer

Enable Microsoft Defender for Cloud and configure SQL vulnerability assessment.

Answer

Enable auditing to a storage account and use Microsoft Intune for monitoring.

Answer

Enable Microsoft Purview Data Map for the managed instance.

Question 2

Your company uses Azure SQL Database with the Hyperscale service tier. You notice that the database is experiencing high I/O latency during peak hours. After analyzing the query performance, you determine that the primary bottleneck is due to log write throughput. You need to reduce log write latency without changing the service tier. What should you do?

Accepted Answer

Scale up to a higher Hyperscale service objective.. Option C is correct because scaling up to a higher Hyperscale service objective increases the log write throughput by provisioning more log I/O capacity and faster storage. Since the bottleneck is specifically log write latency, and you cannot change the service tier, increasing the service objective within Hyperscale directly addresses the issue by allocating more resources to the log write path.

Answer

Configure Query Store hints to force parameterization.

Answer

Enable read scale-out to offload read queries.

Answer

Increase the MAXDOP setting for the database.

Question 3

You are deploying an Azure SQL Database for a development environment. The database will be used for testing and will be deleted after three months. You want to minimize costs while ensuring the database can be restored to any point within the retention period. Which backup retention policy should you configure?

Accepted Answer

Configure short-term backup retention to 7 days.. Option B is correct because the scenario specifies a development database that will be deleted after three months, with the requirement to restore to any point within the retention period. The default short-term backup retention for Azure SQL Database is 7 days, which provides point-in-time restore (PITR) capability within that window. Since the database is temporary and cost minimization is key, 7 days meets the requirement without incurring extra storage costs for longer retention or geo-redundancy.

Answer

Configure geo-redundant backup storage.

Answer

Configure short-term backup retention to 14 days.

Answer

Configure long-term backup retention (LTR) for 1 year.

Question 4

You are configuring Azure SQL Database for an e-commerce application that experiences variable traffic. You need to ensure that the database can automatically scale resources based on demand without manual intervention. The solution must also support scaling to zero compute when not in use to save costs. Which Azure SQL Database offering should you use?

Accepted Answer

Azure SQL Database serverless.. Azure SQL Database serverless is the correct choice because it automatically scales compute resources based on demand and supports pausing the database when idle, effectively scaling to zero compute to save costs. This aligns perfectly with the requirements of variable traffic and cost optimization without manual intervention.

Answer

Azure SQL Database elastic pool.

Answer

Azure SQL Database Hyperscale.

Answer

Azure SQL Database Business Critical tier.

Question 5

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 6

You need to create an Azure SQL Database that will be used by a new application. The database must support JSON data storage and querying. Which data type should you use to store JSON documents?

Accepted Answer

NVARCHAR. B is correct because Azure SQL Database uses the NVARCHAR data type to store JSON documents as text. JSON is stored as a string in NVARCHAR columns, which supports Unicode characters and allows the use of built-in JSON functions like JSON_VALUE, JSON_QUERY, OPENJSON, and ISJSON for validation and querying. NVARCHAR(MAX) is recommended for large or variable-length JSON documents.

Answer

TEXT

Answer

XML

Answer

VARCHAR

Question 7

You are planning to migrate a large number of on-premises SQL Server databases to Azure SQL Managed Instance. The migration must be automated and support schema and data validation. Which TWO tools or services should you consider using? (Choose TWO.)

Accepted Answer

Azure Database Migration Service. Azure Database Migration Service (DMS) is the correct choice because it is a fully managed service designed to automate the migration of on-premises SQL Server databases to Azure SQL Managed Instance with minimal downtime. It supports both schema and data validation through built-in assessment, data integrity checks, and cutover capabilities, making it ideal for large-scale automated migrations.

Answer

Azure SQL Migration tool

Answer

SQL Server Management Studio (SSMS)

Answer

BACPAC export and import

Question 8

You are configuring an Azure SQL Database for a financial application that requires high availability and disaster recovery across regions. The database is in the Premium service tier. Which TWO configurations should you implement to meet these requirements? (Choose TWO.)

Accepted Answer

Create a failover group that includes the primary and secondary.. Option C is correct because a failover group in Azure SQL Database provides automated, coordinated failover of multiple databases across regions, ensuring high availability and disaster recovery with a defined replication policy and a readable secondary endpoint. Option D is correct because active geo-replication allows you to create up to four readable secondary databases in different Azure regions, enabling manual or automatic failover for cross-region disaster recovery, which is essential for a financial application requiring regional resilience.

Answer

Enable automatic tuning for the database.

Answer

Enable zone redundancy within the primary region.

Answer

Enable Transparent Data Encryption (TDE).

Question 9

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 10

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 11

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 12

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.. Option D is correct because the ARM template specifies a restore point of 2025-03-15T10:00:00Z, which is within the 7-day backup retention period (the source database was created on 2025-03-01, so backups are available from 2025-03-01 to 2025-03-22). Azure SQL Database supports point-in-time restore (PITR) to any point within the configured backup retention window, and the deployment will create a new database restored to that exact timestamp. The restore point is valid and the template will succeed.

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 13

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 14

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. Option D is correct because 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 15

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