Sample questions
Microsoft Azure Database Administrator Associate DP-300 practice questions
A DBA needs to create a new Azure SQL Database and wants to ensure that the database automatically fails over to a secondary region without manual intervention. The recovery point objective (RPO) is 5 seconds. What should the DBA configure?
Trap 1: Standard geo-replication
Standard geo-replication is asynchronous and may exceed 5-second RPO.
Trap 2: Local redundancy with automatic failover
Not a valid Azure feature.
Trap 3: Read-scale out
Read-scale out is for read-only replicas, not failover.
- A
Active geo-replication with failover group
Failover groups provide automatic failover and synchronous replication for RPO of 5 seconds.
- B
Standard geo-replication
Why wrong: Standard geo-replication is asynchronous and may exceed 5-second RPO.
- C
Local redundancy with automatic failover
Why wrong: Not a valid Azure feature.
- D
Read-scale out
Why wrong: Read-scale out is for read-only replicas, not failover.
A company uses Azure SQL Managed Instance for its line-of-business applications. They need to ensure that backups are retained for 35 days for compliance purposes. Which configuration should the DBA set?
Trap 1: Create a backup policy in Azure Backup Vault
Azure Backup Vault is not used for SQL Managed Instance backups.
Trap 2: Enable geo-redundant storage (GRS)
GRS is about storage redundancy, not retention.
Trap 3: Configure long-term retention (LTR) policy
LTR is for longer retention beyond 35 days.
- A
Create a backup policy in Azure Backup Vault
Why wrong: Azure Backup Vault is not used for SQL Managed Instance backups.
- B
Enable geo-redundant storage (GRS)
Why wrong: GRS is about storage redundancy, not retention.
- C
Set the backup retention period to 35 days in the backup settings
The backup retention can be set up to 35 days.
- D
Configure long-term retention (LTR) policy
Why wrong: LTR is for longer retention beyond 35 days.
Refer to the exhibit. A DBA is creating an Azure SQL Managed Instance using the ARM template snippet shown. The DBA needs to ensure the instance can survive a regional outage without data loss. What change should be made to the template?
Exhibit
Refer to the exhibit.
Exhibit:
```
{
"properties": {
"collation": "SQL_Latin1_General_CP1_CI_AS",
"maxSizeBytes": 214748364800,
"sku": {
"name": "GP_Gen5_4",
"tier": "GeneralPurpose",
"capacity": 4
},
"storageAccountType": "GRS",
"zoneRedundant": false
},
"location": "eastus"
}
```Trap 1: Set collation to SQL_Latin1_General_CP1_CS_AS
Collation change does not affect availability.
Trap 2: Set zoneRedundant to true
Zone redundancy protects against zonal failures, not regional outages.
Trap 3: Change storageAccountType to RA-GRS
Storage account type does not affect instance availability.
- A
Set collation to SQL_Latin1_General_CP1_CS_AS
Why wrong: Collation change does not affect availability.
- B
Set zoneRedundant to true
Why wrong: Zone redundancy protects against zonal failures, not regional outages.
- C
Set zoneRedundant to true and configure an auto-failover group with a secondary region
Failover group with a secondary region provides regional disaster recovery.
- D
Change storageAccountType to RA-GRS
Why wrong: Storage account type does not affect instance availability.
A company is planning to migrate their on-premises SQL Server databases to Azure SQL Managed Instance. They have a database that uses SQL Server Agent jobs with proxies and also uses cross-database queries extensively. What is the main consideration for this migration?
Trap 1: Migrate to SQL Server on Azure Virtual Machines for full control.
While it supports all features, it requires more management overhead and is not the only option.
Trap 2: Migrate to Azure SQL Database elastic query to handle…
Elastic query is for querying across databases in different servers, not for full cross-database query support within an instance.
Trap 3: Migrate to Azure SQL Database instead to reduce costs.
Azure SQL Database does not support SQL Agent proxies, so it would not meet requirements.
- A
Migrate to Azure SQL Managed Instance as it supports SQL Agent and cross-database queries within the same instance.
Azure SQL Managed Instance supports both SQL Agent with proxies and cross-database queries.
- B
Migrate to SQL Server on Azure Virtual Machines for full control.
Why wrong: While it supports all features, it requires more management overhead and is not the only option.
- C
Migrate to Azure SQL Database elastic query to handle cross-database queries.
Why wrong: Elastic query is for querying across databases in different servers, not for full cross-database query support within an instance.
- D
Migrate to Azure SQL Database instead to reduce costs.
Why wrong: Azure SQL Database does not support SQL Agent proxies, so it would not meet requirements.
A company has an Azure SQL Database with a large table that is frequently updated. They notice performance degradation due to index fragmentation. Which maintenance strategy should you recommend to minimize impact on availability and performance?
Trap 1: Schedule an offline index rebuild during off-peak hours.
Offline rebuild locks the table, causing downtime.
Trap 2: Disable and rebuild indexes manually during maintenance window.
Disabling indexes degrades performance and is not a standard maintenance practice.
Trap 3: Use online index reorganize with low priority locks.
Reorganize is less effective for high fragmentation and may not fully resolve it.
- A
Schedule an offline index rebuild during off-peak hours.
Why wrong: Offline rebuild locks the table, causing downtime.
- B
Use online index rebuild with resumable operation.
Online rebuild allows concurrent access and resumable in case of failure, minimizing impact.
- C
Disable and rebuild indexes manually during maintenance window.
Why wrong: Disabling indexes degrades performance and is not a standard maintenance practice.
- D
Use online index reorganize with low priority locks.
Why wrong: Reorganize is less effective for high fragmentation and may not fully resolve it.
Which THREE of the following are valid options for migrating an on-premises SQL Server database to Azure SQL Database?
Trap 1: Use SQL Server Integration Services (SSIS) to migrate schema and…
SSIS is for ETL, not direct database migration.
Trap 2: Use the Azure portal's 'Import database' wizard directly from…
The Azure portal can import a BACPAC but not directly from on-premises without a file.
- A
Use SQL Server Integration Services (SSIS) to migrate schema and data.
Why wrong: SSIS is for ETL, not direct database migration.
- B
Use the Data Migration Assistant (DMA) to assess and migrate.
DMA is a supported tool for assessment and migration.
- C
Export to a BACPAC file and then import to Azure SQL Database.
BACPAC is a common method for exporting and importing.
- D
Use transactional replication to continuously replicate data to Azure SQL Database.
Transactional replication can be used for online migration with minimal downtime.
- E
Use the Azure portal's 'Import database' wizard directly from on-premises.
Why wrong: The Azure portal can import a BACPAC but not directly from on-premises without a file.
You are managing an Azure SQL Database that runs a critical line-of-business application. Users report that a specific query is running slower than usual. You identify that the query is performing a clustered index scan on a large table with over 10 million rows. The table has a clustered index on an identity column and a nonclustered index on a frequently filtered column. You need to minimize the query execution time without adding additional indexes. What should you do?
Trap 1: Increase the service tier of the Azure SQL Database to provide more…
Increasing resources might improve performance but does not address the root cause of the suboptimal plan; the scan may still occur.
Trap 2: Rebuild the clustered index to reduce fragmentation.
Rebuilding the clustered index can reduce fragmentation but does not guarantee a change from scan to seek if the query predicate is not selective.
Trap 3: Update the statistics on the nonclustered index only.
Updating only the nonclustered index statistics may not affect the decision to use the clustered index scan if the query predicate does not filter on that index's key columns.
- A
Increase the service tier of the Azure SQL Database to provide more resources.
Why wrong: Increasing resources might improve performance but does not address the root cause of the suboptimal plan; the scan may still occur.
- B
Update all statistics on the table.
Updating statistics helps the query optimizer generate a more accurate cardinality estimate, which may lead to an index seek instead of a scan.
- C
Rebuild the clustered index to reduce fragmentation.
Why wrong: Rebuilding the clustered index can reduce fragmentation but does not guarantee a change from scan to seek if the query predicate is not selective.
- D
Update the statistics on the nonclustered index only.
Why wrong: Updating only the nonclustered index statistics may not affect the decision to use the clustered index scan if the query predicate does not filter on that index's key columns.
A company has an Azure SQL Database that experiences periodic performance degradation. The database uses the General Purpose service tier. You need to identify the most common performance bottlenecks. You enable the Query Store and collect data for a week. Which Query Store view should you query to find queries that have the highest total resource consumption over time?
Trap 1: sys.query_store_plan
This view shows execution plan details, not aggregated resource consumption.
Trap 2: sys.dm_exec_query_stats
This DMV returns statistics from the plan cache, which may not have historical data.
Trap 3: sys.dm_db_resource_stats
This DMV shows CPU, IO, and memory consumption at the database level, not per query.
- A
sys.query_store_query_stats
This view provides aggregated runtime statistics like total CPU, IO, and duration for each query.
- B
sys.query_store_plan
Why wrong: This view shows execution plan details, not aggregated resource consumption.
- C
sys.dm_exec_query_stats
Why wrong: This DMV returns statistics from the plan cache, which may not have historical data.
- D
sys.dm_db_resource_stats
Why wrong: This DMV shows CPU, IO, and memory consumption at the database level, not per query.
You are monitoring an Azure SQL Database using the Automatic Tuning feature. The database has a workload that is read-intensive. You enable the CREATE INDEX and DROP INDEX options. After a week, you observe that the database has created several new indexes automatically. However, you notice that one of the new indexes is causing increased write latency for an application that performs frequent updates. What should you do to resolve the issue without losing the benefits of automatic tuning for other indexes?
Trap 1: Use the Azure portal to revert all automatic tuning recommendations…
This would remove beneficial indexes as well.
Trap 2: Manually create the missing indexes that were dropped by automatic…
This does not address the problematic index.
Trap 3: Disable automatic tuning for the entire database.
This would stop all automatic tuning, not just the problematic index.
- A
Use the Azure portal to revert all automatic tuning recommendations for the past week.
Why wrong: This would remove beneficial indexes as well.
- B
Manually create the missing indexes that were dropped by automatic tuning.
Why wrong: This does not address the problematic index.
- C
Disable automatic tuning for the entire database.
Why wrong: This would stop all automatic tuning, not just the problematic index.
- D
Manually drop the problematic index using a DROP INDEX command.
You can manually revert a specific index while leaving automatic tuning active for other indexes.
You are responsible for performance tuning of an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has several tables with millions of rows. Users report that a report query that joins four tables is slow. You examine the query execution plan and notice that the database engine is using an Index Spool (Lazy Spool) operator. Which TWO actions should you take to improve query performance? (Choose two.)
Trap 1: Disable parallelism for the query using the MAXDOP 1 hint.
Disabling parallelism might reduce the spool's cost but does not eliminate it and could slow down other operations.
Trap 2: Increase the DTU or vCore count of the database.
Adding resources may reduce the impact but does not address the root cause of the spool.
Trap 3: Rewrite the query using table hints to force a specific join order.
Forcing a join order may not eliminate the spool and could cause other performance issues.
- A
Disable parallelism for the query using the MAXDOP 1 hint.
Why wrong: Disabling parallelism might reduce the spool's cost but does not eliminate it and could slow down other operations.
- B
Increase the DTU or vCore count of the database.
Why wrong: Adding resources may reduce the impact but does not address the root cause of the spool.
- C
Create appropriate indexes on the columns used in joins and filters.
Proper indexing can allow the optimizer to use index seeks instead of scanning and spooling.
- D
Rewrite the query using table hints to force a specific join order.
Why wrong: Forcing a join order may not eliminate the spool and could cause other performance issues.
- E
Update statistics on all tables involved in the query.
Updated statistics improve cardinality estimates, helping the optimizer choose a better plan without spools.
A company runs a critical Azure SQL Database in the West US region. To meet a Recovery Point Objective (RPO) of 5 seconds and a Recovery Time Objective (RTO) of 30 seconds during a regional outage, which deployment option should be used?
Trap 1: Use an Auto-Failover Group with a secondary in West US 2
Auto-failover groups have RTO of about 1 hour.
Trap 2: Enable geo-zone-redundant backup storage and perform point-in-time…
Restore from backup takes hours, not seconds.
Trap 3: Configure Active Geo-Replication with a readable secondary in East…
Geo-replication has RTO of minutes, not seconds.
- A
Use an Auto-Failover Group with a secondary in West US 2
Why wrong: Auto-failover groups have RTO of about 1 hour.
- B
Deploy the database in the Business Critical tier with zone-redundant configuration in West US
Zone-redundant Business Critical provides fast failover within seconds and RPO of 5 seconds.
- C
Enable geo-zone-redundant backup storage and perform point-in-time restore
Why wrong: Restore from backup takes hours, not seconds.
- D
Configure Active Geo-Replication with a readable secondary in East US
Why wrong: Geo-replication has RTO of minutes, not seconds.
A company has an Azure SQL Managed Instance in the East US region. They need to implement disaster recovery with automatic failover to a paired region. The solution must minimize data loss to less than 5 seconds. Which feature should they use?
Trap 1: Active Geo-Replication with a secondary in East US 2
Active Geo-Replication is not supported for SQL Managed Instance.
Trap 2: Enable geo-redundant backup storage and restore to West US
Geo-restore has RPO of hours, not seconds.
Trap 3: Set up a Failover Group with manual failover to a secondary in West…
Manual failover is not automatic.
- A
Active Geo-Replication with a secondary in East US 2
Why wrong: Active Geo-Replication is not supported for SQL Managed Instance.
- B
Enable geo-redundant backup storage and restore to West US
Why wrong: Geo-restore has RPO of hours, not seconds.
- C
Configure an Auto-Failover Group with a secondary instance in West US
Auto-failover groups for managed instance provide automatic failover and RPO of 5 seconds.
- D
Set up a Failover Group with manual failover to a secondary in West US
Why wrong: Manual failover is not automatic.
A company runs SQL Server 2019 on Azure Virtual Machines in an availability set. They need to achieve high availability for a critical database with automatic failover and no shared storage. The solution must minimize downtime during planned maintenance. What should they implement?
Trap 1: Configure Log Shipping to a secondary VM
Log shipping requires manual failover, not automatic.
Trap 2: Deploy a Failover Cluster Instance using Azure Shared Disks
Requires shared storage, which is not desired.
Trap 3: Use Database Mirroring with automatic failover
Database mirroring is deprecated and not recommended.
- A
Configure Log Shipping to a secondary VM
Why wrong: Log shipping requires manual failover, not automatic.
- B
Deploy a Failover Cluster Instance using Azure Shared Disks
Why wrong: Requires shared storage, which is not desired.
- C
Create an Always On Availability Group with an availability group listener
Always On AG provides automatic failover and no shared storage.
- D
Use Database Mirroring with automatic failover
Why wrong: Database mirroring is deprecated and not recommended.
Which THREE factors should be considered when choosing between Azure SQL Database active geo-replication and auto-failover groups for disaster recovery?
Trap 1: Recovery Point Objective (RPO) of 1 second
Both have RPO of 5 seconds.
Trap 2: Support for SQL Server Authentication
Both support SQL Server Authentication.
- A
Automatic failover capability
Auto-failover groups provide automatic failover; active geo-replication does not.
- B
Recovery Point Objective (RPO) of 1 second
Why wrong: Both have RPO of 5 seconds.
- C
Number of readable secondary replicas required
Active geo-replication supports up to 4, auto-failover groups support only 1.
- D
Support for SQL Server Authentication
Why wrong: Both support SQL Server Authentication.
- E
Granular control over individual database failover
Active geo-replication allows per-database failover; auto-failover groups fail over all databases in the group.
Drag and drop the steps to configure an Azure SQL Managed Instance link for disaster recovery in the correct order.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Drag and drop the steps to configure geo-replication for an Azure SQL Database in the correct order.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Drag and drop the steps to restore an Azure SQL Database to a point in time in the correct order.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Drag and drop the steps to troubleshoot a high CPU usage issue in Azure SQL Database in the correct order.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Drag and drop the steps to configure a failover group for an Azure SQL Database in the correct order.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
Drag and drop the steps to configure transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key in Azure Key Vault in the correct order.
Drag steps to the numbered slots on the right, or tap a step then tap a slot.
A healthcare company is required to encrypt all patient data at rest and in transit. They are deploying Azure SQL Database. Which combination of features should they implement to meet this requirement?
Trap 1: Dynamic data masking and row-level security
Neither provides encryption.
Trap 2: Azure Active Directory authentication and firewall rules
Authentication and firewall do not encrypt data.
Trap 3: Always Encrypted and transparent data encryption (TDE)
Always Encrypted is for specific columns, not all data at rest.
- A
Transparent data encryption (TDE) and TLS 1.2
TDE encrypts data at rest, TLS encrypts data in transit.
- B
Dynamic data masking and row-level security
Why wrong: Neither provides encryption.
- C
Azure Active Directory authentication and firewall rules
Why wrong: Authentication and firewall do not encrypt data.
- D
Always Encrypted and transparent data encryption (TDE)
Why wrong: Always Encrypted is for specific columns, not all data at rest.
Which TWO of the following are valid methods to migrate an on-premises SQL Server database to Azure SQL Database?
Trap 1: Configure transactional replication to replicate changes to Azure…
Replication is not used for migration.
Trap 2: Use log shipping to continuously copy transaction logs
Log shipping is not supported.
Trap 3: Restore a native SQL Server backup to Azure SQL Database
Native backup restore is not supported on Azure SQL Database.
- A
Export the database as a BACPAC file and import into Azure SQL Database
BACPAC is a supported migration method.
- B
Configure transactional replication to replicate changes to Azure SQL Database
Why wrong: Replication is not used for migration.
- C
Use log shipping to continuously copy transaction logs
Why wrong: Log shipping is not supported.
- D
Use the Data Migration Assistant (DMA) to perform an online migration
DMA supports migration to Azure SQL Database.
- E
Restore a native SQL Server backup to Azure SQL Database
Why wrong: Native backup restore is not supported on Azure SQL Database.
You need to monitor Azure SQL Database performance over time and receive alerts when CPU usage exceeds 80%. Which Azure service should you use?
Trap 1: Automatic tuning
Automatic tuning adjusts performance, not monitoring.
Trap 2: Query Performance Insight
Query Performance Insight analyzes query performance.
Trap 3: SQL Assessment
SQL Assessment checks best practices, not real-time alerts.
- A
Automatic tuning
Why wrong: Automatic tuning adjusts performance, not monitoring.
- B
Query Performance Insight
Why wrong: Query Performance Insight analyzes query performance.
- C
Azure Monitor Alerts
Azure Monitor Alerts can trigger on CPU metric thresholds.
- D
SQL Assessment
Why wrong: SQL Assessment checks best practices, not real-time alerts.
You are designing an automated backup strategy for Azure SQL Database. Which TWO actions satisfy a requirement to maintain daily backups for 30 days and ensure recoverability if the Azure region becomes unavailable?
Trap 1: Deploy a read-scale replica in a different Azure region.
Read-scale replicas are for offloading read-only queries and do not provide backup retention.
Trap 2: Set up a long-term retention policy with a retention of 30 days.
LTR is intended for retaining backups beyond 35 days (e.g., monthly, yearly), not for daily backups within 30 days.
Trap 3: Configure active geo-replication to a secondary region.
Active geo-replication replicates the live database, not backups; it doesn't provide backup retention.
- A
Enable geo-redundant backup storage for the server.
Geo-redundant backup storage (RA-GRS) replicates backups to a paired region, ensuring regional recoverability.
- B
Deploy a read-scale replica in a different Azure region.
Why wrong: Read-scale replicas are for offloading read-only queries and do not provide backup retention.
- C
Set point-in-time restore retention to 30 days.
PITR retention of 30 days keeps daily backups for that period, meeting the requirement.
- D
Set up a long-term retention policy with a retention of 30 days.
Why wrong: LTR is intended for retaining backups beyond 35 days (e.g., monthly, yearly), not for daily backups within 30 days.
- E
Configure active geo-replication to a secondary region.
Why wrong: Active geo-replication replicates the live database, not backups; it doesn't provide backup retention.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.