DP-300 Practice Test 31 — 15 Questions

Question 1

You are a database administrator for a financial services company that uses Azure SQL Database. The company requires that all administrative tasks, such as index maintenance and statistics updates, be automated and run on a schedule. You need to implement a solution that uses T-SQL scripts and runs them on a schedule without requiring an external server. What should you use?

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

Accepted Answer

Elastic Database jobs. Elastic Database jobs are a native Azure SQL Database feature that enables scheduling and execution of T-SQL scripts across databases. They require no external compute or agent, simplifying automation of routine administrative tasks. Other options either are not available in Azure SQL Database or require additional infrastructure and code, making them less suitable for this scenario.

Answer

Azure Functions with timer trigger

Answer

SQL Server Agent

Answer

Azure Automation runbooks

Question 2

You are monitoring an Azure SQL Database that uses the vCore purchasing model. You need to identify the top resource-consuming queries. You decide to use Query Store. Which two actions should you perform? (Choose two.)

Accepted Answer

Enable Query Store on the database.. To use Query Store for identifying top resource-consuming queries, you must first enable Query Store on the database. Then, you can use the built-in Top Resource Consuming Queries report, which aggregates query statistics and ranks them by resource usage. The other options are either not required or not the intended use of Query Store for this purpose.

Answer

Configure the MAX_STORAGE_SIZE_MB option for Query Store.

Answer

Enable the LEGACY_CARDINALITY_ESTIMATION database scoped configuration.

Answer

Create a custom Extended Events session to capture query metrics.

Question 3

You are a database administrator for a multinational corporation that uses Azure SQL Managed Instance. The instance is part of a failover group for disaster recovery. You need to automate the process of testing the failover group by performing a planned failover to the secondary region and then failing back. The test must be performed monthly during a maintenance window. The automation must ensure that the failover group is in a healthy state before and after the test and must log the results to a table. What should you do?

Accepted Answer

Create an Azure Automation runbook with PowerShell that uses the Az.Sql module to perform failover and log to a table.. Azure Automation runbooks with PowerShell and the Az.Sql module can programmatically invoke failover group operations (e.g., Invoke-AzSqlDatabaseFailoverGroup), check health state, and log results to a table (e.g., via AzTable or SQL). Automation runbooks support schedules (monthly maintenance window), managed identities for authentication, and full scripting control to verify the failover group is healthy before and after. This is the canonical Azure-native approach for orchestrating cross-region failover tests.

Answer

Use Elastic Database Jobs to run T-SQL that initiates failover and logs results.

Answer

Use Azure Data Factory to execute a stored procedure that performs failover.

Answer

Create a SQL Agent job with T-SQL that performs the planned failover using ALTER AVAILABILITY GROUP and logs the results to a table.

Question 4

A company runs Azure SQL Database and wants to automatically receive an email alert when the database's CPU usage exceeds 90% for 10 minutes. The DBA needs to configure this with minimal effort. What should the DBA do?

Accepted Answer

Create an Azure Monitor alert rule on the cpu_percent metric with a threshold of 90 and an action group that sends email.. Azure Monitor alert rules are the native way to monitor platform metrics for Azure SQL Database. By creating a rule on the cpu_percent metric with a threshold and an action group that includes email, the DBA can automatically receive notifications when CPU exceeds 90% for the specified duration. This requires minimal configuration effort.

Answer

Use Azure Automation to run a runbook every 10 minutes that checks CPU usage and sends an email.

Answer

Configure a SQL Server Agent alert on the CPU usage performance condition.

Answer

Create a Database Mail profile and a SQL Agent operator to send the alert.

Question 5

You are monitoring an Azure SQL Database that uses the General Purpose service tier. You need to configure an alert that triggers when the database's CPU usage exceeds 90% for 10 minutes. What should you use?

Accepted Answer

Azure Monitor metric alert on the CPU percentage metric.. Azure Monitor metric alerts are the correct mechanism for alerting on Azure SQL Database metrics. You can create a metric alert rule that monitors the CPU percentage metric and triggers when the average exceeds 90% over a 10-minute period. This is a native, scalable, and integrated solution that supports various notification actions.

Answer

SQL Server Agent job that queries sys.dm_db_resource_stats and sends an email.

Answer

Azure SQL Database automatic tuning with CPU-based recommendations.

Answer

Query Store alert configured to trigger on high CPU usage.

Question 6

You are a database administrator for a logistics company that uses Azure SQL Database. The company requires an automated task to run every night at 02:00 UTC to archive old shipment records into a separate table. You need to minimize administrative overhead and ensure the task runs reliably even if there is a transient failure. What should you implement?

Accepted Answer

Create an Elastic Job agent, define a target group that includes the database, and create a job with a T-SQL step that performs the archival. Schedule the job to run daily at 02:00 UTC.. Elastic Jobs provide a native, low-overhead way to schedule and run T-SQL across Azure SQL databases. They include built-in retry logic and logging, which ensures the archival task runs reliably even with transient failures. Other options either are not supported on Azure SQL Database (SQL Agent) or require more administrative effort and custom error handling (Azure Automation, Logic Apps with Functions).

Answer

Use Azure Logic Apps to trigger an Azure Function that executes the archival T-SQL against the database on a daily schedule.

Answer

Configure a SQL Agent job on the Azure SQL Database to run the archival T-SQL daily at 02:00 UTC.

Answer

Create an Azure Automation runbook that connects to the database and executes the archival T-SQL, and schedule it with a daily trigger.

Question 7

You administer an Azure SQL Database for a ticketing platform. A nightly Azure Automation runbook must scale the database between the General Purpose and Business Critical service tiers based on the day of the week. The runbook runs under a system-assigned managed identity. Which cmdlet should the runbook use to change the service tier?

Accepted Answer

Set-AzSqlDatabase. Changing the service tier of an existing Azure SQL Database is an update operation on the database resource, which the Az PowerShell module exposes through Set-AzSqlDatabase. The runbook authenticates with its managed identity, obtains an Az context, and calls the cmdlet with the desired edition and service objective. Creation and server-level cmdlets cannot perform this in-place scaling.

Answer

New-AzSqlDatabase

Answer

Set-AzSqlDatabaseInstance

Answer

Update-AzSqlServer

Question 8

You manage an Azure SQL Database that experiences blocking. You need to identify the blocking chain and the T-SQL statements involved in the blocking. Which dynamic management view (DMV) should you query?

Accepted Answer

sys.dm_exec_requests joined with sys.dm_exec_sql_text and sys.dm_exec_sessions. The most effective way to diagnose blocking in Azure SQL Database is to query sys.dm_exec_requests, which includes the blocking_session_id column. By joining this DMV with sys.dm_exec_sql_text on the sql_handle, you can retrieve the T-SQL text for both the blocking and blocked requests. Adding sys.dm_exec_sessions provides additional context like login name and host. This combination reveals the full blocking chain and the statements involved.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_input_buffer

Answer

sys.dm_os_waiting_tasks

Question 9

You manage an Azure SQL Database in the Business Critical service tier. The database has a zone-redundant configuration. During a planned maintenance event, you need to ensure that the database remains online with no data loss and minimal downtime. What should you configure?

Accepted Answer

Ensure the database is configured with zone redundancy and rely on the built-in high availability architecture.. Zone redundancy in the Business Critical tier replicates both compute and storage across availability zones, enabling automatic failover during planned maintenance with no data loss and minimal downtime. Cross-region solutions like failover groups or geo-replication address disaster recovery, not local high availability. Backup and restore is not a high availability mechanism.

Answer

Enable auto-failover groups with a secondary database in a different region.

Answer

Configure active geo-replication to a secondary database in the same region.

Answer

Create a long-term retention policy and restore the database from backup after maintenance.

Question 10

You are a database administrator for a retail company that uses Azure SQL Database. You need to automate the process of detecting and responding to high CPU usage. You want to create an alert that triggers an action when CPU usage exceeds 80% for 10 minutes. Which two components must you configure? (Choose two.)

Accepted Answer

An action group that defines the notification and action types.. To automate detection and response to high CPU usage, you need an alert rule that monitors the CPU percentage metric and an action group that defines the response. The alert rule evaluates the condition and fires when the threshold is exceeded. The action group then executes the specified actions, such as sending notifications or triggering automation. Diagnostic settings and Log Analytics are not required for metric alerts, and Elastic Jobs are not triggered by alerts.

Answer

A diagnostic setting that streams resource logs to a Log Analytics workspace.

Answer

An Elastic Job that runs a T-SQL script to scale the database when the alert fires.

Answer

A Log Analytics workspace to store the CPU metrics for analysis.

Question 11

You manage a SQL Server 2019 availability group on Azure Virtual Machines. The availability group has three replicas: one primary and two synchronous secondary replicas in the same region. A fourth asynchronous replica is in a different Azure region for disaster recovery. During a planned maintenance window, you need to perform a manual failover to one of the synchronous secondary replicas without data loss. Which Transact-SQL command should you run on the target secondary replica?

Accepted Answer

ALTER AVAILABILITY GROUP [AG1] FAILOVER;. To perform a planned manual failover without data loss in an availability group, you run ALTER AVAILABILITY GROUP ... FAILOVER on the target secondary replica. This command requires the secondary to be synchronized with the primary and ensures no data loss. The FORCE_FAILOVER_ALLOW_DATA_LOSS option is for unplanned failover with potential data loss, while the other options are configuration commands that do not initiate a failover.

Answer

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON 'SQL2' WITH (FAILOVER_MODE = MANUAL);

Answer

ALTER AVAILABILITY GROUP [AG1] SET (ROLE = SECONDARY);

Answer

ALTER AVAILABILITY GROUP [AG1] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Question 12

You are a database administrator for a financial services company that uses Azure SQL Database. The company requires that all administrative tasks, such as index maintenance and statistics updates, be automated and run on a schedule. You need to implement a solution that uses T-SQL scripts and runs them on a schedule without requiring an external server. What should you use?

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

Accepted Answer

Store the database schema as a SQL project in an Azure Repos Git repository and configure a build pipeline to generate a DACPAC artifact.. A CI/CD pipeline using Azure DevOps with a SQL project in Git and DACPAC deployment provides version control, automated deployment, and rollback capabilities. The combination of storing the schema in a Git repository and using a DACPAC deployment task ensures that schema changes are tracked, tested, and can be rolled back to a previous state with minimal manual effort.

Answer

Configure Azure Automation runbooks to execute ALTER statements against each database on a schedule.

Answer

Implement Elastic Jobs to run T-SQL scripts that apply schema changes to each database.

Answer

Use SQL Server Data Tools (SSDT) to manually publish schema changes to each database from a developer workstation.

Question 13

You are deploying a new Azure SQL Database for a line-of-business application. The application's workload is unpredictable, with periods of near-zero activity overnight and heavy transactional bursts during business hours. You need to choose a purchasing model and service tier that minimizes cost while automatically scaling compute resources based on demand. What should you do?

Accepted Answer

Deploy the database using the vCore purchasing model with the Serverless compute tier and set the minimum and maximum vCores.. The Serverless compute tier in the vCore purchasing model is designed for databases with intermittent, unpredictable usage. It automatically scales compute up during active periods and down or pauses during inactivity, billing only for resources consumed. Setting minimum and maximum vCores provides cost control while enabling automatic scaling. This aligns with the need to minimize cost while handling variable demand, unlike fixed DTU or provisioned vCore tiers.

Answer

Deploy the database using the DTU purchasing model with the Standard service tier.

Answer

Deploy the database using the vCore purchasing model with the General Purpose service tier and configure auto-scaling.

Answer

Deploy the database using the vCore purchasing model with the Business Critical service tier and enable geo-replication.

Question 14

You are a database administrator for a healthcare company that uses Azure SQL Database. The compliance team requires that all automated tasks that modify data must be logged with the identity that executed them. You need to configure an elastic job to run a T-SQL script that updates patient records. Which authentication method should you use for the job step to meet the auditing requirement?

Accepted Answer

The job agent's managed identity with a contained database user in the target database.. The job agent's managed identity provides a distinct, auditable identity for elastic job execution. By creating a contained database user for that identity, all actions performed by the job are attributed to it, which satisfies the compliance need. Managed identities also eliminate password management, making them the recommended approach for elastic jobs.

Answer

Azure Active Directory authentication using a service principal with a client secret.

Answer

A database-scoped credential that stores a SQL login and password.

Answer

SQL authentication using a server-level login stored in the job database.

Question 15

You are the database administrator for an Azure SQL Database that contains a column storing national ID numbers. A new regulation requires that this column be hidden from users who run ad hoc queries in the Azure portal Query Editor, while still being available to the payroll application. The payroll application connects with a login that has SELECT permission on the table. What should you implement?

Accepted Answer

Apply dynamic data masking to the national ID column and grant UNMASK to the payroll application's user.. Dynamic data masking is designed to limit exposure of sensitive columns to users without the UNMASK permission while preserving the actual stored values. Granting UNMASK to the payroll application's user allows that workload to read real data, and portal users without UNMASK see masked results, matching the regulation and application needs.

Answer

Create a view that excludes the national ID column and grant the ad hoc users SELECT on the view only.

Answer

Enable row-level security on the table with a predicate that filters rows where the national ID is present.

Answer

Encrypt the national ID column with Always Encrypted and share the column master key with the payroll application.