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

Question 1

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?

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

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 2

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.. 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.

Question 3

Which TWO of the following are supported high availability features in Azure SQL Managed Instance?

Accepted Answer

Zone-redundant deployment for Business Critical tier. Option A is correct because Azure SQL Managed Instance in the Business Critical service tier supports zone-redundant deployment, which places the primary and secondary replicas across different availability zones to protect against datacenter-level failures. Option C is correct because SQL Managed Instance includes a built-in Always On Availability Groups mechanism that automatically provisions and manages replicas for high availability, with the Business Critical tier using local SSD and multiple secondary replicas. Database mirroring (B) is a deprecated SQL Server feature and is not offered as a supported HA option in Azure SQL Managed Instance. Log shipping to a secondary instance (D) is not a built-in HA feature of SQL Managed Instance, though it can be configured manually for disaster recovery scenarios. Windows Server Failover Clustering (E) is an underlying infrastructure concept used by SQL Server on-premises and Azure VMs, but it is not exposed or supported as a user-configurable HA feature in Azure SQL Managed Instance.

Answer

Database mirroring

Answer

Log shipping to a secondary instance

Answer

Windows Server Failover Clustering

Question 4

Which THREE of the following are required steps to configure a failover group for an Azure SQL Database with a readable secondary in a different region?

Accepted Answer

Add the primary database to the failover group.. Option B is correct because a failover group is defined over databases, so the primary database must be added to the failover group (via New-AzSqlDatabaseFailoverGroup or the portal) to be replicated and made failover-eligible. Option D is correct because you must first provision a secondary Azure SQL logical server in the target region, and it must use the same administrator login and password as the primary server so the contained database users and logins resolve after failover. Option E is correct because the failover group requires a read/write failover policy to be set to either Automatic or Manual, which determines whether Azure initiates failover automatically or only on manual trigger. Option A is not required because failover group configuration does not mandate a server-level firewall rule on the secondary server; firewall rules are managed separately and the secondary is typically accessed through the failover group listener. Option C is not required because the grace period is a configurable value (default 1 hour) that can be set to other durations, and it is not a mandatory step to create the failover group.

Answer

Create a server-level firewall rule on the secondary server to allow client IPs.

Answer

Configure a grace period of at least 1 hour.

Question 5

Your company has an Azure SQL Managed Instance that hosts multiple databases. You need to implement a solution to automatically detect and alert on potential SQL injection attacks. The solution must integrate with Microsoft Sentinel for incident response. What should you configure?

Accepted Answer

Enable Microsoft Defender for SQL on the Managed Instance. Microsoft Defender for SQL on Azure SQL Managed Instance provides built-in SQL injection detection and alerting, which can be integrated directly with Microsoft Sentinel for automated incident response and investigation. This is the correct solution because it offers native vulnerability assessment and threat detection tailored to SQL databases, meeting the requirement for both detection and Sentinel integration.

Answer

Enable Microsoft Purview Data Map for the Managed Instance

Answer

Configure Microsoft Defender XDR for SQL

Answer

Use Microsoft Intune to manage SQL security policies

Question 6

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database. You need to ensure that tenant data is isolated and that performance is predictable. Cost efficiency is important. Which deployment model should you use?

Accepted Answer

Use an elastic pool with one database per tenant. An elastic pool allows you to provision a shared set of resources (eDTUs or vCores) that is distributed across multiple databases, each representing a tenant. This provides logical isolation of tenant data (each tenant has its own database) while pooling resources to handle variable workloads cost-effectively. The elastic pool model is specifically designed for SaaS multi-tenant scenarios where predictable performance is achieved through resource governance, and cost efficiency comes from sharing resources among databases that do not all peak simultaneously.

Answer

Deploy a single Azure SQL Database per tenant

Answer

Use Azure SQL Managed Instance with multiple databases

Answer

Use a single large database with row-level security

Question 7

You are designing a solution for storing audit logs from Azure SQL Database. The logs must be retained for 7 years and must be immutable to prevent tampering. Which Azure service should you use?

Accepted Answer

Azure Blob Storage with immutable storage policy. Azure Blob Storage with an immutable storage policy (WORM – Write Once, Read Many) is the correct choice because it ensures that audit logs cannot be modified or deleted for a specified retention period (7 years). This meets the immutability and retention requirements for compliance with regulations such as SOX or HIPAA. Azure SQL Database audit logs can be directly streamed to Azure Blob Storage, making it a seamless and secure storage solution.

Answer

Use Azure Files share with read-only permissions

Answer

Send logs to Azure Log Analytics workspace

Answer

Store logs in an audit table in Azure SQL Database

Question 8

You are deploying Azure SQL Database for a new application. You need to ensure that connections from Azure services use a private IP address and do not traverse the public internet. What should you configure?

Accepted Answer

Use Azure Private Link for Azure SQL Database. Azure Private Link creates a private endpoint in a virtual network, mapping the Azure SQL Database logical server to a private IP address within that VNet. Traffic from Azure services (e.g., VMs, App Service) to the database uses Microsoft's backbone network via the private endpoint, never traversing the public internet. This meets the requirement for private, non-internet-routed connectivity.

Answer

Enable Virtual Network service endpoints for Azure SQL Database

Answer

Configure the Azure SQL Database firewall to allow Azure services

Answer

Deploy Azure SQL Database inside a virtual network

Question 9

You are configuring Azure SQL Database for a new e-commerce application that must support high read throughput for product catalog queries. The application uses Entity Framework Core and requires that read-only queries be offloaded to a secondary replica to reduce load on the primary. Which feature should you enable?

Accepted Answer

Enable read scale-out and configure the application to use read-only intent.. Read scale-out in Azure SQL Database allows you to offload read-only workloads to a readable secondary replica by setting the application connection string's `ApplicationIntent=ReadOnly`. This reduces load on the primary replica, which is essential for high read throughput in an e-commerce catalog scenario. Entity Framework Core can use this by specifying `ReadOnly` in the connection string or via a custom interceptor.

Answer

Enable Query Performance Insights and create indexes for frequent queries.

Answer

Configure Active Geo-Replication with a readable secondary in a different region.

Answer

Enable automatic tuning to force parameterization of queries.

Question 10

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database, and you need to ensure that resource usage is isolated and predictable. You also need to manage performance at the tenant level. Which Azure SQL Database offering should you choose?

Accepted Answer

Azure SQL Database elastic pools with per-database min/max DTU or vCore settings.. Azure SQL Database elastic pools with per-database min/max DTU or vCore settings are the correct choice because they provide resource isolation and predictable performance at the tenant level. Elastic pools allow you to allocate a shared pool of resources across multiple databases while setting per-database minimum and maximum limits, ensuring that no single tenant can consume excessive resources and that each tenant gets a guaranteed baseline. This directly addresses the multi-tenant SaaS requirement for isolated and predictable resource usage.

Answer

Azure SQL Database serverless compute tier.

Answer

Azure SQL Database single databases with DTU-based tier.

Answer

Azure SQL Managed Instance with multiple databases.

Question 11

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. You need to implement a solution that allows applications to connect using their own managed identities without storing credentials in connection strings. What should you configure?

Accepted Answer

Enable Microsoft Entra ID authentication and assign managed identities to the applications.. Microsoft Entra ID authentication allows Azure SQL Database to trust tokens issued by Entra ID for managed identities. By assigning a managed identity to each application, the application can acquire an access token from Azure Managed Identity endpoints and present it to the database without ever storing credentials in connection strings. This eliminates the need for passwords or connection string secrets.

Answer

Use Always Encrypted with column master key in Azure Key Vault.

Answer

Configure firewall rules to allow application IP addresses.

Answer

Enable SQL Server authentication and create a login for each application.

Question 12

You are deploying an Azure SQL Database using PowerShell as shown in the exhibit. The database will be used by a development team that works intermittently. You need to ensure the database is cost-effective while being available on demand. What is the purpose of the AutoPauseDelayInMinutes parameter?

Accepted Answer

It controls the automatic pausing of the database after a period of inactivity to save costs.. The AutoPauseDelayInMinutes parameter is used with Azure SQL Database serverless compute tier. It specifies the number of minutes of inactivity (no CPU usage or active sessions) after which the database automatically pauses, stopping compute billing while storage remains billed. This makes the database cost-effective for intermittent development workloads because it eliminates compute costs during idle periods and automatically resumes on the first connection.

Answer

It configures the database to pause during a disaster recovery scenario.

Answer

It sets the maximum duration for which the database can be paused.

Answer

It determines how long the database takes to resume after a pause.

Question 13

You are configuring managed backup for an Azure SQL Managed Instance as shown in the exhibit. What is the purpose of this configuration?

Accepted Answer

To configure automated backups of the managed instance to Azure Blob Storage.. The configuration shown in the exhibit is for managed backup, which automatically schedules and stores full, differential, and transaction log backups of the Azure SQL Managed Instance to Azure Blob Storage. This ensures that backups are retained and available for restore operations without manual intervention, fulfilling the core purpose of automated backups to Azure Blob Storage.

Answer

To enable geo-replication for the managed instance.

Answer

To set up disaster recovery across Azure regions.

Answer

To configure point-in-time restore for the managed instance.

Question 14

You are analyzing the SQL script in the exhibit. This script is used to query data stored in Azure Blob Storage from Azure SQL Database. What is the primary purpose of the database scoped credential?

Accepted Answer

To store the shared access signature token for accessing the blob storage.. The database scoped credential in this context securely stores the Shared Access Signature (SAS) token required to authenticate and authorize access to Azure Blob Storage. When creating an external data source with the CREDENTIAL option, SQL Database uses this credential to pass the SAS token to the storage endpoint, enabling read operations for PolyBase or external table queries.

Answer

To define the file format of the external data.

Answer

To specify the schema of the external table.

Answer

To provide the location of the external data source.

Question 15

You are designing a new Azure SQL Database for a critical OLTP workload. The database will be used by a global application with users in North America, Europe, and Asia. The primary requirement is low-latency reads for all regions. You need to choose a deployment option that supports geo-distributed reads and provides a single write endpoint. Which option should you select?

Accepted Answer

Azure SQL Database with failover groups. Azure SQL Database failover groups (Option C) is the correct choice. A failover group provides a single read-write listener endpoint that automatically directs writes to the current primary, while also exposing a read-only listener endpoint that load-balances connections across geo-replicated readable secondaries in other regions. This directly satisfies the requirements for a single write endpoint and low-latency geo-distributed reads. Active geo-replication (Option A) supports readable secondaries but does not provide a single write endpoint. Azure SQL Managed Instance failover groups (Option B) also provide these capabilities, but the scenario specifies Azure SQL Database, not Managed Instance. Hyperscale with geo-replication (Option D) is not required to meet these requirements; Hyperscale is primarily a scalability and performance tier, and geo-replication alone does not provide a single write endpoint.

Answer

Azure SQL Database with active geo-replication

Answer

Azure SQL Managed Instance with failover groups

Answer

Azure SQL Database Hyperscale with geo-replication