DP-300 Configure and manage automation of tasks Practice Test 8 — 15 Questions

Question 1

You manage an Azure SQL Database that uses elastic jobs to automate index maintenance. The job fails intermittently with the error 'Cannot open server 'myserver' requested by the login.' The job agent uses a database-scoped credential. What is the most likely cause?

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

Accepted Answer

The credential is not mapped to a server login in the master database.. The error 'Cannot open server...' indicates that the login used by the credential is not recognized at the server level. For elastic jobs with database-scoped credentials, the credential must be associated with a login in the master database of the target server. If the credential is not mapped to a server login, the login cannot access the server, causing this error. Option D correctly identifies this. Option A is incorrect because ALTER ANY DATABASE permission is not required for server access. Option B is incorrect because firewall blocks cause a different error (e.g., connection timeout). Option C is incorrect because the job agent itself does not require a server-level login; it uses the credential.

Answer

The credential's user does not have ALTER ANY DATABASE permission.

Answer

The target database's firewall is blocking the job agent's IP address.

Answer

The job agent itself needs a server-level login.

Question 2

You need to automate the backup of an Azure SQL Database to a storage account. Which built-in feature should you configure?

Accepted Answer

Configure a long-term retention policy for the database.. Azure SQL Database has automated backups enabled by default, which are managed by Microsoft. To retain backups beyond the default retention period (7-35 days), you can configure a long-term retention (LTR) policy to store backups for up to 10 years. Option A is incorrect because elastic jobs are used for performing administrative tasks across multiple databases, not for scheduling backups (backups are automatic and cannot be scheduled via elastic jobs). Option B is incorrect because Azure Backup is designed for IaaS VMs and Azure Files, not for Azure SQL Database, which has its own built-in backup mechanism. Option C is incorrect because SQL Server Agent is not available in Azure SQL Database (it is a feature of SQL Server on-premises or on IaaS VMs); therefore, you cannot use SQL Server Agent jobs for backup automation. Option D is correct because a long-term retention policy is the built-in feature that allows you to keep automated backups for extended periods.

Answer

Use elastic jobs to schedule backups.

Answer

Use Azure Backup to create a backup policy.

Answer

Configure a SQL Server Agent job to perform backups.

Question 3

You have an Azure SQL Database that uses automatic tuning. You notice that a forced plan regression is causing performance degradation. You need to revert to the previous plan and prevent the automatic tuning from forcing the same plan again. What should you do?

Accepted Answer

Create a plan guide for the previous plan and then disable the automatic tuning recommendation for that query.. Creating a plan guide for the previous plan manually enforces that plan for the query, and disabling the automatic tuning recommendation for that specific query prevents the automatic tuning engine from forcing the same plan again. Option A is incorrect because reindexing may not resolve the regression and does not stop automatic tuning from forcing the bad plan. Option C is incorrect because disabling automatic tuning globally affects all queries unnecessarily. Option D is incorrect because clearing the plan cache is only temporary and automatic tuning would likely force the same plan again.

Answer

Reindex the tables involved in the query.

Answer

Disable automatic tuning for the database.

Answer

Run DBCC FREEPROCCACHE to clear the plan cache.

Question 4

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 5

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 6

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 7

You have an Azure SQL Database that uses SQL Server Agent for automation tasks. You migrated the database to Azure SQL Managed Instance. After migration, some SQL Server Agent jobs fail because they reference a linked server that no longer exists. You need to automate the removal of all linked server references from the jobs. What should you do?

Accepted Answer

Create a T-SQL script that updates the job steps to remove linked server references, and execute it via Azure Automation runbook.. You can create a T-SQL script that updates the job steps to remove linked server references and execute it via Azure Automation runbook. This allows automated execution against the SQL Managed Instance. Option A is incorrect because elastic jobs are used for scheduling tasks across multiple databases, not for modifying SQL Agent jobs directly. Option C is incorrect because Azure CLI does not have native commands to manage SQL Agent jobs. Option D is incorrect because while Azure Automation can run PowerShell scripts, the T-SQL script approach is more direct and reliable for modifying job steps.

Answer

Use elastic jobs to remove the linked server references.

Answer

Use Azure CLI to remove the linked server references.

Answer

Use Azure Automation to run a PowerShell script that removes the linked server references.

Question 8

You need to automatically notify the operations team when an Azure SQL Database reaches 80% storage usage. Which Azure service should you use to create the alert?

Accepted Answer

Azure Monitor. Azure Monitor alerts can be configured to trigger based on metrics like storage percent, enabling automatic notification when an Azure SQL Database reaches 80% storage usage. Option A is incorrect because Azure Automation is used for runbooks and process automation, not for alerting. Option B is incorrect because Microsoft Sentinel is a SIEM solution for security analytics, not for simple metric alerts. Option C is incorrect because Azure Logic Apps can be triggered by alerts, but the alert itself must be created in Azure Monitor.

Answer

Azure Automation

Answer

Microsoft Sentinel

Answer

Azure Logic Apps

Question 9

You are designing an automation strategy for an Azure SQL Database that requires the following: 1) Automatically scale up the service tier when CPU usage exceeds 90% for 5 minutes. 2) Automatically scale down when CPU usage drops below 10% for 15 minutes. 3) The solution must be cost-effective and use built-in Azure features. Which TWO options should you combine? (Choose two.)

Accepted Answer

Azure Logic Apps with a metric trigger. Azure Logic Apps can trigger scaling actions based on metric thresholds using a metric trigger, and Azure Automation runbooks can execute the scaling commands (such as changing the service tier) via PowerShell or Azure CLI. Together, they provide a cost-effective, built-in solution. Option A (Elastic Database Jobs) is used for scheduled database tasks like index maintenance, not scaling. Option B (Azure Monitor autoscale) is not directly available for Azure SQL Database; autoscale is only for Azure Virtual Machine scale sets, App Service, etc. Option C (Azure Functions) could also be used, but the recommended combination for this scenario is Logic Apps and Automation runbooks because they are serverless and integrate natively with Azure SQL Database.

Answer

Elastic Database Jobs

Answer

Azure Monitor autoscale

Answer

Azure Functions

Question 10

You need to automate the deployment of schema changes to an Azure SQL Database using Azure DevOps. Which THREE components are required? (Choose three.)

Accepted Answer

Build pipeline. A build pipeline (A) produces artifacts like a .dacpac file. A release pipeline (B) deploys those artifacts to the target database. A variable group (E) stores configuration values such as connection strings. Options C (Elastic job agent) and D (Azure Automation runbook) are not required for schema deployment via DevOps CI/CD. Elastic jobs are used for scheduled tasks across databases, not for deploying schema changes. Azure Automation runbooks automate processes outside of DevOps pipelines.

Answer

Elastic job agent

Answer

Azure Automation runbook

Question 11

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 12

You are reviewing an ARM template snippet for an Azure SQL Database. The database should be configured to automatically pause after 60 minutes of inactivity and resume with a minimum capacity of 0.5 vCores. However, the database is not pausing as expected. What is the most likely cause?

Accepted Answer

The database is not configured with the 'Serverless' compute tier.. Auto-pause functionality is only available for Azure SQL Database in the serverless compute tier. Without configuring the database as serverless (by setting the 'sku' property to include 'Serverless' tier), the auto-pause and auto-resume features will not work, regardless of other property settings. Option A is incorrect because the 'zoneRedundant' property does not affect auto-pause. Option C is incorrect because the minimum 'autoPauseDelay' for serverless is 60 minutes, not 360. Option D is incorrect because the API version used supports serverless features.

Answer

The 'zoneRedundant' property is set to false, which prevents auto-pause.

Answer

The 'autoPauseDelay' value is too low; it must be at least 360 minutes.

Answer

The API version does not support auto-pause.

Question 13

You are reviewing a PowerShell script that is part of an Azure Automation runbook. The script is intended to monitor resource usage of an Azure SQL Database and trigger an alert if DTU usage exceeds 80%. The script runs successfully but does not trigger the alert. What is the most likely reason?

Accepted Answer

The script uses Out-GridView, which is not supported in Azure Automation.. In Azure Automation runbooks, there is no interactive desktop environment, so cmdlets that require a graphical user interface, such as Out-GridView, are not supported. When the script runs in a runbook, Out-GridView fails silently or does not produce any output, causing the subsequent alert logic (which likely depends on the output) to never trigger. Option A is incorrect because the -OutputAs parameter is used with Invoke-SqlCmd to specify the output format, not to return results for alerting. Option C is incorrect because Invoke-SqlCmd is fully compatible with Azure SQL Database when proper authentication is used. Option D is incorrect because the script runs successfully, indicating the query syntax is valid.

Answer

The script must use the -OutputAs parameter to return results.

Answer

The Invoke-SqlCmd cmdlet is not compatible with Azure SQL Database.

Answer

The query syntax is incorrect for Azure SQL Database.

Question 14

You are reviewing an Azure CLI command that creates an elastic job step. The job step is intended to rebuild all indexes on the Sales.Orders table, but the job fails. What is the error in the JSON configuration?

Accepted Answer

The command is missing a semicolon at the end.. The T-SQL command 'ALTER INDEX ALL ON Sales.Orders REBUILD' must end with a semicolon to be valid in an Azure Elastic Job step. Without the semicolon, the job fails. Option B is incorrect because the target group type for a single database is 'SqlDatabase'. Option C is incorrect because 'ALTER INDEX ... REBUILD' is the correct syntax. Option D is incorrect because there is no version incompatibility.

Answer

The target group should specify 'SqlServer' type instead of 'SqlDatabase'.

Answer

The command should be 'REBUILD INDEX' instead of 'ALTER INDEX'.

Answer

The job agent version is incompatible with the command.

Question 15

A company has multiple Azure SQL Databases and wants to automate the deployment of schema changes using a CI/CD pipeline. They need to ensure that changes are automatically rolled back if any step fails. Which Azure service should they use?

Accepted Answer

Azure DevOps with Azure SQL Database deployment task. Azure SQL Database elastic jobs are designed for automating administrative tasks, not CI/CD. Azure Automation with PowerShell runbooks can orchestrate rollbacks but requires custom scripting. Azure DevOps with Azure SQL Deployment task supports transactional deployments with built-in rollback on failure. Azure Machine Learning is irrelevant.

Answer

Azure Automation with PowerShell runbooks

Answer

Azure SQL Database elastic jobs

Answer

Azure Machine Learning