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

Question 1

A company is deploying Azure SQL Managed Instance and needs to ensure that the failover group provides automatic failover with zero data loss during a regional outage. The secondary region is 500 miles away. Which data replication mode should be configured?

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

Accepted Answer

Synchronous replication. Azure SQL Managed Instance failover groups require synchronous replication to guarantee zero data loss during automatic failover. Synchronous replication ensures that transactions are committed on both the primary and secondary replicas before acknowledging the commit, so no data is lost even if the primary region fails. This mode is mandatory for failover groups with the 'automatic failover' and 'zero data loss' requirements, despite the 500-mile distance which introduces latency.

Answer

Geo-replication

Answer

Snapshot replication

Answer

Asynchronous replication

Question 2

A retail company is migrating its on-premises SQL Server database to Azure SQL Database. The database has a table with 500 million rows and receives 10,000 INSERT operations per second during peak hours. The application requires read-committed snapshot isolation. Which Azure SQL Database tier and configuration should the DBA recommend to minimize cost while meeting performance requirements?

Accepted Answer

Hyperscale tier with 4 vCores. The Hyperscale tier with 4 vCores is correct because it supports up to 100 TB of storage, provides fast scaling for high-volume INSERT workloads (10,000 rows/second), and natively supports read-committed snapshot isolation (RCSI) without additional configuration. It minimizes cost compared to Business Critical while offering the necessary performance and isolation level for a 500-million-row table.

Answer

Business Critical tier with 8 vCores

Answer

Serverless tier with auto-pause

Answer

General Purpose tier with 2 vCores

Question 3

A company has an Azure SQL Managed Instance that is experiencing high CPU usage. The DBA observes that a specific query is causing high compile time due to parameter sniffing. The query is executed frequently with varying parameter values. Which approach should the DBA use to reduce CPU usage without changing the T-SQL code?

Accepted Answer

Enable forced parameterization for the database. Forced parameterization (Option D) converts literal values in the query to parameters, allowing SQL Server to reuse cached execution plans across different parameter values. This reduces CPU usage by eliminating frequent recompilations caused by parameter sniffing, without requiring any changes to the T-SQL code itself.

Answer

Create a plan guide to force a specific plan

Answer

Use optimize for ad hoc workloads

Answer

Add query store and force the last good plan

Question 4

A healthcare company is required to encrypt all patient data at rest and in transit. They are deploying Azure SQL Database. Which combination of features should they implement to meet this requirement?

Accepted Answer

Transparent data encryption (TDE) and TLS 1.2. Transparent Data Encryption (TDE) encrypts data at rest by performing real-time I/O encryption and decryption of the database, data files, and transaction logs, while TLS 1.2 encrypts data in transit between the client and Azure SQL Database. Together, they satisfy the requirement to encrypt all patient data both at rest and in transit.

Answer

Dynamic data masking and row-level security

Answer

Azure Active Directory authentication and firewall rules

Answer

Always Encrypted and transparent data encryption (TDE)

Question 5

A DBA is migrating a large on-premises database to Azure SQL Database using the Data Migration Assistant (DMA). The migration fails with an error indicating that the source database contains cross-database queries. What is the best remediation?

Accepted Answer

Refactor the application to eliminate cross-database queries or use elastic query. Azure SQL Database does not support cross-database queries natively. The Data Migration Assistant (DMA) blocks migrations that rely on such queries because the PaaS service lacks the necessary server-level context. The correct remediation is to refactor the application to eliminate cross-database dependencies or use elastic query, which provides a read-only, schema-bound mechanism to query remote databases via external data sources and external tables.

Answer

Use elastic database query to reference external tables

Answer

Create a linked server in Azure SQL Database

Answer

Migrate to Azure SQL Managed Instance instead

Question 6

A company plans to deploy Azure SQL Database in a new region. They require that the database be accessible only from a specific set of Azure virtual machines in the same region. What should the DBA configure?

Accepted Answer

Configure a VNet service endpoint and a firewall rule for the VNet subnet. A VNet service endpoint extends your virtual network private address space and the identity of your VNet to the Azure SQL Database service over a direct connection. By combining a service endpoint on the subnet with a firewall rule that allows traffic only from that specific VNet/subnet, you restrict access exclusively to Azure VMs in that subnet, meeting the requirement without exposing the database to the public internet.

Answer

Use a private endpoint only

Answer

Configure a point-to-site VPN

Answer

Set the firewall to allow Azure services and resources to access the server

Question 7

A company uses Azure SQL Managed Instance for its line-of-business applications. They need to ensure that backups are retained for 35 days for compliance purposes. Which configuration should the DBA set?

Accepted Answer

Set the backup retention period to 35 days in the backup settings. Azure SQL Managed Instance allows you to configure the backup retention period directly in the backup settings, with a maximum of 35 days for point-in-time restore (PITR). This meets the compliance requirement without needing additional services or policies.

Answer

Create a backup policy in Azure Backup Vault

Answer

Enable geo-redundant storage (GRS)

Answer

Configure long-term retention (LTR) policy

Question 8

Which TWO of the following are valid methods to migrate an on-premises SQL Server database to Azure SQL Database?

Accepted Answer

Export the database as a BACPAC file and import into Azure SQL Database. A BACPAC file is a logical backup that contains the database schema and data in a compressed format. Azure SQL Database supports importing BACPAC files directly via the Azure portal, PowerShell, or the SqlPackage utility, making this a straightforward and fully supported migration method.

Answer

Configure transactional replication to replicate changes to Azure SQL Database

Answer

Use log shipping to continuously copy transaction logs

Answer

Restore a native SQL Server backup to Azure SQL Database

Question 9

Which THREE of the following are features available in Azure SQL Managed Instance that are not available in Azure SQL Database (single database)?

Accepted Answer

Cross-database queries (three- and four-part names). Cross-database queries using three- and four-part names (e.g., `[DB1].[schema].[table]`) are supported in Azure SQL Managed Instance because it provides full SQL Server engine compatibility, including the ability to query across databases within the same instance. Azure SQL Database single databases are isolated and do not support cross-database queries, as each database is a separate logical boundary without instance-level context.

Answer

Transparent Data Encryption (TDE)

Answer

Elastic pools

Question 10

Refer to the exhibit. A DBA is creating an Azure SQL Managed Instance using the ARM template snippet shown. The DBA needs to ensure the instance can survive a regional outage without data loss. What change should be made to the template?

Accepted Answer

Set zoneRedundant to true and configure an auto-failover group with a secondary region. Azure SQL Managed Instance does not natively support zone redundancy for regional outage protection; instead, it requires configuring an auto-failover group with a secondary instance in a paired region. Setting zoneRedundant to true alone only protects against zone failures within a single region, not a full regional outage. The auto-failover group ensures continuous replication and automatic failover to the secondary region, meeting the requirement of surviving a regional outage without data loss.

Answer

Set collation to SQL_Latin1_General_CP1_CS_AS

Answer

Set zoneRedundant to true

Answer

Change storageAccountType to RA-GRS

Question 11

Refer to the exhibit. An Azure SQL Database administrator sees the error in the SQL Server error log. The database is in the General Purpose tier with 100 GB of storage. Which action should the DBA take to resolve the issue?

Accepted Answer

Increase the max size of the database. The error indicates the database has reached its maximum size limit of 100 GB. In Azure SQL Database General Purpose tier, the max size is set at the database level, not the log file level. Increasing the max size of the database (Option D) directly resolves the 'could not allocate space' error by allowing more data to be stored, without requiring a tier change or manual log file manipulation.

Answer

Shrink the database

Answer

Scale up to a higher service objective (e.g., from GP to BC)

Answer

Increase the log file size

Question 12

You are the Azure DBA for a global e-commerce company. The company uses Azure SQL Database for its product catalog. The database is in the Business Critical tier with 16 vCores and 1 TB of storage. During a major sales event, the application experiences severe performance degradation. Analysis reveals that the database is experiencing high write latency on the secondary replicas due to heavy read workload routed to them. The primary replica is not overloaded. The application uses read-only routing to direct queries to readable secondaries. The DBA needs to reduce write latency on the secondaries without affecting the read workload or incurring additional cost. What should the DBA do?

Accepted Answer

Disable read-scale out for non-critical queries and use the primary for read-write. Disabling read-scale out for non-critical queries forces those queries to use the primary replica, reducing the read workload on the secondaries. This directly lowers the write latency on secondaries, as fewer reads compete for resources with the log apply process. The change does not affect the read workload for critical queries that still use read-only routing, and it incurs no additional cost.

Answer

Scale up the service tier to Hyperscale to offload writes

Answer

Add more replicas to distribute the read load

Answer

Remove the secondary replicas and use only the primary

Question 13

You are the DBA for a financial services company. The company is migrating its on-premises SQL Server databases to Azure SQL Managed Instance. One of the databases is 2 TB in size and has a high transaction volume. The migration must have minimal downtime. The source environment is SQL Server 2019 running on Windows Server. The target is a General Purpose managed instance in the same region. The network bandwidth between on-premises and Azure is 500 Mbps. The DBA plans to use Azure Database Migration Service (DMS) for an online migration. However, during the initial sync, the migration is progressing slowly and the estimated time to completion is 48 hours. The DBA needs to reduce the migration time. What should the DBA do?

Accepted Answer

Upgrade the DMS instance to a Premium SKU to increase performance. Upgrading the DMS instance to a Premium SKU increases the underlying compute and I/O resources allocated to the migration service, which directly improves the throughput of data movement during the online migration. Since the bottleneck is the DMS processing speed rather than network bandwidth (500 Mbps is sufficient for 2 TB over 48 hours), a Premium SKU provides higher performance tiers that can process change data capture (CDC) and bulk inserts faster, reducing the overall sync time.

Answer

Switch to an offline migration using BACPAC export and import

Answer

Request a higher bandwidth connection from the network team

Answer

Change the target to a different Azure region that is closer to the source

Question 14

A company is planning to migrate their on-premises SQL Server databases to Azure SQL Managed Instance. They have a database that uses SQL Server Agent jobs with proxies and also uses cross-database queries extensively. What is the main consideration for this migration?

Accepted Answer

Migrate to Azure SQL Managed Instance as it supports SQL Agent and cross-database queries within the same instance.. Azure SQL Managed Instance is the correct target because it provides full support for SQL Server Agent, including proxies, and enables cross-database queries within the same instance. Unlike Azure SQL Database, Managed Instance maintains instance-level scope, allowing queries that reference other databases in the same instance without requiring external data sources or elastic queries.

Answer

Migrate to SQL Server on Azure Virtual Machines for full control.

Answer

Migrate to Azure SQL Database elastic query to handle cross-database queries.

Answer

Migrate to Azure SQL Database instead to reduce costs.

Question 15

You are designing a disaster recovery strategy for an Azure SQL Database that requires a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 1 hour. Which option should you choose?

Accepted Answer

Use active geo-replication and plan manual failover.. Active geo-replication provides a continuous replication stream with an RPO of less than 5 seconds and supports manual failover, which can achieve an RTO of less than 1 hour when the failover is initiated promptly. This option meets the strict RPO and RTO requirements by maintaining a readable secondary in a different Azure region that is kept nearly synchronized with the primary.

Answer

Use auto-failover groups with read-write failover policy.

Answer

Use geo-restore from geo-redundant backups.

Answer

Use long-term retention (LTR) backups with daily frequency.