DP-300 Practice Test 4 — 25 Questions

Question 1

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup retention policy. The database is in a business-critical environment that requires point-in-time restore (PITR) for the last 28 days. What is the issue with this configuration?

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

Accepted Answer

Long-term retention properties (weeklyRetention, monthlyRetention, yearlyRetention) are not valid in a backupShortTermRetentionPolicies resource.. The ARM template shows both short-term retention (PITR) and long-term retention (LTR) properties. However, the resource type is 'backupShortTermRetentionPolicies', which only supports short-term retention properties like retentionDays. Long-term retention properties (weeklyRetention, monthlyRetention, yearlyRetention) belong to a different resource type ('backupLongTermRetentionPolicies'). Including them in the same resource will cause a deployment error. Option B correctly identifies this. Option A is incorrect because the JSON syntax is valid (camelCase is used in ARM templates). Option C is incorrect because diffBackupIntervalInHours is not relevant to this issue. Option D is incorrect because 28 days is within the allowed range for PITR (up to 35 days).

Answer

The JSON syntax is invalid because property names use camelCase instead of PascalCase.

Answer

The diffBackupIntervalInHours should be 12 for a business-critical database.

Answer

The retentionDays value of 28 exceeds the maximum allowed for PITR.

Question 2

Refer to the exhibit. A Logic App is configured to pause an Azure SQL Database named AdventureWorks every Friday at 8 PM. However, the database is not pausing. What is the most likely reason?

Accepted Answer

The pause action is not supported for Azure SQL Database.. Azure SQL Database does not support pause/resume; only Azure SQL Data Warehouse (now Azure Synapse Analytics dedicated SQL pool) supports it. Therefore, the pause action is not supported for Azure SQL Database, making option D correct. Option A is incorrect because the connection authentication is not the issue here. Option B is incorrect because the recurrence trigger schedule is valid. Option C is incorrect because the API version is not the primary reason; the operation itself is unsupported.

Answer

The SQL connection is not authenticated.

Answer

The recurrence trigger uses a schedule that is not valid.

Answer

The API version specified is outdated.

Question 3

Which TWO statements about Azure SQL Database zone-redundant configuration are true? (Choose two.)

Accepted Answer

It can be enabled during database creation or by updating an existing database.. Options B and D are correct. Zone-redundant configuration can be enabled during database creation or by updating an existing database (B), and it provides resilience within a single Azure region across multiple availability zones (D). Option A is incorrect because zone-redundant configuration does not require a failover group; failover groups are used for geo-replication, not for zone redundancy. Option C is incorrect because zone-redundant configuration is available in both the General Purpose and Business Critical service tiers. Option E is incorrect because the cost impact varies by tier: General Purpose tier does not incur additional cost for zone redundancy, while Business Critical does; thus, the blanket statement that it always incurs additional cost is false.

Answer

It requires a failover group to be configured.

Answer

It is only available in the Business Critical service tier.

Answer

It incurs additional cost compared to non-zone-redundant configurations.

Question 4

Your company uses Azure SQL Database Hyperscale tier for a large data warehouse. You need to implement disaster recovery with the ability to perform point-in-time restore in the secondary region. What is the best approach?

Accepted Answer

Configure geo-replication to a secondary Hyperscale database in the secondary region.. Azure SQL Database Hyperscale tier supports geo-replication (currently in preview), which creates a readable secondary database in a different region. This secondary database also supports point-in-time restore within the secondary region, meeting the requirement for disaster recovery with point-in-time restore capability. Option B is incorrect because auto-failover groups are not supported for Hyperscale databases. Option C is incorrect because geo-restore of automated backups has a higher recovery point objective (RPO) compared to geo-replication. Option D is incorrect as failover groups are not supported for Hyperscale.

Answer

Configure auto-failover groups between regions.

Answer

Use geo-restore of automated backups.

Answer

Create a failover group with manual failover.

Question 5

Your company wants to ensure business continuity for an Azure SQL Database that is used by a critical application. The database must remain available in the event of a single availability zone failure within a region. Which configuration should you use?

Accepted Answer

Configure zone-redundant availability for the database. Azure SQL Database zone-redundant availability replicates the database across multiple availability zones within the same region, providing high availability in the event of a single zone failure. Option B (active geo-replication) replicates to a different region, which protects against regional outages but not specifically a zone failure. Option C (locally redundant storage) stores data within a single data center, offering no zone-level protection. Option D (read scale-out) provides read-only replicas but does not ensure write availability during a zone failure.

Answer

Use active geo-replication to a different region

Answer

Deploy the database with locally redundant storage

Answer

Configure read scale-out with a secondary replica

Question 6

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 7

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 8

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 9

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup retention policy. The database is in a business-critical environment that requires point-in-time restore (PITR) for the last 28 days. What is the issue with this configuration?

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

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 10

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 11

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 12

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 13

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 14

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 15

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.

Question 16

Refer to the exhibit. An Azure SQL Database is receiving Intelligent Insights degradation alerts. Which action should be taken first?

Accepted Answer

Implement automatic tuning recommendations. The database is hitting DTU limits (100% CPU and data IO). The alert recommends scaling up or optimizing queries. Since CPU and data IO are both high, query optimization is the better first step to avoid cost increase.

Answer

Increase the maximum storage size

Answer

Change the service tier to BusinessCritical

Answer

Scale up to Standard S3 (100 DTU)

Question 17

Refer to the exhibit. The ARM template snippet configures auditing for an Azure SQL Database. Based on the configuration, which events are audited?

Accepted Answer

Successful and failed database authentication attempts.. The auditActionsAndGroups include SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP, which audit successful and failed logins respectively. Option A is wrong because data modification statements (DML) are not included. Option C is wrong because data modifications are not included. Option D is wrong because only authentication events are listed.

Answer

All data modification statements (DML).

Answer

Only failed authentication attempts.

Answer

All database schema changes (DDL).

Question 18

You are configuring workload management for an Azure SQL Database using the JSON exhibit above for a classifier named 'MyWorkloadClassifier'. The classifier is intended to assign high importance to queries from user 'User1' in the 'SalesDB' database. However, after deployment, you notice that queries from 'User1' are not getting the expected resource guarantees. What is the most likely reason?

Accepted Answer

The 'memberName' in the context is not correctly formatted; it should be a single user or group name without a backslash.. The most likely reason is that the classifier's context is incorrectly formatted. In Azure SQL Database workload management, the 'memberName' property within the classifier context must be specified as a single user or group name without a backslash. If the JSON exhibit contains a backslash (e.g., 'SalesDB\User1' instead of 'User1'), the classifier fails to match the user. Option A is incorrect because 'importance' is a string ('high'), not an integer. Option C is incorrect because 'min_percentile_resource' is not the issue; resource guarantees depend on the workload group's allocation. Option D is incorrect because a classifier does not need to be associated with a workload group directly; it is defined at the database level.

Answer

The 'importance' property is set to 'high' but the classifier requires 'importance' to be an integer.

Answer

The 'min_percentile_resource' value is too low to guarantee resources.

Answer

The classifier is not associated with a workload group.

Question 19

Your team uses Azure SQL Database and wants to use Microsoft Entra ID authentication. You need to create a contained database user mapped to a Microsoft Entra ID application (service principal). Which T-SQL command should you use?

Accepted Answer

CREATE USER [app-name] FROM EXTERNAL PROVIDER;. `CREATE USER [app-name] FROM EXTERNAL PROVIDER` creates a contained database user mapped to a Microsoft Entra ID identity (user, group, or application) directly in the database, without requiring a server-level login. This is the required syntax for authenticating a service principal (application) in Azure SQL Database using Microsoft Entra ID authentication.

Answer

CREATE USER [app-name] FROM LOGIN [app-name];

Answer

CREATE LOGIN [app-name] FROM EXTERNAL PROVIDER;

Answer

CREATE USER [app-name] WITH PASSWORD = 'password';

Question 20

You have an Azure SQL Managed Instance configured with a failover group between the primary region (East US) and secondary region (West US). The secondary instance is used for read-only query workloads. During a planned failover test, you notice that after failover, the read-only queries on the secondary instance fail with error 4060 (cannot open database). What is the most likely cause?

Accepted Answer

The failover changed the secondary instance to primary, and read-only routing is not configured for the new secondary.. After failover, the secondary role changes to primary, and the database becomes writable. Read-only routing must be reconfigured or the connection string must specify ApplicationIntent=ReadOnly to reach the new secondary. Option A (secondary is not readable) is false because failover groups support readable secondary. Option B (firewall rules) would cause a different error. Option D (license type) does not affect read access.

Answer

The secondary instance is not in the same license type as the primary.

Answer

The firewall rules on the secondary instance do not allow the client IP.

Answer

The secondary instance is not configured to allow read-only access.