DP-300 Practice Test 23 — 15 Questions

Question 1

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?

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

Accepted Answer

Set the backup retention period to 35 days in the backup settings. Azure SQL Managed Instance allows you to configure the backup retention period directly in the backup settings, with a maximum of 35 days for point-in-time restore (PITR). This meets the compliance requirement without needing additional services or policies.

Answer

Create a backup policy in Azure Backup Vault

Answer

Enable geo-redundant storage (GRS)

Answer

Configure long-term retention (LTR) policy

Question 2

Match each Azure SQL Database monitoring metric to its meaning.

Accepted Answer

DTU percentage: Measures the percentage of DTU limit used by the database.. These metrics are used to monitor resource usage and performance in Azure SQL Database.

Answer

DTU percentage: Measures the percentage of CPU limit used by the database.

Answer

CPU percentage: Measures the percentage of data I/O limit used by the database.

Answer

Data IO percentage: Measures the percentage of DTU limit used by the database.

Question 3

You have an Azure SQL Database configured with active geo-replication to a secondary region. A regional outage occurs in the primary region. You need to manually fail over to the secondary database with the least amount of data loss. Which PowerShell cmdlet should you run?

Accepted Answer

Set-AzSqlDatabaseSecondary -ResourceGroupName ... -ServerName ... -DatabaseName ... -PartnerResourceGroupName ... -Failover. Set-AzSqlDatabaseSecondary with -Failover initiates a planned failover that synchronizes data before failing over, minimizing data loss. Option A (Start-AzSqlDatabaseFailover) is for failing over a single database within the same region, not geo-failover. Option C (Invoke-AzSqlDatabaseFailover) does not exist. Option D (Switch-AzSqlDatabaseSecondary) is not a valid cmdlet.

Answer

Start-AzSqlDatabaseFailover -ResourceGroupName ... -ServerName ... -DatabaseName ...

Answer

Invoke-AzSqlDatabaseFailover -ResourceGroupName ... -ServerName ... -DatabaseName ... -Secondary

Answer

Switch-AzSqlDatabaseSecondary -ResourceGroupName ... -ServerName ... -DatabaseName ...

Question 4

You need to migrate an on-premises SQL Server 2019 database to Azure SQL Database with minimal downtime. The database is 500 GB and uses some features not supported in Azure SQL Database, such as FileTables. What is the best migration strategy?

Accepted Answer

Use Azure Database Migration Service with online mode after removing FileTables. Azure Database Migration Service (DMS) with online mode supports minimal-downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Database. However, FileTables are not supported in Azure SQL Database, so they must be removed from the source database before migration. This approach ensures near-zero downtime while addressing the unsupported feature.

Answer

Set up log shipping to an Azure SQL Database

Answer

Perform an offline migration using Azure Database Migration Service

Answer

Export a BACPAC file from the source and import it to Azure SQL Database

Question 5

You are reviewing an ARM template snippet for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' in resource group 'rg1'. The source database 'db1' is in the same server 'server1' and is currently active. What will be the result of deploying this template?

Accepted Answer

It will create a new empty database with the specified service objective.. The ARM template snippet specifies `createMode: Default` and a `requestedServiceObjectiveName`, but does not include a `sourceDatabaseId` or `restorePointInTime` property. With `createMode: Default`, Azure SQL Database creates a new empty database using the specified service objective, regardless of the presence of a `sourceDatabaseId` in the template (which is ignored when `createMode` is not set to `Copy`, `PointInTimeRestore`, or `Secondary`). Therefore, the deployment succeeds and creates a new empty database named 'db2' with the defined performance tier.

Answer

The deployment will fail because sourceDatabaseId is specified but not used.

Answer

It will create a copy of db1 in the same server.

Answer

It will perform a point-in-time restore of db1 to the current time.

Question 6

Refer to the exhibit. You are monitoring index fragmentation in an Azure SQL Database. You need to automate the rebuild of this index when fragmentation exceeds 50%. The rebuild must be online to minimize downtime. Which T-SQL statement should you include in your automated maintenance job?

Accepted Answer

ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON);. It uses the `ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON)` statement, which rebuilds all indexes on the table online, allowing concurrent user access and minimizing downtime. This meets the requirement to automate the rebuild when fragmentation exceeds 50%, as online rebuilds are supported in Azure SQL Database for this purpose.

Answer

ALTER INDEX Orders ON Orders REBUILD;

Answer

ALTER INDEX ALL ON Orders REORGANIZE;

Answer

ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = OFF);

Question 7

You are configuring Microsoft Defender for SQL for an Azure SQL Database. You want to receive email notifications when a suspicious activity is detected. What should you configure?

Accepted Answer

In the Microsoft Defender for SQL settings, enable 'Email notifications to admins and subscription owners'.. Microsoft Defender for SQL includes a dedicated 'Email notifications to admins and subscription owners' setting under its threat detection policy. When enabled, this sends email alerts to Azure subscription owners and administrators whenever Defender detects suspicious activities such as SQL injection, brute-force attacks, or anomalous access patterns. This is the direct, built-in mechanism for email-based alerting on threat detections.

Answer

Configure a vulnerability assessment recurring scan and email the report.

Answer

Create an Azure Monitor alert rule for the 'SQL database threat detection' metric.

Answer

Enable SQL auditing and stream logs to a Log Analytics workspace.

Question 8

Refer to the exhibit. A user reports being unable to connect to the database. What is the most likely cause?

Accepted Answer

The user is using an incorrect password.. The error logs show multiple login failures for user 'appuser' from IP 192.168.1.100, with reasons indicating password issues. The combination of error messages suggests the password is incorrect or expired.

Answer

The user account is locked out due to too many failed login attempts.

Answer

The user does not have permission to access the database.

Answer

The server firewall is blocking the IP address.

Question 9

You are responsible for cost optimization of a non-production Azure SQL Database that is used for development testing. The database is only active during business hours (9 AM to 5 PM) on weekdays. Which compute tier and configuration would minimize cost while ensuring the database is available during working hours?

Accepted Answer

Use the serverless compute tier with auto-pause enabled and a 1-hour auto-pause delay.. The serverless compute tier with auto-pause enabled and a 1-hour auto-pause delay is the most cost-effective choice for a non-production database used only during business hours. Serverless automatically pauses the database after 1 hour of inactivity (e.g., overnight and weekends), charging only for compute during active periods and storage at all times. This aligns perfectly with the 9 AM–5 PM weekday usage pattern, minimizing cost while ensuring the database is available when needed.

Answer

Use the Hyperscale tier with a minimum of 1 vCore and disable auto-pause.

Answer

Use the provisioned General Purpose tier with 2 vCores and disable auto-pause.

Answer

Use the provisioned Business Critical tier with 1 vCore and enable auto-pause.

Question 10

Refer to the exhibit. The SalesDB database is experiencing log space full errors. Based on the exhibit, what is the most likely reason?

Accepted Answer

The transaction log is not being truncated, possibly due to an active transaction or replication. The exhibit shows a transaction log with very low free space (0.01%) and a high log used percentage (99.99%), but the data file has ample free space. This indicates the transaction log is not being truncated, likely due to an active transaction preventing log reuse or a replication scenario that marks log records as needed. In Azure SQL Database, log space is managed automatically, but long-running transactions or replication can block log truncation, causing log space full errors even when storage is not full.

Answer

The database storage is almost full, preventing log growth

Answer

The log rate limit is being throttled due to high log IO percentage

Answer

The database should be scaled to BusinessCritical tier for faster log writes

Question 11

Your organization is migrating a legacy on-premises SQL Server database to Azure SQL Managed Instance. The database contains sensitive financial data. You need to implement column-level encryption so that even database administrators cannot view the plaintext data. The encryption keys must be stored in Azure Key Vault, and the application must be able to encrypt and decrypt data transparently. The application currently uses Entity Framework Core and runs on Azure App Service. You have the following requirements:
- Use a solution that provides the strongest security by ensuring the database never has access to the plaintext.
- Minimize changes to the application code.
- The application must be able to perform equality searches on encrypted columns.

What should you implement?

Accepted Answer

Use Always Encrypted with deterministic encryption and store column master key in Azure Key Vault.. Always Encrypted with deterministic encryption provides column-level encryption, stores the column master key in Azure Key Vault, and ensures the database never sees plaintext. The client-side driver in Entity Framework Core handles encryption transparently, and deterministic encryption allows equality searches. Option A is incorrect because randomized encryption does not support equality searches; secure enclaves could enable that but add complexity. Option B is incorrect because dynamic data masking does not encrypt data; it only hides it from unauthorized users at query time, and the database still has access to plaintext. Option C is incorrect because Transparent Data Encryption (TDE) encrypts the entire database at rest, not individual columns, and does not prevent the database from seeing plaintext during operations.

Answer

Use Always Encrypted with secure enclaves and randomized encryption.

Answer

Use dynamic data masking to hide the data from unauthorized users.

Answer

Use Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault.

Question 12

Which THREE are features of Azure SQL Database Hyperscale?

Accepted Answer

Storage up to 100 TB. Azure SQL Database Hyperscale supports storage up to 100 TB by using a distributed architecture where data is stored across multiple page servers, each managing a subset of pages. This allows the database to scale beyond the 4 TB limit of other service tiers, making it suitable for very large databases.

Answer

Zone redundant compute by default

Answer

Unlimited log rate

Question 13

Your company uses Azure SQL Managed Instance. You need to configure a server-level firewall rule to allow access from a specific Azure service, but you want to minimize the attack surface. What is the best practice?

Accepted Answer

Set the firewall rule to allow Azure services and resources to access this server.. Enabling the 'Allow Azure services and resources to access this server' firewall rule is the best practice for Azure SQL Managed Instance when you need to allow access from a specific Azure service while minimizing the attack surface. This setting uses a special firewall rule that permits connections from any Azure service (including those within the same region) without exposing the server to all public IP addresses, as it relies on Azure's internal network boundaries and does not open the server to the entire internet. It is more secure than allowing all IPs (0.0.0.0/0) because it restricts access to only Azure-originated traffic, and it is simpler than manually specifying IP ranges, which can change dynamically.

Answer

Set the firewall rule to allow all IP addresses (0.0.0.0/0).

Answer

Disable public endpoint and use private endpoint.

Answer

Create a firewall rule with the specific IP range of the Azure service.

Question 14

You are evaluating the configuration of an Azure SQL Database as shown in the exhibit. You need to ensure that the database remains available during a zonal failure without data loss. Which feature contributes to this requirement?

Accepted Answer

Zone redundancy enabled on Business Critical tier.. Zone redundancy on the Business Critical tier ensures that database replicas are placed in different availability zones within the same Azure region. During a zonal failure, the service automatically fails over to a synchronous replica in another zone, guaranteeing zero data loss because all transactions are synchronously committed across replicas. This directly meets the requirement of remaining available without data loss during a zonal outage.

Answer

General Purpose service tier.

Answer

Read scale-out.

Answer

Geo-redundant backup storage.

Question 15

You are a database administrator for a startup that uses Azure SQL Database to run an e-commerce application. The application uses a service principal (Microsoft Entra ID application) to authenticate to the database. You need to grant the service principal the ability to read data from all tables in the 'sales' schema. Which THREE actions should you take?

Accepted Answer

Create a database user for the service principal using the CREATE USER statement with the FROM EXTERNAL PROVIDER option. A service principal (Microsoft Entra ID application) must have a corresponding database user created using the CREATE USER statement with the FROM EXTERNAL PROVIDER clause. This maps the external identity to a database principal, enabling authentication and authorization within Azure SQL Database.

Answer

Grant CONTROL permission on the database to the user

Answer

Add the user to the 'sales' database role