CCNA Configure and manage automation of tasks Questions — Page 2 of 2

MCQeasy

You need to automate the deployment of Azure SQL Database logical servers and databases using Bicep. What is the best practice for storing the administrative password securely?

A.Reference the password from Azure Key Vault using the getSecret function

B.Use the adminPassword property with a generated password

C.Use an environment variable in the deployment script

D.Store the password as a plain text parameter in the Bicep file

AnswerA

Referencing the password via Key Vault's getSecret function keeps the credential out of the Bicep template and its deployment history, satisfying the requirement to store the administrative password securely. The secret is resolved at deployment time from Key Vault, so plaintext never appears in source control or ARM deployment records.

Why this answer

Azure Key Vault is the recommended secure storage for secrets like administrative passwords in Azure deployments. Using the `getSecret` function in Bicep allows you to reference a secret from Key Vault at deployment time without exposing the password in the Bicep file or deployment logs, aligning with Azure security best practices and the principle of least privilege.

Exam trap

The trap here is that candidates may think environment variables or generated passwords are acceptable for automation, but the DP-300 exam specifically tests the secure secret management pattern using Azure Key Vault with Bicep's `getSecret` function, not just any method of hiding the password.

How to eliminate wrong answers

Option B is wrong because using the `adminPassword` property with a generated password, while functional, does not securely store the password; it is typically passed as a parameter and can be exposed in deployment logs or outputs. Option C is wrong because environment variables in the deployment script are not encrypted and can be captured in process dumps or logs, failing to meet security compliance requirements. Option D is wrong because storing the password as a plain text parameter in the Bicep file directly exposes the secret in source control and deployment history, violating fundamental security practices.

Practice this question →

MCQmedium

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?

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

B.Transparent Data Encryption is disabled.

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

D.The server’s TDE protector is changed to a customer-managed key from Azure Key Vault.

AnswerD

The cmdlet sets a customer-managed key as the server's TDE protector, satisfying the requirement to move encryption key custody from Microsoft-managed to your own Azure Key Vault. The protector must reside in the same region as the server, and the key's permissions grant the server access for wrap and unwrap operations.

Why this answer

The PowerShell command Set-AzSqlServerTransparentDataEncryptionProtector with -Type AzureKeyVault changes the server-level TDE protector to a customer-managed key stored in Azure Key Vault. This is the documented cmdlet for configuring Bring Your Own Key (BYOK) TDE at the Azure SQL logical server level, and the result is that the server's TDE protector is now the specified Key Vault key.

Exam trap

The trap is confusing server-level and database-level TDE operations — candidates may pick the database-scoped answer because the question mentions a database name, but the cmdlet and the TDE protector are server-scoped objects.

How to eliminate wrong answers

Option A is wrong because the question asks for the result of the command, not a failure scenario — while permissions are required, the cmdlet is designed to succeed when the service principal has been granted wrapKey/unwrapKey/get permissions on the key vault, and the question does not indicate a permissions problem. Option B is wrong because setting a TDE protector does not disable TDE — TDE remains enabled and is now backed by a customer-managed key rather than the service-managed key. Option C is wrong because the cmdlet operates at the server level (Set-AzSqlServerTransparentDataEncryptionProtector), not the database level; the TDE protector is a server-scoped object, and databases inherit it.

Practice this question →

MCQhard

You are a database administrator for a multinational corporation that uses Azure SQL Managed Instance. The instance is part of a failover group for disaster recovery. You need to automate the process of testing the failover group by performing a planned failover to the secondary region and then failing back. The test must be performed monthly during a maintenance window. The automation must ensure that the failover group is in a healthy state before and after the test and must log the results to a table. What should you do?

A.Use Elastic Database Jobs to run T-SQL that initiates failover and logs results.

B.Create an Azure Automation runbook with PowerShell that uses the Az.Sql module to perform failover and log to a table.

C.Use Azure Data Factory to execute a stored procedure that performs failover.

D.Create a SQL Agent job with T-SQL that performs the planned failover using ALTER AVAILABILITY GROUP and logs the results to a table.

AnswerB

Correct. Azure Automation runbooks can be scheduled to run monthly, use the Az.Sql module to perform planned failover and failback, and log results to a table. Although not entirely self-contained, it is the only viable option.

Why this answer

Azure Automation runbooks with PowerShell and the Az.Sql module can programmatically invoke failover group operations (e.g., Invoke-AzSqlDatabaseFailoverGroup), check health state, and log results to a table (e.g., via AzTable or SQL). Automation runbooks support schedules (monthly maintenance window), managed identities for authentication, and full scripting control to verify the failover group is healthy before and after. This is the canonical Azure-native approach for orchestrating cross-region failover tests.

Exam trap

DP-300 often tests the distinction between control-plane operations (Azure PowerShell/CLI/REST) and data-plane T-SQL — candidates pick SQL Agent or Elastic Jobs because they think failover can be done in T-SQL, but failover group operations require Azure control-plane APIs.

How to eliminate wrong answers

Option A is wrong because Elastic Database Jobs are designed to run T-SQL across multiple databases (e.g., sharded workloads) and do not have native cmdlets to initiate Azure SQL Managed Instance failover group operations — failover is a control-plane operation, not a T-SQL operation. Option C is wrong because Azure Data Factory is an ETL/orchestration service for data movement and transformation; it can run stored procedures but cannot perform control-plane failover group operations and is not designed for this automation. Option D is wrong because SQL Agent jobs run T-SQL inside the instance, and while ALTER AVAILABILITY GROUP exists for availability groups, failover group failover in Azure SQL MI is performed via Azure control-plane APIs (PowerShell/CLI/REST), not T-SQL — SQL Agent also cannot easily authenticate to the Azure control plane.

Practice this question →

MCQmedium

You administer an Azure SQL Database that must run a nightly index maintenance job. The job must execute T-SQL against the database, and you want to minimize administrative overhead by avoiding external schedulers or custom code. You create an elastic job agent and a target group that includes the database. What should you do next to define the T-SQL command that the job runs?

A.Create an Azure Automation runbook that connects to the database and runs the T-SQL.

B.Create a stored procedure in the master database of the logical server.

C.Create a SQL Server Agent job on the logical server that hosts the database.

D.Create a job step in the elastic job that contains the T-SQL script.

AnswerD

A job step is the unit that holds the T-SQL to be executed by the elastic job agent. After creating the job, you add one or more job steps, each with a command and a target group. This directly meets the requirement without external schedulers, because the agent handles scheduling and execution against the target database.

Why this answer

Elastic jobs consist of a job, one or more job steps, and a target group. The job step holds the T-SQL command that the agent executes against the target databases. Creating a job step is the necessary next action to define the maintenance script, while the other options either use unsupported features or add unnecessary external components.

Exam trap

The trap here is assuming that SQL Server Agent is available in Azure SQL Database, when it is not; scheduling T-SQL natively requires elastic jobs or an external orchestrator.

Practice this question →

MCQmedium

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?

A.Create an Azure Automation runbook that uses the Export-AzureRmSqlDatabase cmdlet and schedule it to run nightly.

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

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

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

AnswerA

This is correct because Azure Automation provides a native scheduler for PowerShell runbooks, and Export-AzureRmSqlDatabase creates a BACPAC file (a transactionally consistent backup) in Azure Storage. The cmdlet orchestrates the export through the Azure SQL Database management plane, ensuring a consistent snapshot of the live database. Running nightly via schedule meets the backup requirement without manual intervention, making it the simplest and most reliable option for automated database export.

Why this answer

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.

Exam trap

The trap here is that candidates often overcomplicate the solution by choosing Azure Data Factory or Logic Apps, thinking they need a full ETL tool, when a simple scheduled PowerShell runbook is the most direct and low-overhead method for a consistent database export to storage.

How to eliminate wrong answers

Option B is wrong because Elastic Database Jobs are designed for executing T-SQL scripts across multiple databases (e.g., schema changes, index maintenance), not for exporting a database to a storage account; they lack native support for storage account interactions. Option C is wrong because Azure Data Factory pipelines with Copy activity can export data, but they require building a full pipeline with linked services and datasets, which introduces more administrative overhead than a simple scheduled runbook for a straightforward export task. Option D is wrong because Azure Logic Apps with the SQL Server connector can perform operations like querying or inserting data, but they do not support exporting an entire database to a storage account as a consistent backup; the connector lacks the necessary export functionality.

Practice this question →

MCQmedium

You are a database administrator for a healthcare company that uses Azure SQL Database. You need to automate the process of exporting a BACPAC file to Azure Blob Storage every day at 3:00 AM. You want to minimize development effort and use an Azure-native service. What should you use?

A.SQL Server Agent job on an Azure virtual machine that runs a T-SQL script using BACKUP DATABASE TO URL.

B.Azure Logic Apps with a recurrence trigger and a SQL Server connector to execute an export command.

C.Azure Automation runbook with a PowerShell script that uses the New-AzSqlDatabaseExport cmdlet.

D.Azure Data Factory pipeline with a copy activity that exports the database to Blob Storage.

AnswerC

Azure Automation provides a serverless way to run PowerShell scripts on a schedule. The New-AzSqlDatabaseExport cmdlet initiates a BACPAC export to Azure Blob Storage. This requires minimal development effort, as the cmdlet handles the export, and Automation manages the schedule and execution. It is an Azure-native solution that directly meets the requirement.

Why this answer

Azure Automation runbooks can run PowerShell on a schedule, and the New-AzSqlDatabaseExport cmdlet is designed to export an Azure SQL Database to a BACPAC in Blob Storage. This combination is Azure-native, requires minimal code, and directly automates the daily export. Other options either use unsupported commands, require more development, or do not produce BACPAC files.

Exam trap

The trap here is assuming that any data movement service can create a BACPAC, when only specific cmdlets or the portal export feature produce that format.

Practice this question →

MCQeasy

You are automating the creation of an Azure SQL database. You need to ensure that the deployment is idempotent using Azure Resource Manager (ARM) templates. Which deployment mode should you use?

A.Complete

B.Automatic

C.Incremental

D.Validate

AnswerC

Incremental mode deploys resources without deleting existing ones, so re-running the same ARM template leaves unchanged resources intact and creates only missing ones. This satisfies the idempotency requirement, whereas Complete mode would delete resources absent from the template, breaking repeated deployments.

Why this answer

Incremental deployment mode is the default ARM template mode and is idempotent: resources declared in the template are created or updated, and existing resources not in the template are left untouched. This makes repeated deployments safe and consistent, which is exactly what idempotency requires. Complete mode, by contrast, deletes resources in the resource group that are not in the template, so it is not idempotent in the safe sense.

Exam trap

DP-300 often tests the misconception that Complete mode is 'more thorough' and therefore better for automation, when in fact Complete mode's deletion behavior breaks idempotency and can destroy resources not in the template.

How to eliminate wrong answers

Option A is wrong because Complete mode deletes any resources in the target resource group that are not defined in the template, which is destructive and not idempotent for repeated deployments. Option B is wrong because 'Automatic' is not a valid ARM deployment mode; the valid modes are Incremental, Complete, and Validate. Option D is wrong because Validate mode only checks template syntax and permissions without actually deploying resources, so it cannot be used to create or update a database.

Practice this question →

MCQhard

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?

A.Create an Elastic Database Job with step scripts for each maintenance task, targeting all databases, and configure retry logic.

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

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

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

AnswerA

Elastic Database Jobs run T-SQL against every database in the target group from a single job agent, satisfying the centralised, low-touch requirement across 100 databases in 10 pools. Per-database execution isolates failures, so one database erroring does not halt the others, and built-in retry logic plus job history logging meet the resilience and centralised logging constraints.

Why this answer

Elastic Database Jobs are purpose-built for running T-SQL across many Azure SQL databases, including those in elastic pools across regions. You can define step scripts for DBCC CHECKDB, index rebuilds, and statistics updates, target all databases, and configure retry logic so a failure on one database doesn't stop the others — meeting the automation, centralized logging, and resilience requirements.

Exam trap

DP-300 often tests the misconception that Azure SQL Database supports SQL Agent jobs like SQL Server — candidates pick SQL Agent jobs, forgetting that Azure SQL Database is PaaS and lacks SQL Agent.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database does not expose SQL Agent; SQL Agent is only available on SQL Server (IaaS/Managed Instance), so you cannot create SQL Agent jobs on Azure SQL Database servers. Option C is wrong because ADF pipelines with ForEach can call stored procedures but lack native per-database retry/continue-on-error semantics and centralized job logging tailored to database maintenance. Option D is wrong because Azure Automation runbooks with Invoke-SqlCmd require managing credentials, handling connectivity per database, and lack built-in job history and retry semantics for hundreds of databases.

Practice this question →