Microsoft Azure Database Administrator Associate DP-300 practice test

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.

Question 2mediummultiple choice

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.

Question 3mediummultiple choice

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.

Question 4mediummultiple choice

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.

Question 5hardmultiple choice

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.

Question 6mediummulti select

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.