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

Question 1

You are planning to migrate several on-premises SQL Server databases to Azure SQL Managed Instance. The databases use SQL Server Agent jobs, cross-database queries, and CLR assemblies. Which migration tool should you use to assess compatibility?

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

Accepted Answer

Data Migration Assistant (DMA). The Data Migration Assistant (DMA) is specifically designed to assess compatibility of on-premises SQL Server databases with Azure SQL Managed Instance, including features like SQL Server Agent jobs, cross-database queries, and CLR assemblies. DMA performs a detailed feature parity check and identifies blocking issues, such as unsupported CLR permissions or cross-database dependencies, before migration. It provides a comprehensive report with remediation guidance, making it the ideal tool for this pre-migration assessment phase.

Answer

Azure Database Migration Service (DMS)

Answer

Azure Data Studio with SQL Server migration extension

Answer

SQL Server Management Studio (SSMS)

Question 2

You have an Azure SQL Database that experiences performance degradation due to high concurrency and blocking. You need to implement a solution that offloads reporting queries to a read-only copy of the database without impacting the primary workload. What should you configure?

Accepted Answer

Enable read scale-out on the database by adding a secondary replica. Adding the database to an auto-failover group creates a readable secondary replica that can offload read-only reporting queries without affecting the primary. The secondary is kept in sync via asynchronous replication (or synchronous if within the same region for Premium/Business Critical tiers), providing a read-only copy suitable for reporting. This approach works across all service tiers and does not require upgrading. While read scale-out (Option D) is ideal for read offloading in Business Critical or Hyperscale tiers, auto-failover groups offer a comparable solution that is always available.

Answer

Add the database to an auto-failover group and use the readable secondary for reporting

Answer

Create a database snapshot and use it for reporting

Answer

Configure a geo-replica and use it for reporting

Question 3

Your organization is implementing Microsoft Purview for data governance. You need to scan an Azure SQL Database to automatically classify sensitive data. The security team requires that the scan use a managed identity and that the data classification be stored in Microsoft Purview. What should you configure on the Azure SQL Database?

Accepted Answer

Assign the Microsoft Purview managed identity the db_datareader role and configure the firewall to allow Purview IP ranges. Microsoft Purview uses its own managed identity to authenticate to data sources when scanning. To scan an Azure SQL Database, you must grant Purview's managed identity the `db_datareader` role in the database to allow read access for classification, and you must configure the Azure SQL firewall to allow inbound connections from the Purview IP ranges (or enable 'Allow Azure services and resources to access this server'). This satisfies the security team's requirement to use a managed identity and store classification in Purview.

Answer

Enable system-assigned managed identity on the database and grant it permissions to access Purview

Answer

Use SQL authentication with a dedicated login and firewall rule

Answer

Create a user-assigned managed identity and assign it to the database

Question 4

You are deploying an Azure SQL Managed Instance for a financial application. The compliance requirements dictate that all data at rest must be encrypted using TDE with a customer-managed key stored in Azure Key Vault. Additionally, the key must be accessible only from the managed instance's virtual network. How should you configure the Key Vault firewall and managed instance?

Accepted Answer

Enable the Key Vault firewall, select 'Allow trusted Microsoft services', and configure the managed instance with a system-assigned managed identity. To meet the compliance requirement that the TDE key be accessible only from the managed instance's virtual network, you enable the Key Vault firewall and select 'Allow trusted Microsoft services,' which permits Azure SQL Managed Instance to access the key vault without exposing it to the public internet. The managed instance uses a system-assigned managed identity to authenticate to Key Vault, and this identity is recognized as a trusted Microsoft service. This configuration satisfies both the network isolation and the customer-managed key requirement.

Answer

Disable the Key Vault firewall and assign the managed instance a service principal

Answer

Enable the Key Vault firewall and add the managed instance's public IP address to the firewall rules

Answer

Disable the Key Vault firewall and use a user-assigned managed identity for the managed instance

Question 5

Which TWO Azure SQL Database features can help you scale out read-intensive workloads without modifying application code?

Accepted Answer

Geo-replication with readable secondary. Option B (Geo-replication with readable secondary) is correct because active geo-replication creates readable secondary databases in other Azure regions, and applications can be pointed at the secondary's connection string to offload read-only queries without changing application logic beyond the connection target. Option E (Read scale-out) is correct because it lets you route read-only workloads to a built-in read-only replica using the ApplicationIntent=ReadOnly parameter in the connection string, providing additional read capacity for read-intensive workloads without code changes. Option A (Database sharding) is incorrect because sharding requires application-level data partitioning and routing logic, which typically means modifying application code. Option C (In-memory OLTP) is incorrect because it accelerates transactional processing in memory rather than scaling out read workloads across replicas. Option D (Elastic pool) is incorrect because it shares resources and manages cost/performance across multiple databases, but it does not by itself provide read scale-out for a single database's read-intensive workload.

Answer

Database sharding

Answer

In-memory OLTP

Answer

Elastic pool

Question 6

Which THREE actions should you take when planning to implement Azure SQL Database with Microsoft Defender for SQL and Microsoft Sentinel integration?

Accepted Answer

Configure Microsoft Sentinel data connectors for Azure SQL Database. Option B is correct because Microsoft Sentinel ingests Azure SQL telemetry through its built-in data connectors, such as the 'Azure SQL Database' connector, which must be configured to bring Defender for SQL alerts and audit events into Sentinel for analytics and incident creation. Option C is correct because Microsoft Defender for SQL is enabled at the logical server (or managed instance) level, which automatically protects all databases on that server rather than requiring per-database activation. Option D is correct because streaming SQL audit logs to a Log Analytics workspace is required so that Sentinel can query and correlate the audit data alongside Defender alerts. Option A is not needed since Defender for SQL is turned on at the server level and covers all databases, so enabling it individually per database is redundant. Option E is incorrect because streaming metrics to Event Hub does not feed Sentinel; Sentinel requires data in a Log Analytics workspace, and metrics alone are not the relevant security telemetry.

Answer

Enable Advanced Threat Protection for each database individually

Answer

Create a diagnostic setting to stream metrics to Event Hub

Question 7

Which TWO conditions must be met to use Azure SQL Database serverless compute tier?

Accepted Answer

The database must be a single database, not part of an elastic pool. Option C is correct because the serverless compute tier is only available for single Azure SQL databases; databases that are members of an elastic pool cannot use serverless compute. Option E is correct because serverless databases must run on Gen5 hardware, which is the supported hardware generation for the serverless tier. Options A and D are incorrect because serverless is a vCore-based purchasing model option within the General Purpose service tier, not a DTU model requirement, and the tier itself is General Purpose rather than a separate condition to be met. Option B is incorrect because Hyperscale is a different service tier and does not support serverless compute.

Answer

The database must use the DTU purchasing model

Answer

The database must be in the Hyperscale service tier

Answer

The database must be in the General Purpose service tier

Question 8

You are a database administrator for a global e-commerce company. The company runs a critical transactional workload on an Azure SQL Managed Instance (MI) in the West US region. The database is approximately 1 TB in size and uses the Business Critical tier with zone redundancy enabled. The application requires read latency for reporting queries to be under 10 ms. The company is expanding to Europe and needs to deploy a secondary copy of the database in the North Europe region to serve read-only reporting traffic for European users. The secondary must be kept in sync with the primary with a recovery point objective (RPO) of less than 5 seconds. Additionally, the solution must support automatic failover if the primary region becomes unavailable. You need to design the disaster recovery and read scaling solution. Which approach should you take?

Accepted Answer

Create an auto-failover group with the primary in West US and add a readable secondary in North Europe, using synchronous commit. Azure SQL Managed Instance supports auto-failover groups with a readable secondary in a paired region. By configuring synchronous commit within the failover group, you achieve an RPO of less than 5 seconds (typically 0-2 seconds) while the secondary in North Europe can serve read-only traffic with low latency. The auto-failover group also provides automatic failover if the primary region becomes unavailable, meeting both the read scaling and disaster recovery requirements.

Answer

Create a geo-replica of the MI in North Europe and manually failover when needed

Answer

Deploy a separate managed instance in North Europe and use transactional replication to keep it in sync

Answer

Create an auto-failover group with the primary in West US and secondary in North Europe, but do not make the secondary readable

Question 9

You are designing a new Azure SQL Database deployment for a mission-critical application. The application requires 99.99% availability SLA, automated failover within 30 seconds, and read-scale capability for reporting. Which tier and configuration should you choose?

Accepted Answer

Business Critical with zone redundancy and a failover group. Business Critical with zone redundancy and a failover group meets all requirements: 99.99% SLA (supported by zone-redundant Business Critical), automated failover within 30 seconds (failover groups provide automatic, fast failover across regions), and read-scale capability (Business Critical includes a readable secondary replica).

Answer

General Purpose serverless with geo-replication

Answer

General Purpose with active geo-replication and read-scale

Answer

Hyperscale with zone redundancy enabled

Question 10

Refer to the exhibit. The exhibit shows a partial ARM template for deploying an Azure SQL Database server. You run the deployment but get an error stating the server cannot be created because of a missing firewall rule. The template has public network access disabled and no firewall rules defined. What is the most likely cause?

Accepted Answer

The Entra ID admin SID is invalid (all zeros).. The error occurs because the Entra ID admin SID is set to all zeros ('00000000-0000-0000-0000-000000000000'), which is an invalid GUID. Azure SQL Database requires a valid, non-zero SID (object ID) for the Entra ID admin to establish authentication. When public network access is disabled, the server can still be created without firewall rules, but the invalid SID prevents the deployment from succeeding.

Answer

The firewallRules array must include at least one rule when publicNetworkAccess is Disabled.

Answer

The tenantId is missing in the Entra ID admin configuration.

Answer

The Entra ID admin type should be 'Group' not 'User'.

Question 11

You are migrating an on-premises SQL Server database with 5 TB of data to Azure SQL Database. The database has multiple large tables and indexes. You need to minimize downtime during migration. Which service should you use?

Accepted Answer

Azure Database Migration Service with online migration mode. Azure Database Migration Service (DMS) with online migration mode is the correct choice because it minimizes downtime by continuously synchronizing ongoing changes from the on-premises SQL Server to Azure SQL Database while the initial bulk data copy is in progress. This allows you to cut over to the target with only a brief pause, which is critical for a 5 TB database where offline methods would cause extended unavailability.

Answer

Export to BACPAC and import using SqlPackage

Answer

Backup and restore using URL with log shipping

Answer

Transactional replication from on-premises to Azure SQL Database

Question 12

You have an Azure SQL Database with a table that stores historical data. To improve query performance, you create a nonclustered columnstore index on the table. However, after a large data load, you notice that query performance is worse than before. What is the most likely reason?

Accepted Answer

The data was loaded in small batches, causing rowgroups to be smaller than optimal.. When data is loaded in small batches into a columnstore index, each batch may form its own rowgroup. Rowgroups with fewer than 102,400 rows are considered 'delta rowgroups' and are not compressed into the columnstore format, leading to poor compression and suboptimal query performance. The large data load likely consisted of many small batches, resulting in numerous small rowgroups that degrade scan performance.

Answer

The columnstore index is fragmented and needs to be rebuilt.

Answer

The table has too many columns included in the index.

Answer

The index was created with compression disabled.

Question 13

Which THREE factors should you consider when choosing between Azure SQL Database single database and Azure SQL Managed Instance for a new application?

Accepted Answer

Need for cross-database queries and transactions.. Option B is correct because Azure SQL Managed Instance provides near-100% compatibility with SQL Server, including cross-database queries and distributed transactions within the same instance, which Azure SQL Database single database does not support natively (it requires elastic query or external tables). Option D is correct because SQL Server Agent with job scheduling is available in Azure SQL Managed Instance, whereas Azure SQL Database single database does not include SQL Server Agent (you must use Elastic Jobs or Azure Automation instead). Option E is correct because Azure SQL Managed Instance is deployed inside an Azure virtual network with a private IP address, while Azure SQL Database single database uses a public endpoint (or private link) and does not natively reside in a VNet. Option A is not a distinguishing factor because elastic pools are a feature of Azure SQL Database single databases, not Managed Instance, so it favors single database rather than being a general consideration for choosing between them. Option C is not a distinguishing factor because both Azure SQL Database and Azure SQL Managed Instance provide automated backups with point-in-time restore by default.

Answer

Requirement for elastic pool scalability.

Answer

Need for automated backups with point-in-time restore.

Question 14

Which TWO features can help you monitor and troubleshoot a query performance issue in Azure SQL Database without making changes to the application code?

Accepted Answer

Enabling Automatic Tuning to force plan corrections.. Option B is correct because Automatic Tuning in Azure SQL Database can detect plan-choice regressions and automatically apply plan forcing (via Query Store) to correct a bad plan, which directly helps resolve query performance issues without any application code changes. Option E is correct because Query Store captures query, plan, and runtime statistics over time, letting you identify regressed queries and compare plans, which is a core monitoring and troubleshooting mechanism that requires no application modifications. Option A is not appropriate because sp_setapprole sets application role security context and has nothing to do with query performance monitoring. Option C is not appropriate because Always Encrypted is a data-protection feature for sensitive columns and does not help diagnose query performance. Option D is not appropriate because Azure Data Migration Assistant assesses migration readiness and compatibility, not ongoing query performance troubleshooting in Azure SQL Database.

Answer

Using sp_setapprole to set application roles.

Answer

Implementing Always Encrypted for sensitive columns.

Answer

Using Azure Data Migration Assistant for performance assessment.

Question 15

You are a database administrator for a large e-commerce company. The company has an Azure SQL Database named 'OrdersDB' in the General Purpose service tier, with a 2 TB database. The database has a table named 'OrderDetails' that contains over 1 billion rows. The table is partitioned by date using a partition function that splits data by month. You notice that queries that filter on a specific date range are performing poorly, and the query plan shows a clustered index scan on the partition column. The table has a clustered columnstore index to support analytics. You need to improve query performance for date-range queries without affecting the analytics workload. What should you do?

Accepted Answer

Create a nonclustered index on the partition key column (OrderDate) and include other columns as needed.. Creating a nonclustered index on the partition key (OrderDate) allows SQL Server to perform an index seek for date-range queries, avoiding the full clustered columnstore index scan. This index does not interfere with the existing columnstore index, which remains optimal for analytics workloads. The nonclustered index can include additional columns as included columns to cover the query without touching the base table.

Answer

Create a new partition scheme with a different partition function.

Answer

Rebuild the clustered columnstore index with a different compression setting.

Answer

Change the clustered index to a rowstore B-tree index on the partition key.