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

Question 1

You manage an Azure SQL Database that is experiencing performance degradation during peak hours. You suspect that the current pricing tier is insufficient. You need to increase performance with minimal downtime. Which action should you take?

Accepted Answer

Use the Azure portal to scale up the DTU or vCore service tier.. Scaling up the DTU or vCore service tier in the Azure portal is a dynamic scaling operation that typically completes within minutes and does not require application downtime. Azure SQL Database supports online scaling, meaning the database remains available during the transition, with only a brief connection failover at the end. This directly addresses the need to increase performance during peak hours with minimal disruption.

Answer

Create a new database on a higher tier and copy data manually.

Answer

Enable read scale-out to offload read workloads.

Answer

Implement horizontal sharding across multiple databases.

Question 2

You are migrating a large on-premises SQL Server database (5 TB) to Azure SQL Managed Instance. The network bandwidth between your on-premises data center and Azure is limited to 100 Mbps. You need to minimize the migration time. Which approach should you use?

Accepted Answer

Use Azure Data Box to physically transfer the data.. Azure Data Box is the correct approach because it physically transfers the 5 TB database via a secure storage device, bypassing the 100 Mbps network bottleneck. At 100 Mbps, a 5 TB transfer would take approximately 5.5 days continuously, while Data Box can complete the transfer in a few days including shipping, making it the fastest option for such a large dataset over a limited bandwidth link.

Answer

Use transaction log shipping to continuously sync changes.

Answer

Export as BACPAC and upload to Azure Blob Storage, then import.

Answer

Use Azure Database Migration Service (DMS) online migration.

Question 3

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?

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 4

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 5

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 6

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 7

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 8

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

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

Accepted Answer

Up to 100 TB of database storage.. Option C is correct because 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 10

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.. In the ARM template snippet, the weeklyRetention property is set to 'P1M' (ISO 8601 duration format), which means 1 month. Since a month is approximately 4 weeks, weekly backups are retained for 4 weeks. The LTR policy uses ISO 8601 durations, where 'P1M' explicitly specifies a monthly retention period.

Answer

4 days.

Answer

4 months.

Answer

4 years.

Question 11

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 12

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 13

You are designing a disaster recovery strategy for an Azure SQL Database in the West US region. The database is used by a mission-critical application with an RPO of 5 seconds and an RTO of 30 seconds. Which deployment option should you choose?

Accepted Answer

Deploy the database with zone-redundant configuration in the primary region. Option C is correct because zone-redundant configuration for Azure SQL Database replicates the database across three Azure Availability Zones within the same region, providing automatic failover with an RPO of 0 seconds and an RTO of typically 10-30 seconds. This meets the stringent RPO of 5 seconds and RTO of 30 seconds without relying on cross-region replication, which would introduce higher latency and potential data loss.

Answer

Use active geo-replication with a readable secondary in another Azure region

Answer

Use a failover group with a secondary in a paired region

Answer

Deploy the database with an auto-failover group to a secondary region

Question 14

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 15

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