DP-300 Practice Test 36 — 15 Questions

Question 1

You are deploying an Azure SQL Database with the above ARM template snippet. How many readable replicas will be available for read-only queries?

Accepted Answer

2. (2). The highAvailabilityReplicaCount property in the ARM template is set to 2, which creates two readable replicas in addition to the primary. With read scale-out enabled, both replicas are available for read-only queries. Option A (3) is incorrect because it counts the primary as a readable replica, but the primary is not used for read-only queries in read scale-out. Option C (1) is incorrect because it underestimates the number of replicas. Option D (4) is incorrect because the value in the template is 2, not 4.

Answer

3

Answer

1

Answer

4

Question 2

Your Azure SQL Database is configured with a failover group between two regions. The primary database experiences a catastrophic failure that prevents any connectivity. You need to initiate a failover to the secondary region. However, the failover group status shows 'Primary is down'. What should you do?

Accepted Answer

Run a forced failover accepting potential data loss.. A forced failover (also called an unplanned failover) is used when the primary database is completely unavailable, and you accept potential data loss. Option A is incorrect because a planned failover requires both the primary and secondary to be online to ensure zero data loss. Option B is incorrect because waiting for the primary to come back online is not an appropriate action when immediate failover is required due to catastrophic failure. Option C is incorrect because you cannot remove the primary database from a failover group when it is down; the failover group must be failed over as a whole.

Answer

Run a planned failover to ensure zero data loss.

Answer

Wait for the primary to come back online and then failover.

Answer

Remove the primary database from the failover group and then failover.

Question 3

You are reviewing a JSON configuration for an Azure SQL Database. The exhibit shows the database properties. Which statement about this database is correct?

Accepted Answer

The database is in General Purpose tier and supports zone redundancy.. The JSON configuration shows zoneRedundant set to true and the service tier is General Purpose. As of current Azure SQL Database documentation, zone redundancy is supported for the General Purpose tier (vCore purchasing model) in many regions. Therefore, the configuration is valid. Option D correctly states that the database is in General Purpose tier and supports zone redundancy. Option A is incorrect because highAvailabilityReplicaCount is 0, not 1. Option B is incorrect because zone redundancy is available for General Purpose. Option C is incorrect because backupStorageRedundancy is Geo (geo-redundant), not locally-redundant.

Answer

The database has one high-availability replica.

Answer

The configuration is invalid because General Purpose tier cannot be zone-redundant.

Answer

The database uses locally-redundant backup storage.

Question 4

Which THREE are valid methods to implement disaster recovery for Azure SQL Database? (Select three.)

Accepted Answer

Failover groups. Options A, C, and E are correct. Failover groups and active geo-replication are built-in disaster recovery features for Azure SQL Database that allow failover to a secondary region. Geo-restore (point-in-time restore to a different region) is also a valid DR method by restoring from geo-replicated backups. Option B (Always On availability groups) is a feature for SQL Server on VMs or on-premises, not for Azure SQL Database. Option D (long-term backup retention) is a backup retention capability, not a disaster recovery method.

Answer

Always On availability groups

Answer

Long-term backup retention

Question 5

You have an Azure SQL Database in the General Purpose tier. You need to implement a disaster recovery solution that provides an RPO of 5 seconds and an RTO of 1 hour. The solution must use Azure PaaS features only. What should you do?

Accepted Answer

Configure active geo-replication to a secondary server in a different region.. Active geo-replication provides asynchronous replication with a typical RPO of 5 seconds, and manual failover can be completed within the 1‑hour RTO window. Failover groups (Option A) are fully supported for Azure SQL Database (not limited to managed instances) and also meet the RPO/RTO targets, but they are designed to manage multiple databases as a group and introduce additional overhead for a single‑database scenario. Geo‑restore (Option C) has an RPO of up to one hour and a much longer RTO. Copy‑only backups (Option D) are not a disaster recovery solution.

Answer

Create a failover group between two servers in different regions.

Answer

Use geo-restore to recover the database from the latest geo-replicated backup.

Answer

Configure copy-only backups to a secondary region.

Question 6

Your company uses Azure SQL Database in a General Purpose service tier. You need to minimize downtime during a planned patching event. Which feature should you enable?

Accepted Answer

Zone-redundant configuration. Zone-redundant configuration for Azure SQL Database provides high availability by replicating across zones, minimizing downtime during planned patching events. Option A is incorrect because auto-failover groups are primarily for disaster recovery across regions. Option B is incorrect because read scale-out only offloads read workloads to a readable secondary replica, which does not reduce downtime during patching of the primary. Option C is incorrect because active geo-replication is for disaster recovery, not for reducing downtime during patching within a region.

Answer

Auto-failover group

Answer

Read scale-out

Answer

Active geo-replication

Question 7

You need to design a high availability solution for an Azure SQL Database that supports an online transaction processing (OLTP) application. The solution must provide automatic failover within seconds in case of a node failure and guarantee zero data loss. Which deployment option should you choose?

Accepted Answer

Business Critical tier with zone redundancy. (Business Critical tier with zone redundancy) is correct because the Business Critical tier uses synchronous replication and a quorum-based commit to replicas, ensuring zero data loss on failover and failover within seconds. Option A (Basic tier) does not support zone redundancy or automatic failover with zero data loss. Option B (General Purpose tier) uses asynchronous replication, which may result in data loss during failover. Option C (Hyperscale tier) uses a log-based service with a buffer, which can lead to data loss on failover.

Answer

Basic tier with zone redundancy

Answer

General Purpose tier with zone redundancy

Answer

Hyperscale tier with zone redundancy

Question 8

Your company has a compliance requirement to keep database backups for 10 years. You are using Azure SQL Database. Which backup retention feature should you use?

Accepted Answer

Long-term retention (LTR) policy. Long-term retention (LTR) policy is the correct feature for keeping database backups for 10 years, as it supports retention of full backups for up to 10 years. Point-in-time restore (PITR) is limited to a maximum of 35 days and cannot meet the 10-year requirement. Geo-redundant storage (GRS) provides geographic redundancy for backups but does not extend the retention period. Automated backups are retained for a maximum of 35 days by default and cannot be configured for 10-year retention.

Answer

Geo-redundant storage (GRS)

Answer

Automated backups

Answer

Point-in-time restore (PITR)

Question 9

You are responsible for a SQL Server 2019 instance on an Azure VM. The VM is part of a failover cluster instance (FCI) using Azure shared disks. During a recent failover test, the cluster took 15 minutes to bring the database online. You need to reduce the failover time to under 5 minutes. What should you do?

Accepted Answer

Remove unnecessary databases from the FCI and distribute them to other instances.. Removing unnecessary databases from the FCI reduces the number of databases that need to be recovered during failover, thereby decreasing the overall failover time. Option A is correct. Option B is incorrect because upgrading the VM does not directly reduce the number of databases to recover. Option C is incorrect because increasing disk size does not improve failover time. Option D is incorrect because instant file initialization helps with file growth, not failover recovery.

Answer

Upgrade the VM to a larger size with more CPU and memory.

Answer

Increase the size of the Azure shared disks.

Answer

Enable instant file initialization on all SQL Server instances.

Question 10

You are a database administrator for a gaming company that uses Azure SQL Database with multiple databases in a single logical server. The databases experience varying load patterns. You need to automate the process of scaling up or down the DTU/ vCore purchasing model based on performance metrics. Specifically, if average DTU consumption exceeds 80% for 10 minutes, you want to scale up the database tier. If consumption drops below 20% for 30 minutes, you want to scale down. The automation must use Azure native services and should not require custom scripting. What should you do?

Accepted Answer

Set up an Azure Logic App with a trigger on Azure Monitor metrics (e.g., when average DTU > 80% for 10 minutes) and an action to update the database tier via Azure Resource Manager connector.. Azure Logic Apps natively integrate with Azure Monitor metrics (e.g., average DTU consumption) and can trigger an action to update the database tier via the Azure Resource Manager connector. This provides a no-code / low-code solution that meets the requirement of not requiring custom scripting. Option A is incorrect because Azure Automation runbooks require PowerShell scripting. Option C is incorrect because Elastic Database Jobs cannot directly change service tiers based on metrics; they are for executing T-SQL scripts. Option D is incorrect because Azure Functions require custom code.

Answer

Create an Azure Automation runbook with PowerShell that queries performance metrics and scales the database accordingly.

Answer

Use Elastic Database Jobs to run T-SQL that alters the database service tier based on scheduled times.

Answer

Create an Azure Function app that uses the SQL Server metrics API to scale the database.

Question 11

You are a database administrator for a global e-commerce company. The company uses Azure SQL Database for its product catalog, which is a mission-critical OLTP workload. The database is currently deployed in the West US region using the Business Critical service tier with zone redundancy enabled. The database size is 200 GB and grows at 10 GB per month. The company has a disaster recovery requirement: in the event of a regional outage, the database must be failed over to a secondary region with an RPO of less than 5 seconds and an RTO of less than 1 minute. Additionally, the secondary database must be readable to support read-heavy reporting workloads. The solution must minimize additional compute costs. You need to recommend a configuration. Which option should you choose?

Accepted Answer

Configure active geo-replication to a secondary database in a paired region using Business Critical tier with a readable secondary.. Active geo-replication to a secondary database in a paired region using Business Critical tier with a readable secondary meets all requirements: it provides cross-region DR with an RPO of less than 5 seconds (synchronous replication within the primary region, asynchronous to secondary), RTO of less than 1 minute (failover is fast), and the secondary is readable for reporting. Zone redundancy within the primary region does not provide cross-region DR, so options B and C are incorrect. Hyperscale tier (option D) is more expensive and does not inherently provide cross-region DR with the required RPO; named replicas add cost.

Answer

Create a failover group within the same region using Business Critical tier with a readable secondary.

Answer

Add a second zone-redundant replica in the same region and configure a failover group.

Answer

Upgrade to Hyperscale tier with zone redundancy and configure a named replica in a secondary region.

Question 12

You are a database administrator for a large e-commerce company. The company uses Azure SQL Database in a Business Critical tier for its transactional systems. The database is part of an elastic pool with multiple databases. You need to automate the process of purging old data from a specific table (OrderHistory) that grows by 5 GB per day. The data must be retained for 90 days. After 90 days, the data should be moved to an Azure Blob Storage archive for long-term storage. The automation must run daily at midnight and must minimize impact on the transactional workload. Additionally, the solution should be cost-effective and require minimal manual intervention. What should you do?

Accepted Answer

Use Azure Data Factory with a scheduled trigger to copy data older than 90 days to Blob Storage and then delete it from the table.. Azure Data Factory (ADF) with a scheduled trigger can copy data older than 90 days to Azure Blob Storage and then delete it from the table. This approach minimizes impact on the transactional workload because ADF runs outside the database, is cost-effective for scheduled workloads, and requires minimal manual intervention. Option A is not available in Azure SQL Database (SQL Agent is only for on-premises or Managed Instance). Option B requires a Hybrid Runbook Worker if the database is not publicly accessible, adding complexity. Option C is incorrect because PolyBase in Azure SQL Database does not support writing to Blob Storage; it only reads external data.

Answer

Create a SQL Agent job on the database to run a cleanup script at midnight.

Answer

Use Azure Automation with a PowerShell runbook that connects to the database, deletes old data, and uploads it to Blob Storage using AzCopy.

Answer

Create a stored procedure that deletes data older than 90 days and exports it to Blob Storage using PolyBase. Schedule the procedure using Elastic Database Jobs.

Question 13

You are the database administrator for a global e-commerce company that uses Azure SQL Database for its product catalog. The database is currently deployed in the East US region using the General Purpose service tier. The company is expanding to Europe and wants to improve disaster recovery posture. The new requirements are: RPO of 5 seconds, RTO of 1 hour, and the ability to serve read traffic from the secondary region during normal operations. The budget is limited, so you must minimize cost while meeting these requirements. The application connection strings can be updated to a listener endpoint. What should you recommend?

Accepted Answer

Upgrade the primary to Business Critical, configure a failover group with a readable secondary in West Europe using Business Critical, and use the listener endpoint for application connections.. It meets all requirements: upgrading the primary to Business Critical provides synchronous replication with an RPO of 2-5 seconds, and configuring a failover group with a readable secondary in West Europe using Business Critical ensures the secondary can serve read traffic from Europe and failover can occur within 1 hour. The listener endpoint simplifies application connection management. Option A is incorrect because General Purpose tier has an RPO of 5-30 minutes, which cannot meet the 5-second requirement, and a same-region secondary does not serve European read traffic. Option C is incorrect because zone-redundancy only protects within a region, not across regions, and a General Purpose secondary would have higher RPO. Option D is incorrect because a readable secondary in the same region does not serve read traffic from Europe, and geo-restore has an RTO of hours, not 1 hour.

Answer

Keep the primary as General Purpose, create a readable secondary in the same region, and configure active geo-replication to a secondary in West Europe.

Answer

Deploy a zone-redundant Business Critical database in East US and use auto-failover groups to a secondary in West Europe with General Purpose.

Answer

Upgrade the primary database to Business Critical, create a readable secondary in the same region, and use geo-restore for DR.

Question 14

You are responsible for the disaster recovery of an Azure SQL Database that supports a customer-facing application. The database is 50 GB and uses the General Purpose service tier. The application requires an RPO of 1 hour and an RTO of 2 hours. The company has a limited budget and wants to minimize costs. You need to recommend a DR solution that meets the requirements without incurring additional compute costs for a secondary database. Which option should you choose?

Accepted Answer

Use point-in-time restore (PITR) with geo-redundant backup storage.. Point-in-time restore (PITR) using geo-redundant backup storage provides disaster recovery without requiring a secondary database, thus avoiding additional compute costs. Geo-redundant backups are automatically stored in a paired region, so in the event of a regional outage, you can restore the database to any point within the backup retention period (typically up to 35 days). For a 50 GB database, the RTO of 2 hours is achievable, and the RPO of 1 hour is satisfied because backups are taken every 5-10 minutes. Option A (active geo-replication) would incur compute costs for a readable secondary database. Option C (zone redundancy) provides high availability within a region, not cross-region DR. Option D (failover group with a secondary) also requires a secondary database costing compute resources.

Answer

Enable active geo-replication to a secondary database in a different region.

Answer

Enable zone redundancy on the primary database.

Answer

Configure a failover group with a secondary in a different region.

Question 15

You are preparing a disaster recovery runbook. You plan to use the PowerShell command shown in the exhibit to restore a database to a different region. What must be true for this command to succeed?

Accepted Answer

The source database must have geo-redundant backup storage configured.. The Restore-AzSqlDatabase cmdlet with the -FromGeoBackup parameter requires the source database to have geo-redundant backup storage (also known as geo-redundant storage) enabled. This allows restoration from geographically replicated backups. Option B is incorrect because point-in-time restore is not required for a geo-restore; geo-restore uses the geo-redundant backup independently. Option C is incorrect because the source database does not need to be online at the time of restoration; geo-backups are stored in Azure storage and are available even if the source database is offline. Option D is incorrect because the purpose of geo-restore is to restore to a different region; therefore, the target server can be in a different region than the source.

Answer

The source database must have point-in-time restore enabled.

Answer

The source database must be online at the time of restoration.

Answer

The target server must be in the same region as the source server.