CCNA Automation Tasks Questions — Page 3 of 3

151

MCQhard

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?

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

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

C.The job agent itself needs a server-level login.

D.The credential is not mapped to a server login in the master database.

AnswerD

The credential must be mapped to a server login that can access the target server.

Why this answer

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.

Practice this question →

152

MCQhard

You are a database administrator for a gaming company that uses Azure SQL Database with multiple databases in a single logical server. The databases experience varying load patterns. You need to automate the process of scaling up or down the DTU/ vCore purchasing model based on performance metrics. Specifically, if average DTU consumption exceeds 80% for 10 minutes, you want to scale up the database tier. If consumption drops below 20% for 30 minutes, you want to scale down. The automation must use Azure native services and should not require custom scripting. What should you do?

A.Create an Azure Automation runbook with PowerShell that queries performance metrics and scales the database accordingly.

B.Set up an Azure Logic App with a trigger on Azure Monitor metrics (e.g., when average DTU > 80% for 10 minutes) and an action to update the database tier via Azure Resource Manager connector.

C.Use Elastic Database Jobs to run T-SQL that alters the database service tier based on scheduled times.

D.Create an Azure Function app that uses the SQL Server metrics API to scale the database.

AnswerB

No custom scripting needed, uses native connectors.

Why this answer

Azure Logic Apps natively integrate with Azure Monitor metrics (e.g., average DTU consumption) and can trigger an action to update the database tier via the Azure Resource Manager connector. This provides a no-code / low-code solution that meets the requirement of not requiring custom scripting. Option A is incorrect because Azure Automation runbooks require PowerShell scripting.

Option C is incorrect because Elastic Database Jobs cannot directly change service tiers based on metrics; they are for executing T-SQL scripts. Option D is incorrect because Azure Functions require custom code.

Practice this question →

153

Multi-Selecteasy

Which TWO of the following are native options to automate index maintenance on Azure SQL Database? (Select exactly two.)

Select 2 answers

A.Create Elastic Database Jobs that run index maintenance T-SQL scripts.

B.Use Azure Automation PowerShell runbooks to invoke index rebuilds.

C.Enable automatic tuning with 'CREATE INDEX' and 'DROP INDEX' options.

D.Schedule SQL Agent jobs with ALTER INDEX statements.

E.Use Azure Data Factory to copy data and rebuild indexes.

AnswersA, C

Elastic Jobs can automate T-SQL index maintenance across databases.

Why this answer

Options A and C are correct. Elastic Database Jobs (Option A) allow running custom T-SQL scripts for index maintenance across databases. Automatic tuning (Option C) can automatically create and drop indexes based on workload patterns, which is a form of index maintenance.

Option B is incorrect because Azure Automation PowerShell runbooks are not a native option for Azure SQL Database index maintenance; they would require additional setup and do not directly execute T-SQL. Option D is incorrect because SQL Agent jobs are not available in Azure SQL Database (they are available in SQL Server on-premises or on Azure VMs). Option E is incorrect because Azure Data Factory is primarily for data movement and orchestration, not for direct index maintenance.

Practice this question →

154

MCQhard

Your company uses GitHub Actions to deploy changes to Azure SQL Database. You need to ensure that rollbacks can be performed automatically if a deployment fails. Which strategy should you implement?

A.Use Azure App Service deployment slots for the database

B.Use GitHub Actions deployment environments with approval gates

C.Create a database copy or snapshot before each deployment

D.Use Azure SQL Managed Instance point-in-time restore

AnswerC

A snapshot or copy can be used to restore the previous state if deployment fails.

Why this answer

The correct strategy is to create a database copy or snapshot before each deployment. This allows you to revert the database to its pre-deployment state if the deployment fails, enabling automated rollback. Option A (Azure App Service deployment slots) is designed for app deployment, not databases.

Option B (approval gates) controls when deployments proceed but does not automate rollback. Option D (point-in-time restore) can restore to a point in time but is not a deployment-specific rollback method and may not cover the exact previous state.

Practice this question →

155

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

Key Vault integration securely retrieves secrets during deployment.

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 →

156

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 command sets the protector to a key vault key.

Why this answer

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.

Practice this question →

157

Multi-Selecthard

You are designing an automated backup strategy for Azure SQL Database. Which TWO actions satisfy a requirement to maintain daily backups for 30 days and ensure recoverability if the Azure region becomes unavailable?

Select 2 answers

A.Enable geo-redundant backup storage for the server.

B.Deploy a read-scale replica in a different Azure region.

C.Set point-in-time restore retention to 30 days.

D.Set up a long-term retention policy with a retention of 30 days.

E.Configure active geo-replication to a secondary region.

AnswersA, C

Geo-redundant backup storage (RA-GRS) replicates backups to a paired region, ensuring regional recoverability.

Why this answer

Enabling geo-redundant backup storage (RA-GRS) for Azure SQL Database ensures that automated backups are replicated to a paired Azure region. This satisfies the requirement for recoverability if the primary region becomes unavailable, as backups can be restored from the secondary region. Combined with setting point-in-time restore retention to 30 days (Option C), you maintain daily backups for the full 30-day retention period, meeting both requirements.

Exam trap

The trap here is confusing high-availability features (like geo-replication or read-scale replicas) with backup retention and storage redundancy, leading candidates to select options that provide failover capability but do not satisfy the backup retention or geo-redundant backup storage requirements.

Practice this question →

158

MCQhard

A company uses Azure SQL Database with elastic pools. They need to automatically scale up the pool DTU when CPU usage exceeds 80% for 5 minutes and scale down when below 20% for 10 minutes. Which solution should they implement?

A.Use Elastic Database Transactions to handle scaling

B.Configure autoscale settings on the elastic pool in the Azure portal

C.Use Azure Automation runbook with PowerShell to check metrics and scale

D.Create a SQL Agent job to monitor and alter the pool

AnswerC

Azure Automation runbooks can use Get-AzMetric and Set-AzSqlElasticPool to implement custom autoscaling.

Why this answer

Azure SQL Database autoscale is not natively supported; scaling must be done programmatically. Azure Automation with PowerShell runbooks can query metrics and trigger scaling. SQL Agent jobs are not available in Azure SQL Database.

Elastic Database Transactions are for distributed transactions, not scaling. Azure Logic Apps can also be used but are more complex for this scenario.

Practice this question →

159

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 can be scheduled to run monthly and use the Az.Sql module to perform a planned failover of the failover group and log results to a table. Although this relies on an external Azure service, it is the only viable option to automate failover for a failover group because T-SQL does not support failover group failover. Option A is incorrect because Elastic Database Jobs are not available for Managed Instance.

Option C is incorrect because Data Factory is not designed for failover orchestration. Option D is incorrect because the ALTER AVAILABILITY GROUP command cannot be used to failover a failover group in Azure SQL Managed Instance; failover groups require Azure-level operations.

Exam trap

Candidates may assume that T-SQL can be used to failover a failover group, but in Azure SQL Managed Instance, failover groups require Azure-level PowerShell or REST API.

Practice this question →

160

MCQeasy

You need to automate the backup of an Azure SQL Managed Instance to a storage account every 4 hours. Which feature should you use?

A.Built-in automated backups

B.SQL Agent jobs

C.Long-term retention (LTR) policies

D.Azure Automation with PowerShell runbooks

AnswerD

Azure Automation runbooks can schedule and execute backup commands for Azure SQL Managed Instance.

Why this answer

Azure SQL Managed Instance has built-in automated backups, but they run automatically and cannot be customized to run every 4 hours or target a specific storage account. Long-term retention (LTR) policies are for retaining backups beyond the automated backup period, not for scheduling. SQL Server Agent is available in Azure SQL Managed Instance and can be used to schedule jobs, but it does not provide a direct way to back up to an Azure storage account every 4 hours.

Azure Automation with PowerShell runbooks can invoke backups using REST APIs or T-SQL, and schedule them precisely, making it the best choice for this requirement.

Practice this question →

161

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 updates resources in the resource group based on the template, making it idempotent.

Why this answer

'Incremental' mode processes the template as an incremental update to the resource group, making it idempotent. Only resources specified in the template are added or modified; existing resources not in the template are left unchanged. 'Complete' mode deletes resources not in the template, which is not idempotent. 'Validate' mode only validates without deploying, and 'Automatic' is not a valid ARM deployment mode.

Practice this question →

162

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 Jobs are designed for multi-database maintenance with resilience.

Why this answer

Elastic Database Jobs (formerly Elastic Database Jobs) allows you to run T-SQL scripts across many databases with built-in retry logic, error handling, and scheduling. It is designed for such maintenance tasks over elastic pools. Option B (SQL Agent) is not available in Azure SQL Database because it is a PaaS service with no SQL Agent.

Option C (Azure Data Factory) is primarily for data movement and transformation, not for executing T-SQL maintenance scripts across databases. Option D (Azure Automation runbooks) can run PowerShell, but would require custom error handling and retry logic, and might be less efficient for large numbers of databases.

Practice this question →

163

MCQmedium

A company uses Azure SQL Managed Instance. They need to automate index maintenance for all databases in the instance. The solution must minimize administrative overhead and use built-in Azure features. What should you do?

A.Use Azure Automation with a PowerShell runbook that connects to each database and runs index maintenance.

B.Configure a SQL Agent job on the instance to run index maintenance on the master database.

C.Create an elastic job agent with a T-SQL script for index maintenance targeting all databases.

D.Use Azure Data Factory to schedule a stored procedure execution for each database.

AnswerC

Elastic job agent is designed for automating tasks across databases in a managed instance with minimal overhead.

Why this answer

An elastic job agent is a built-in Azure feature designed specifically for automating administrative tasks across multiple databases in Azure SQL Managed Instance. It allows you to create a T-SQL script for index maintenance and target all databases in the instance with minimal overhead, as it manages scheduling, retries, and target group membership natively.

Exam trap

The trap here is that candidates might confuse SQL Agent jobs (which are database-scoped in Managed Instance) with the ability to run cross-database tasks, or assume Azure Automation is the only built-in option, when in fact elastic job agents are the native, low-overhead solution for multi-database automation in Azure SQL Managed Instance.

How to eliminate wrong answers

Option A is wrong because Azure Automation with a PowerShell runbook introduces unnecessary administrative overhead and is not a built-in Azure SQL feature; it requires managing runbook accounts, modules, and authentication separately, and does not integrate natively with SQL Managed Instance's database engine. Option B is wrong because configuring a SQL Agent job on the master database only runs maintenance on that single database, not on all databases in the instance; SQL Agent jobs in Managed Instance are scoped to the database where they are created, unless explicitly targeting others via cross-database queries, which is not a built-in automation feature for all databases. Option D is wrong because Azure Data Factory is an orchestration service for data movement and transformation, not a built-in tool for automating index maintenance on SQL Managed Instance; it would require complex pipeline design and external triggers, adding overhead rather than minimizing it.

Practice this question →