DP-300 Practice Exam 58 — 20 Questions

Question 1

You are deploying an Azure SQL Database using PowerShell as shown in the exhibit. The database will be used by a development team that works intermittently. You need to ensure the database is cost-effective while being available on demand. What is the purpose of the AutoPauseDelayInMinutes parameter?

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

Accepted Answer

It controls the automatic pausing of the database after a period of inactivity to save costs.. The AutoPauseDelayInMinutes parameter is used with Azure SQL Database serverless compute tier. It specifies the number of minutes of inactivity (no CPU usage or active sessions) after which the database automatically pauses, stopping compute billing while storage remains billed. This makes the database cost-effective for intermittent development workloads because it eliminates compute costs during idle periods and automatically resumes on the first connection.

Answer

It configures the database to pause during a disaster recovery scenario.

Answer

It sets the maximum duration for which the database can be paused.

Answer

It determines how long the database takes to resume after a pause.

Question 2

Refer to the exhibit. You review the configuration of an Azure SQL Database. Which statement about this database is true?

Accepted Answer

The database can automatically increase storage.. The storage profile shows autogrow enabled, allowing the database to automatically increase storage when needed. Option B is wrong because geo-redundant backup storage is not enabled; it is configured as locally redundant. Option C is wrong because the high availability configuration is not zone-redundant; it uses locally redundant storage. Option D is wrong because the service tier shown is General Purpose, not Hyperscale.

Answer

Geo-redundant backup storage is enabled.

Answer

The database uses zone-redundant high availability.

Answer

The database is using the Hyperscale service tier.

Question 3

Which TWO features can help you monitor and troubleshoot a query performance issue in Azure SQL Database without making changes to the application code?

Accepted Answer

Enabling Automatic Tuning to force plan corrections.. Automatic Tuning in Azure SQL Database can automatically identify and force plan corrections for regressed query execution plans without requiring any application code changes. It uses the Query Store to detect plan regressions and applies the last known good plan, improving query performance transparently. Option E is correct because Query Store captures query execution statistics and plan history, allowing you to review regressed queries and manually force a better plan without modifying the application.

Answer

Using sp_setapprole to set application roles.

Answer

Implementing Always Encrypted for sensitive columns.

Answer

Using Azure Data Migration Assistant for performance assessment.

Question 4

Your company is deploying a new application that will use Azure SQL Database. You need to ensure that all connections to the database use Microsoft Entra ID authentication. Which step is required to enable this?

Accepted Answer

Configure an Entra ID administrator for the Azure SQL logical server.. To enforce Microsoft Entra ID authentication for all connections to Azure SQL Database, you must first configure an Entra ID administrator at the logical server level. This step establishes the server’s trust relationship with the Entra ID tenant, enabling token-based authentication using OAuth 2.0. Without this administrator, Entra ID authentication cannot be used, and only SQL authentication would be available.

Answer

Enable the Azure SQL Database firewall to allow only Entra ID IP addresses.

Answer

Create a contained database user mapped to an Entra ID identity.

Answer

Set the database to read-only mode.

Question 5

You have an Azure SQL Database with Query Store enabled. You notice that a critical stored procedure has regressed in performance. You need to force a previous, better-performing execution plan for that query. What should you do?

Accepted Answer

Execute sys.sp_query_store_force_plan with the query_id and plan_id.. `sys.sp_query_store_force_plan` is the dedicated system stored procedure in Azure SQL Database that forces the Query Store to use a specific execution plan for a given query. When a stored procedure's performance regresses, you identify the query_id and plan_id of the better-performing plan from Query Store views and execute this procedure to pin that plan, overriding the optimizer's choice.

Answer

Disable and re-enable Query Store to reset the plans.

Answer

Execute sys.sp_query_store_set_hints to add a query hint.

Answer

Use sys.dm_db_tuning_recommendations to apply a plan.

Question 6

Your company has an Azure SQL Database configured with Active Geo-Replication between two regions. The primary database is experiencing performance degradation due to a sudden increase in write-intensive workloads. You need to minimize downtime and ensure no data loss. What should you do?

Accepted Answer

Initiate a planned failover to the secondary region. A planned failover (Option D) is correct because Active Geo-Replication supports a graceful, synchronous failover that promotes the secondary database to become the new primary without data loss. This is achieved by first replicating all pending transactions to the secondary, ensuring zero data loss, and then switching roles with minimal downtime. In contrast, a forced failover (Option B) would cause data loss because it does not wait for pending transactions to be replicated.

Answer

Enable read scale-out on the primary database

Answer

Initiate a forced failover to the secondary region

Answer

Increase the service tier of the primary database

Question 7

You are monitoring an Azure SQL Database that hosts a financial application. You notice that the average DTU consumption is 20%, but occasionally spikes to 95% for 5-minute intervals. Users report slow response times during these spikes. You need to ensure consistent performance without over-provisioning resources. What should you do?

Accepted Answer

Identify and optimize the queries running during the spike periods, possibly rescheduling a heavy ETL job.. The issue is not a consistent resource shortage but periodic spikes caused by specific queries, likely from a heavy ETL job. By identifying and optimizing those queries or rescheduling the job, you can eliminate the spikes without permanently scaling up resources, which would waste cost and capacity. This aligns with the DP-300 focus on performance tuning and resource optimization rather than blind scaling.

Answer

Migrate the database to the Hyperscale service tier.

Answer

Scale the database to a higher service tier to absorb the spikes.

Answer

Enable Query Store and use the Regressed Queries feature to find slow queries.

Question 8

Your company uses Azure SQL Database with a server-level Microsoft Entra ID admin. You need to implement a solution where database-level roles are automatically assigned based on the user's group membership in Microsoft Entra ID. What should you use?

Accepted Answer

Create database users from Microsoft Entra ID groups and grant roles to those users.. Azure SQL Database supports creating database users mapped to Microsoft Entra ID (formerly Azure AD) groups. By creating a user for the Entra ID group and then granting database roles to that group user, all members of the group automatically inherit the assigned permissions. This directly satisfies the requirement for role assignment based on group membership without custom scripting or triggers.

Answer

Use Azure RBAC to assign roles to the Entra ID groups.

Answer

Configure a SQL Server Agent job to update database roles based on group membership.

Answer

Create a DDL trigger that assigns roles when users log in.

Question 9

Refer to the exhibit. You are reviewing a script that is executed as part of a data migration to Azure SQL Database. The script attempts to insert a row with a specific OrderID into an Orders table that has an identity column. The script runs successfully in a test environment but fails in production with an error. The production environment has the same schema. What is the most likely cause?

Accepted Answer

The user running the script does not have ALTER permission on the table. The script uses SET IDENTITY_INSERT ON to allow explicit insertion into an identity column. In Azure SQL Database, this requires ALTER permission on the table, not just INSERT. The test environment likely granted the user ALTER, but production did not, causing the failure despite identical schema.

Answer

The table in production does not have an identity column

Answer

A different session is already using SET IDENTITY_INSERT ON for the same table

Answer

The identity column in production has a different seed value

Question 10

Your organization uses Azure SQL Database and wants to implement a defense-in-depth strategy. You have already enabled Transparent Data Encryption (TDE) and firewall rules. You need to add an additional layer of security that protects against unauthorized access to the physical storage files (e.g., if someone gains access to the storage account). What should you enable?

Accepted Answer

Infrastructure encryption (double encryption) using platform-managed keys. Infrastructure encryption (double encryption) using platform-managed keys adds a second layer of encryption at the storage infrastructure level, below the TDE layer. This protects data at rest even if the physical storage files (e.g., Azure Storage blobs) are accessed directly, because the storage infrastructure itself is encrypted with a separate, platform-managed key. This fulfills the defense-in-depth requirement for protecting against unauthorized access to physical storage.

Answer

Dynamic Data Masking

Answer

Microsoft Defender for SQL

Answer

Always Encrypted with secure enclaves

Question 11

Your organization has a regulatory requirement to audit all data modifications in an Azure SQL Database. You enable Azure SQL Database auditing and configure it to send logs to a Log Analytics workspace. However, you notice that DELETE operations on a specific table are not being audited. What is the most likely cause?

Accepted Answer

The audit action group does not include 'DATABASE_OPERATION_GROUP'. Azure SQL Database auditing uses audit action groups to define which operations are logged. By default, the audit policy includes 'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP', 'FAILED_DATABASE_AUTHENTICATION_GROUP', and 'BATCH_COMPLETED_GROUP', but 'DATABASE_OPERATION_GROUP' is required to capture DDL and DML operations like DELETE. If this group is not explicitly added to the audit specification, DELETE operations on specific tables will not be recorded, even though general auditing is enabled.

Answer

The diagnostic settings for the database are misconfigured

Answer

Audit logs are being written to a storage account instead of Log Analytics

Answer

The table has been configured to ignore auditing for read operations

Question 12

You are the database administrator for a company that uses Azure SQL Managed Instance. You need to allow a specific application to connect to the database using a service principal. The application authenticates with Microsoft Entra ID. What should you configure?

Accepted Answer

Create a contained database user mapped to the Microsoft Entra service principal.. A contained database user mapped to a Microsoft Entra ID service principal allows the application to authenticate directly to the database using its Microsoft Entra identity, without requiring a SQL Server login. This is the correct approach because Azure SQL Managed Instance supports Microsoft Entra authentication for service principals, enabling token-based authentication from applications that authenticate with Microsoft Entra ID.

Answer

Enable Always Encrypted and configure column master key with the service principal.

Answer

Add a server-level firewall rule with the application's IP address.

Answer

Create a SQL authentication login and user for the application.

Question 13

Your company has an Azure SQL Managed Instance in the General Purpose tier. You need to configure a failover group for disaster recovery. The secondary managed instance must be in a different region and must also be used for read-only workloads. During a failover, you want to minimize data loss. Which configuration should you use?

Accepted Answer

Create a failover group and configure the secondary instance to allow read-only connections.. Failover groups for Azure SQL Managed Instance allow you to configure the secondary instance to allow read-only connections, enabling it to serve read-only workloads. This minimizes data loss by using automatic replication with a Recovery Point Objective (RPO) of less than 1 second. Option A (zone redundancy) provides high availability within a region, not disaster recovery across regions. Option C (log shipping) is not a built-in feature for managed instances and does not support automatic failover. Option D (active geo-replication) is not supported for managed instances; failover groups are the recommended solution.

Answer

Enable zone redundancy on the primary and secondary instances.

Answer

Configure log shipping between the primary and secondary instances.

Answer

Use active geo-replication instead of a failover group.

Question 14

You need to protect Azure SQL Database from SQL injection attacks. Which THREE of the following measures should you implement?

Accepted Answer

Enable Microsoft Defender for SQL to detect and alert on SQL injection.. Microsoft Defender for SQL provides advanced security capabilities, including vulnerability assessment and threat detection. It specifically monitors for anomalous activities that indicate a SQL injection attack, such as unusual login attempts or queries that match known injection patterns, and can alert administrators or trigger automated responses.

Answer

Implement dynamic data masking to hide sensitive data from unauthorized users.

Answer

Enable Transparent Data Encryption (TDE) to encrypt the database at rest.

Question 15

Your organization has a compliance requirement to automatically classify and protect sensitive data in Azure SQL Database. You need to configure Microsoft Purview to scan and classify the database. Which THREE actions are required?

Accepted Answer

Run a scan to discover and classify sensitive data.. Running a scan is the primary action that discovers and classifies sensitive data in Azure SQL Database using Microsoft Purview. The scan applies the classification rules from the scan rule set to identify patterns like credit card numbers or social security numbers, and then labels the data accordingly for compliance.

Answer

Create a Microsoft Entra ID application and grant it access to the database.

Answer

Enable Microsoft Defender for SQL to automatically classify data.

Question 16

You are configuring Azure SQL Database for a financial application that must meet PCI DSS compliance. The database contains credit card numbers stored in a column encrypted with Always Encrypted. You need to ensure that database administrators cannot view the plaintext credit card numbers while allowing application users with the proper credentials to access them. What should you implement?

Accepted Answer

Use Always Encrypted with secure enclaves and store the column master key in Azure Key Vault with access granted only to the application. Always Encrypted with secure enclaves ensures that credit card numbers are encrypted at all times, including during computation, and that the column master key is stored in Azure Key Vault with access granted only to the application. This prevents database administrators (DBAs) from viewing plaintext data because they never have access to the column master key, while authorized application users can decrypt the data via the client driver. This meets PCI DSS requirements for protecting cardholder data at rest and in use.

Answer

Use column-level encryption with a certificate stored in the database

Answer

Enable Transparent Data Encryption (TDE)

Answer

Implement Dynamic Data Masking (DDM)

Question 17

Your organization requires that all Azure SQL Database administrators use multi-factor authentication (MFA) when connecting. Which authentication method must be used?

Accepted Answer

Microsoft Entra ID authentication with Conditional Access policy. Microsoft Entra ID authentication combined with a Conditional Access policy is required to enforce multi-factor authentication (MFA) for Azure SQL Database administrators. Conditional Access policies can mandate MFA as a condition for authentication, which is not possible with SQL Server authentication, certificate-based authentication, or Windows authentication alone. This method integrates with Microsoft Entra ID (formerly Azure AD) to provide the necessary security controls.

Answer

SQL Server authentication

Answer

Certificate-based authentication

Answer

Windows authentication

Question 18

Refer to the exhibit. You run these commands in an Azure SQL Database. What is the result?

Accepted Answer

A Microsoft Entra ID user is created and granted read access to the database.. The commands create a user in an Azure SQL Database mapped to a Microsoft Entra ID (formerly Azure AD) identity. The CREATE USER statement with FROM EXTERNAL PROVIDER creates a user that corresponds to an Entra ID user or group. The ALTER ROLE statement then adds this user to the db_datareader database role, granting read access to all tables and views. Therefore, option D correctly describes the outcome: a Microsoft Entra ID user is created and granted read access.

Answer

The user is created but not granted any permissions.

Answer

The commands fail because Entra ID users cannot be created in Azure SQL Database.

Answer

A SQL Server authentication user is created and granted read access.

Question 19

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database is 500 GB and requires a disaster recovery solution with an RPO of 15 seconds and automatic failover to a secondary region. Which configuration meets these requirements?

Accepted Answer

Create an auto-failover group with a secondary instance in a paired region using synchronous replication.. Auto-failover groups with a secondary instance in a paired region using synchronous replication meet the requirements for automatic failover and an RPO of 15 seconds for Azure SQL Managed Instance. Option A is incorrect because geo-redundant backup storage and geo-restore do not provide automatic failover or a low RPO. Option B is incorrect because active geo-replication is not supported for SQL Managed Instance; it is used for Azure SQL Database. Option C is incorrect because log shipping is not supported for SQL Managed Instance.

Answer

Enable geo-redundant backup storage and configure geo-restore.

Answer

Configure active geo-replication to a secondary instance in another region.

Answer

Set up log shipping to a secondary instance in another region.

Question 20

Your company is migrating on-premises SQL Server databases to Azure SQL Managed Instance. You need to ensure that database backups are encrypted at rest using customer-managed keys stored in Azure Key Vault. You also need to allow the backup service to access the keys. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) with customer-managed keys and grant the managed instance's system-assigned managed identity 'get', 'wrapKey', and 'unwrapKey' permissions on the key vault.. Transparent Data Encryption (TDE) with customer-managed keys (CMK) in Azure SQL Managed Instance encrypts database backups at rest. To allow the Azure backup service to access the key for backup encryption, the managed instance's system-assigned managed identity must be granted 'get', 'wrapKey', and 'unwrapKey' permissions on the Azure Key Vault where the CMK is stored. This ensures that backups are encrypted using the customer-controlled key, meeting the requirement for encryption at rest with customer-managed keys.

Answer

Use Always Encrypted with column master key stored in Azure Key Vault.

Answer

Configure Azure Backup for SQL Server in Azure VM and use Backup Center to manage encryption.

Answer

Configure server-level firewall rules to allow Azure services to access the server.