CCNA Configure and manage automation of tasks Questions

For Azure SQL Managed Instance, automating database creation with CI/CD integration can be achieved using Elastic Database Jobs (now Elastic Jobs). These jobs can run T-SQL commands such as 'CREATE DATABASE AS COPY OF' to create copies of a template database. Elastic Jobs can be triggered programmatically via REST API or Azure DevOps tasks, enabling seamless integration into a CI/CD pipeline.

Option A (Logic App) does not support database copy operations. Option B (portal feature) requires manual steps. Option D uses PowerShell cmdlets ('az sql db copy' or 'Restore-AzSqlDatabase') that are specific to Azure SQL Database, not Managed Instance; the correct cmdlets for Managed Instance would be 'Restore-AzSqlInstanceDatabase' or T-SQL commands.

Therefore, option C is the most appropriate recommendation.

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.

Azure SQL Database's automatic tuning feature can automatically create and drop indexes based on workload patterns, minimizing administrative overhead and leveraging built-in Azure capabilities. Option B is incorrect because deploying a third-party solution would introduce additional complexity and cost, not leveraging built-in capabilities. Option C is incorrect because although Azure Automation can schedule scripts, it still requires custom scripting and maintenance, increasing overhead compared to fully automated built-in tuning.

Option D is incorrect because scheduling a SQL Agent job requires manual setup and is not a built-in automated feature of Azure SQL Database; SQL Agent is available only in managed instances, not in single databases or elastic pools, and requires custom T-SQL scripting.

Azure SQL Managed Instance automatically applies OS and SQL Server engine updates during the user-configured maintenance window, minimizing downtime by performing patching during off-peak hours. Option B is wrong because Elastic Job agents are used for scheduling T-SQL jobs across multiple databases, not for patching the instance or OS. Option C is wrong because manual patching via the Azure portal is not an automated solution and would require manual effort and cause downtime.

Option D is wrong because Azure Update Manager is designed for Azure VMs and Arc-enabled servers, not for Azure SQL Managed Instance, which has its own built-in patching mechanism.

Options B and C are correct because Azure Monitor Alerts (C) can be configured to monitor performance metrics of Azure SQL Database and trigger actions when thresholds are exceeded. Log Analytics Workspace (B) collects and analyzes the diagnostic logs and metrics, enabling deeper analysis and alert rules. Option A (Azure Sentinel) is a SIEM tool for security monitoring, not primarily for performance alerts.

Option D (Application Insights) is for application-level telemetry, not database-specific performance monitoring. Option E (Azure Advisor) provides recommendations but does not generate real-time alerts.

The most efficient solution because Azure Monitor provides built-in metrics for transactional replication on Azure SQL Managed Instance, such as 'Log Send Queue Size'. By creating a metric alert with a threshold of 300000 KB (which corresponds to approximately 5 minutes of latency at typical throughput), you can be notified automatically when latency exceeds this threshold. This approach uses native Azure Monitoring capabilities without requiring custom jobs or scripts.

Options A, B, and C are less efficient: A requires Elastic Database Jobs and a logic app; B uses a performance counter that is not directly exposed in Azure Monitor for SQL Managed Instance and is more complex to set up; C requires creating a SQL Agent job to check a DMV and send email, which is manual and not as scalable as Azure Monitor alerts.

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.

Azure Automation with a runbook can run PowerShell or Python scripts that execute T-SQL against Azure SQL Database on a schedule. Option A is wrong because Azure Data Factory is focused on data movement and transformation, not direct T-SQL execution. Option B is wrong because Elastic Database Jobs require a job agent and are designed for management tasks across multiple databases, not simple hourly script execution.

Option C is wrong because Azure Logic Apps is geared towards integration workflows, and while it can execute SQL, it is less efficient than Azure Automation for this straightforward scheduling task.

Azure Automation runbooks can schedule a point-in-time restore of the production database to a new database, then rename the databases to swap them, making the restored database the new staging database. This leverages the Hyperscale tier's fast restore and minimizes storage costs by avoiding multiple copies. Alerts can be configured to notify the operations team on failure.

Option A is incorrect because Elastic Database Jobs cannot perform restore operations; they are for running T-SQL scripts across databases, not restoring from backups. Option C is incorrect because Azure Data Factory is an ETL tool and cannot perform point-in-time restore of a database. Option D is incorrect because export/import via BACPAC is slower, more expensive, and does not guarantee point-in-time consistency.

Microsoft Defender for SQL provides built-in SQL injection detection, which can supersede the need for separate Security Alert Policy rules. When Defender for SQL is enabled, it automatically monitors and alerts on SQL injection attempts, so duplicating with a custom alert policy would be redundant and could cause confusion. Option A is incorrect because disabling alerts does not save costs; it weakens security.

Option B is incorrect because vulnerability assessment policies do not override alert rules. Option C is incorrect because there is no known incompatibility between SQL injection alerts and storage account endpoints.

Options B and D are correct. Azure SQL Managed Instance supports automated backups through the service's built-in point-in-time restore (PITR) and long-term retention (LTR) policies (option D). Additionally, you can schedule manual copy-only backups to Azure Blob Storage using T-SQL BACKUP DATABASE TO URL statements via SQL Agent jobs (option B).

Option A is incorrect because Azure Backup does not natively support Azure SQL Managed Instance. Option C is incorrect because Azure Site Recovery is a disaster recovery solution, not a backup service. Option E is incorrect because Azure VM backup is designed for IaaS virtual machines, not for PaaS managed instances.

Azure Logic Apps can trigger scaling actions based on metric thresholds using a metric trigger, and Azure Automation runbooks can execute the scaling commands (such as changing the service tier) via PowerShell or Azure CLI. Together, they provide a cost-effective, built-in solution. Option A (Elastic Database Jobs) is used for scheduled database tasks like index maintenance, not scaling.

Option B (Azure Monitor autoscale) is not directly available for Azure SQL Database; autoscale is only for Azure Virtual Machine scale sets, App Service, etc. Option C (Azure Functions) could also be used, but the recommended combination for this scenario is Logic Apps and Automation runbooks because they are serverless and integrate natively with Azure SQL Database.

The most appropriate solution because Azure Logic Apps provides a recurrence trigger that can be scheduled to run at specific times (e.g., every Sunday at 2:00 AM UTC) and includes built-in retry policies to handle failures, ensuring resilience without custom code. Option B (Azure Automation Runbook) can be scheduled and can include custom error handling for retries, but it requires more manual implementation and is less integrated for simple retry logic. Option C (T-SQL Agent job) is not available in Azure SQL Database; Azure SQL Database does not support SQL Agent jobs natively.

Option D (Azure Function with timer trigger) requires custom retry logic in code, which is more complex than using Logic Apps' built-in retry policy.

Azure Logic Apps can be scheduled to run a workflow that uses the Azure SQL Database REST API (or Azure Resource Manager REST API) to update the service objective (DTU or vCore) of an Azure SQL Database. This enables automated scaling without custom infrastructure. Option A is incorrect because SQL Agent jobs run within the SQL Server context and cannot directly change the service objective; the ALTER DATABASE MODIFY (SERVICE_OBJECTIVE) command is not valid.

Option C is incorrect because Elastic Job agents are designed for executing T-SQL scripts across multiple databases, not for changing service tiers. Option D is incorrect because Azure SQL Database does not support autoscale; scaling must be performed manually or programmatically.

You can create a T-SQL script that updates the job steps to remove linked server references and execute it via Azure Automation runbook. This allows automated execution against the SQL Managed Instance. Option A is incorrect because elastic jobs are used for scheduling tasks across multiple databases, not for modifying SQL Agent jobs directly.

Option C is incorrect because Azure CLI does not have native commands to manage SQL Agent jobs. Option D is incorrect because while Azure Automation can run PowerShell scripts, the T-SQL script approach is more direct and reliable for modifying job steps.

In Azure Automation runbooks, there is no interactive desktop environment, so cmdlets that require a graphical user interface, such as Out-GridView, are not supported. When the script runs in a runbook, Out-GridView fails silently or does not produce any output, causing the subsequent alert logic (which likely depends on the output) to never trigger. Option A is incorrect because the -OutputAs parameter is used with Invoke-SqlCmd to specify the output format, not to return results for alerting.

Option C is incorrect because Invoke-SqlCmd is fully compatible with Azure SQL Database when proper authentication is used. Option D is incorrect because the script runs successfully, indicating the query syntax is valid.

Azure SQL Database active geo-replication supports manual failover only; automatic failover is not available. To automate the process, you can use Azure Automation runbooks that invoke the Start-AzSqlDatabaseFailover cmdlet. This allows for scheduled or monitored failover, supports testing by targeting the secondary replica, and minimizes data loss by ensuring the primary is synchronized before failover.

Option A is incorrect because Azure Traffic Manager performs DNS-level routing, not database failover. Option B is incorrect because Azure Load Balancer is for network traffic distribution, not SQL database failover. Option D is incorrect because geo-replication does not provide automatic failover; failover must be initiated manually or via automation.

When the -ElasticPoolName parameter is specified, the database is moved into the elastic pool, and the -Edition and -RequestedServiceObjectiveName parameters are ignored because the elastic pool's service tier and performance level determine the database's settings. The script runs without errors, but the database remains at Standard S2 because the pool's tier overrides the specified edition and SLO. Option A is incorrect because the command succeeded, indicating the pool exists.

Option B is incorrect because the parameter is correctly spelled. Option D is incorrect because the script succeeded without privilege errors.

Azure SQL Managed Instance fully supports SQL Server Agent, which can schedule Ola Hallengren's IndexOptimize stored procedure for index maintenance across all databases. This approach allows centralized management, scheduling during off-peak hours, and granular control over parallelism and throttling to minimize performance impact. Option A is incorrect because Elastic Database Jobs are not available on Azure SQL Managed Instance; they are designed for Azure SQL Database.

Option B is incorrect because Azure Logic Apps are not suited for executing T-SQL scripts across multiple databases with performance-sensitive throttling. Option C is incorrect because Azure Automation Runbooks would require complex orchestration and lack native scheduling and throttling capabilities compared to SQL Agent.

Options B and D are correct. Azure Automation Hybrid Runbook Worker can execute T-SQL scripts using PowerShell Invoke-SqlCmd on a schedule, making it suitable for automating script execution against Azure SQL Database. Elastic Database Jobs are specifically designed to run T-SQL scripts on a schedule across one or more Azure SQL databases.

Option A is incorrect because Azure Logic Apps with SQL connector is better suited for workflows and not primarily for scheduled T-SQL script execution. Option C is incorrect because Azure Functions, while capable of running code with timer triggers, is not the optimal service for scheduled T-SQL scripts; Elastic Database Jobs is the dedicated solution. Option E is incorrect because Azure Data Factory is focused on data movement and orchestration, not direct scheduled execution of T-SQL scripts.

Using an Azure SQL Database project with DACPAC (data-tier application package) allows version-controlled, repeatable schema deployments and supports rollback by redeploying a previous DACPAC. Option E is correct because the Azure SQL Database deployment task in Azure Pipelines can deploy DACPACs or execute SQL scripts as part of a CI/CD pipeline, enabling rollback through previous pipeline runs. Option A is incorrect: Azure Automation runbooks are designed for operational tasks like incident remediation, not for CI/CD schema deployment.

Option B is incorrect: Azure Data Factory is an ETL service, not for schema change management. Option C is incorrect: although PowerShell with Invoke-SqlCmd can run scripts, it lacks built-in rollback support and is not a CI/CD component; also, Azure SQL Managed Instance is a different deployment option, but the question is about Azure SQL Database.

Options D and E are correct. Enabling SQL Insights (preview) provides intelligent performance monitoring with built-in anomaly detection, and creating an Azure Monitor alert rule on DTU/CPU and storage metrics enables automated notifications when thresholds are exceeded. Option A is incorrect because Power BI is a reporting tool, not designed for real-time alerting on performance anomalies.

Option B is incorrect because Query Store tracks query performance but does not natively support email notifications. Option C is incorrect because SQL Agent alerts are for job scheduling events, not for monitoring performance counters like DTU or CPU.

Azure Automation accounts in each region with PowerShell runbooks can be scheduled to run daily at 10 PM local time. The runbook connects to the local Managed Instance, performs a full database backup to a local blob container (using BACKUP TO URL or similar), and then uses AzCopy to copy the backup file to the central storage account. This approach is resilient to regional outages because each region has its own automation account.

Option A is incorrect because managing SQL Agent jobs manually across instances is less automated and less resilient; also, backing up directly to a central storage account may be affected by network issues. Option C is incorrect because Elastic Database Jobs are not available for Azure SQL Managed Instance. Option D is incorrect because Azure Backup for SQL Server is designed for SQL Server on Azure VMs, not for Managed Instance.

Elastic Database Jobs (option B) is the correct native Azure service for running complex T-SQL scripts on Azure SQL Database on a schedule with logging. It is designed for scheduled execution of T-SQL scripts across one or more databases and provides job logging and history. Option A is incorrect because SQL Agent is not available in Azure SQL Database single databases (only in SQL Server on VMs or Managed Instances).

Option C is incorrect because Azure Data Factory is primarily an ETL service, not optimized for simple scheduled script execution, though it can be used, it is not the native simple solution. Option D is incorrect because Azure Automation Runbooks execute PowerShell, not T-SQL directly; while Invoke-SqlCmd can be used, it is not the native T-SQL scheduling service.

Azure DevOps with database projects and release pipelines provides a CI/CD solution specifically designed for deploying schema changes to Azure SQL Databases. Option B is incorrect because SQL Agent jobs are used for scheduled administrative tasks within a single database, not for multi-database schema deployment automation. Option C is incorrect because Azure Automation Account with PowerShell runbooks is suitable for general automation tasks but lacks native CI/CD capabilities for database schema updates.

Option D is incorrect because Azure Data Factory with copy activity focuses on data movement and transformation, not on executing schema changes.

Azure Logic Apps can automate the failover process by periodically checking the primary database's connectivity. If it remains unreachable for more than 5 minutes, it can initiate a geo-replication failover and send alerts to the operations team. This meets all requirements using Azure services with minimal manual steps.

Option B is incorrect because SQL Agent is not available in Azure SQL Database as a PaaS service. Option C is incorrect because Azure Site Recovery is designed for virtual machines, not for Azure SQL Database geo-replication. Option D is incorrect because the 'Deadlock count' metric is unrelated to database connectivity; it measures deadlocks, not reachability, and the described threshold does not correctly detect unreachability.

Azure Logic Apps can be easily triggered by an Azure Monitor metric alert and can call the Azure SQL Database REST API to update the service tier. This provides a simple, low-code solution for automating scaling based on CPU thresholds. Option B is incorrect because Elastic Database Jobs are designed for executing T-SQL scripts across multiple databases, not for scaling operations.

Option C is incorrect: although Azure Automation Runbooks can be triggered by alerts and use PowerShell, they require more setup and overhead compared to Logic Apps. Option D is also less optimal: Azure Functions can work but Logic Apps offer a more straightforward integration with Azure Monitor alerts and REST APIs for this specific use case.

Azure SQL Managed Instance supports configuring a long-term retention (LTR) backup policy that automatically stores backups in a geo-redundant storage account for up to 10 years, minimizing administrative overhead while supporting point-in-time restore within the retention period. Option B is incorrect because Azure Backup is designed for IaaS VMs and other workloads, not for SQL Managed Instance native backups. Option C is incorrect because scheduling BACPAC exports is not a backup solution — it does not support point-in-time restore and requires manual setup.

Option D is incorrect because using SQL Agent jobs with BACKUP DATABASE TO URL adds complexity and does not integrate with Azure's automated backup management.

SQL Agent job steps have built-in 'Retry attempts' and 'Retry interval (minutes)' settings that allow you to configure automatic retries directly in the job step properties. This is the simplest and most native way to retry a failed job step. Option A is incorrect because a T-SQL loop checking job history is complex and not an automated built-in feature.

Option B is incorrect because Power Automate polling is an external solution that adds unnecessary complexity. Option C is incorrect because Azure Monitor alerts can notify but do not automatically retry the job; they would require additional logic (e.g., webhook to PowerShell) which is not native or as reliable as the built-in retry settings.

To automate the export of an Azure SQL Database to a BACPAC file on a monthly basis, you need a storage location for the file and an automation method. Option B is correct because Azure Automation or an Azure Function can trigger the Export-AzSqlDatabase cmdlet or REST API to perform the export on a schedule. Option E is correct because an Azure Storage account with a container is required to store the resulting BACPAC file.

Option A is incorrect because LTR policy is for automated backups, not export to BACPAC. Option C is incorrect because a SQL Server on Azure VM is not needed; the export is initiated directly against the Azure SQL Database logical server. Option D is incorrect because SSIS is an ETL tool that is not necessary for a simple export operation; the export can be done via PowerShell, CLI, or REST API without SSIS.

Azure Automation Runbooks can be scheduled to execute PowerShell scripts that change the service objective of an Azure SQL Database (e.g., Set-AzSqlDatabase with the -RequestedServiceObjectiveName parameter). This allows scheduling a scale-up before Black Friday and a scale-down after 48 hours, meeting the temporary upgrade requirement. Additionally, Azure Policy can enforce backup retention policies (e.g., 35-day PITR) and specify geo-redundant storage only for critical databases, ensuring compliance and cost minimization.

Option A is incorrect because Elastic Job agents are for executing T-SQL scripts across many databases, not for scheduling one-off scaling actions, and backup retention via portal is not automated. Option B is incorrect because Azure SQL Database's autoscale feature is only available in the serverless compute tier, not for provisioned service objectives like S6/S9, and it adjusts based on workload automatically, not on a scheduled temporary upgrade basis. Option C is incorrect because SQL Server Agent is not available in Azure SQL Database (it's used in SQL Server on-premises or Azure SQL Managed Instance), and ALTER DATABASE MODIFY SERVICE_OBJECTIVE is not supported in Azure SQL Database; scaling requires Azure PowerShell or REST API.