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

Question 1

You have an Azure SQL Database that uses a managed identity to access Azure Key Vault for storing column master keys. You need to automate the rotation of the column master key using Azure Automation. Which steps should you include in the runbook?

Accepted Answer

Authenticate using Connect-AzAccount -Identity, then use Set-AzKeyVaultKey to create a new key version, and update the column encryption key using Invoke-SqlCmd.. Option C is correct because the runbook must authenticate to Key Vault using the managed identity, create a new key, update the column encryption key, and then clean up. Options A and B miss authentication steps. Option D incorrectly uses connection strings.

Answer

Use PowerShell cmdlets without authentication; the runbook runs under the Automation account's identity.

Answer

Use the SQL Server Management Objects (SMO) to rotate the key directly from the runbook without Key Vault.

Answer

Use the Key Vault REST API with a client secret stored in the runbook.

Question 2

You are configuring automated performance tuning for an Azure SQL Database. You want to automatically create missing indexes and drop unused indexes, but you need to prevent automatic index creation during peak business hours (9 AM - 5 PM). What is the best approach?

Accepted Answer

Enable automatic tuning but set the 'INDEX_CREATE' option to 'OFF' and use a schedule to run a script that enables it during off-peak hours and disables during peak hours.. Option D is correct because Azure SQL Database's automatic tuning can be enabled but with a schedule that restricts index creation to off-peak hours using the 'SET AUTOMATIC_TUNING' command with a schedule. Options A and B do not provide scheduling. Option C is not available.

Answer

Enable automatic indexing in Azure SQL Database and rely on the system to decide when to create indexes.

Answer

Use SQL Server Agent to schedule index creation scripts that check for missing indexes.

Answer

Use Azure Automation to run index analysis scripts only during off-peak hours.

Question 3

You need to automate the process of refreshing a development copy of an Azure SQL Database from a production backup every night. The production database is in the West US region, and the dev database is in East US. What is the most cost-effective method?

Accepted Answer

Use the Restore-AzSqlDatabase PowerShell cmdlet to restore the production database to the dev server using a point-in-time backup from the same day.. Option B is correct because Azure SQL Database supports point-in-time restore to a different server and region, which is cost-effective and automated via PowerShell or CLI. Options A and D require manual steps. Option C is for cross-region replication, which is not designed for nightly refresh.

Answer

Export the production database to a BACPAC file, store it in Azure Blob Storage, and import it to dev using a Data Factory pipeline.

Answer

Configure active geo-replication between production and dev, then initiate a planned failover each night.

Answer

Use Azure Data Studio to export the database as a script and run it on the dev server.

Question 4

You need to automate the monitoring of Azure SQL Database performance and receive alerts when certain conditions are met. Which TWO Azure services can be used together to achieve this?

Accepted Answer

Log Analytics Workspace. Options B and D are correct because Azure Monitor collects metrics and logs from Azure SQL Database, and Log Analytics can analyze the data; Azure Monitor Alerts can trigger actions. Option A (Application Insights) is for application-level monitoring. Option C (Azure Sentinel) is for security. Option E (Azure Advisor) provides recommendations but not real-time alerts.

Answer

Azure Sentinel

Answer

Application Insights

Answer

Azure Advisor

Question 5

You need to automate the deployment of Azure SQL Database with a specific configuration across multiple environments (dev, test, prod). The deployment must include firewall rules, auditing settings, and threat detection policies. Which THREE tools can be used to implement this automation?

Accepted Answer

ARM templates with Bicep. Options A, C, and D are correct because ARM/Bicep templates can define the entire infrastructure, Azure CLI can be used in scripts, and Azure PowerShell can also be used. Option B (Azure Data Studio) is a client tool for querying, not automation. Option E (Azure Migrate) is for migration.

Answer

Azure Migrate

Answer

Azure Data Studio

Question 6

You are tasked with automating the backups of multiple Azure SQL Databases to ensure long-term retention. Which TWO Azure services can be used to achieve automated backups with retention beyond the default 7-35 days?

Accepted Answer

Azure Backup for SQL Server in Azure VMs. Options A and B are correct because Azure Backup for SQL Server in Azure VMs supports long-term retention (LTR) up to 10 years, and Azure SQL Database's built-in long-term retention (LTR) policy can be configured for up to 10 years. Options C, D, and E do not provide long-term backup retention for SQL databases.

Answer

Azure Site Recovery

Answer

Azure Blob Storage snapshots

Answer

Azure Storage lifecycle management

Question 7

You are the database administrator for a large e-commerce company that uses Azure SQL Database for its transactional systems. The environment consists of 100 databases spread across 10 elastic pools in different regions. You need to implement an automated solution to perform the following tasks every night: (1) Run integrity checks (DBCC CHECKDB) on all databases, (2) Rebuild indexes with fragmentation > 30%, (3) Update statistics with full scan for databases that have had significant data changes (>20% of rows). The solution must minimize manual intervention, provide centralized logging, and be resilient to failures (e.g., if one database fails, the others should continue). Which approach should you use?

Accepted Answer

Create an Elastic Database Job with step scripts for each maintenance task, targeting all databases, and configure retry logic.. Option B is correct because Elastic Database Jobs can run T-SQL scripts across multiple databases with built-in retry and error handling, and can be scheduled. Option A (SQL Agent) is not available on Azure SQL Database. Option C (Azure Automation) requires custom error handling. Option D (Azure Data Factory) is not designed for database maintenance tasks.

Answer

Create a SQL Agent job on each server to run a maintenance script.

Answer

Use Azure Data Factory pipelines with a ForEach activity to execute stored procedures.

Answer

Use Azure Automation runbooks with Invoke-SqlCmd to loop through each database.

Question 8

You manage an Azure SQL Database that supports a critical financial application. The database is in the General Purpose tier and uses active geo-replication for disaster recovery. You need to automate the process of failing over to the secondary region in case of a regional outage, but only after confirming that the primary is unreachable for more than 5 minutes. Additionally, you need to send an alert to the operations team when the failover occurs. The solution should use Azure services and minimize manual steps. What should you implement?

Accepted Answer

Configure an Azure Monitor metric alert on the 'Deadlock count' metric (or custom metric) with a threshold of 0 for 5 minutes, then use an action group to trigger an Azure Automation runbook that runs the failover PowerShell cmdlet.. Option A is correct because Azure Monitor can detect the primary database unavailability (e.g., through heartbeat metrics) and trigger an alert that invokes an Azure Automation runbook to perform the failover. Options B and C require manual intervention. Option D (Azure Site Recovery) is not designed for SQL Database geo-replication failover.

Answer

Use Azure Logic Apps with a timer trigger to check the database status every minute and initiate failover if unreachable.

Answer

Schedule a SQL Agent job on the secondary to run a script that checks connectivity and fails over.

Answer

Configure Azure Site Recovery for the SQL database.

Question 9

You are responsible for managing a fleet of 20 Azure SQL Databases used by different departments. Each database has its own schema and data. You need to automate the creation of weekly exports of each database to a BACPAC file stored in a specific Azure Storage container. The exports should be done outside business hours (Sundays at 2 AM). You also need to ensure that the export process does not impact production performance. What is the most straightforward and cost-effective solution?

Accepted Answer

Create an Azure Automation runbook that uses the Export-AzSqlDatabase cmdlet for each database, and schedule the runbook to run weekly on Sundays at 2 AM.. Option A is correct because Azure SQL Database's export to BACPAC can be automated using Azure Automation runbooks with the Export-AzSqlDatabase cmdlet, and can be scheduled. Options B and C require additional services and complexity. Option D is not automated.

Answer

Use Elastic Database Jobs to run a SQL script that exports the database using the BACPAC export command.

Answer

Use Azure Data Factory with a copy activity to export the database to BACPAC via the SQL Server Import/Export service.

Answer

Manually export each database using Azure Portal once a week.

Question 10

You are a database administrator for a company that uses Azure SQL Managed Instance. You need to automate the process of patching the operating system and SQL Server engine for all managed instances in a specific region. The automation must minimize downtime and ensure high availability. Which two actions should you include in your automation strategy?

Accepted Answer

Configure a maintenance window using Azure Portal or PowerShell.. Option A is correct because Azure SQL Managed Instance automatically applies updates, but you can configure a maintenance window to control the timing. Option C is correct because using a failover group with read-scale replicas ensures that during patching, traffic can be redirected to a secondary replica, minimizing downtime. Option B is incorrect because manual patching is not supported for Azure SQL Managed Instance; Microsoft manages patching. Option D is incorrect because scaling up is not related to patching automation. Option E is incorrect because enabling Azure Update Manager is not applicable to Azure SQL Managed Instance; it's for IaaS VMs.

Answer

Use Azure Policy to automatically scale up the instance before patching.

Answer

Create an Azure Automation runbook to manually apply OS and SQL patches.

Answer

Enable Azure Update Manager for the managed instances.

Question 11

You are responsible for automating index maintenance and statistics updates across multiple Azure SQL Databases in an elastic pool. Each database has varying workloads and sizes. The automation must run weekly during low-traffic periods and should not degrade performance for critical applications. Which three actions should you take to implement a reliable and efficient automation solution?

Accepted Answer

Create an Azure Automation account and schedule a PowerShell runbook that connects to each database and runs index maintenance scripts.. Option A is correct because Azure Automation with PowerShell runbooks allows scheduling and executing T-SQL scripts across databases. Option C is correct because Elastic Database Jobs can target a group of databases in an elastic pool. Option E is correct because Azure SQL Database's automatic tuning can handle index and statistics maintenance, reducing manual effort. Option B is incorrect because enabling auto-failover groups does not automate maintenance tasks. Option D is incorrect because SQL Agent jobs are not available in Azure SQL Database (only in Managed Instance).

Answer

Configure SQL Agent jobs in each database to run the maintenance scripts.

Answer

Enable auto-failover groups for all databases to automate failover during maintenance.

Question 12

You are designing an automation solution to deploy Azure SQL Database schema changes using CI/CD pipelines. The solution must support rollback if a deployment fails and must integrate with Azure DevOps. Which two components should you include in your pipeline?

Accepted Answer

Azure SQL Database project with DACPAC deployment.. Option B is correct because Azure SQL Database deployment task in Azure DevOps can execute schema scripts. Option E is correct because using Azure SQL Database projects with DACPAC allows version-controlled schema deployment. Option A is incorrect because Azure Automation runbooks are more for operational tasks, not CI/CD. Option C is incorrect because Azure SQL Managed Instance is a different service. Option D is incorrect because Data Factory is for ETL, not schema deployment.

Answer

Azure Automation runbook to execute T-SQL scripts.

Answer

Azure Data Factory pipeline to copy schema changes.

Answer

A PowerShell script that uses Invoke-SqlCmd to deploy to Azure SQL Managed Instance.

Question 13

Your company uses Azure SQL Managed Instance for a critical application. You need to automate the process of rebuilding all indexes that have fragmentation above 30% across all databases on the instance. The automation must run every Sunday at 2:00 AM and must log the results to a table. Which three steps should you take to implement this automation?

Accepted Answer

Create a logging table in a database to record start time, end time, and status.. Option A is correct because SQL Agent can run T-SQL jobs on Managed Instance. Option C is correct because creating a stored procedure to check fragmentation and rebuild indexes is a standard approach. Option D is correct because logging to a table is necessary for tracking. Option B is incorrect because Azure Automation runbooks cannot directly connect to SQL Managed Instance easily without hybrid workers. Option E is incorrect because Elastic Jobs is for Azure SQL Database, not Managed Instance.

Answer

Use an Azure Automation runbook with the PowerShell module SqlServer to connect and run the maintenance.

Answer

Use Elastic Database Jobs to run the script across all databases.

Question 14

You are a database administrator for a large e-commerce company. The company uses Azure SQL Database in a Business Critical tier for its transactional systems. The database is part of an elastic pool with multiple databases. You need to automate the process of purging old data from a specific table (OrderHistory) that grows by 5 GB per day. The data must be retained for 90 days. After 90 days, the data should be moved to an Azure Blob Storage archive for long-term storage. The automation must run daily at midnight and must minimize impact on the transactional workload. Additionally, the solution should be cost-effective and require minimal manual intervention. What should you do?

Accepted Answer

Create a stored procedure that deletes data older than 90 days and exports it to Blob Storage using PolyBase. Schedule the procedure using Elastic Database Jobs.. Option B is the correct answer. Elastic Database Jobs can run a stored procedure that deletes data older than 90 days and exports it to Blob Storage using BULK INSERT or PolyBase. This approach is designed for minimal impact and uses Azure's PaaS capabilities. Option A (Azure Data Factory) is more suitable for complex ETL, but for simple purge and archive, Elastic Jobs is simpler and more cost-effective. Option C (Azure Automation runbook) would require additional components like Hybrid Runbook Worker and may introduce latency. Option D (SQL Agent) is not available in Azure SQL Database.

Answer

Create a SQL Agent job on the database to run a cleanup script at midnight.

Answer

Use Azure Automation with a PowerShell runbook that connects to the database, deletes old data, and uploads it to Blob Storage using AzCopy.

Answer

Use Azure Data Factory with a scheduled trigger to copy data older than 90 days to Blob Storage and then delete it from the table.

Question 15

You are a database administrator for a financial services company. The company has multiple Azure SQL Managed Instances in different regions for disaster recovery. Each Managed Instance hosts several databases. You need to automate the process of backing up all databases and copying the backup files to a central Azure Blob Storage account for long-term retention. The backup must be taken daily at 10 PM local time for each region. The solution must be resilient to regional outages and must not use native backup retention more than 7 days. Additionally, you must ensure that backup files are encrypted at rest and in transit. What should you do?

Accepted Answer

Create an Azure Automation account in each region with a PowerShell runbook that connects to the local Managed Instance, performs a full database backup to a local blob container, and then copies the backup file to the central storage account using AzCopy. Schedule the runbook to run daily at 10 PM local time.. Option A is correct. Azure Automation runbooks with PowerShell can schedule backup commands using the BACKUP TO URL command (which supports native SQL Server backup to Azure Blob Storage). This allows you to copy backups to a central storage account. Using Azure Automation ensures resiliency and can be scheduled per region. Option B is incorrect because SQL Agent is available on Managed Instance, but it would require managing schedules across instances. Option C is incorrect because Elastic Database Jobs is not available for Managed Instance. Option D is incorrect because Azure Backup for SQL Server is for IaaS VMs, not Managed Instance.

Answer

Configure a SQL Agent job on each Managed Instance to perform backups to the central storage account directly using BACKUP TO URL.

Answer

Use Elastic Database Jobs to schedule backups across all databases in all Managed Instances.

Answer

Enable Azure Backup for SQL Server in Azure Backup vault and configure backup policies for each Managed Instance.