DP-300 Practice Test 14 — 15 Questions

Question 1

You have an Azure SQL Database that stores sensitive data. You need to automatically classify and apply sensitivity labels to new columns as they are added. What should you use?

Accepted Answer

Microsoft Purview Information Protection. Microsoft Purview Information Protection enables automatic classification and labeling of sensitive data in Azure SQL Database through its data classification capabilities. It can be configured to automatically detect and apply sensitivity labels to new columns based on built-in or custom rules. Option B (Azure Policy) can enforce compliance but does not perform automatic classification of data within the database. Option C (Dynamic Data Masking) obscures sensitive data but does not classify or label it. Option D (Azure Automation with PowerShell) requires custom scripting and does not provide built-in automatic classification integration for new columns.

Answer

Azure Policy with custom policy definition

Answer

Dynamic Data Masking

Answer

Azure Automation with PowerShell script to run sp_addsensitivityclassification

Question 2

Your company uses Azure SQL Managed Instance. You need to automate the execution of a stored procedure that processes sales data every night at 2 AM. The solution must use native capabilities and minimize latency. What should you do?

Accepted Answer

Create a SQL Agent job with a schedule to execute the stored procedure.. Azure SQL Managed Instance includes SQL Server Agent, which can natively schedule T-SQL jobs with minimal latency. Option A is incorrect because Elastic Database Jobs require a separate job database and are designed for multi-database scalability, adding overhead. Option B is incorrect because Azure Logic Apps introduce external orchestration, potentially increasing latency and cost. Option D is incorrect because Azure Functions require external connectivity and are not as tightly integrated as SQL Agent.

Answer

Create an Elastic Database Job to run the stored procedure on the target database.

Answer

Use Azure Logic Apps with a recurrence trigger to call the stored procedure via a connector.

Answer

Deploy an Azure Function with a timer trigger to invoke the stored procedure via the SQL binding.

Question 3

You are implementing automation for an Azure SQL Database. Which TWO scenarios are appropriate for using elastic database jobs? (Choose two.)

Accepted Answer

Checking data consistency across multiple databases. Elastic database jobs are designed for executing administrative tasks across multiple databases in Azure SQL Database. Checking data consistency (B) and rebuilding indexes (D) are common maintenance tasks that benefit from automation across many databases. Option A is incorrect because scaling up a database is handled by Azure's automatic scaling features, not elastic jobs. Option C is incorrect because creating logins and users is better managed using contained database users or Azure AD authentication for security and simplicity. Option E is incorrect because full database backups are automatically managed by Azure SQL Database, so a job is unnecessary.

Answer

Scaling up a database when CPU usage is high

Answer

Creating logins and users across multiple databases

Answer

Performing a full database backup

Question 4

A company uses Azure SQL Managed Instance and needs to automatically rebuild fragmented indexes weekly during low usage. They also need to update statistics. Which approach should they take?

Accepted Answer

Use Azure Automation runbook with Invoke-SqlCmd to run maintenance scripts. Azure Automation runbooks with Invoke-SqlCmd allow custom scheduling and execution of T-SQL scripts for index maintenance and statistics updates. Option A (Elastic Database Jobs) is designed for Azure SQL Database, not Managed Instance. Option C (Azure Logic Apps) can trigger but is not optimal for long-running maintenance tasks. Option D (SQL Agent job) is available in Azure SQL Managed Instance, but it is less flexible for automated scheduling during low usage windows and may impact performance; Azure Automation provides better orchestration and monitoring capabilities. Therefore, the best approach is to use Azure Automation runbook.

Answer

Use Elastic Database Jobs to run index maintenance

Answer

Use Azure Logic Apps with SQL connector

Answer

Create a SQL Agent job on the managed instance

Question 5

Which TWO actions are required to automate the export of an Azure SQL Database to a BACPAC file on a monthly basis? (Choose two.)

Accepted Answer

Use Azure Automation or a scheduled Azure Function to call the Export-AzSqlDatabase cmdlet.. To automate the export of an Azure SQL Database to a BACPAC file on a monthly basis, you need a storage location for the file and an automation method. Option B is correct because Azure Automation or an Azure Function can trigger the Export-AzSqlDatabase cmdlet or REST API to perform the export on a schedule. Option E is correct because an Azure Storage account with a container is required to store the resulting BACPAC file. Option A is incorrect because LTR policy is for automated backups, not export to BACPAC. Option C is incorrect because a SQL Server on Azure VM is not needed; the export is initiated directly against the Azure SQL Database logical server. Option D is incorrect because SSIS is an ETL tool that is not necessary for a simple export operation; the export can be done via PowerShell, CLI, or REST API without SSIS.

Answer

Configure long-term retention (LTR) policy for the database.

Answer

Deploy a SQL Server on Azure VM to run the export command.

Answer

Install SQL Server Integration Services (SSIS) on a virtual machine.

Question 6

You manage an Azure SQL Managed Instance that hosts several databases. You need to automate the process of patching the operating system and SQL Server engine with minimal downtime. What should you use?

Accepted Answer

Configure the maintenance window for the Managed Instance.. Azure SQL Managed Instance automatically applies OS and SQL Server engine updates during the user-configured maintenance window, minimizing downtime by performing patching during off-peak hours. Option B is wrong because Elastic Job agents are used for scheduling T-SQL jobs across multiple databases, not for patching the instance or OS. Option C is wrong because manual patching via the Azure portal is not an automated solution and would require manual effort and cause downtime. Option D is wrong because Azure Update Manager is designed for Azure VMs and Arc-enabled servers, not for Azure SQL Managed Instance, which has its own built-in patching mechanism.

Answer

Use an Elastic Job agent to run a script that applies updates.

Answer

Schedule a manual patching using the Azure portal.

Answer

Use Azure Update Manager to schedule patching.

Question 7

You are automating the creation of Azure SQL Databases using ARM templates. You need to ensure that the databases are created with the 'Hyperscale' service tier and with 'ZoneRedundant' enabled. Which ARM template property should you set?

Accepted Answer

Set 'sku' to 'HS_Gen5_2' and 'properties.zoneRedundant' to true.. In ARM templates for Azure SQL Database, the 'sku' property defines the service tier and performance level. For Hyperscale, the 'sku' name is 'HS_Gen5_2' (Gen5, 2 vCores) and the 'sku.tier' is 'Hyperscale'. The 'zoneRedundant' property is set under 'properties' to true for zone redundancy. Option B is incorrect because 'requestedServiceObjectiveName' is used for DTU-based tiers, not vCore-based Hyperscale. Option C is incorrect because 'edition' is not a valid ARM template property; the tier is specified through 'sku.tier'. Option D is incorrect because 'maxSizeBytes' only sets the maximum database size, not the service tier.

Answer

Set 'requestedServiceObjectiveName' to 'HS_Gen5_2' and 'zoneRedundant' to true.

Answer

Set 'edition' to 'Hyperscale' and 'zoneRedundant' to true.

Answer

Set 'maxSizeBytes' to a value and 'zoneRedundant' to true.

Question 8

You are responsible for automating the deployment of an Azure SQL Database with associated firewall rules, a Microsoft Entra ID admin, and a vulnerability assessment baseline. The deployment must be idempotent and repeatable. Which THREE tools or approaches should you use? (Choose three.)

Accepted Answer

Bicep template.. Bicep templates (B) and ARM templates (C) are both declarative Infrastructure as Code (IaC) tools for defining Azure resources, including Azure SQL Database, firewall rules, Entra ID admin, and vulnerability assessment baselines. An Azure DevOps release pipeline (D) can automate the deployment of these templates, ensuring idempotent and repeatable deployments. SQL Server Management Studio (A) is a management tool for SQL Server, not for IaC or automated deployment. The Azure portal (E) is a manual GUI and not suitable for repeatable automation.

Answer

SQL Server Management Studio.

Answer

Azure portal.

Question 9

You have an Azure SQL Database that uses a managed identity to access Azure Key Vault for storing column master keys. You need to automate the rotation of the column master key using Azure Automation. Which steps should you include in the runbook?

Accepted Answer

Authenticate using Connect-AzAccount -Identity, then use Set-AzKeyVaultKey to create a new key version, and update the column encryption key using Invoke-SqlCmd.. The runbook must authenticate to Key Vault using the managed identity, create a new key, update the column encryption key, and then clean up. Options A and B miss authentication steps. Option D incorrectly uses connection strings.

Answer

Use PowerShell cmdlets without authentication; the runbook runs under the Automation account's identity.

Answer

Use the SQL Server Management Objects (SMO) to rotate the key directly from the runbook without Key Vault.

Answer

Use the Key Vault REST API with a client secret stored in the runbook.

Question 10

You are a senior database administrator for a financial services company that uses Azure SQL Managed Instance to host multiple customer databases. The company has a requirement to automatically execute a series of compliance scripts every Sunday at 2:00 AM against all databases in the instance. The scripts include checking for orphaned users, verifying data encryption, and auditing login attempts. The solution must log the execution results in a central table and send an email summary to the compliance team. You have been asked to implement this automation using built-in Azure features without relying on external tools like Power Automate or custom schedulers. What should you use?

Accepted Answer

Create a SQL Agent job that runs a T-SQL script using sp_foreachdb to execute the compliance scripts on each database, store results in a central database, and send an email using Database Mail.. SQL Agent jobs in Azure SQL Managed Instance can execute T-SQL scripts across databases using sp_foreachdb (or a cursor-based loop) to run compliance scripts on each database. Results can be inserted into a central database using three-part names (or cross-database queries). Email notifications can be sent using Database Mail (sp_send_dbmail). Option A is incorrect because Logic Apps are external tools and not built-in Azure features for this purpose. Option B is incorrect because Azure Automation Runbooks require external connectivity and are not directly integrated with SQL Managed Instance for cross-database execution. Option D is incorrect because Elastic Jobs are designed for Azure SQL Database, not for SQL Managed Instance.

Answer

Schedule a Logic App that connects to each database via the SQL connector and runs the scripts.

Answer

Use Azure Automation Runbooks with the PowerShell module for SQL Server to execute scripts on each database.

Answer

Create an Elastic Job agent with a job that runs the scripts against each database and logs results to a central database.

Question 11

You are responsible for automating index maintenance in Azure SQL Database. You need to ensure that index rebuilds and reorganizations are performed only when fragmentation exceeds 30% and 10%, respectively, and that the job runs weekly. Which approach should you use?

Accepted Answer

Use Elastic Database Jobs to run a script that rebuilds/reorganizes indexes.. Elastic Database Jobs can be used to run a T-SQL script on a schedule, checking fragmentation thresholds and performing rebuilds/reorganizations. This meets the requirement of weekly execution while checking thresholds (30% and 10%). Option D is incorrect because automatic index tuning in Azure SQL Database does not support a fixed schedule; it operates in real-time based on workload and cannot be set to run weekly. Options A and C are not suitable: A uses a SQL Agent job which is not available in Azure SQL Database (only in SQL Server on VMs), and C pertains to SQL Server on VMs, not Azure SQL Database.

Answer

Create a SQL Agent job using T-SQL script with sys.dm_db_index_physical_stats.

Answer

Schedule a maintenance task using SQL Server Agent in a VM running SQL Server.

Answer

Enable automatic index tuning in Azure SQL Database with the desired fragmentation thresholds.

Question 12

Which TWO actions should you take to minimize downtime during a planned failover of an Azure SQL Database failover group? (Choose two.)

Accepted Answer

Ensure the failover group is configured for manual failover.. For a planned failover of an Azure SQL Database failover group, you should ensure the failover is manual (C) and then initiate a planned failover (E). This approach gives you control over the timing and ensures no data loss because the secondary is fully synchronized before the switch. Option A (scaling up) does not reduce downtime during failover; it affects performance but not failover behavior. Option B (terminating connections) is unnecessary because the failover process handles connection redirection. Option D (disabling automatic failover) is not required for a planned failover and could actually increase risk if an unplanned event occurs.

Answer

Scale up the primary database to a higher service objective.

Answer

Terminate all active connections to the primary database.

Answer

Disable automatic failover on the failover group.

Question 13

You are designing a disaster recovery plan for an Azure SQL Database that stores critical financial data. The RPO must be 5 seconds, and the RTO must be 30 seconds. Which solution should you recommend?

Accepted Answer

Failover groups with automatic failover policy.. Failover groups with automatic failover policy can achieve an RPO of 5 seconds and an RTO of 30 seconds for Azure SQL Database. Active geo-replication also achieves an RPO of 5 seconds but requires manual failover, increasing RTO beyond 30 seconds. Zone-redundant configuration only protects within a region and does not provide disaster recovery across regions. Automated backups with geo-restore have much higher RPO and RTO (typically hours).

Answer

Zone-redundant configuration within the primary region.

Answer

Active geo-replication with manual failover.

Answer

Automated backups with geo-restore.

Question 14

You need to configure high availability for an Azure SQL Database that supports a critical application. The database must be resilient to a single compute node failure. Which TWO options should you consider? (Choose two.)

Accepted Answer

Use the Business Critical service tier.. Options D and E are correct. The Business Critical service tier provides synchronous replicas that automatically failover on node failure, ensuring high availability within a single region. Zone redundancy, when enabled in the Business Critical tier, places replicas across different availability zones, adding resilience to zone-level failures. Option A (Hyperscale read replicas) are for read scaling, not high availability. Option B (active geo-replication) is for disaster recovery across regions, not single node failure. Option C (auto-failover group) is for managing failover of multiple databases, not directly for compute node resilience.

Answer

Configure Hyperscale read replicas.

Answer

Enable active geo-replication.

Answer

Create an auto-failover group.

Question 15

You have an Azure SQL Database in the Business Critical tier with zone redundancy enabled. You need to maintain high availability during a planned patching event. Which THREE actions should you take? (Choose three.)

Accepted Answer

Verify that zone redundancy is enabled on the database.. To maintain high availability during a planned patching event on a Business Critical tier with zone redundancy enabled, you should first verify that zone redundancy is enabled (B) to confirm the configuration. Perform a planned failover test (D) to ensure the failover mechanism works correctly. Ensure the database has a read-scale replica configured (E) to offload read workloads and maintain performance. Option A (enable active geo-replication) is not required for high availability during patching; it is for disaster recovery. Option C (scale to lower tier) would reduce availability and is unnecessary.

Answer

Enable active geo-replication to a secondary region.

Answer

Scale the database to a lower tier to reduce cost.