DP-300 Practice Test 9 — 10 Questions

Question 1

Refer to the exhibit. A PowerShell script is used to move an Azure SQL Database into an elastic pool. The script runs without error. Which condition must be true before the script runs?

Accepted Answer

The database must be in the same server as the elastic pool. Moving a database into an elastic pool requires the database to be in the same server as the pool. Option A is wrong because the elastic pool must be in the same server as the database, not a different server. Option C is wrong because the database can be any service tier (Basic, Standard, Premium, etc.) as long as it fits within the pool's resource limits. Option D is wrong because the database size can be any size up to the pool's max size limit, which is not necessarily less than 10 GB.

Answer

The elastic pool must be in a different server

Answer

The database must be in the Basic tier

Answer

The database size must be less than 10 GB

Question 2

You need to create an Azure SQL Database that will be used by a new application. The database must support JSON data storage and querying. Which data type should you use to store JSON documents?

Accepted Answer

NVARCHAR. B is correct because Azure SQL Database uses the NVARCHAR data type to store JSON documents as text. JSON is stored as a string in NVARCHAR columns, which supports Unicode characters and allows the use of built-in JSON functions like JSON_VALUE, JSON_QUERY, OPENJSON, and ISJSON for validation and querying. NVARCHAR(MAX) is recommended for large or variable-length JSON documents.

Answer

TEXT

Answer

XML

Answer

VARCHAR

Question 3

Refer to the exhibit. You are configuring a backup policy for an Azure SQL Database. The database is in the East US region. You need to ensure that the database can be restored to a different region in the event of a regional disaster. Which setting in the exhibit must be correctly configured?

Accepted Answer

The backupStorageRedundancy is set to Geo.. BackupStorageRedundancy must be set to 'Geo' to enable geo-redundant backups that can be restored to a different region in case of a regional disaster. Option A is incorrect because the geoBackupPolicy state being Enabled simply enables the geo-backup policy, but it does not ensure geo-redundancy if the storage redundancy is not set to Geo. Option B is incorrect because the geoBackupPolicy location indicates where the geo-backup is stored, but the key setting for cross-region restore is the storage redundancy, not the location. Option D is incorrect because weeklyRetention defines how long weekly backups are retained, which does not affect the ability to restore to a different region.

Answer

The geoBackupPolicy state is Enabled.

Answer

The geoBackupPolicy location is set to eastus2.

Answer

The weeklyRetention is set to P4W.

Question 4

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup policy. The database is used for a reporting workload that is updated daily. The compliance team requires that point-in-time restore (PITR) be available for the past 30 days. What action should you take?

Accepted Answer

Change the retentionDays property to 30.. The retentionDays property in the ARM template controls the point-in-time restore (PITR) retention period. Currently set to 14 days, it must be changed to 30 days to meet the compliance requirement. Option B is incorrect because diffBackupIntervalInHours specifies the interval for differential backups, not retention. Option C is incorrect because 14 days does not satisfy the 30-day requirement. Option D is incorrect because long-term retention (LTR) is for archival purposes beyond PITR and does not replace the PITR retention setting.

Answer

Change the diffBackupIntervalInHours to 24.

Answer

No action is needed; the current policy meets the requirement.

Answer

Add a long-term retention policy with a weekly retention of 30 days.

Question 5

Match each Azure SQL Database migration tool to its description.

Accepted Answer

Azure Database Migration Service: A fully managed service designed for online migrations to Azure SQL Database with minimal downtime.. Correct matches: A - Azure Database Migration Service (online/offline migration), B - Data Migration Assistant (assessment), C - SSMA (cross-platform migration), D - Azure Migrate (central hub). Common confusion: mixing up the migration service with offline-only tools or ignoring the assessment step.

Answer

Azure Database Migration Service: A tool that only performs offline migrations and requires significant downtime.

Answer

Data Migration Assistant: A service that automatically migrates databases with no assessment step.

Question 6

Refer to the exhibit. An Azure SQL Database administrator sees the error in the SQL Server error log. The database is in the General Purpose tier with 100 GB of storage. Which action should the DBA take to resolve the issue?

Accepted Answer

Increase the max size of the database. The error indicates the database has reached its maximum size limit of 100 GB. In Azure SQL Database General Purpose tier, the max size is set at the database level, not the log file level. Increasing the max size of the database (Option D) directly resolves the 'could not allocate space' error by allowing more data to be stored, without requiring a tier change or manual log file manipulation.

Answer

Shrink the database

Answer

Scale up to a higher service objective (e.g., from GP to BC)

Answer

Increase the log file size

Question 7

Which THREE components are required to configure an auto-failover group for Azure SQL Database? (Choose three.)

Accepted Answer

A failover group name. The correct components required to configure an auto-failover group for Azure SQL Database are a failover group name (which acts as the logical container), a primary logical server, and a secondary logical server in a different region. Active geo-replication (Option A) is not required because the auto-failover group itself manages the replication process. An availability group listener (Option E) is not needed; the failover group automatically provides a read/write listener endpoint. Therefore, the correct choices are B, C, and D.

Answer

Active geo-replication

Answer

An availability group listener

Question 8

Your company uses Azure SQL Database with Active Geo-Replication for disaster recovery. During a routine failover drill, you observe that after failover to the secondary region, the application experiences significantly higher latency for write operations. The secondary database is in a different Azure region and has the same service objective. What is the most likely cause of the increased write latency?

Accepted Answer

The secondary database does not have a local read-scale replica configured.. After failover to the secondary region, the new primary (formerly the secondary) is in a different Azure region. The secondary database did not have a local read-scale replica configured before failover, so the new primary lacks a read-scale replica to offload read traffic. This can cause resource contention and increased latency for write operations, as the database handles both reads and writes without dedicated replicas. Option A is incorrect because geo-replication does not inherently add latency to all writes—only replicating writes asynchronously may cause slight delay, but not significant latency. Option B is incorrect because the service objective is stated to be the same. Option C is incorrect because after failover, the secondary becomes writable and accepts write traffic.

Answer

Geo-replication introduces additional latency for all write operations.

Answer

The secondary database has a lower service objective than the primary.

Answer

The secondary database is not configured to accept write traffic.

Question 9

You are planning to deploy Azure SQL Database for a new application. The application requires high availability and the ability to scale out read workloads. You need to choose the appropriate deployment option and feature. Which TWO options should you select? (Choose two.)

Accepted Answer

Configure a failover group with a readable secondary.. A failover group with a readable secondary provides both high availability and read-scale capabilities. The readable secondary allows read-only workloads to be offloaded, while automatic failover ensures business continuity during an outage.

Answer

Use an elastic pool to group databases for resource sharing.

Answer

Enable zone redundancy for the database.

Answer

Deploy a single Azure SQL Database with locally redundant backup storage.

Question 10

Your company uses Azure SQL Managed Instance and wants to automate the creation of new databases for each development sprint. Each database must be a copy of a template database with specific schema and data. You need to recommend an automation solution that minimizes manual steps and integrates with your CI/CD pipeline. What should you use?

Accepted Answer

Create an Elastic Database Job that runs CREATE DATABASE AS COPY OF.... For Azure SQL Managed Instance, automating database creation with CI/CD integration can be achieved using Elastic Database Jobs (now Elastic Jobs). These jobs can run T-SQL commands such as 'CREATE DATABASE AS COPY OF' to create copies of a template database. Elastic Jobs can be triggered programmatically via REST API or Azure DevOps tasks, enabling seamless integration into a CI/CD pipeline. Option A (Logic App) does not support database copy operations. Option B (portal feature) requires manual steps. Option D uses PowerShell cmdlets ('az sql db copy' or 'Restore-AzSqlDatabase') that are specific to Azure SQL Database, not Managed Instance; the correct cmdlets for Managed Instance would be 'Restore-AzSqlInstanceDatabase' or T-SQL commands. Therefore, option C is the most appropriate recommendation.

Answer

Deploy an Azure Logic App with a SQL connector that triggers on a schedule.

Answer

Use the Azure portal's 'Create database from backup' feature.

Answer

Use an Azure DevOps pipeline with a PowerShell task that runs the 'az sql db copy' command or the Restore-AzSqlDatabase cmdlet.