DP-300 Configure and manage automation of tasks — Timed 15 Questions

Question 1

A company uses Azure SQL Managed Instance. They need to automate index maintenance for all databases in the instance. The solution must minimize administrative overhead and use built-in Azure features. What should you do?

Accepted Answer

Create an elastic job agent with a T-SQL script for index maintenance targeting all databases.. Option C is correct because an elastic job agent is a built-in Azure feature designed specifically for automating administrative tasks across multiple databases in Azure SQL Managed Instance. It allows you to create a T-SQL script for index maintenance and target all databases in the instance with minimal overhead, as it manages scheduling, retries, and target group membership natively.

Answer

Use Azure Automation with a PowerShell runbook that connects to each database and runs index maintenance.

Answer

Configure a SQL Agent job on the instance to run index maintenance on the master database.

Answer

Use Azure Data Factory to schedule a stored procedure execution for each database.

Question 2

You need to automatically scale an Azure SQL Database based on workload patterns. The solution must use built-in Azure features and minimize manual intervention. Which feature should you configure?

Accepted Answer

Configure autoscale settings for the Azure SQL Database.. Azure SQL Database supports built-in autoscale through the 'Autoscale' feature (serverless compute tier or DTU-based scaling policies), which automatically adjusts resources based on workload patterns without manual intervention. This is the only option that leverages a native Azure feature for dynamic, reactive scaling rather than scheduled or manual actions.

Answer

Use Azure Data Factory to scale the database based on pipeline triggers.

Answer

Create an Azure Automation runbook that scales the database on a schedule.

Answer

Use an elastic pool and manually adjust eDTUs.

Question 3

You are responsible for automating backups of on-premises SQL Server databases to Azure Blob Storage. The solution must use the least administrative effort and provide point-in-time restore capability. What should you implement?

Accepted Answer

Configure SQL Server Managed Backup to Microsoft Azure.. SQL Server Managed Backup to Microsoft Azure (also known as Managed Backup) is the correct choice because it provides automated, policy-based backup management with minimal administrative effort. It natively supports point-in-time restore by automatically scheduling full, differential, and transaction log backups to Azure Blob Storage, and it handles backup retention and recovery point management without requiring custom scripts or additional infrastructure.

Answer

Install Azure Backup Server on-premises and configure backup of SQL Server databases.

Answer

Use SQL Server Agent jobs to perform full, differential, and log backups to an Azure Blob Storage URL.

Answer

Use Azure Data Factory to copy database backups to Blob Storage.

Question 4

You manage an Azure SQL Database that supports a critical application. You need to automate the process of rebuilding indexes that have fragmentation above 30% on a weekly basis. The solution must use built-in database features and minimize performance impact. What should you do?

Accepted Answer

Enable automatic tuning and set the 'Force plan' and 'Create index' options.. Option C is correct because Azure SQL Database's automatic tuning feature includes the 'Create index' option, which can automatically rebuild indexes with high fragmentation when enabled. This uses built-in database features without requiring external scripts or jobs, and it minimizes performance impact by leveraging the database engine's own intelligent scheduling and execution.

Answer

Create a SQL Agent job that runs ALTER INDEX REBUILD on all indexes with fragmentation >30%.

Answer

Use Azure Automation to run a PowerShell script that checks fragmentation and rebuilds indexes.

Answer

Schedule a weekly job using elastic jobs to reorganize all indexes.

Question 5

You need to automate the deployment of schema changes to multiple Azure SQL Databases in different regions. The solution must support rollback and version control. Which technology should you use?

Accepted Answer

Use Azure DevOps with a database project and release pipelines.. Azure DevOps with a database project and release pipelines is the correct choice because it provides source control for schema changes, automated deployment across multiple environments, and built-in rollback capabilities through pipeline versioning and deployment history. This approach aligns with infrastructure-as-code principles, enabling consistent, repeatable, and auditable schema deployments to Azure SQL Databases in different regions.

Answer

Use Azure Data Factory to run stored procedures for schema changes.

Answer

Use SQL Server Agent jobs to run deployment scripts on schedule.

Answer

Use Azure Automation with PowerShell scripts to execute T-SQL scripts.

Question 6

You are configuring automated backups for an Azure SQL Database. Which TWO settings can you configure?

Accepted Answer

Backup retention period (in days).. Option D is correct because the backup retention period (in days) is a configurable setting for Azure SQL Database automated backups. You can set the retention period for point-in-time restore (PITR) backups between 1 and 35 days, and for long-term retention (LTR) backups up to 10 years. This directly controls how far back you can restore your database.

Answer

Backup compression.

Answer

Backup frequency (full, differential, log).

Answer

Point-in-time restore interval.

Question 7

You need to automate monitoring and alerting for an Azure SQL Database. Which THREE actions can you achieve using Azure Monitor and SQL Insights?

Accepted Answer

Stream diagnostic logs to a Log Analytics workspace.. Option C is correct because Azure Monitor and SQL Insights allow you to configure diagnostic settings to stream SQL Database metrics and resource logs (e.g., query store runtime statistics, wait statistics) to a Log Analytics workspace. This enables centralized log analysis, custom KQL queries, and long-term retention for auditing and troubleshooting.

Answer

Automatically rebuild fragmented indexes.

Answer

Automatically scale the database to a higher service tier.

Question 8

Refer to the exhibit. You are reviewing the JSON configuration for an elastic job. The job is expected to run every week on Sunday at midnight UTC. However, the job is not executing as scheduled. What is the most likely cause?

Accepted Answer

The interval and frequency properties are conflicting; interval should be removed for weekly frequency.. The job configuration includes both an 'interval' property (e.g., 'PT1H' for every hour) and a 'frequency' property set to 'Weekly'. In Azure Elastic Jobs, when 'frequency' is specified, the 'interval' property is ignored or causes a conflict because the schedule is defined by the cron-like recurrence pattern. Removing the 'interval' property resolves the conflict and allows the weekly schedule to execute correctly.

Answer

The retention policy is set too low, causing the job to be pruned before execution.

Answer

The startTime is in the past; the job should have a future start time.

Answer

The job definition is missing the 'scheduleType' property.

Question 9

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);. Option B is correct because 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 10

Your company has a critical Azure SQL Database named SalesDB in the West US region. To meet disaster recovery requirements, you configured active geo-replication with a readable secondary replica in East US. You need to automate failover in the event of a regional outage. The solution must ensure that the failover is initiated automatically based on health metrics and that the application connection string is updated to point to the new primary. Currently, the application uses a connection string with the primary server name. You have the following requirements: minimize manual intervention, use built-in Azure features, and ensure that failover is tested regularly. You consider the following options: A) Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover. B) Configure auto-failover group with read-write failover policy and grace period. C) Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS. D) Use Azure Automation with a runbook that checks the database status and initiates failover via REST API. Which option best meets the requirements?

Accepted Answer

Configure auto-failover group with read-write failover policy and grace period.. Option C is correct because auto-failover groups are the built-in Azure feature designed specifically for automating failover of Azure SQL Database geo-replication. They support a read-write failover policy with a configurable grace period that automatically triggers failover based on health metrics, and they automatically update the connection string endpoint for applications using the failover group listener name, not the individual server name. This minimizes manual intervention and allows regular testing via manual failover without affecting the production environment.

Answer

Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover.

Answer

Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS.

Answer

Use Azure Automation with a runbook that checks the database status and initiates failover via REST API.

Question 11

A company uses Azure SQL Database for a critical application. They need to automate the process of exporting a database to a storage account every night, ensuring the export is consistent. The solution must minimize administrative overhead. What should they use?

Accepted Answer

Create an Azure Automation runbook that uses the Export-AzureRmSqlDatabase cmdlet and schedule it to run nightly.. Option A is correct because Azure Automation runbooks can execute PowerShell cmdlets like Export-AzureRmSqlDatabase (or the newer Export-AzSqlDatabase) to perform a consistent export of an Azure SQL Database to a storage account. By scheduling the runbook to run nightly, you automate the export with minimal administrative overhead, as the export operation uses database snapshots to ensure consistency without requiring complex orchestration.

Answer

Create an Elastic Database Job that runs a T-SQL script to export the database.

Answer

Deploy an Azure Data Factory pipeline with a Copy activity to export the database.

Answer

Use an Azure Logic App with the SQL Server connector to export the database.

Question 12

You are tasked with automating index maintenance for an Azure SQL Database. Which Azure service should you use to run T-SQL scripts on a recurring schedule?

Accepted Answer

Elastic Database Jobs. Elastic Database Jobs (B) is the correct service for automating T-SQL script execution across Azure SQL Database on a recurring schedule. It is specifically designed for Azure SQL Database and Azure SQL Managed Instance, providing a job scheduler that can run T-SQL scripts against multiple databases, handle retries, and manage job history. SQL Server Agent is not available in Azure SQL Database (only in SQL Server on-premises or Azure SQL Managed Instance), making Elastic Database Jobs the appropriate choice for this PaaS scenario.

Answer

SQL Server Agent

Answer

Azure Automation Runbook

Answer

Azure Logic Apps

Question 13

A company uses Azure SQL Managed Instance. They want to automate backups to a storage account for long-term retention beyond the default 35 days. What should they use?

Accepted Answer

Create an Azure Automation runbook that uses the Backup-SqlDatabase cmdlet with -BackupAction 'Database' -CopyOnly and schedule it.. Option D is correct because Azure SQL Managed Instance does not support native T-SQL BACKUP DATABASE TO URL for automated long-term retention; instead, you must use the Azure Backup service, which leverages the Backup-SqlDatabase cmdlet with the -CopyOnly flag to perform copy-only backups that do not disrupt the backup chain. This method integrates with Azure Backup policies to store backups in a Recovery Services vault for retention beyond 35 days.

Answer

Configure a SQL Server Agent job on the Managed Instance to run a T-SQL BACKUP DATABASE TO URL statement.

Answer

Use Azure Backup to configure a backup policy for the Managed Instance.

Answer

Create an Elastic Database Job that runs a T-SQL BACKUP DATABASE TO URL statement.

Question 14

You need to automate the creation of a new Azure SQL Database whenever a new customer signs up. The solution should use infrastructure as code and integrate with your CI/CD pipeline. What should you use?

Accepted Answer

Create an ARM template that defines the database and deploy it from your CI/CD pipeline.. B is correct because ARM (Azure Resource Manager) templates are the recommended infrastructure-as-code approach for defining and deploying Azure SQL Databases in a repeatable, declarative manner. Integrating ARM template deployment into a CI/CD pipeline ensures consistent, version-controlled database creation as part of automated workflows, aligning with DevOps best practices.

Answer

Create an Azure Automation runbook that calls New-AzureRmSqlDatabase and trigger it from your CI/CD pipeline.

Answer

Set up an Elastic Database Job that runs a CREATE DATABASE statement.

Answer

Configure a SQL Server Agent job on the logical server to run a CREATE DATABASE statement.

Question 15

Which TWO options are valid services for scheduling automated tasks for Azure SQL Database? (Choose two.)

Accepted Answer

Azure Automation. Azure Automation is correct because it provides a cloud-based automation and configuration service that supports scheduling PowerShell or Python runbooks to execute tasks against Azure SQL Database, such as running T-SQL scripts or performing maintenance operations. It integrates natively with Azure SQL via the Az module and can be triggered on a recurring schedule, making it a valid service for scheduling automated tasks.

Answer

SQL Server Agent

Answer

Azure Functions

Answer

Azure Logic Apps