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

Question 1

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.. The command `Set-AzSqlServerTransparentDataEncryptionProtector` configures the TDE protector at the server level. By specifying the `-Type AzureKeyVault` and `-KeyId` parameters, it sets the server's TDE protector to a customer-managed key stored in Azure Key Vault. Therefore, Option D is correct. Option A is incorrect because the command does not fail due to permissions; it succeeds if permission is granted. Option B is incorrect because TDE remains enabled; only the protector key is changed. Option C is incorrect because the command updates the server's TDE protector, not 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 2

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.. The auditActionsAndGroups include SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP, which audit successful and failed logins respectively. Option A is wrong because data modification statements (DML) 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 3

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 4

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. 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 Elastic Database Jobs are for ad-hoc scripts, not for complex deployments with rollback. Option C is wrong because SQL Server Management Studio (SSMS) is a manual tool. 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 5

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

Accepted Answer

Serverless compute tier. 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

Question 6

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 7

You need to automatically send an email notification when an Azure SQL Database reaches 80% storage usage. What should you configure?

Accepted Answer

Azure Monitor alert with action group. Azure Monitor alerts can monitor metrics like 'storage_percent' for Azure SQL Database and trigger an action group configured to send email notifications. Option B (CDC with Logic Apps) captures data changes, not storage metrics. Option C (Elastic Database Job) is for executing T-SQL scripts across databases, not for alerts. Option D (SQL Agent Mail) is not available in Azure SQL Database.

Answer

Change Data Capture (CDC) with Logic Apps

Answer

Elastic Database Job with sp_send_dbmail

Answer

SQL Agent Mail

Question 8

Your company has a policy to automatically pause Azure SQL Databases during non-business hours to save costs. You need to implement this with minimal administrative overhead. What should you use?

Accepted Answer

Serverless compute tier with auto-pause enabled. The serverless compute tier can be configured to automatically pause the database after a period of inactivity, meeting the requirement with minimal overhead. Option A is incorrect because Elastic Database Jobs require creating a job to pause the database, adding overhead. Option C is incorrect because Azure Logic Apps with the SQL connector would require building a workflow, adding complexity. Option D is incorrect because Azure Automation runbooks require scripting and maintenance, which is more overhead than the serverless auto-pause feature.

Answer

Elastic Database Job that pauses the database

Answer

Azure Logic Apps with SQL connector to execute pause/resume

Answer

Azure Automation runbook with a schedule to pause/resume

Question 9

You have an Azure SQL Database that stores sensitive data. You need to automatically classify and apply sensitivity labels to new columns as they are added. What should you use?

Accepted Answer

Microsoft Purview Information Protection. Microsoft Purview Information Protection enables automatic classification and labeling of sensitive data in Azure SQL Database through its data classification capabilities. It can be configured to automatically detect and apply sensitivity labels to new columns based on built-in or custom rules. Option B (Azure Policy) can enforce compliance but does not perform automatic classification of data within the database. Option C (Dynamic Data Masking) obscures sensitive data but does not classify or label it. Option D (Azure Automation with PowerShell) requires custom scripting and does not provide built-in automatic classification integration for new columns.

Answer

Azure Policy with custom policy definition

Answer

Dynamic Data Masking

Answer

Azure Automation with PowerShell script to run sp_addsensitivityclassification

Question 10

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 11

You have an Azure SQL Database that uses a failover group for high availability. You need to automate the failover to the secondary region during a planned maintenance window. What is the best approach?

Accepted Answer

Use Azure CLI or PowerShell to invoke planned failover. Azure SQL Database failover groups support planned failover via Azure CLI or PowerShell, which can be automated. Option B is wrong because Azure Automation runbooks can also be used, but the CLI is more direct. Option C is wrong because Elastic Jobs are not for failover. Option D is wrong because Azure Traffic Manager is for DNS-level traffic routing, not database failover.

Answer

Create an Azure Automation runbook that uses REST API

Answer

Schedule an Elastic Database Job to execute a failover script

Answer

Configure Azure Traffic Manager to route traffic to secondary

Question 12

Which TWO Azure services can be used to automate the deployment of schema changes to Azure SQL Database?

Accepted Answer

Azure DevOps Services. Options D and E are correct. Azure DevOps Services provides pipeline capabilities for CI/CD to automate deployment of schema changes. Azure Automation can be used with runbooks to execute T-SQL scripts on a schedule or event trigger. Option A (Azure Portal) is a manual interface. Option B (SSMS) is a manual tool. Option C (Elastic Database Jobs) is for ad-hoc execution, not a full deployment pipeline.

Answer

Azure Portal

Answer

SQL Server Management Studio (SSMS)

Answer

Elastic Database Jobs

Question 13

Which THREE components are required to set up automated monitoring and alerting for an Azure SQL Database using Azure Monitor?

Accepted Answer

Diagnostic setting for the database. The three required components are: a diagnostic setting on the database to send logs and metrics to a Log Analytics workspace, a Log Analytics workspace to store the data, and an alert rule with an action group to notify when conditions are met. A SQL Agent Job (option D) is not required for automated monitoring and alerting; it is used for scheduled tasks. An Azure Automation account (option C) is also not required; it is used for runbooks and process automation, not for monitoring alerts.

Answer

Azure Automation account

Answer

SQL Agent Job

Question 14

Which TWO methods can be used to automatically restart an Azure SQL Database after a maintenance operation?

Accepted Answer

Azure CLI az sql db pause/resume. The Azure CLI `az sql db pause/resume` can be scripted and automated, effectively restarting the database. Option E is also correct because an Azure Automation runbook can use the `Start-AzSqlDatabase` cmdlet (and corresponding `Stop-AzSqlDatabase`) to stop and start the database, which achieves an automated restart. Options B, C, and D are incorrect: B is manual via the portal, C is manual via SSMS, and D's `ALTER DATABASE SET ONLINE` does not restart the database.

Answer

Azure Portal stop/start

Answer

SQL Server Management Studio (SSMS) restart command

Answer

Elastic Database Job with ALTER DATABASE SET ONLINE

Question 15

Refer to the exhibit. You deploy this ARM template for an Azure SQL Database. The deployment succeeds. Which statement about the deployed database is true?

Accepted Answer

The database is provisioned tier. The ARM template specifies `requestedServiceObjectiveName: S2`, which corresponds to a provisioned tier (S2). The `autoPauseDelay` property is only applicable to serverless databases; when used with a provisioned tier, it is ignored and does not cause the deployment to fail. Therefore, the database is deployed as a provisioned tier S2 database. Option A is correct. Option B is incorrect because the database is not serverless. Option C is incorrect because `zoneRedundant` is set to false. Option D is incorrect because `maxSizeBytes` is 5 GB, which is less than 250 GB.

Answer

The database is in serverless tier

Answer

The database is zone redundant

Answer

The database maximum size is 250 GB