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

Question 1

You are planning to migrate a 500 GB on-premises SQL Server database to Azure SQL Managed Instance. The database has high transaction volume and cannot tolerate more than 15 minutes of downtime. Which migration methods should you consider? (Choose TWO.)

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

Accepted Answer

Azure Database Migration Service (online mode). Azure Database Migration Service (online mode) (A) is correct because it performs a continuous, near-zero-downtime migration from on-premises SQL Server to Azure SQL Managed Instance, keeping the source and target synchronized until you cut over, which fits the 15-minute downtime limit. Transactional replication (E) is also correct because it continuously replicates committed transactions from the on-premises publisher to the SQL Managed Instance subscriber, allowing a short cutover well within the 15-minute window. Back up to URL and restore (B) is not suitable because it requires taking the database offline during the final backup/restore, likely exceeding 15 minutes for a 500 GB database. Export to BACPAC and import (C) is a slow, offline logical migration that cannot meet the tight downtime requirement. Log shipping (D) is not supported as a migration path to Azure SQL Managed Instance and would also incur longer downtime during the final log restore.

Answer

Back up to URL and restore

Answer

Export to BACPAC and import

Answer

Log shipping

Question 2

You are designing an Azure SQL Database solution for a SaaS application that will host multiple tenants. Each tenant has a dedicated database. You need to ensure that tenants are isolated and resource usage is optimized. Which two features should you use? (Choose TWO.)

Accepted Answer

DTU-based purchasing model. Option C (Elastic pool) is correct because an elastic pool lets you place multiple tenant databases on a shared set of resources, so the pooled eDTUs or vCores are dynamically shared across databases, which optimizes resource usage for the multi-tenant SaaS pattern where individual databases have variable, intermittent demand. Option B (DTU-based purchasing model) is correct because elastic pools are natively supported with the DTU-based model, where you provision and pay for pooled eDTUs that all databases in the pool draw from, making it a valid and cost-effective choice for hosting many dedicated tenant databases with predictable aggregate load. The other options do not fit: Serverless compute tier (A) is a single-database compute option that auto-scales and auto-pauses per database, so it does not provide the shared-resource pooling needed for many tenants; Hyperscale service tier (D) targets very large single databases with rapid scale and fast backups rather than multi-tenant resource sharing; and vCore-based purchasing model (E) is an alternative purchasing model that, while it can be used with elastic pools, is not the feature being asked for here and is not marked correct.

Answer

Serverless compute tier

Answer

Hyperscale service tier

Answer

vCore-based purchasing model

Question 3

You need to secure an Azure SQL Database. The requirements are: (1) encrypt sensitive columns such as credit card numbers, (2) prevent unauthorized users from seeing the full credit card number even if they have access to the database, and (3) allow authorized applications to decrypt the data. Which two features should you implement? (Choose TWO.)

Accepted Answer

Always Encrypted. Always Encrypted (C) is correct because it encrypts sensitive columns such as credit card numbers at the client side, so the database engine never sees the plaintext, and only authorized applications holding the column encryption keys can decrypt the data. Column-level security (E) is correct because it lets you grant or deny SELECT/UPDATE permissions on specific columns, ensuring unauthorized users cannot retrieve the full credit card number even if they can access the table. Together these satisfy encryption of sensitive columns, protection from unauthorized viewing, and authorized application decryption. TDE (A) only encrypts data at rest and does not prevent users with database access from reading plaintext. Dynamic Data Masking (B) merely obscures data in query results and is not true encryption. Row-level security (D) filters rows, not columns, so it does not protect specific sensitive fields.

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Answer

Row-level security

Question 4

You are designing a disaster recovery plan for an Azure SQL Database that is used by a critical application. The database is currently in the West US region. You need to ensure that if a regional outage occurs, the database can be failed over to another region with minimal data loss. The solution must also minimize costs for the secondary replica. Which deployment option should you recommend?

Accepted Answer

Configure active geo-replication to a secondary in East US using a lower service tier.. Active geo-replication allows you to create a readable secondary database in a different region (East US) with a lower service tier, which reduces costs while still providing a disaster recovery target. In the event of a regional outage, you can manually initiate a failover to the secondary, and because replication is asynchronous, data loss is limited to the replication lag (typically a few seconds). This meets the requirement of minimal data loss and cost minimization.

Answer

Deploy an auto-failover group with a secondary in East US using the same service tier.

Answer

Deploy a zone-redundant database in West US with a failover group to East US.

Answer

Use a failover group with a secondary in East US and enable zone redundancy on both.

Question 5

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 6

You are deploying an Azure SQL Database for a new application that requires high availability with automatic failover within the same region. The database must be able to recover from a zonal failure without data loss. Which deployment option should you use?

Accepted Answer

Business Critical tier with zone redundancy enabled. The Business Critical tier with zone redundancy enabled provides the highest level of availability within a single Azure region by synchronously replicating data across three different availability zones. This ensures that if one zone fails, automatic failover occurs with no data loss because all transactions are committed to at least two replicas before being acknowledged. This meets the requirement for zonal failure recovery without data loss.

Answer

Hyperscale tier with zone redundancy

Answer

Standard tier with failover group

Answer

Basic tier with active geo-replication

Question 7

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

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

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 10

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 (C) is correct because it is a fully managed, automated migration service that supports large-scale, online and offline migrations from on-premises SQL Server to Azure SQL Managed Instance, and it includes built-in schema and data validation capabilities to confirm the migrated databases match the source. Azure Data Studio with the SQL Migration extension (E) is correct because this extension provides an automated, wizard-driven assessment and migration workflow to Azure SQL Managed Instance, including SKU recommendations and schema/data validation through the integrated Azure Database Migration Service. The Azure SQL Migration tool (A) is not a distinct product by that name, so it does not fit. SSMS (B) is a management client that can run manual migrations but does not provide the automated, validated migration workflow required here. BACPAC export and import (D) is a manual, schema-and-data-only approach that lacks the automation and validation features needed for a large-scale migration.

Answer

Azure SQL Migration tool

Answer

SQL Server Management Studio (SSMS)

Answer

BACPAC export and import

Question 11

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 an auto-failover group (failover group) provides a read-write listener endpoint and automatic failover of the primary Azure SQL Database to a secondary server in another region, delivering cross-region high availability and disaster recovery with a single connection string. Option D is correct because active geo-replication creates continuously replicated readable secondaries in up to four other regions, enabling cross-region DR and manual failover if the primary region fails; it is supported on the Premium tier. Option A is incorrect because automatic tuning only improves performance and stability through index and plan corrections, not cross-region availability or DR. Option B is incorrect because zone redundancy protects against a single datacenter/availability-zone failure within one region, not across regions. Option E is incorrect because TDE provides encryption at rest for data and backups, which is a security control, not a high-availability or disaster-recovery mechanism.

Answer

Enable automatic tuning for the database.

Answer

Enable zone redundancy within the primary region.

Answer

Enable Transparent Data Encryption (TDE).

Question 12

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 13

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 14

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