DP-300 Practice Test 34 — 15 Questions

Question 1

Which TWO Azure services can be used to automate the execution of T-SQL scripts on a schedule against Azure SQL Database?

Accepted Answer

Azure Automation Hybrid Runbook Worker with PowerShell Invoke-SqlCmd.. Options B and D are correct. Azure Automation Hybrid Runbook Worker can execute T-SQL scripts using PowerShell Invoke-SqlCmd on a schedule, making it suitable for automating script execution against Azure SQL Database. Elastic Database Jobs are specifically designed to run T-SQL scripts on a schedule across one or more Azure SQL databases. Option A is incorrect because Azure Logic Apps with SQL connector is better suited for workflows and not primarily for scheduled T-SQL script execution. Option C is incorrect because Azure Functions, while capable of running code with timer triggers, is not the optimal service for scheduled T-SQL scripts; Elastic Database Jobs is the dedicated solution. Option E is incorrect because Azure Data Factory is focused on data movement and orchestration, not direct scheduled execution of T-SQL scripts.

Answer

Azure Logic Apps with SQL connector to execute stored procedures.

Answer

Azure Functions with timer trigger and SqlConnection.

Answer

Azure Data Factory with a SQL Server Stored Procedure activity.

Question 2

You are a database administrator for a logistics company that uses Azure SQL Managed Instance. The instance hosts a database that tracks shipments. You need to automate the process of updating statistics for all tables in the database every night. The update should only include tables that have had more than 10% of rows modified since the last statistics update. The automation must be efficient and not rebuild statistics unnecessarily. Additionally, the solution should be self-contained within the Managed Instance and not rely on external services. What should you do?

Accepted Answer

Create a SQL Agent job with a T-SQL script that uses sys.dm_db_stats_properties to identify tables needing statistics updates based on modification counters, then executes UPDATE STATISTICS only on those tables.. SQL Agent is available on Azure SQL Managed Instance, and you can create a T-SQL script that uses sys.dm_db_stats_properties to check modification counters and then executes UPDATE STATISTICS only on tables that have had more than 10% of rows modified. This meets the efficiency requirement without rebuilding statistics unnecessarily. Option B is incorrect because automatic tuning does not update statistics based on modification counters; it manages indexes and query plan issues. Option C is incorrect because Azure Automation requires external dependencies like a hybrid worker and is not self-contained within the Managed Instance. Option D is incorrect because Elastic Database Jobs are not available for Azure SQL Managed Instance; they are for Azure SQL Database.

Answer

Enable automatic tuning for the database to automatically update statistics.

Answer

Use Azure Automation with a PowerShell runbook that connects to the instance and runs sp_updatestats.

Answer

Use Elastic Database Jobs to schedule a T-SQL script that updates all statistics unconditionally.

Question 3

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

Accepted Answer

Batch transactions and reduce log writes.. Batching transactions reduces the number of log write operations, lowering the log write throughput below the service tier limit and avoiding throttling. Option A is incorrect because increasing the max log size does not affect the log write rate; it only provides more storage. Option C is incorrect because accelerated database recovery (ADR) improves recovery time and may reduce log I/O for crash recovery, but it does not directly reduce the sustained log write throughput from transaction processing. Option D is incorrect because the requirement explicitly states not to move to Business Critical.

Answer

Increase the max log size using ALTER DATABASE.

Answer

Enable accelerated database recovery to reduce log I/O.

Answer

Move to Business Critical tier.

Question 4

You need to automate the execution of a T-SQL script against an Azure SQL Database every hour. Which Azure service should you use?

Accepted Answer

Azure Automation. Azure Automation with a runbook can run PowerShell or Python scripts that execute T-SQL against Azure SQL Database on a schedule. Option A is wrong because Azure Data Factory is focused on data movement and transformation, not direct T-SQL execution. Option B is wrong because Elastic Database Jobs require a job agent and are designed for management tasks across multiple databases, not simple hourly script execution. Option C is wrong because Azure Logic Apps is geared towards integration workflows, and while it can execute SQL, it is less efficient than Azure Automation for this straightforward scheduling task.

Answer

Azure Data Factory

Answer

Elastic Database Jobs

Answer

Azure Logic Apps

Question 5

Your Azure SQL Database is experiencing high DTU consumption. You need to identify the top resource-consuming queries. What should you do?

Accepted Answer

Use the Query Store reports in the Azure portal. Query Store in Azure SQL Database provides built-in reports to identify top resource-consuming queries by CPU, IO, and duration, making it the easiest and most direct method. Option B (SQL Server Profiler) is not supported in Azure SQL Database. Option C (Extended Events) is more complex and not the simplest approach for this task. Option D (sys.dm_exec_query_stats) can be used but lacks persistent historical data and is more effort than using Query Store reports.

Answer

Use SQL Server Profiler

Answer

Create an Extended Events session to capture query events

Answer

Query sys.dm_exec_query_stats

Question 6

Your Azure SQL Database is configured with active geo-replication. You need to automate the failover process in case of a regional outage. The solution should ensure minimal data loss and support testing without affecting the production environment. What should you use?

Accepted Answer

Create an Azure Automation runbook that monitors health and executes a manual failover using PowerShell cmdlet Start-AzSqlDatabaseFailover.. Azure SQL Database active geo-replication supports manual failover only; automatic failover is not available. To automate the process, you can use Azure Automation runbooks that invoke the Start-AzSqlDatabaseFailover cmdlet. This allows for scheduled or monitored failover, supports testing by targeting the secondary replica, and minimizes data loss by ensuring the primary is synchronized before failover. Option A is incorrect because Azure Traffic Manager performs DNS-level routing, not database failover. Option B is incorrect because Azure Load Balancer is for network traffic distribution, not SQL database failover. Option D is incorrect because geo-replication does not provide automatic failover; failover must be initiated manually or via automation.

Answer

Configure an Azure Traffic Manager profile with endpoint monitoring and failover.

Answer

Use Azure Load Balancer with a health probe to redirect traffic.

Answer

Enable automatic failover for the geo-replication group.

Question 7

You have an Azure SQL Database that runs a critical workload. You need to automate index maintenance to reduce fragmentation without impacting performance. What should you use?

Accepted Answer

Elastic Database Jobs with T-SQL script. Elastic Database Jobs can be used to schedule and run T-SQL scripts for index maintenance across one or more databases in Azure SQL Database. This allows you to automate index rebuilds or reorganizations during off-peak hours to reduce fragmentation without impacting performance. Option B is incorrect because automatic tuning in Azure SQL Database focuses on creating and dropping indexes based on query patterns, not on rebuilding fragmented indexes. Options C and D are incorrect: SQL Agent Jobs are not available in Azure SQL Database single databases, and while Azure Automation runbooks could be used, Elastic Database Jobs provide a more integrated and seamless solution for database-specific tasks.

Answer

Automatic tuning for index management

Answer

SQL Agent Job with OLE Automation

Answer

Azure Automation runbook with T-SQL script

Question 8

Which TWO actions can you perform using Elastic Database Jobs in Azure SQL Database?

Accepted Answer

Schedule a job to rebuild indexes on a set of databases.. Options B and E are correct. Elastic Database Jobs in Azure SQL Database allow you to schedule and run T-SQL scripts across multiple databases, including those in an elastic pool. You can use them to schedule index rebuilding (B) and to update statistics via T-SQL (E). Option A is incorrect because firewall rules are managed through Azure SQL Server firewall settings, not Elastic Database Jobs. Option C is incorrect because creating users in Microsoft Entra ID (formerly Azure AD) is done through Active Directory, not Elastic Database Jobs. Option D is incorrect because scaling service tiers is an administrative operation handled by Azure SQL Database scaling commands or automation, not by Elastic Database Jobs.

Answer

Add a firewall rule to allow access from a specific IP address.

Answer

Create users in Microsoft Entra ID for database access.

Answer

Automatically scale the service tier of a database based on CPU usage.

Question 9

You manage an Azure SQL Database that is part of a failover group. You need to automate the failover to the secondary region in the event of a disaster. Which approach should you use?

Accepted Answer

Configure the auto-failover group to automatically fail over.. Auto-failover groups are designed to automatically fail over to the secondary region in the event of a disaster, providing built-in automation. Option C is incorrect because while an Azure Automation runbook could be used to initiate a failover manually, it is redundant since the auto-failover group already handles automatic failover. Options B and D are incorrect because elastic jobs are for management tasks like data consistency, and SQL Server Agent is not available in Azure SQL Database.

Answer

Schedule a failover using elastic jobs.

Answer

Create an Azure Automation runbook that initiates the failover.

Answer

Use a SQL Server Agent job to trigger failover.

Question 10

You have an Azure SQL Managed Instance. You need to automate the execution of a stored procedure every hour to clean up historical data. What is the most appropriate solution?

Accepted Answer

SQL Agent Job. SQL Agent Jobs are a built-in feature of Azure SQL Managed Instance, providing native scheduling for T-SQL jobs like executing a stored procedure every hour. Option B is less appropriate because Azure Logic Apps is an external service that adds complexity and latency. Option C is designed for executing tasks across multiple databases, not a single instance. Option D is external and less integrated compared to the native SQL Agent Job.

Answer

Azure Logic Apps with SQL connector

Answer

Elastic Database Job

Answer

Azure Automation runbook with T-SQL

Question 11

Refer to the exhibit. An Azure SQL Database has the above ARM template for long-term retention (LTR) backup policy. Which statement is true about the retention duration?

Accepted Answer

Yearly backups are retained for 5 years.. In the ARM template for LTR backup policy, the yearlyRetention is set to P5Y, which means 5 years. WeeklyRetention is P2W (2 weeks), and monthlyRetention is P6M (6 months). Therefore, only option C is accurate. Option A is incorrect because monthly backups are retained for 6 months, not 12. Option B is incorrect because yearly backups are retained for 5 years, not 10. Option D is incorrect because weekly backups are retained for 2 weeks, not 1 week.

Answer

Monthly backups are retained for 12 months.

Answer

Yearly backups are retained for 10 years.

Answer

Weekly backups are retained for 1 week.

Question 12

You are designing an automated backup strategy for Azure SQL Managed Instance. The solution must ensure point-in-time restore (PITR) within 2 hours for the last 7 days and long-term retention (LTR) for 5 years. Which configuration should you use?

Accepted Answer

Set PITR retention to 7 days (default) and configure LTR backup policy with yearly backups for 5 years.. Azure SQL Managed Instance supports point-in-time restore (PITR) with a configurable retention period from 1 to 35 days; the default is 7 days, which satisfies the requirement to restore within 2 hours for the last 7 days (the 2-hour target is a recovery point objective, not retention). For long-term retention (LTR) of 5 years, Managed Instance supports LTR backup policies with yearly backups that can retain backups for up to 10 years. Therefore, setting PITR retention to 7 days (default) and configuring an LTR policy with yearly backups for 5 years meets both requirements. Option A is wrong because Azure Backup for SQL Server in Azure VM is for SQL Server on Azure VMs, not for Managed Instance. Option B is wrong because "geo-redundant backup storage for LTR" is a storage redundancy option, not an LTR retention policy; LTR requires explicit backup frequency and retention configuration. Option C is wrong because PITR retention cannot be set to 2 hours (minimum is 1 day) and custom backup jobs using Elastic Database Jobs are unnecessary; Managed Instance automates backups natively.

Answer

Use Azure Backup for SQL Server in Azure VM to back up the managed instance.

Answer

Set PITR retention to 7 days and use geo-redundant backup storage for LTR.

Answer

Set PITR retention to 2 hours and configure a custom backup job using Elastic Database Jobs.

Question 13

You need to automate the deployment of an Azure SQL Database along with its firewall rules and performance tier using infrastructure as code. Which technology should you use?

Accepted Answer

Bicep templates. Bicep is a domain-specific language for deploying Azure resources declaratively. It is the recommended infrastructure as code tool for Azure. Option A is correct because Bicep templates allow you to define Azure SQL Database, firewall rules, and performance tier in a declarative manner. Option B (SSDT) is used for database schema management, not resource deployment. Option C (T-SQL scripts) are for database queries and management, not infrastructure. Option D (PowerShell scripts) can automate tasks but are procedural, not declarative IaC like Bicep.

Answer

SQL Server Data Tools (SSDT) database projects

Answer

T-SQL scripts

Answer

PowerShell scripts

Question 14

You need to automate the backup of an Azure SQL Database to a storage account in a different region for disaster recovery. What should you configure?

Accepted Answer

Configure geo-redundant backup storage. Azure SQL Database automatically includes geo-redundant backup storage (RA-GRS) that replicates backups to a paired region for disaster recovery. Option A is incorrect because Azure Backup for SQL Server in Azure VM is for SQL Server installed on VMs, not for Azure SQL Database. Option C is incorrect because long-term retention (LTR) in the same region retains backups within the same region and does not provide cross-region disaster recovery. Option D is incorrect because Elastic Database Jobs are for scheduling T-SQL scripts across multiple databases, not for automating backup to a different region.

Answer

Azure Backup for SQL Server in Azure VM

Answer

Configure long-term retention (LTR) in the same region

Answer

Elastic Database Job to export to BACPAC

Question 15

You need to automate the process of scaling an Azure SQL Database to a higher service tier when CPU usage exceeds 80% for 5 consecutive minutes. Which Azure service is best suited for this automation?

Accepted Answer

Azure Logic App triggered by an Azure Monitor metric alert, calling the Azure SQL Database REST API to update the tier.. Azure Logic Apps can be easily triggered by an Azure Monitor metric alert and can call the Azure SQL Database REST API to update the service tier. This provides a simple, low-code solution for automating scaling based on CPU thresholds. Option B is incorrect because Elastic Database Jobs are designed for executing T-SQL scripts across multiple databases, not for scaling operations. Option C is incorrect: although Azure Automation Runbooks can be triggered by alerts and use PowerShell, they require more setup and overhead compared to Logic Apps. Option D is also less optimal: Azure Functions can work but Logic Apps offer a more straightforward integration with Azure Monitor alerts and REST APIs for this specific use case.

Answer

Elastic Database Job that monitors sys.dm_db_resource_stats and executes ALTER DATABASE.

Answer

Azure Automation Runbook triggered by an Azure Monitor alert, using PowerShell to scale the database.

Answer

Azure Function triggered by an Azure Monitor alert, using the Azure SDK to scale.