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

Question 1

Your company is adopting Microsoft Defender XDR for enhanced security. You need to enable Microsoft Defender for SQL for your Azure SQL Database to receive security alerts and vulnerability assessments. What is the first step you must take?

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

Accepted Answer

Enable Microsoft Defender for SQL on the Azure SQL logical server.. Microsoft Defender for SQL must be enabled at the logical server level before any security alerts or vulnerability assessments can be applied to the Azure SQL Database. This server-level enablement activates the Defender for SQL features (including vulnerability assessment and threat detection) for all databases under that server, making it the prerequisite step.

Answer

Configure Microsoft Intune to manage access.

Answer

Run a vulnerability assessment scan from the Azure portal.

Answer

Configure Microsoft Purview to scan the database.

Question 2

You are deploying an Azure SQL Database that will be used by a global application. You need to ensure that read-intensive workloads are offloaded from the primary database to improve performance. Which feature should you enable?

Accepted Answer

Read scale-out.. Read scale-out (A) is the correct feature because it allows you to offload read-only workloads to a read-only replica of the Azure SQL Database. By enabling the 'Read scale-out' property, the database automatically routes connections with `ApplicationIntent=ReadOnly` to a secondary replica, freeing the primary from read-intensive queries and improving overall performance for write operations.

Answer

Automatic tuning.

Answer

Connection pooling.

Answer

Active geo-replication.

Question 3

You are designing a disaster recovery solution for an Azure SQL Database in the Business Critical tier. You require automatic failover to a secondary region with zero data loss. Which configuration should you use?

Accepted Answer

Create a failover group with the Business Critical tier.. The Business Critical tier in Azure SQL Database supports failover groups, which provide automatic failover across regions with zero data loss by using synchronous replication at the primary region and asynchronous replication to the secondary region. Failover groups ensure that all committed transactions are preserved during a failover, meeting the zero data loss requirement for disaster recovery.

Answer

Configure geo-replication between two servers.

Answer

Enable read scale-out in a secondary region.

Answer

Use active geo-replication with manual failover.

Question 4

Your organization requires that all Azure SQL Database backups be retained for 10 years to meet compliance requirements. Which backup retention policy should you configure?

Accepted Answer

Configure long-term retention (LTR) policy.. Long-term retention (LTR) in Azure SQL Database allows you to retain full database backups for up to 10 years, which meets the compliance requirement for 10-year backup retention. LTR policies are configured separately from point-in-time restore (PITR) and store backups in isolated containers for extended periods, ensuring regulatory compliance.

Answer

Configure point-in-time restore (PITR) retention to 10 years.

Answer

Enable automatic tuning to optimize backups.

Answer

Enable geo-redundant backup storage (GRS).

Question 5

Which TWO actions are required to implement Azure SQL Database automatic failover with zero data loss?

Accepted Answer

Create an auto-failover group.. An auto-failover group is required because it provides automatic failover capability across Azure SQL Database instances, which is essential for achieving zero data loss during a regional outage. The Premium or Business Critical service tier is required because these tiers use locally redundant storage with synchronous replication to ensure that transactions are committed without data loss, unlike the Standard or General Purpose tiers that use asynchronous replication.

Answer

Configure active geo-replication.

Answer

Enable read scale-out.

Answer

Configure multiple readable secondaries.

Question 6

Which THREE considerations are important when planning a migration from SQL Server on-premises to Azure SQL Managed Instance?

Accepted Answer

File tables and FILESTREAM.. Azure SQL Managed Instance does not support FILESTREAM or FileTables. These features rely on the NTFS file system for storing BLOB data, which is not available in the managed instance environment. Any migration using FILESTREAM or FileTables must be refactored to use alternative storage, such as Azure Blob Storage with the remote BLOB store (RBS) or separate file storage.

Answer

SQL Server Integration Services (SSIS) packages.

Answer

Use of contained databases.

Question 7

Which TWO benefits does the Hyperscale service tier of Azure SQL Database provide?

Accepted Answer

Up to 100 TB of database storage.. The Hyperscale service tier of Azure SQL Database supports up to 100 TB of database storage, which is significantly larger than the 4 TB limit of the General Purpose tier and the 4 TB limit of the Business Critical tier. This massive storage capacity is achieved through a distributed architecture where data is stored in page servers with a shared disk model, allowing near-instantaneous scaling of storage without downtime.

Answer

Built-in in-memory OLTP support.

Answer

Zone-redundant configuration by default.

Answer

Zero data loss in all scenarios.

Question 8

You are reviewing an ARM template snippet that configures a long-term retention (LTR) policy for an Azure SQL Database. Based on the exhibit, how long will weekly backups be retained?

Accepted Answer

4 weeks. [CORRECT]. In the ARM template snippet, the weeklyRetention property is set to 'P4W' (ISO 8601 duration format), which means 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks.

Answer

4 days. [wrong]

Answer

4 months. [wrong]

Answer

4 years. [wrong]

Question 9

You are evaluating the configuration of an Azure SQL Database as shown in the exhibit. You need to ensure that the database remains available during a zonal failure without data loss. Which feature contributes to this requirement?

Accepted Answer

Zone redundancy enabled on Business Critical tier.. Zone redundancy on the Business Critical tier ensures that database replicas are placed in different availability zones within the same Azure region. During a zonal failure, the service automatically fails over to a synchronous replica in another zone, guaranteeing zero data loss because all transactions are synchronously committed across replicas. This directly meets the requirement of remaining available without data loss during a zonal outage.

Answer

General Purpose service tier.

Answer

Read scale-out.

Answer

Geo-redundant backup storage.

Question 10

A database administrator runs the PowerShell script shown in the exhibit against an Azure SQL Database. What does the script output?

Accepted Answer

Service tier and performance level of the database.. The PowerShell script uses the Get-AzSqlDatabase cmdlet, which retrieves an Azure SQL Database object. The output includes properties such as Edition (service tier) and ServiceObjective (performance level), making option D correct.

Answer

Details of table schemas.

Answer

List of backup retention policies.

Answer

List of users and their permissions.

Question 11

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. Which PaaS option should you choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. These features are not available in Azure SQL Database, which is a PaaS offering with a more restricted surface area. SQL Server on Azure VM is IaaS and requires manual management, while elastic query in Azure SQL Database only supports cross-database queries across different databases, not the full Service Broker or Agent functionality.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machine

Answer

Azure SQL Database with elastic query

Question 12

You need to ensure that all queries executed against an Azure SQL Database are audited and logged to a Log Analytics workspace for security analysis. Which feature should you enable?

Accepted Answer

Azure SQL Auditing. Azure SQL Auditing is the feature specifically designed to track database events and write them to an audit log destination, including a Log Analytics workspace. This enables security analysis by capturing all queries executed against the database, which can then be queried and analyzed within the workspace. The other options focus on vulnerability scanning, threat detection, or security posture management, not on recording query execution logs.

Answer

SQL Vulnerability Assessment

Answer

Advanced Threat Protection

Answer

Microsoft Defender for Cloud

Question 13

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 14

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

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