Configure and manage automation of tasks practice questions

Create a SQL Agent job that runs ALTER INDEX REBUILD on all indexes with fragmentation >30%.

Why wrong: SQL Agent is not available in Azure SQL Database (single database); it is only supported in Azure SQL Managed Instance. Therefore, you cannot create a SQL Agent job in a single database environment, regardless of the T-SQL logic. Even if SQL Agent were present, rebuilding all indexes with fragmentation >30% using ALTER INDEX REBUILD could cause blocking and resource contention; but the primary showstopper is that the service itself is unavailable. Azure SQL Database's native scheduling alternative is Elastic Jobs (now called Elastic Database Jobs), which runs T-SQL scripts on a schedule across one or more databases.

Use Azure Automation to run a PowerShell script that checks fragmentation and rebuilds indexes.

Why wrong: Azure Automation is an external orchestration service, not a built-in database feature. Running a PowerShell script in an Azure Automation runbook requires storing and managing credentials outside the database, adding operational overhead and security complexity. The requirement explicitly asks for built-in database features, and Azure Automation does not integrate natively within the Azure SQL Database engine for scheduled index maintenance. Additionally, while a PowerShell script could connect to the database and execute index rebuilds, it relies on external scheduling and network connectivity, making it less reliable and not a platform-native solution.

Enable automatic tuning and set the 'Force plan' and 'Create index' options.

Why wrong: Incorrect. Automatic tuning in Azure SQL Database includes 'Create index' and 'Force plan' options, but these do not rebuild indexes with high fragmentation. 'Create index' adds new indexes, and 'Force plan' stabilizes query plans. Neither addresses index fragmentation.

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

Why wrong: Elastic Database Jobs execute T-SQL scripts across multiple databases in an elastic pool or across servers. They are designed for administrative tasks like index maintenance or data consistency checks, but they do not have direct capability to export a database to a BACPAC file. A T-SQL script cannot invoke the Azure SQL export operation; the export requires a PowerShell cmdlet, REST API, or portal action. Therefore, this approach cannot fulfill the requirement.

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

Why wrong: An Azure Data Factory Copy activity directly moves data, but it does not create a transactionally consistent snapshot of an entire live Azure SQL Database. This means the exported data could be inconsistent if writes occur during the copy, failing the consistency requirement for the critical application. However, Data Factory pipelines are ideal for general data movement, ETL processes, and replicating specific datasets between various Azure data stores, making it a powerful tool for data integration where snapshot consistency isn't paramount.

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

Why wrong: The SQL Server connector in Logic Apps is intended for executing queries, stored procedures, and modifying data. It does not have a native action to produce a BACPAC export. While you could potentially call an Azure REST API or trigger an Automation runbook from a Logic App, the connector itself does not provide the export operation. This adds unnecessary complexity and requires custom API calls to achieve what Automation does natively, so it's not a straightforward solution.

Configure a SQL Server Agent job on the Managed Instance to run a T-SQL BACKUP DATABASE TO URL statement.

Why wrong: Incorrect. SQL Server Agent on Managed Instance has limitations; it cannot schedule T-SQL BACKUP DATABASE TO URL for automated backups. Additionally, this would create native backups that conflict with Azure's automatic backups.

Use Azure Backup to configure a backup policy for the Managed Instance.

Why wrong: Incorrect. Azure Backup does not support backing up user databases in Azure SQL Managed Instance. It only supports SQL Server on Azure VMs.

Create an Elastic Database Job that runs a T-SQL BACKUP DATABASE TO URL statement.

Why wrong: Incorrect. Elastic Database Jobs are for executing T-SQL scripts across multiple databases, not for scheduling backups. They cannot be used to automate T-SQL BACKUP DATABASE statements reliably.

Create a second Elastic Database Job to run at 4:00 AM to rebuild indexes that failed.

Why wrong: Creating a second Elastic Database Job at 4:00 AM is merely a retry workaround that does not address the root cause: the index rebuild operation itself is too resource-intensive and exceeds the job's execution or query timeout. If the original rebuild times out due to resource contention or the sheer size of the indexes, a rescheduled run will likely encounter the same conditions—especially if 4:00 AM overlaps with other maintenance, backups, or heavy ETL processes. The job will still attempt a full rebuild with the same high-resource settings, so it will probably fail again. You need to change the maintenance strategy itself, not just the schedule.

Change the fill factor to 70 to reduce future fragmentation.

Why wrong: Lowering the fill factor to 70 reserves 30% free space in each leaf page, which can reduce page splits shortly after a rebuild, but it does nothing to reduce the time required for the current rebuild operation. In fact, rebuilding with a lower fill factor creates more pages because each index row takes more physical space, making the index larger and increasing I/O for both future queries and subsequent rebuilds. Over time, as data changes, those free pages get consumed and FILLFACTOR no longer prevents fragmentation, so this change is neither an immediate nor a scalable solution to the timeout problem. It also bypasses the real need: making the maintenance operation less resource-hungry.

Increase the DTU level of the database to 200 DTUs to speed up the index rebuild.

Why wrong: Scaling the database to 200 DTUs might provision more CPU, I/O, and memory, which could shorten the rebuild time, but it is an expensive, capacity-based workaround that does not fix the job's inefficient configuration. If the timeout is caused by the online versus offline nature of the rebuild or by excessive parallelism competing with live traffic, simply adding DTUs may still result in failure because the query timeout or resource governance limits remain. Additionally, scaling up increases monthly costs and does not address the underlying operational pattern of using a full rebuild where a lighter reorganize would suffice. This option treats the symptom (slow execution) rather than the cause (poorly chosen maintenance operation).

Failed logins

Why wrong: Failed logins have different action_id.

Schema changes

Why wrong: AUSC is not for schema.

Data manipulation statements

Why wrong: DML events have different action_ids.

Enable automatic tuning and set 'INDEX_REBUILD' option.

Why wrong: Automatic tuning does not rebuild indexes; it only creates/drops indexes based on recommendations.

Create a SQL Agent job on each database to rebuild indexes during off-peak.

Why wrong: A SQL Agent job on each database lacks centralised orchestration across the elastic pool, so it cannot coordinate index rebuilds to minimise resource contention at the pool level; each job runs independently, potentially saturating shared DTU or vCore resources simultaneously. This approach is tempting because SQL Agent is the native scheduler for individual database maintenance tasks, and it would be correct for a standalone Azure SQL Database where no shared resource pool exists.

Write a PowerShell script that connects to each database sequentially and rebuilds indexes.

Why wrong: While possible, it requires manual scheduling and does not leverage built-in automation.

Configure SQL Agent on the Azure SQL Database logical server to run index maintenance jobs.

Why wrong: SQL Agent is not available in Azure SQL Database; it is only available in SQL Server on Azure VMs or Managed Instance.

Use Azure Automation runbooks with the Invoke-Sqlcmd cmdlet to run index maintenance scripts on a schedule.

Why wrong: Azure Automation can work, but it requires more overhead for runbook development, module management, and doesn't natively integrate with Azure SQL schedules as cleanly as Elastic Jobs.

Deploy an Azure Function with a timer trigger that connects to the database and runs index maintenance commands.

Why wrong: Azure Functions can perform this task but lack the built-in job scheduling, retry, and logging features provided by Elastic Database Jobs, making it less efficient.

Elastic Database Jobs

Why wrong: Elastic Database Jobs run T-SQL scripts across databases but cannot perform backup operations.

SQL Agent jobs

Why wrong: SQL Agent jobs are not available in Azure SQL Database.

Azure Backup

Why wrong: Azure Backup uses Recovery Services vaults, not storage accounts directly, for Azure SQL Database backups.

Disable auto-pause for the database and use Azure Automation to scale down the database during idle periods.

Why wrong: Does not minimize costs as effectively as auto-pause.

Use Elastic Database Jobs to keep the database active by running a lightweight query every 59 minutes.

Why wrong: This would prevent auto-pause and increase costs.

Set the auto-pause delay to 0 minutes to minimize costs, and create an Azure Automation runbook to keep the database active during the reporting job.

Why wrong: Auto-pause delay of 0 would pause immediately, causing the reporting job to fail.

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

Why wrong: This requires custom scripting and additional management, not minimizing overhead.

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

Why wrong: SQL Agent job runs on a single database, not all databases.

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

Why wrong: Data Factory is not optimized for database maintenance tasks and adds complexity.

Use Azure Data Factory to scale the database based on pipeline triggers.

Why wrong: Data Factory is not designed for scaling databases.

Create an Azure Automation runbook that scales the database on a schedule.

Why wrong: Schedule-based scaling does not respond to workload patterns.

Use an elastic pool and manually adjust eDTUs.

Why wrong: Manual adjustment is not automated.

Install Azure Backup Server on-premises and configure backup of SQL Server databases.

Why wrong: Azure Backup Server is for Azure VMs, not on-premises SQL Server directly.

Use SQL Server Agent jobs to perform full, differential, and log backups to an Azure Blob Storage URL.

Why wrong: Requires manual configuration of backup schedules and retention, increasing administrative effort.

Use Azure Data Factory to copy database backups to Blob Storage.

Why wrong: Data Factory is for data movement, not backup automation with point-in-time restore.

Use Azure Data Factory to run stored procedures for schema changes.

Why wrong: Primarily for data movement, not schema deployment.

Use SQL Server Agent jobs to run deployment scripts on schedule.

Why wrong: No version control or rollback support.

Use Azure Automation with PowerShell scripts to execute T-SQL scripts.

Why wrong: No built-in version control or rollback capabilities.

Backup compression.

Why wrong: Compression is applied automatically and cannot be configured.

Backup frequency (full, differential, log).

Why wrong: Backup frequency is managed automatically by Azure.

Point-in-time restore interval.

Why wrong: The restore interval is determined by the retention period, not directly configured.

Automatically rebuild fragmented indexes.

Why wrong: Index rebuilding is not an Azure Monitor feature.

Automatically scale the database to a higher service tier.

Why wrong: Auto-scaling is not available through Azure Monitor; it requires autoscale settings.

The retention policy is set too low, causing the job to be pruned before execution.

Why wrong: Retention policy affects execution history, not scheduling.

The startTime is in the past; the job should have a future start time.

Why wrong: Jobs can start in the past; the schedule will run at the next occurrence.

The job definition is missing the 'scheduleType' property.

Why wrong: Schedule type is not required; the structure is valid.

ALTER INDEX Orders ON Orders REBUILD;

Why wrong: Syntax is incorrect; should be ALTER INDEX ALL or specify index name.

ALTER INDEX ALL ON Orders REORGANIZE;

Why wrong: Reorganize does not reduce fragmentation sufficiently for high fragmentation.

ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = OFF);

Why wrong: Offline rebuild causes downtime.

Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover.

Why wrong: Traffic Manager does not perform database failover; it only routes traffic. The database itself must be failed over separately.

Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS.

Why wrong: Adds complexity and relies on custom health checks; not a built-in feature.

Use Azure Automation with a runbook that checks the database status and initiates failover via REST API.

Why wrong: Requires custom scripting and does not provide automated health monitoring.

SQL Server Agent

Why wrong: SQL Server Agent is not available in Azure SQL Database; it is only available in SQL Server on-premises or Azure SQL Managed Instance.

Azure Automation Runbook

Why wrong: Automation runbooks run PowerShell or Python scripts, not T-SQL directly, unless you use Invoke-SqlCmd which adds complexity.

Azure Logic Apps

Why wrong: Logic Apps can execute T-SQL via the SQL connector, but it is more of an integration tool and not the standard for scheduled database maintenance.

Create an Azure Automation runbook that calls New-AzureRmSqlDatabase and trigger it from your CI/CD pipeline.

Why wrong: While possible, Automation runbooks are not the standard for infrastructure provisioning in CI/CD; ARM templates are preferred.

Set up an Elastic Database Job that runs a CREATE DATABASE statement.

Why wrong: Elastic Database Jobs run T-SQL scripts, but they are used for maintenance tasks, not for provisioning new databases in a CI/CD context.

Configure a SQL Server Agent job on the logical server to run a CREATE DATABASE statement.

Why wrong: SQL Server Agent is not available in Azure SQL Database (only in Managed Instance).