Configure and manage automation of tasks practice questions

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

Why it fails: 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 it fails: 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 it fails: 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 it fails: Data Factory is not designed for scaling databases.

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

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

Use an elastic pool and manually adjust eDTUs.

Why it fails: Manual adjustment is not automated.

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

Why it fails: 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 it fails: 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 it fails: 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.

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

Why it fails: Retention policy affects execution history, not scheduling.

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

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

The job definition is missing the 'scheduleType' property.

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

ALTER INDEX Orders ON Orders REBUILD;

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

ALTER INDEX ALL ON Orders REORGANIZE;

Why it fails: Reorganize does not reduce fragmentation sufficiently for high fragmentation.

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

Why it fails: Offline rebuild causes downtime.

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

Why it fails: 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 it fails: 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 it fails: Requires custom scripting and does not provide automated health monitoring.

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

Why it fails: 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 it fails: 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 it fails: 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.

SQL Server Agent

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

Azure Functions

Why it fails: Azure Functions can run code on a timer, but they are not a dedicated service for database automation; they are event-driven compute.

Azure Logic Apps

Why it fails: Logic Apps can automate workflows, but they are not the primary service for scheduling database tasks; they are more for integration.

Monthly backups are retained for 24 months.

Why it fails: MonthlyRetention is P12M, which is 12 months.

Weekly backups are retained for 8 weeks.

Why it fails: WeeklyRetention is P4W, not P8W.

Yearly backups are retained for 1 year.

Why it fails: YearlyRetention is P5Y, which is 5 years.

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

Why it fails: 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 it fails: 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 it fails: 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).

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

Why it fails: Read-scale replicas are for offloading read-only queries and do not provide backup retention.

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

Why it fails: LTR is intended for retaining backups beyond 35 days (e.g., monthly, yearly), not for daily backups within 30 days.

Configure active geo-replication to a secondary region.

Why it fails: Active geo-replication replicates the live database, not backups; it doesn't provide backup retention.

Create a read replica and redirect read queries to it during the surge.

Why it fails: Read replicas offload reads but do not scale automatically.

Manually scale up the database service tier before 2:00 AM each day.

Why it fails: Manual intervention is not automated.

Move the database to an Elastic Database Pool and rely on its built-in autoscaling.

Why it fails: Elastic pools autoscale pooled resources, not a single database.

Always use SORT_IN_TEMPDB = ON to reduce tempdb contention.

Why it fails: SORT_IN_TEMPDB may increase tempdb load.

Set MAXDOP to 0 to use all CPUs for faster execution.

Why it fails: Using all CPUs can starve other workloads.

The backup interval of 12 hours does not allow 1-minute granularity for point-in-time restore.

Why it fails: PITR granularity is limited by backup frequency.

The policy must specify a minimum retention of 30 days for point-in-time restore.

Why it fails: No such requirement.

Geo-redundant backup must be disabled for point-in-time restore to work.

Why it fails: Geo-redundancy does not affect PITR.

Deploy Azure SQL Database, then assess database compatibility, then fix compatibility issues, then migrate schema and data

Why it fails: This is incorrect because deploying the target before assessing and fixing issues may lead to a target database that does not support the source schema, causing migration failures.

Assess database compatibility, then deploy Azure SQL Database, then fix compatibility issues, then migrate schema and data

Why it fails: This is incorrect because fixing compatibility issues should be done before deploying the target, as the fixes ensure the schema is compatible with Azure SQL Database, allowing a smooth deployment.

Migrate schema and data, then assess database compatibility, then fix compatibility issues, then deploy Azure SQL Database

Why it fails: This is incorrect because migration must be the last step after ensuring compatibility, fixing issues, and deploying the target; otherwise, the migration will likely fail.

Zone-redundant availability: Automatically fails over databases across different Azure regions.

Why it fails: Incorrect — this describes Auto-failover groups, not zone-redundant availability.

Active geo-replication: Creates four replicas in the same datacenter to provide high availability.

Why it fails: Incorrect — this describes Business Critical tier replicas, not active geo-replication.

CREATE DATABASE: Deletes a database

Why it fails: Incorrect: This describes DROP DATABASE. CREATE DATABASE creates a new database, not deletes.

ALTER DATABASE: Creates a backup

Why it fails: Incorrect: This describes BACKUP DATABASE. ALTER DATABASE modifies settings, not backs up.

Configure a maintenance configuration for the virtual machine scale set

Why it fails: This applies to Azure VMs, not SQL Managed Instance.

Set a maintenance window in the Azure SQL Database server settings

Why it fails: This is for Azure SQL Database, not Managed Instance.

Use Azure Update Management to schedule patching

Why it fails: Azure Update Management is for IaaS VMs, not PaaS services.

Use Azure-SSIS Integration Runtime with a Data Flow activity

Why it fails: Azure-SSIS IR is for SSIS packages, not direct incremental loading.

Use Azure Integration Runtime with a Copy activity

Why it fails: Azure IR cannot access on-premises data sources.

Use a Stored Procedure activity in Data Factory

Why it fails: Stored Procedure activity is for executing SQL, not for data movement.

Use Azure App Service deployment slots for the database

Why it fails: Deployment slots are for App Service, not Azure SQL Database.

Use GitHub Actions deployment environments with approval gates

Why it fails: Approval gates prevent deployment but do not provide rollback.

Use Azure SQL Managed Instance point-in-time restore

Why it fails: PITR is for Managed Instance, and is not automatic on failure.