CCNA Configure and manage automation of tasks Questions

Elastic job target groups can include an elastic pool as a target. When the job runs, the agent enumerates all databases in the pool at that time, so databases added later are automatically included. This satisfies the requirement to run on all current and future databases without manual updates.

Elastic Database Jobs run T-SQL against a target group of databases, so a statistics-update script can execute across many Azure SQL databases from one job definition. This satisfies the stem's requirement for cross-database administrative scripting.

On Azure SQL Managed Instance, SQL Server Agent is available and you can create jobs using T-SQL stored procedures in the msdb database or via SQL Server Management Studio. The agent runs automatically, so you simply define the job, steps, and schedule. This is the correct method to automate T-SQL scripts on a schedule.

Azure DevOps Pipelines can integrate with source control to automatically build and deploy a DACPAC (Data-tier Application Package) to Azure SQL Database. The DACPAC contains the schema and can be deployed using SqlPackage.exe. This supports version control, continuous integration, and continuous deployment, making it ideal for repeatable multi-environment deployments. It aligns with DevOps practices and automates the entire process.

PITR retention governs the point-in-time restore window, so 7 days meets the 2-hour PITR requirement for the last week. LTR policies separately retain yearly backups for 5 years, satisfying the stem's long-term retention constraint without extending PITR.

Azure SQL Database long-term retention (LTR) backup policy extends automated backups to up to 10 years, satisfying the requirement for retention beyond the default 7–35 days. LTR stores full backups in Azure Blob storage as read-only copies, independent of the standard retention window, with no manual intervention needed.

GitHub Actions orchestrates pipeline jobs, and the Azure SQL Database deployment action connects to the server and applies schema changes via SqlPackage. This satisfies the CI/CD automation constraint by executing DACPAC or SQL script deployment against Azure SQL Database on each pipeline run.

Planned failover via Azure CLI or PowerShell performs a controlled switchover with zero data loss, letting you schedule it inside the maintenance window. It gracefully drains the primary before promoting the secondary, unlike an unplanned forced failover.

Elastic Database jobs in Azure SQL Database allow you to run T-SQL scripts against a target group of databases on a schedule. They are a built-in feature of Azure SQL Database and do not require an external server or agent. This makes them ideal for automating administrative tasks like index maintenance and statistics updates across one or many databases.

SQL Agent Jobs are the native scheduling mechanism in Azure SQL Managed Instance, supporting recurring T-SQL steps such as executing a stored procedure hourly. Unlike Azure Automation or Elastic Jobs, it runs in-instance with no external orchestrator, satisfying the hourly cleanup requirement directly.

Elastic Database Jobs run T-SQL on a defined target group, so scheduling index rebuilds across a set of databases is a supported maintenance scenario. This satisfies the stem by automating recurring index maintenance without per-database scripting.

Set-AzSqlDatabase modifies properties of an existing Azure SQL Database, including the requested service objective and edition via parameters such as -RequestedServiceObjectiveName. Using it with the managed identity's authenticated Az context changes the tier without recreating the database, which is exactly what the nightly scaling runbook requires.

Auto-pausing after inactivity and resuming at a minimum 0.5 vCores are exclusive to the serverless compute tier. Provisioned and hyperscale tiers never auto-pause, so the database must be serverless for the configured behaviour to take effect.

Azure Monitor metric alerts evaluate platform metrics such as CPU percentage on a near-real-time basis and fire an action group that invokes the Azure Automation runbook via a webhook. This satisfies the stem's requirement to monitor CPU usage and trigger scaling automatically, unlike log-based alerts which add query latency.

Dynamic Data Masking applies a masking function to the column so non-admin users see obfuscated values at query time, while privileged users retain full data. This satisfies the requirement to automate masking for non-admin users without altering stored data or application code.

Elastic Jobs are a built-in Azure SQL Database feature designed to automate T-SQL execution across one or more databases on a schedule. You configure a job with a step containing the index rebuild script, set a schedule for 2:00 AM, and the service handles execution without needing a VM or external orchestrator. This directly meets the requirement with minimal overhead.

Runbook can automate planned failover without data loss.

Elastic Database Jobs are a native Azure SQL Database feature that can run T-SQL scripts against one or many databases on a schedule. They require no on-premises infrastructure and minimal setup: create a job agent, a job, a step, and a schedule. This directly satisfies the requirement to run a T-SQL script daily with low overhead.

SQL Agent is built into Azure SQL Managed Instance and runs T-SQL jobs on a schedule, so a nightly job at 2 AM executes the stored procedure natively with no external orchestrator or added latency.

Elastic Database Jobs run T-SQL across every database in a pool from a job agent, using a target group rather than per-database connections. This satisfies the scheduled, pool-wide execution requirement, and job step output can be written to a logging table.

Bicep is a declarative DSL that transpiles to ARM templates, letting you define the database, firewall rules, auditing, and threat detection policies once and redeploy identically across dev, test, and prod. Parameter files handle per-environment differences.

ALTER PARTITION FUNCTION ... SPLIT adds the next month's boundary, while MERGE collapses the oldest one, keeping the monthly range current. Elastic Database Jobs run this T-SQL on a schedule without external compute, satisfying the automation requirement for Azure SQL Database, where SQL Agent is unavailable.

Azure SQL Managed Instance supports native BACKUP DATABASE TO URL, which writes backups directly to Azure Blob Storage. SQL Server Agent is available on Managed Instance, so scheduling a job with a T-SQL step is a native, low-code solution. This meets the requirement without external orchestration.

An action group specifies what happens when an alert fires, such as sending an email, SMS, or triggering a runbook. To respond to high CPU usage, you must associate an action group with the alert rule. Without an action group, the alert would only be visible in the Azure portal but would not perform any automated response.

The command alters the database's service objective, moving it to the S2 performance level within its existing elastic pool or standalone tier. This immediately changes the compute and DTU allocation applied to OrdersDB, taking effect without recreating the database.

Azure SQL Managed Instance includes SQL Server Agent, which is fully supported. You can create a job with a T-SQL step that runs BACKUP DATABASE TO URL, and schedule it to run daily at midnight. This uses the built-in capabilities of the managed instance and does not require additional Azure services, minimizing cost and administrative effort.

Storing the schema as a SQL project in Azure Repos Git provides version control. A build pipeline that generates a DACPAC artifact ensures that the schema is packaged for deployment. This, combined with a release pipeline, enables automated deployment and rollback to any previous version, fulfilling the requirements.

Configurable from 7 to 35 days.

Elastic jobs are a native Azure SQL Database feature designed to automate T-SQL execution across one or many databases on a schedule. By creating an elastic job agent and a job with a T-SQL step, you can run the stored procedure nightly without external orchestration. This minimizes administrative overhead because the scheduling and execution are fully managed by the service, and it directly meets the requirement.

Enabling a managed identity for the Automation account creates an identity in Azure AD that the runbook can use to authenticate to Azure SQL Database without storing credentials. This is a secure, recommended practice. The identity must then be granted access to the database. This action is essential for the runbook to obtain a token and connect.

Azure Monitor alert rules can evaluate platform metrics like cpu_percent for Azure SQL Database. Setting a threshold of 90 and an action group with email notification directly fulfills the requirement. This is the native, low-effort solution for metric-based alerts on Azure SQL Database.

Diagnostic settings allow log streaming.

A managed identity for the Automation account allows the runbook to authenticate to Azure SQL Database without embedding credentials. You create a contained database user mapped to the managed identity and grant necessary permissions. This approach eliminates secret management and is the recommended secure method for Azure services to access databases. The runbook can use the identity to obtain a token and connect using Azure AD authentication.

Target groups define the servers and databases that elastic jobs execute against, satisfying the requirement to specify which Azure SQL Database targets the job runs on. Without a target group, the job agent has no scope for execution, so this component is mandatory alongside the job agent and job credentials.

Elastic Database Jobs run T-SQL against Azure SQL Database on a schedule, letting a script evaluate sys.dm_db_index_physical_stats and rebuild above 30% or reorganise above 10% fragmentation weekly. No SQL Agent exists in Azure SQL Database.

An Azure Automation runbook can query sys.dm_db_index_physical_stats on a schedule, evaluate fragmentation against the 30% threshold, and dispatch email via Office 365 or SendGrid. Azure SQL Database lacks native alerting on DMV output, so automation is required.

Elastic pools group databases sharing resources within a single logical server, so a database must already reside on that same server before being added. Moving a database across servers requires a separate operation, such as export/import or geo-restore, which the script does not perform. This satisfies the same-server constraint.

A build pipeline compiles and packages the database project into a DACPAC artefact, which the release stage later deploys. It is a required component because it produces the deployable schema artefact consumed by subsequent pipeline stages.

An ARM template declares the Azure SQL Database resource in JSON, so the CI/CD pipeline can deploy it repeatably per customer signup. This satisfies the infrastructure-as-code requirement, unlike portal or manual scripting approaches that lack declarative version control.

Bicep is a declarative infrastructure-as-code language that defines the Azure SQL Database, its firewall rules and performance tier in a single template, then deploys them repeatably. This satisfies the requirement to automate deployment of all three resources together.

Azure SQL Managed Instance handles OS and SQL engine patching automatically; the maintenance window merely schedules when that built-in patching occurs, letting you align it to low-traffic periods. Since the stem requires automation with minimal downtime, configuring the window satisfies both constraints without manual intervention or additional tooling.

The Azure SQL Database deployment task in Azure Pipelines is designed to execute T-SQL scripts against an Azure SQL Database. It supports inline scripts or script files and handles authentication. This task is the native way to apply schema updates in a CI/CD pipeline for Azure SQL Database.

Log Analytics stores and queries the diagnostic and metric data collected from Azure SQL Database. It supplies the log store that alert rules evaluate, satisfying the requirement for a queryable repository underpinning automated performance monitoring and alerting.

Elastic Database Jobs run T-SQL against Azure SQL Database on a defined recurrence, satisfying the scheduled index-maintenance requirement. Unlike SQL Agent, which is unavailable in Azure SQL Database, elastic jobs target logical servers and databases directly, executing scripts such as index rebuilds without external orchestration.

Azure Monitor alert rules evaluate platform metrics such as storage_percent and trigger action groups, which deliver email notifications. This satisfies the requirement for native, automated alerting at the 80% threshold without custom code or external tooling, unlike query-based or scheduled approaches.

Azure Data Factory with a self-hosted integration runtime can connect to on-premises SQL Server and Azure SQL Database. The copy activity can be scheduled and supports parallel reads to minimize source impact. It also has built-in retry policies to handle transient failures, making it resilient. This is the recommended approach for large-scale data movement.

ARM templates declaratively define the Azure SQL Database, firewall rules, and threat detection settings in one deployment, satisfying the Infrastructure as Code requirement. They natively support all three resource types without custom scripting or extra tooling.

Long-term retention extends Azure SQL Database backups beyond the default 7–35 day point-in-time window, storing full backups in RA-GRS blob storage on separate daily, weekly, monthly and yearly schedules. This directly satisfies the stem's 7-year yearly retention requirement, which the built-in short-term policy cannot provide.

Azure DevOps database projects keep schema definitions in version control, and release pipelines deploy them across regions with tracked, repeatable steps. This satisfies both the rollback requirement, via redeploying prior versions, and version control of schema changes.

An Azure Monitor alert evaluates the database's storage metric against an 80% threshold and triggers an action group, which delivers the email notification. This satisfies the requirement for automatic notification without custom scripting or manual monitoring.

Purview can automatically scan and classify sensitive data.

Managed identities provide secure authentication without storing credentials.

Azure Monitor hosts metric alerts that evaluate platform metrics such as storage space used against a threshold, triggering action groups to notify the operations team. It satisfies the automatic notification requirement without custom code, unlike Query Store or Elastic Jobs.

The Azure SQL Database deployment task in a release pipeline executes the DACPAC or SQL script against the target database, providing the actual deployment mechanism. Without this task, the pipeline has no way to apply schema changes to Azure SQL Database.

PITR covers 35 days; LTR can retain backups for up to 10 years, satisfying the 7-year requirement.

Out-GridView requires an interactive desktop session to render its graphical window, and Azure Automation runbooks execute headlessly in a sandbox with no GUI. The cmdlet therefore fails silently or blocks, so the DTU threshold comparison never completes and no alert fires, satisfying the stem's non-interactive constraint.

Azure Monitor metric alerts evaluate the database's DTU percentage against an 80% threshold sustained over a five-minute window, then fire an action group. That action group can invoke automation to scale the service tier, giving the required metric-driven trigger.

After the contained database user exists for the managed identity, the minimal data-plane permission needed to run the stored procedure is EXECUTE on that specific object. This grants exactly the required capability without broader rights, satisfying least privilege for the Automation runbook scenario described.

Runbooks can connect to Azure SQL Database and run maintenance scripts.

A target group defines the set of databases against which the job runs. By including all databases in the elastic pool, the job automatically applies the script to each database. This, combined with the version check, ensures the script is applied only once per database. Target groups can be dynamically populated based on server or pool membership, simplifying maintenance.

Azure DevOps database projects (DACPAC) with release pipelines provide version-controlled, repeatable schema deployment across multiple Azure SQL Databases. This satisfies the automation requirement, unlike ad hoc scripts or manual changes, by generating idempotent deployment plans per target.

Elastic jobs can authenticate to target databases using the job agent's managed identity. By creating a contained database user mapped to that managed identity and granting it the necessary permissions, all T-SQL steps run under that identity. This provides a clear, auditable identity for any data modifications, satisfying the compliance requirement. Managed identities are also more secure because they avoid storing passwords.

Elastic jobs require an elastic job agent as the parent Azure resource that stores job definitions, credentials and target databases. Without creating the agent first, you cannot define the nightly T-SQL job that clears the procedure cache, so it satisfies the prerequisite constraint in the stem.

Elastic Database Jobs require a job database to store job definitions, execution history and step metadata. It holds the T-SQL scripts and schedules the job agent executes against target members, making it an essential component alongside the job agent and target group.

Database Mail is supported on Azure SQL Managed Instance and can be configured with an SMTP account. Adding a notification step to the SQL Server Agent job allows the job to send email results directly from the instance, meeting the notification requirement without external services.

Export-AzSqlDatabase performs the actual BACPAC export, but it must run unattended. Azure Automation runbooks or a timer-triggered Azure Function supply that monthly schedule and authenticate to the subscription, satisfying the automation requirement the question specifies.

ARM templates are declarative JSON files stored in source control, so the server-level firewall rule and database deploy together in one repeatable, version-controlled operation. This directly satisfies the repeatability and version-control constraints, unlike imperative scripts or portal-based creation.

Elastic Jobs are designed to automate T-SQL tasks across one or many databases in Azure SQL Database. They natively support scheduling, retry on failure, and logging. By targeting the specific database and scheduling a daily job, you meet the requirement with minimal administrative overhead and built-in reliability for transient failures.

SQL Server Managed Backup to Microsoft Azure automates full, differential and transaction log backups to Blob Storage with built-in scheduling and retention, requiring no custom scripts. It satisfies both constraints: least administrative effort and point-in-time restore through log chain continuity.

Auto-failover groups replicate databases to a secondary region and trigger failover automatically when the primary becomes unavailable, without manual intervention. This satisfies the disaster-recovery automation requirement, since the group's policy initiates the regional switch based on outage detection rather than operator action.

Azure Automation runbooks execute scheduled PowerShell that can invoke Az.Sql cmdlets to restore the production backup and then run post-restore T-SQL scripts. This satisfies the orchestration requirement spanning restore plus subsequent scripting, which a plain backup policy alone cannot perform.

Azure SQL Database serverless auto-pause with a 60-minute delay suspends the database during idle periods, cutting compute cost. Any incoming connection, including the hourly reporting job, automatically triggers resume, so no extra configuration is required to keep the job working.

Creating a plan guide pins the previous execution plan, satisfying the requirement to revert the regressed query, while disabling the automatic tuning recommendation prevents the automatic tuning feature from re-forcing the same plan. Together these two actions address both the immediate regression and its recurrence.

Elastic Database Jobs execute T-SQL across Azure SQL Database instances on a schedule, satisfying the requirement for a native automation mechanism. Unlike SQL Server Agent, which Azure SQL Database lacks, elastic jobs run index rebuild and reorganise scripts directly against the target databases without external tooling.