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

Question 1

You are a database administrator for a global SaaS provider. You have multiple Azure SQL Databases in a single logical server. You need to implement a disaster recovery strategy that provides automatic failover across Azure regions with the lowest possible RPO and RTO for critical databases. The solution must not require any application code changes. What should you implement?

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

Accepted Answer

Configure an auto-failover group across two Azure regions with a grace period of 1 hour and include all critical databases in the group.. Auto-failover groups provide automatic failover across Azure regions with the lowest possible RPO (typically 5 seconds) and RTO (around 1 minute) for critical databases, without requiring any application code changes. By setting the grace period to 1 hour, you allow the system to tolerate transient outages before triggering failover, which balances availability and data loss tolerance. Including all critical databases in the group ensures consistent failover behavior and prevents split-brain scenarios.

Answer

Use Azure SQL Database backup and restore across regions with a recovery point objective (RPO) of 1 hour.

Answer

Configure an auto-failover group across two Azure regions with a grace period of 0 and include only the most critical database.

Answer

Use active geo-replication for each critical database and configure a custom health check to trigger failover.

Question 2

You are planning a migration of on-premises SQL Server databases to Azure SQL Managed Instance. The source databases use SQL Server Agent jobs, cross-database queries, and SQL Server Integration Services (SSIS) packages. Which migration approach minimizes the need to modify existing code or processes?

Accepted Answer

Migrate to Azure SQL Managed Instance and use Azure Data Factory to run SSIS packages with an Azure-SSIS integration runtime.. Azure SQL Managed Instance provides near-100% compatibility with on-premises SQL Server, including SQL Server Agent and cross-database queries. For SSIS packages, the Azure-SSIS integration runtime in Azure Data Factory allows you to lift-and-shift existing SSIS packages without rewriting them, minimizing code changes.

Answer

Migrate to SQL Server on Azure Virtual Machines and keep all existing functionality unchanged.

Answer

Migrate to Azure Database for PostgreSQL and rewrite all SQL Agent jobs as pg_cron tasks.

Answer

Migrate to Azure SQL Database and use elastic jobs to replace SQL Server Agent.

Question 3

Your company is deploying a new Azure SQL Database that will store Personally Identifiable Information (PII). The security team requires that all queries against the database are audited and that any access to sensitive columns is logged with the actual data values. Which combination of features should you enable?

Accepted Answer

Enable SQL Server Audit on the database and create a database audit specification that captures SELECT and UPDATE on the sensitive columns.. SQL Server Audit (available in Azure SQL Database) allows you to create a database audit specification that captures SELECT and UPDATE operations on specific columns, including the actual data values accessed. This meets the security requirement to log access to sensitive columns with the actual data, whereas other options either lack column-level detail or do not capture the data values.

Answer

Enable Transparent Data Encryption (TDE) and configure a server-level audit specification for schema changes.

Answer

Enable Dynamic Data Masking on the sensitive columns and use Azure SQL Database Auditing.

Answer

Enable Azure SQL Database Auditing and configure the diagnostic settings to send logs to a Log Analytics workspace.

Question 4

You manage an Azure SQL Database that uses a Business Critical tier with a zone-redundant configuration. During a planned maintenance event, you notice a significant increase in query latency. You suspect that the read-only intent routing is not working as expected. What should you check first to ensure read-only connections are using the secondary replica?

Accepted Answer

Verify that the application connection string includes 'ApplicationIntent=ReadOnly' for read-only queries.. The read-only intent routing in Azure SQL Database relies on the client connection string including `ApplicationIntent=ReadOnly`. Without this parameter, all connections, including those intended for read-only workloads, will be directed to the primary replica, even if a readable secondary replica is available. This is the first and most fundamental check to ensure read-only queries are routed to the secondary.

Answer

Review the maintenance window schedule to see if the primary replica was being updated.

Answer

Check the 'ReadScale' property of the database to ensure it is set to 'Enabled'.

Answer

Confirm that the database is in a zone-redundant configuration by checking the 'ZoneRedundant' property.

Question 5

You are deploying a new Azure SQL Database that will be used by an application requiring high availability and automatic failover across Azure regions. The RPO must be less than 5 seconds, and the RTO must be less than 1 minute. You also need to ensure that the secondary region can be used for read-only workloads during normal operation. Which solution should you implement?

Accepted Answer

Use active geo-replication with a readable secondary in a different Azure region.. Active geo-replication with a readable secondary in a different Azure region meets the requirements because it provides an asynchronous replication mechanism with an RPO of less than 5 seconds and an RTO of less than 1 minute during failover. The secondary database is fully readable for read-only workloads, supporting the application's need for read-scale in the secondary region during normal operation.

Answer

Use an auto-failover group with a readable secondary in a different region.

Answer

Use Azure SQL Database backup to a second region and restore on demand.

Answer

Use an auto-failover group with a read-scale secondary in the same region.

Question 6

Your company is planning to deploy a new application that uses Azure SQL Database with a Hyperscale service tier. The application requires near-instantaneous scaling of compute resources without downtime. The database will handle large data volumes (over 10 TB). Additionally, the application team wants to use the latest database features, including JSON support and graph queries. Which considerations should you evaluate before choosing Hyperscale?

Accepted Answer

Hyperscale does not support cross-database queries or elastic queries.. Azure SQL Database Hyperscale does not support cross-database queries or elastic queries. This limitation is critical for the application team if they need to query across multiple databases in a single query, which is a common requirement for distributed applications. The Hyperscale architecture separates compute from storage, making cross-database query capabilities unavailable, unlike other service tiers such as General Purpose or Business Critical.

Answer

Hyperscale does not support graph data features such as node and edge tables.

Answer

Hyperscale does not support zone-redundant configuration for high availability.

Answer

Hyperscale does not support JSON functions like JSON_VALUE and JSON_QUERY.

Question 7

Which TWO of the following are valid options for migrating an on-premises SQL Server database to Azure SQL Database with minimal downtime?

Accepted Answer

Configure transactional replication between on-premises SQL Server and Azure SQL Database.. Option B is correct because transactional replication continuously streams committed transactions from the on-premises SQL Server publisher to Azure SQL Database as a subscriber, so the target stays nearly in sync and only a brief cutover is needed, which satisfies the minimal-downtime requirement. Option C is correct because Azure Database Migration Service (DMS) in online migration mode performs a one-time full load followed by continuous log-based synchronization of ongoing changes, allowing you to migrate with minimal downtime before cutting over. Option A is not appropriate because BULK INSERT is a bulk data-loading operation that copies data at a point in time and does not replicate ongoing changes, so it cannot achieve minimal downtime. Option D is not valid for this scenario because restoring a SQL Server backup to Azure SQL Database is not a supported migration path (backup/restore to URL targets Azure SQL Managed Instance or SQL Server on Azure VMs, not Azure SQL Database). Option E is not suitable because the Generate Scripts wizard only produces schema and data scripts, which is a manual, offline process that causes significant downtime and does not keep the target synchronized.

Answer

Use BULK INSERT to export data from on-premises and import to Azure SQL Database.

Answer

Use SQL Server backup to URL and restore to Azure SQL Database.

Answer

Use the Generate Scripts wizard in SQL Server Management Studio.

Question 8

You are designing a disaster recovery solution for a critical Azure SQL Database that runs in the West US region. The database is 2 TB in size and requires a recovery point objective (RPO) of less than 5 seconds and a recovery time objective (RTO) of less than 30 seconds. Which deployment option should you recommend?

Accepted Answer

Use Azure SQL Database Business Critical with zone-redundant configuration. Azure SQL Database Business Critical with zone-redundant configuration is the correct choice because it provides synchronous replication across three availability zones within the same region, ensuring an RPO of 0 (no data loss) and an RTO of under 30 seconds for zonal failures. This meets the strict RPO of less than 5 seconds and RTO of less than 30 seconds for a 2 TB database, as Business Critical offers high-performance local SSD storage and automatic failover without data loss.

Answer

Use Azure SQL Database General Purpose with auto-failover group

Answer

Use Azure SQL Database Hyperscale with geo-replication

Answer

Use Azure SQL Managed Instance Business Critical with zone-redundant configuration

Question 9

Your company is migrating an on-premises SQL Server 2016 database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Database Mail, and Service Broker. The database is 500 GB. You need to minimize downtime during migration. Which migration approach should you use?

Accepted Answer

Use Azure Database Migration Service with online migration. Azure Database Migration Service (DMS) with online migration mode uses continuous data synchronization via change tracking or log shipping to minimize downtime. This approach supports SQL Server Agent jobs, Database Mail, and Service Broker, which are preserved during migration to Azure SQL Managed Instance. The online mode allows the source database to remain operational until the final cutover, reducing downtime to seconds.

Answer

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

Answer

Export the database as a BACPAC file and import to Azure SQL Managed Instance

Answer

Back up the database to URL and restore to Azure SQL Managed Instance

Question 10

You are planning to deploy Azure SQL Database for a new application. The application requires a predictable performance with reserved resources and the ability to scale up during peak season. You want to minimize costs. Which purchasing model should you choose?

Accepted Answer

vCore-based purchasing model with reserved capacity. The vCore-based purchasing model with reserved capacity is correct because it allows you to reserve compute resources for a one- or three-year term, providing a significant discount (up to 40-60%) compared to pay-as-you-go pricing. This model also supports predictable performance with dedicated resources and the ability to scale up (e.g., increase vCores or storage) during peak seasons, while the reserved capacity commitment minimizes costs for the baseline workload.

Answer

DTU-based purchasing model

Answer

Hyperscale service tier

Answer

Serverless compute tier

Question 11

You need to migrate an on-premises SQL Server database to Azure SQL Database. The database uses SQL Server Integration Services (SSIS) packages for ETL. You want to continue using SSIS in the cloud with minimal changes. Which Azure service should you use?

Accepted Answer

Use Azure Data Factory with Azure-SSIS Integration Runtime. Azure Data Factory with Azure-SSIS Integration Runtime is the correct choice because it provides a fully managed service that lifts and shifts existing SSIS packages to the cloud with minimal changes. The Azure-SSIS IR runs a dedicated cluster of Azure VMs that host the SSIS engine, allowing you to deploy, execute, and manage your existing SSIS packages directly in Azure without rewriting code.

Answer

Deploy SSIS on Azure SQL Database

Answer

Migrate SSIS packages to Azure Databricks

Answer

Use Azure SQL Managed Instance to run SSIS

Question 12

Your company uses Azure SQL Database with Active Geo-Replication for disaster recovery. During a regional outage, you fail over to the secondary region. When the primary region recovers, you need to fail back with zero data loss. What should you do?

Accepted Answer

Perform a planned failover from the current primary to the original primary. A planned failover in Azure SQL Database with Active Geo-Replication ensures zero data loss by synchronizing all committed transactions from the current primary (secondary region) to the original primary before switching roles. This process uses synchronous replication during the final phase, guaranteeing that no transactions are lost when failing back to the recovered primary region.

Answer

Delete the secondary database and recreate it from the primary

Answer

Add a new secondary in the original primary region and then fail over

Answer

Fail over the original primary to become the primary again

Question 13

You are planning a migration of on-premises SQL Server databases to Azure SQL Managed Instance. Which TWO tools or services can you use to assess compatibility and identify potential migration blockers? (Select TWO.)

Accepted Answer

Data Migration Assistant (DMA). Data Migration Assistant (DMA) is correct because it is Microsoft's dedicated assessment tool that analyzes on-premises SQL Server databases and reports compatibility issues, deprecated features, and migration blockers for targets such as Azure SQL Managed Instance. Azure Migrate is also correct because it provides a unified assessment experience, including discovery and readiness/compatibility assessment of SQL Server workloads for migration to Azure SQL Managed Instance. The Upgrade Data Tier Application wizard in SSMS is not an assessment tool for identifying migration blockers; it deploys a DAC package. Azure Database Migration Service (DMS) performs the actual migration rather than the compatibility assessment, and the Azure Data Studio Migration Wizard is designed to guide migrations, not to produce a detailed compatibility/blocker assessment.

Answer

SQL Server Management Studio (SSMS) – Upgrade Data Tier Application wizard

Answer

Azure Database Migration Service (DMS)

Answer

Azure Data Studio – Migration Wizard

Question 14

You have an Azure SQL Database in the General Purpose tier. You need to implement high availability with automatic failover to a secondary database in a different Azure region. The solution must provide an RPO of 5 seconds and RTO of 1 hour. Which TWO components must you configure? (Select TWO.)

Accepted Answer

Configure active geo-replication. Option C (Configure active geo-replication) is correct because active geo-replication is the Azure SQL Database feature that asynchronously replicates a primary database to up to four readable secondaries in different regions, and it supports the required RPO of 5 seconds (typical replication lag is under 5 seconds) with manual or scripted failover. Option D (Create a secondary database in a different Azure region) is correct because active geo-replication requires you to provision the secondary database in a different Azure region, which is the target for failover and satisfies the cross-region requirement. Option A is not required because Business Critical is not necessary to meet a 5-second RPO and 1-hour RTO; General Purpose with active geo-replication can meet these targets. Option B is incorrect because zone redundancy provides intra-region high availability across availability zones, not cross-region failover. Option E is incorrect because auto-failover groups, while they also provide cross-region replication and automatic failover, are a separate feature and are not the components being asked for here; the question specifically requires configuring active geo-replication and its secondary database.

Answer

Change the service tier to Business Critical

Answer

Enable zone redundancy on the primary database

Answer

Create an auto-failover group

Question 15

Your company is deploying Azure SQL Database for a new application. The security policy requires that all queries be encrypted in transit and that the database be protected against accidental deletion. Which THREE configurations should you implement? (Select THREE.)

Accepted Answer

Set the minimum TLS version to 1.2. Option A is correct because setting the minimum TLS version to 1.2 ensures that all client connections to Azure SQL Database are encrypted in transit using at least TLS 1.2, satisfying the requirement that queries be encrypted in transit. Option C is correct because enabling Microsoft Entra ID authentication strengthens access control by replacing or supplementing SQL logins with centralized identity-based authentication, which supports the security policy for the new application. Option E is correct because a resource lock (for example, a CanNotDelete lock) on the Azure SQL Database logical server prevents accidental deletion of the server and its databases, directly meeting the protection-against-accidental-deletion requirement. Option B is not correct because firewall rules restrict network access by IP address but do not themselves encrypt queries in transit or prevent accidental deletion. Option D is not correct because Transparent Data Encryption protects data at rest by encrypting database files, not queries in transit, and it does not prevent accidental deletion.

Answer

Configure firewall rules to allow only specific IP addresses

Answer

Enable Transparent Data Encryption (TDE)