DP-300 Practice Test 3 — 10 Questions

Question 1

Which TWO of the following are valid disaster recovery options for Azure SQL Database that provide a secondary in a different Azure region?

Accepted Answer

Active geo-replication. Options A and B are correct. Active geo-replication and Failover groups are both valid disaster recovery options for Azure SQL Database that provide a secondary database in a different Azure region. Options C, D, and E are incorrect: Geo-restore (C) restores from backups and does not maintain a continuous secondary; Zone-redundant configuration (D) provides high availability within a single region, not disaster recovery; Auto-failover groups (E) is a feature that enables automatic failover for failover groups, but it is not a separate disaster recovery option—it is part of failover groups.

Answer

Geo-restore

Answer

Zone-redundant configuration

Answer

Auto-failover groups

Question 2

Refer to the exhibit. You apply this JSON configuration to an Azure SQL Database server. Which actions will be audited?

Accepted Answer

Only successful and failed database authentication attempts. The configuration only includes SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP. Option A is wrong because it includes actions not in the config. Option B is wrong because it includes data modification actions. Option D is wrong because it includes all actions.

Answer

All successful and failed login attempts and all schema changes

Answer

All database-level actions

Answer

All successful and failed logins and all data modification statements

Question 3

You are deploying an Azure SQL Database for a development environment. The database will be used for testing and will be deleted after three months. You want to minimize costs while ensuring the database can be restored to any point within the retention period. Which backup retention policy should you configure?

Accepted Answer

Configure short-term backup retention to 7 days.. The scenario specifies a development database that will be deleted after three months, with the requirement to restore to any point within the retention period. The default short-term backup retention for Azure SQL Database is 7 days, which provides point-in-time restore (PITR) capability within that window. Since the database is temporary and cost minimization is key, 7 days meets the requirement without incurring extra storage costs for longer retention or geo-redundancy.

Answer

Configure geo-redundant backup storage.

Answer

Configure short-term backup retention to 14 days.

Answer

Configure long-term backup retention (LTR) for 1 year.

Question 4

Which THREE factors should you consider when choosing between Azure SQL Database single database and Azure SQL Managed Instance for a new application?

Accepted Answer

Need for cross-database queries and transactions.. Azure SQL Database single database does not support cross-database queries and transactions, whereas Azure SQL Managed Instance provides full SQL Server instance-level features, including cross-database queries and transactions via linked servers or within the same instance. This makes Managed Instance the appropriate choice when the application requires querying or updating multiple databases in a single transaction.

Answer

Requirement for elastic pool scalability.

Answer

Need for automated backups with point-in-time restore.

Question 5

Your company uses Azure SQL Database with the Hyperscale service tier. You notice that the database is experiencing high I/O latency during peak hours. After analyzing the query performance, you determine that the primary bottleneck is due to log write throughput. You need to reduce log write latency without changing the service tier. What should you do?

Accepted Answer

Scale up to a higher Hyperscale service objective.. Scaling up to a higher Hyperscale service objective increases the log write throughput by provisioning more log I/O capacity and faster storage. Since the bottleneck is specifically log write latency, and you cannot change the service tier, increasing the service objective within Hyperscale directly addresses the issue by allocating more resources to the log write path.

Answer

Configure Query Store hints to force parameterization.

Answer

Enable read scale-out to offload read queries.

Answer

Increase the MAXDOP setting for the database.

Question 6

You are configuring performance optimization for an Azure SQL Database that uses the Hyperscale service tier. The database has heavy read-write workloads. Which THREE actions should you take to optimize performance?

Accepted Answer

Create nonclustered columnstore indexes on large fact tables.. Options A, C, and D are correct. Hyperscale benefits from nonclustered indexes on read-intensive tables, accelerated database recovery reduces log IO, and read-scale replicas distribute read load. Option B is wrong because Hyperscale does not support resource governor. Option E is wrong because increasing service tier is not an optimization action.

Answer

Configure resource governor to limit CPU usage.

Answer

Increase the service tier to Business Critical.

Question 7

Refer to the exhibit. An administrator is trying to perform a point-in-time restore for a managed instance database. The output shows the state of the instance and databases. What is the most likely reason db1 shows 'Restoring' status?

Accepted Answer

The database is being restored from a geo-redundant backup.. The 'Restoring' status on db1 indicates that a restore operation is currently in progress on that database. The geo-backup list shows an available backup, which might be used for a geo-restore or point-in-time restore that is ongoing.

Answer

The database is being upgraded to a new service tier.

Answer

The database is corrupted and Azure is automatically repairing it.

Answer

The database is in the process of replication to a secondary region.

Question 8

You need to migrate an on-premises SQL Server database to Azure SQL Database. The database uses SQL Server Integration Services (SSIS) packages for ETL. You want to continue using SSIS in the cloud with minimal changes. Which Azure service should you use?

Accepted Answer

Use Azure Data Factory with Azure-SSIS Integration Runtime. Azure Data Factory with Azure-SSIS Integration Runtime is the correct choice because it provides a fully managed service that lifts and shifts existing SSIS packages to the cloud with minimal changes. The Azure-SSIS IR runs a dedicated cluster of Azure VMs that host the SSIS engine, allowing you to deploy, execute, and manage your existing SSIS packages directly in Azure without rewriting code.

Answer

Deploy SSIS on Azure SQL Database

Answer

Migrate SSIS packages to Azure Databricks

Answer

Use Azure SQL Managed Instance to run SSIS

Question 9

Your organization needs to comply with a regulation that requires data to be encrypted at rest using a customer-managed key stored in Azure Key Vault. You have an Azure SQL Database. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) and use a customer-managed key in Azure Key Vault as the TDE protector.. Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault meets the requirement for data at rest encryption using a customer-controlled key. TDE encrypts the database files (data and log files) at rest, and by using a customer-managed key as the TDE protector, you retain control over key rotation and revocation, satisfying regulatory compliance.

Answer

Use Azure Policy to enforce encryption at rest.

Answer

Enable Always Encrypted and store the column master key in Azure Key Vault.

Answer

Configure Azure Storage Service Encryption for the database files.

Question 10

Refer to the exhibit. You are managing db1, which is currently in Standard tier. What is the maximum size you can set for db1 without changing the service objective?

Accepted Answer

268,435,456,000 bytes (250 GB). The first command shows that Standard S2 supports a max size of 268435456000 bytes (250 GB). The second command shows the current max size is 107374182400 bytes (100 GB). Since db1 is already on S2, you can increase the max size up to 250 GB within the same service objective.

Answer

107,374,182,400 bytes (100 GB)

Answer

1,073,741,824,000 bytes (1 TB)

Answer

536,870,912,000 bytes (500 GB)