DP-300 Plan and implement data platform resources — Timed 20 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?

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 DBA needs to create a new Azure SQL Database and wants to ensure that the database automatically fails over to a secondary region without manual intervention. The recovery point objective (RPO) is 5 seconds. What should the DBA configure?

Accepted Answer

Active geo-replication with failover group. Active geo-replication with a failover group is the correct choice because it provides automatic, customer-managed failover to a secondary region with an RPO of 5 seconds. The failover group orchestrates the failover of multiple databases simultaneously and supports automatic failover policies, meeting the requirement for zero manual intervention. Standard geo-replication does not support automatic failover, and other options do not provide cross-region disaster recovery.

Answer

Standard geo-replication

Answer

Local redundancy with automatic failover

Answer

Read-scale out

Question 4

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 5

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. Option A is correct because 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 6

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 7

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. Option B is correct because 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 8

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. Option C is correct because 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 9

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 10

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 11

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. Option C is correct because 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 12

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 13

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. Option B is correct because 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 14

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 15

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 16

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.

Question 17

A company has an Azure SQL Database with a large table that is frequently updated. They notice performance degradation due to index fragmentation. Which maintenance strategy should you recommend to minimize impact on availability and performance?

Accepted Answer

Use online index rebuild with resumable operation.. Option B is correct because online index rebuild with resumable operation allows the index to be rebuilt while the table remains available for reads and writes, minimizing downtime. The resumable feature lets you pause and resume the rebuild if it is interrupted, which is critical for large tables that are frequently updated. This approach reduces performance impact compared to offline rebuilds and avoids blocking issues associated with other online methods.

Answer

Schedule an offline index rebuild during off-peak hours.

Answer

Disable and rebuild indexes manually during maintenance window.

Answer

Use online index reorganize with low priority locks.

Question 18

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

Accepted Answer

Use the Data Migration Assistant (DMA) to assess and migrate.. Option B is correct because the Data Migration Assistant (DMA) is a Microsoft tool specifically designed to assess on-premises SQL Server databases for compatibility issues and then perform a seamless migration of schema and data to Azure SQL Database. DMA handles both assessment and migration in a single workflow, making it a valid and recommended option for migrating to Azure SQL Database.

Answer

Use SQL Server Integration Services (SSIS) to migrate schema and data.

Answer

Use the Azure portal's 'Import database' wizard directly from on-premises.

Question 19

You are the database administrator for a large e-commerce company. The company has a SQL Server 2019 Enterprise Edition instance running on-premises with multiple databases. One of the databases, 'OrdersDB', is 2 TB in size and has a high transaction rate. The company plans to migrate 'OrdersDB' to Azure SQL Managed Instance to reduce operational overhead. The migration must have minimal downtime, and the target service tier must be Business Critical with 16 vCores. You have set up a test environment and performed a successful test migration using the Azure Database Migration Service (DMS) with online mode. During the final migration, the network bandwidth between on-premises and Azure is limited to 100 Mbps. The migration is taking longer than expected, and you are concerned about the log backup growth. What should you do to minimize the risk of the migration failing due to log space exhaustion?

Accepted Answer

Increase the frequency of transaction log backups on the source database to keep the log small.. Option D is correct because increasing the frequency of transaction log backups on the source database reduces the size of the active log, preventing log space exhaustion during the long-running online migration. The Azure Database Migration Service (DMS) in online mode continuously captures and applies log changes, so a smaller log reduces the risk of the log file filling up before backups can be taken, especially under constrained network bandwidth.

Answer

Switch to offline migration mode to avoid log growth issues.

Answer

Pause the migration and request a higher network bandwidth from the ISP.

Answer

Reduce the Azure SQL Managed Instance storage performance tier to standard to reduce cost.

Question 20

You are designing a database solution for an e-commerce application that requires high read throughput with sub-5 ms latency. The application runs on Azure VMs. You need to choose between Azure SQL Database and Azure SQL Managed Instance. Which factor most strongly supports choosing Azure SQL Database over SQL Managed Instance?

Accepted Answer

Azure SQL Database supports Hyperscale tier with sub-5 ms read latency on local SSDs.. Option B is correct because the Hyperscale tier of Azure SQL Database uses a distributed architecture with local SSD-based buffer pool extensions on compute nodes, enabling sub-5 ms read latency for data cached locally. This directly meets the e-commerce application's requirement for high read throughput with low latency, whereas Azure SQL Managed Instance relies on remote premium page blobs for storage, which introduces higher latency for reads that are not cache-resident.

Answer

Azure SQL Database offers a wider range of built-in instance-level features.

Answer

Azure SQL Database allows databases up to 100 TB in size.

Answer

Azure SQL Database supports active geo-replication with read-scale secondaries.