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

Question 1

You are configuring an Azure SQL Database elastic pool for a SaaS application. The pool will host 50 databases with varying workloads. You need to minimize cost while ensuring performance meets baseline requirements. Which tier and configuration should you choose?

Accepted Answer

Serverless tier with General Purpose and auto-pause enabled. Option C is correct because the Serverless tier with General Purpose and auto-pause enabled is the most cost-effective choice for a pool of 50 databases with varying workloads. Serverless automatically scales compute based on demand and pauses databases during inactivity, charging only for storage and used vCores, which minimizes cost while meeting baseline performance requirements.

Answer

Hyperscale tier with 4 vCores

Answer

Provisioned tier with General Purpose and 2 vCores

Answer

DTU-based elastic pool with 200 DTUs

Question 2

Your Azure SQL Managed Instance is experiencing performance degradation. You suspect a query plan regression caused by parameter-sensitive plan issues. Which feature should you use to identify and resolve the issue?

Accepted Answer

Query Store with Query Store Hints. Query Store with Query Store Hints is the correct feature because it allows you to identify parameter-sensitive plan regression by analyzing historical execution plans stored in Query Store, and then force a specific plan using a hint without changing application code. This directly addresses the root cause of parameter-sensitive plan issues, where different parameter values lead to suboptimal cached plans.

Answer

Intelligent Insights

Answer

Automatic tuning

Answer

Database Advisor

Question 3

You need to migrate an on-premises SQL Server 2019 database to Azure SQL Database with minimal downtime. The database is 500 GB and uses some features not supported in Azure SQL Database, such as FileTables. What is the best migration strategy?

Accepted Answer

Use Azure Database Migration Service with online mode after removing FileTables. Option D is correct because Azure Database Migration Service (DMS) with online mode supports minimal-downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Database. However, FileTables are not supported in Azure SQL Database, so they must be removed from the source database before migration. This approach ensures near-zero downtime while addressing the unsupported feature.

Answer

Set up log shipping to an Azure SQL Database

Answer

Perform an offline migration using Azure Database Migration Service

Answer

Export a BACPAC file from the source and import it to Azure SQL Database

Question 4

You are configuring security for an Azure SQL Database. You need to ensure that only traffic from a specific virtual network and a specific set of public IP addresses can connect to the database. Which two features should you enable?

Accepted Answer

VNet service endpoints and firewall rules. To restrict access to an Azure SQL Database to traffic from a specific virtual network and a specific set of public IP addresses, you need to combine VNet service endpoints with firewall rules. VNet service endpoints allow you to restrict inbound traffic from a specific subnet in a virtual network, while firewall rules (IP-based) allow you to specify allowed public IP address ranges. Together, they provide a layered network security approach that meets the requirement.

Answer

Microsoft Entra ID authentication and firewall rules

Answer

Advanced Threat Protection and VNet service endpoints

Answer

Private endpoint and VNet service endpoints

Question 5

Your Azure SQL Database is running in the General Purpose tier. You notice that read queries are experiencing high latency. You need to improve read performance without changing the compute size. What should you implement?

Accepted Answer

Enable read scale-out. Read scale-out in Azure SQL Database allows you to offload read-only queries to a secondary replica, reducing latency on the primary replica without changing the compute size. This feature is available in the Premium and Business Critical service tiers, but not in General Purpose. Since the question specifies the General Purpose tier, enabling read scale-out is not actually supported, making this a trick scenario. However, among the given options, enabling read scale-out is the only one that directly addresses read latency by leveraging a read-only replica, even though it requires a tier change to Premium or Business Critical.

Answer

Upgrade to the Hyperscale service tier

Answer

Create columnstore indexes on the tables

Answer

Use in-memory OLTP for the tables

Question 6

You are designing a data archiving solution for an Azure SQL Database. The database contains a table with over 100 million rows of historical sales data that is rarely accessed. You need to reduce storage costs while keeping the data available for occasional queries. What should you do?

Accepted Answer

Use Stretch Database to migrate cold data to Azure Blob Storage. Stretch Database (now deprecated but still tested in DP-300) transparently migrates cold data from an Azure SQL Database table to Azure Blob Storage while keeping the data online and queryable via T-SQL. This reduces storage costs for rarely accessed historical data without requiring application changes, as the database engine automatically routes queries to the appropriate storage tier.

Answer

Partition the table by date and compress each partition

Answer

Use page compression on the table

Answer

Delete old data and store it in Azure Blob Storage as CSV files

Question 7

A company is designing a new Azure SQL Database for an e-commerce application. The database will experience variable workloads with frequent read-heavy operations. To optimize performance and cost, the company wants to use a serverless compute tier. However, they also need to ensure the database can handle sudden bursts of user activity without significant latency. Which configuration should the team implement?

Accepted Answer

Set the serverless auto-pause delay to a high value (e.g., 24 hours) and configure a minimum vCore count that matches the expected baseline load.. Option D is correct because setting a high auto-pause delay (e.g., 24 hours) prevents the serverless database from pausing during idle periods, avoiding cold-start latency. Configuring a minimum vCore count that matches the expected baseline load ensures the database always has sufficient resources for steady-state read-heavy operations, while the maximum vCore count can scale up to handle sudden bursts without significant latency.

Answer

Use a provisioned General Purpose tier with read scale-out enabled to offload read traffic to a secondary replica.

Answer

Set the serverless auto-pause delay to 0 (disabled) and configure a maximum vCore count equal to the expected burst peak.

Answer

Enable auto-pause with a 1-minute delay and configure the maximum vCore count to handle bursts.

Question 8

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?

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

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 10

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

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

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 13

You are responsible for cost optimization of a non-production Azure SQL Database that is used for development testing. The database is only active during business hours (9 AM to 5 PM) on weekdays. Which compute tier and configuration would minimize cost while ensuring the database is available during working hours?

Accepted Answer

Use the serverless compute tier with auto-pause enabled and a 1-hour auto-pause delay.. The serverless compute tier with auto-pause enabled and a 1-hour auto-pause delay is the most cost-effective choice for a non-production database used only during business hours. Serverless automatically pauses the database after 1 hour of inactivity (e.g., overnight and weekends), charging only for compute during active periods and storage at all times. This aligns perfectly with the 9 AM–5 PM weekday usage pattern, minimizing cost while ensuring the database is available when needed.

Answer

Use the Hyperscale tier with a minimum of 1 vCore and disable auto-pause.

Answer

Use the provisioned General Purpose tier with 2 vCores and disable auto-pause.

Answer

Use the provisioned Business Critical tier with 1 vCore and enable auto-pause.

Question 14

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.. Option D is correct because 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 15

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. Each application uses a separate service principal managed in Microsoft Entra ID. You need to ensure that each service principal has the minimum required permissions to access only its own set of tables. What should you implement?

Accepted Answer

Create a contained database user for each service principal and grant SELECT, INSERT, UPDATE, DELETE on specific tables.. Option B is correct because it creates a contained database user for each service principal (mapped to the Microsoft Entra ID identity) and grants only the specific table-level permissions (SELECT, INSERT, UPDATE, DELETE) required for that application. This follows the principle of least privilege by avoiding broad database roles and ensuring each service principal can only access its own set of tables.

Answer

Create a contained database user for each service principal and grant the db_owner role.

Answer

Create a server-level login for each service principal and assign db_datareader role in the database.

Answer

Configure a server-level firewall rule for each service principal IP address.