DP-300 Practice Exam 55 — 20 Questions

Question 1

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 2

Your company is deploying Azure SQL Database for a new application. The security policy requires that all queries be encrypted in transit and that the database be protected against accidental deletion. Which THREE configurations should you implement? (Select THREE.)

Accepted Answer

Set the minimum TLS version to 1.2. Setting the minimum TLS version to 1.2 ensures that all connections to Azure SQL Database use a secure, encrypted channel, enforcing encryption in transit as required by the security policy. TLS 1.2 is the minimum recommended version, as older versions (1.0, 1.1) are deprecated and vulnerable to attacks like POODLE and BEAST.

Answer

Configure firewall rules to allow only specific IP addresses

Answer

Enable Microsoft Entra ID authentication

Question 3

You are reviewing an ARM template snippet for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' in resource group 'rg1'. The source database 'db1' is in the same server 'server1' and is currently active. What will be the result of deploying this template?

Accepted Answer

It will create a new empty database with the specified service objective.. The ARM template snippet specifies `createMode: Default` and a `requestedServiceObjectiveName`, but does not include a `sourceDatabaseId` or `restorePointInTime` property. With `createMode: Default`, Azure SQL Database creates a new empty database using the specified service objective, regardless of the presence of a `sourceDatabaseId` in the template (which is ignored when `createMode` is not set to `Copy`, `PointInTimeRestore`, or `Secondary`). Therefore, the deployment succeeds and creates a new empty database named 'db2' with the defined performance tier.

Answer

The deployment will fail because sourceDatabaseId is specified but not used.

Answer

It will create a copy of db1 in the same server.

Answer

It will perform a point-in-time restore of db1 to the current time.

Question 4

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 5

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 6

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 7

Your company plans to migrate several on-premises SQL Server databases to Azure SQL Database. The databases are used by different applications, each with varying performance requirements. You need to choose a deployment option that provides the most cost-effective solution while allowing for independent scaling of each database. Which option should you choose?

Accepted Answer

Create an Azure SQL Database elastic pool and add databases to the pool.. Azure SQL Database elastic pools allow multiple databases to share a fixed set of resources (eDTUs or vCores), enabling cost savings by pooling underutilized databases while providing the ability to independently scale each database's resource consumption within the pool. This meets the requirement for independent scaling and cost-effectiveness, as opposed to single databases that each require their own dedicated resources.

Answer

Install SQL Server on Azure Virtual Machines and host all databases on a single instance.

Answer

Migrate all databases to a single Azure SQL Managed Instance.

Answer

Deploy each database as a single Azure SQL Database with its own DTU-based tier.

Question 8

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 9

Your organization has multiple Azure SQL Databases in an elastic pool. You need to monitor the performance of the pool and identify which databases are consuming the most resources. You want to view historical resource usage for the past month. Which tool should you use?

Accepted Answer

Azure SQL Analytics (Azure Monitor). Azure SQL Analytics (Azure Monitor) is the correct tool because it provides built-in monitoring and alerting for elastic pools, including historical resource usage (CPU, DTU, storage) for up to 30 days. It aggregates data across all databases in the pool and allows you to drill down into individual database consumption, making it ideal for identifying top resource consumers over the past month.

Answer

Azure portal metrics for the elastic pool

Answer

SQL Server Profiler

Answer

Query Performance Insight

Question 10

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 11

You are reviewing the firewall and virtual network rules for an Azure SQL Database server as shown in the exhibit. The server has public network access disabled. A client application in a different virtual network (vnet2) needs to connect to the database. What must you do to allow the connection?

Accepted Answer

Add a virtual network rule that references subnet2 in vnet2.. Since public network access is disabled, the server rejects all public IP-based connections. To allow a client in a different virtual network (vnet2) to connect, you must create a virtual network rule that references subnet2 in vnet2. This rule establishes a private endpoint-like connection through the Azure backbone, bypassing the public internet and honoring the disabled public network access setting.

Answer

Add the IP address of the client to the ipRules array.

Answer

Set publicNetworkAccess to 'Enabled' and add an IP firewall rule.

Answer

Create a server-level firewall rule to allow all Azure services.

Question 12

You are tasked with designing a disaster recovery plan for an Azure SQL Database. The database is mission-critical and must have a Recovery Point Objective (RPO) of 5 seconds and a Recovery Time Objective (RTO) of 30 minutes in case of a regional failure. Which configuration should you implement?

Accepted Answer

Active geo-replication with failover group. Active geo-replication with failover groups is the correct choice because it provides a continuous, synchronous replication mechanism that can achieve an RPO of 5 seconds and an RTO of 30 minutes during a regional failure. The failover group automates the process of failing over to a readable secondary database in a paired region, meeting both the stringent RPO and RTO requirements without manual intervention.

Answer

Long-term backup retention

Answer

Automated backups with geo-restore

Answer

Zone-redundant configuration

Question 13

Your company uses Azure SQL Database with the Hyperscale service tier. You notice that the database is experiencing high I/O latency during peak hours. After analyzing the query performance, you determine that the primary bottleneck is due to log write throughput. You need to reduce log write latency without changing the service tier. What should you do?

Accepted Answer

Scale up to a higher Hyperscale service objective.. Option C is correct because scaling up to a higher Hyperscale service objective increases the log write throughput by provisioning more log I/O capacity and faster storage. Since the bottleneck is specifically log write latency, and you cannot change the service tier, increasing the service objective within Hyperscale directly addresses the issue by allocating more resources to the log write path.

Answer

Configure Query Store hints to force parameterization.

Answer

Enable read scale-out to offload read queries.

Answer

Increase the MAXDOP setting for the database.

Question 14

Your team is migrating an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses Service Broker for cross-database messaging. The compliance requirement mandates that the migration must be performed with minimal downtime and that the target must support the Service Broker feature. What migration strategy should you recommend?

Accepted Answer

Use Azure Database Migration Service (DMS) with online migration to Azure SQL Managed Instance. Option D is correct because Azure SQL Managed Instance fully supports Service Broker, and the Azure Database Migration Service (DMS) with online migration mode enables minimal downtime by continuously replicating changes from the source SQL Server to the target Managed Instance until a cutover. This satisfies both the Service Broker feature requirement and the compliance mandate for minimal downtime.

Answer

Perform a native backup and restore to Azure SQL Managed Instance

Answer

Use the Data Migration Assistant (DMA) to migrate to Azure SQL Database

Answer

Use Azure Database Migration Service (DMS) with offline migration to Azure SQL Database

Question 15

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. Geo-replication with readable secondary (Option B) is correct because it creates a continuously synchronized read-only replica of your Azure SQL Database in a different Azure region. Applications can connect to this secondary endpoint using the same connection string with 'ApplicationIntent=ReadOnly', enabling read-intensive workloads to be offloaded without any code changes. This provides both scalability for reads and disaster recovery.

Answer

Database sharding

Answer

In-memory OLTP

Answer

Elastic pool

Question 16

You need to provision an Azure SQL Database that supports a high-availability SLA of 99.995% and provides automatic failover to a secondary region in case of a regional outage. Which deployment option should you choose?

Accepted Answer

Azure SQL Database with active geo-replication and failover group. Active geo-replication with a failover group provides an SLA of 99.995% by replicating data asynchronously to a secondary region and enabling automatic failover during a regional outage. This is the only option that meets both the high-availability SLA and cross-region disaster recovery requirement.

Answer

Azure SQL Database in Hyperscale tier

Answer

Azure SQL Database with zone-redundant configuration

Answer

Azure SQL Managed Instance with failover group

Question 17

You are managing an Azure SQL Managed Instance that hosts multiple databases for a financial application. You need to implement a security solution that meets compliance requirements by auditing all database activity and sending the audit logs to a centralized Log Analytics workspace for analysis. The solution must also support real-time alerts on suspicious activities. What should you configure?

Accepted Answer

Enable auditing to a Log Analytics workspace and integrate with Microsoft Sentinel.. Option C is correct because auditing to a Log Analytics workspace allows centralized collection of audit logs, which can then be integrated with Microsoft Sentinel for real-time analytics, threat detection, and automated alerting on suspicious activities. This meets both the compliance requirement for auditing and the operational need for real-time alerts.

Answer

Enable Microsoft Defender for Cloud and configure SQL vulnerability assessment.

Answer

Enable auditing to a storage account and use Microsoft Intune for monitoring.

Answer

Enable Microsoft Purview Data Map for the managed instance.

Question 18

You are designing a new Azure SQL Database for an e-commerce application. The database will contain sensitive customer data and must be isolated from other tenants. Which deployment option provides the highest level of resource isolation?

Accepted Answer

Single database. A single database in Azure SQL Database runs on a dedicated logical server and, when configured with the appropriate service tier and compute size, provides the highest level of resource isolation because it does not share CPU, memory, or IO resources with any other database. This ensures that the sensitive customer data is isolated from other tenants, meeting strict compliance and performance requirements.

Answer

Elastic pool

Answer

Hyperscale

Answer

Managed Instance

Question 19

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' from 'db1'. The source database 'db1' is currently in a failed state due to a storage issue. What will be the result of deploying this template?

Accepted Answer

The deployment will fail because db1 is not in a recoverable state.. The ARM template creates a new database by copying from a source database. Azure SQL Database requires the source database to be in an online and healthy state to perform a copy operation. Since db1 is in a failed state due to a storage issue, it is not accessible for copying, so the deployment will fail.

Answer

It will create an empty database because the source is not accessible.

Answer

It will delete db1 and create db2 as a replacement.

Answer

It will create a new database by recovering db1 to its last known good state.

Question 20

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. Which step is required to enable TDE in the target Managed Instance?

Accepted Answer

Export the certificate to a PFX file, import it to Azure Key Vault, and configure TDE with a key vault key. Azure SQL Managed Instance does not support importing user-provided certificates for TDE. Instead, it relies on Azure Key Vault as the external key store. Therefore, you must export the on-premises TDE certificate to a PFX file, import it into Azure Key Vault, and then configure the Managed Instance to use that key vault key for TDE. This ensures the database encryption key can be unwrapped and the database remains encrypted during and after migration.

Answer

Create a self-signed certificate in the Managed Instance and restore the database with KEEP_TDE

Answer

Use the same certificate file by uploading it to the Managed Instance via SQL Server Management Studio

Answer

Disable TDE on the source database before migration, then enable it after migration