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

Question 1

Your team uses Azure DevOps to deploy database changes to Azure SQL Database. You need to automate the generation and deployment of database schema changes based on a Git branch merge. Which Azure service should you integrate?

Accepted Answer

Azure Pipelines with a release pipeline that uses the Azure SQL Database deployment task.. Option D is correct because Azure Pipelines (part of Azure DevOps) can be configured to run on branch merges and execute SQL scripts using the Azure SQL Database deployment task. Option A is wrong because GitHub Actions could be used but the team already uses Azure DevOps. Option B is wrong because Azure Automation Runbooks are not CI/CD focused. Option C is wrong because Azure Logic Apps are not designed for CI/CD.

Answer

Azure Automation Runbook triggered by a webhook from Azure DevOps.

Answer

GitHub Actions with a workflow that runs on pull request merge, using the Azure SQL action.

Answer

Azure Logic Apps with an HTTP trigger from Azure DevOps, then use SQL connector to run scripts.

Question 2

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.. Option A is correct because Managed Instance supports PITR retention up to 35 days (7 days is fine) and LTR policies for up to 10 years. Option B is wrong because LTR is not available for Azure SQL Database single database with just Backup Storage Redundancy. Option C is wrong because Azure Backup for SQL Server in Azure VM is a different scenario. Option D is wrong because PITR retention can be up to 35 days, not 2 hours.

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 3

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.. Option B is correct because Azure Logic Apps can be triggered by metrics alerts and can call the REST API to update the database tier. Option A is wrong because Azure Automation Runbooks can be triggered by alerts but require more setup. Option C is wrong because Azure Functions can work but Logic Apps are simpler. Option D is wrong because Elastic Database Jobs cannot scale databases.

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.

Question 4

Your Azure SQL Database uses a failover group for disaster recovery. You need to automate a planned failover for disaster recovery testing without data loss. What should you use?

Accepted Answer

Create an Azure Automation Runbook that uses the Start-AzSqlDatabaseFailover command with the -AllowDataLoss parameter set to false.. Option C is correct because Azure Automation Runbooks can execute the Start-AzSqlDatabaseFailover cmdlet which supports planned failover without data loss. Option A is wrong because failover group automatic failover is for unplanned events. Option B is wrong because T-SQL ALTER DATABASE FAILOVER is for manual failover but not automated. Option D is wrong because Logic Apps can call REST API but Runbooks are more appropriate.

Answer

Set up an Azure Logic App with a failover trigger from Azure Monitor.

Answer

Configure automatic failover policy in the failover group.

Answer

Use a T-SQL script with ALTER DATABASE FAILOVER scheduled via SQL Server Agent.

Question 5

You have an Azure SQL Database that stores sensitive customer data. You need to automate the masking of a specific column for non-admin users. Which feature should you use?

Accepted Answer

Dynamic Data Masking with a masking function configured on the column.. Option A is correct because Dynamic Data Masking can be configured via T-SQL or Azure portal and masks data automatically for non-admin users. Option B is wrong because Always Encrypted encrypts data at rest and in transit, but does not mask for non-admins. Option C is wrong because Row-Level Security filters rows, not columns. Option D is wrong because Azure Policy cannot mask columns.

Answer

Always Encrypted with a column encryption key.

Answer

Row-Level Security to filter rows for non-admin users.

Answer

Azure Policy to enforce masking rules on the database.

Question 6

You need to automatically send a weekly email report summarizing the performance metrics (DTU consumption, storage used) of all Azure SQL Databases in a subscription. Which Azure service should you use?

Accepted Answer

Azure Logic App with a recurrence trigger, using Azure Monitor connector to get metrics and Office 365 Outlook connector to send email.. Option D is correct because Azure Logic Apps can query metrics via the Azure Monitor connector and send emails via Office 365 connector, all in a scheduled workflow. Option A is wrong because Azure Automation Runbooks can do it but require more scripting for email. Option B is wrong because Elastic Database Jobs are for T-SQL, not metrics. Option C is wrong because Azure Data Factory is for data movement, not reporting.

Answer

Azure Automation Runbook with PowerShell to collect metrics and send email via Send-MailMessage.

Answer

Elastic Database Job that queries sys.dm_db_resource_stats and sends email via sp_send_dbmail.

Answer

Azure Data Factory pipeline that runs weekly and sends an email with the report.

Question 7

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 A and D are correct. Elastic Database Jobs can run T-SQL scripts across multiple databases and can be scheduled. Option B is wrong because Elastic Database Jobs cannot scale databases. Option C is wrong because Elastic Database Jobs cannot configure firewall rules. Option E is wrong because Microsoft Entra ID user creation is not a typical use case for 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 8

Which THREE configurations are required to automate the deployment of database schema changes from a Git repository to Azure SQL Database using Azure Pipelines?

Accepted Answer

An Azure SQL Database deployment task in the pipeline.. Options A, B, and E are correct. A service connection to Azure SQL Database is needed to authenticate. A YAML pipeline definition specifies the steps. The SQL task (e.g., Azure SQL Database deployment task) runs the scripts. Option C is wrong because an Azure Automation Runbook is not part of Azure Pipelines. Option D is wrong because an Elastic Database Job agent is not required for a single database deployment.

Answer

An Elastic Database Job agent to run the deployment scripts.

Answer

An Azure Automation Runbook to execute the deployment.

Question 9

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 A and D are correct. Azure Automation Hybrid Runbook Worker can run T-SQL scripts on-premises or in Azure. Elastic Database Jobs are designed to run T-SQL scripts on schedules across databases. Option B is wrong because Azure Logic Apps can run SQL queries but not T-SQL scripts in a batch. Option C is wrong because Azure Data Factory is for data movement, not script execution. Option E is wrong because Azure Functions are for code execution, but not specifically for scheduled T-SQL.

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 10

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.. Option D is correct. The yearlyRetention is P5Y (5 years), weeklyRetention is P2W (2 weeks), monthlyRetention is P6M (6 months). Option A is wrong because weeklyRetention is 2 weeks, not 1 week. Option B is wrong because monthlyRetention is 6 months. Option C is wrong because yearlyRetention is 5 years.

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 11

Refer to the exhibit. You run the above PowerShell command to set the Transparent Data Encryption (TDE) protector for an Azure SQL Database server. What is the result?

Accepted Answer

The server’s TDE protector is changed to a customer-managed key from Azure Key Vault.. Option B is correct. The command sets the TDE protector to an Azure Key Vault key, enabling customer-managed key (CMK) for TDE. Option A is wrong because the command does not disable TDE. Option C is wrong because it does not check key permissions. Option D is wrong because the command is for the server-level TDE protector, not for a specific database.

Answer

The command fails because the service principal does not have permissions to the key vault.

Answer

Transparent Data Encryption is disabled.

Answer

The TDE protector for the database "mydb" is updated.

Question 12

Refer to the exhibit. The ARM template snippet configures auditing for an Azure SQL Database. Based on the configuration, which events are audited?

Accepted Answer

Successful and failed database authentication attempts.. Option B is correct. The auditActionsAndGroups include SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP, which audit successful and failed logins respectively. Option A is wrong because DDL changes are not included. Option C is wrong because data modifications are not included. Option D is wrong because only authentication events are listed.

Answer

All data modification statements (DML).

Answer

Only failed authentication attempts.

Answer

All database schema changes (DDL).

Question 13

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

Automatic tuning for index management. Option D is correct because Azure SQL Database's automatic tuning includes index management that can be configured to fix fragmentation automatically. Option A is wrong because Azure Automation with runbooks requires custom scripts and may not be as efficient. Option B is wrong because Elastic Database Jobs are for multi-database tasks, not single-database index maintenance. Option C is wrong because SQL Agent Jobs are not available in Azure SQL Database single databases.

Answer

Elastic Database Jobs with T-SQL script

Answer

SQL Agent Job with OLE Automation

Answer

Azure Automation runbook with T-SQL script

Question 14

You manage a fleet of 100 Azure SQL Databases used by different departments. You need to automate the deployment of schema changes across all databases with rollback capability. What is the best approach?

Accepted Answer

Implement a CI/CD pipeline using Azure DevOps with Azure SQL Database deployment tasks. Option B is correct because Azure SQL Database supports deployment pipelines with CI/CD using Azure DevOps or GitHub Actions, allowing automated schema changes with rollback. Option A is wrong because SQL Server Management Studio (SSMS) is a manual tool. Option C is wrong because Elastic Database Jobs are for ad-hoc scripts, not for complex deployments with rollback. Option D is wrong because it lacks full automation and rollback.

Answer

Create an Elastic Database Job that runs the schema script

Answer

Use SQL Server Management Studio (SSMS) to run scripts against each database

Answer

Use Azure Automation with PowerShell to run Invoke-SqlCmd

Question 15

You need to automatically scale an Azure SQL Database based on workload patterns. Which Azure feature should you use?

Accepted Answer

Serverless compute tier. Option A is correct because Azure SQL Database's serverless compute tier automatically scales based on workload. Option B is wrong because auto-scaling is not available in provisioned tier without manual intervention. Option C is wrong because Azure Automation with runbooks can perform scaling but is less efficient than serverless. Option D is wrong because Elastic Database Jobs are for multi-database operations, not scaling.

Answer

Provisioned tier with auto-scale setting

Answer

Azure Automation runbook that modifies the service tier

Answer

Elastic Database Jobs to add replicas