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 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.

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

Why wrong: May cause performance impact during rebuild, and not fully automated without monitoring.

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

Why wrong: External automation adds complexity and is not a built-in database feature.

Schedule a weekly job using elastic jobs to reorganize all indexes.

Why wrong: Reorganize may not be sufficient for high fragmentation and does not rebuild.

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.

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

Why wrong: Elastic Database Jobs are designed for running T-SQL scripts across databases, not for performing BACPAC exports to storage.

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

Why wrong: Data Factory is a data integration service; while it can copy data, it is more complex and not the simplest solution for a simple scheduled export.

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

Why wrong: The SQL connector can export data but is primarily for data manipulation, and the export action creates a BACPAC in blob storage, but it requires additional configuration and is less straightforward than Automation.

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.

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

Why wrong: This is possible, but the question asks for automation; SQL Agent is already available. However, the key issue is that COPY_ONLY backups are not possible with T-SQL directly; you need to use a PowerShell cmdlet for COPY_ONLY. So this option is incorrect because it doesn't specify COPY_ONLY.

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

Why wrong: Azure Backup does not support Azure SQL Managed Instance; it supports SQL Server on Azure VMs and Azure SQL Database.

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

Why wrong: Elastic Database Jobs run T-SQL scripts, but they are not designed for Azure SQL Managed Instance; they are for Azure SQL Database. Also, Managed Instance supports SQL Agent.

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).

SQL Server Agent

Why wrong: 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 wrong: 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 wrong: Logic Apps can automate workflows, but they are not the primary service for scheduling database tasks; they are more for integration.

Create a new Azure SQL Database.

Why wrong: Creating a new database is not a job task; jobs are for running scripts on existing databases.

Change the service tier objective (SLO) of a database.

Why wrong: Changing SLO requires ALTER DATABASE with appropriate permissions, but it is not a typical job task and is better done via Azure Automation or PowerShell.

Monthly backups are retained for 24 months.

Why wrong: MonthlyRetention is P12M, which is 12 months.

Weekly backups are retained for 8 weeks.

Why wrong: WeeklyRetention is P4W, not P8W.

Yearly backups are retained for 1 year.

Why wrong: 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 wrong: This does not address the root cause; the job still may fail due to timeouts.

Change the fill factor to 70 to reduce future fragmentation.

Why wrong: A lower fill factor can actually increase fragmentation over time because more page splits occur when data is inserted; it does not help with the rebuild time or timeout.

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

Why wrong: Increasing DTUs may speed up the rebuild but does not prevent timeouts if the job itself is poorly configured; also, it increases cost.

Set point-in-time restore (PITR) retention to 90 days.

Why wrong: PITR retention for Azure SQL Managed Instance is limited to 35 days at maximum.

Set PITR retention to 90 days and configure geo-replication to achieve the 7-year retention.

Why wrong: PITR cannot exceed 35 days, and geo-replication does not extend retention.

Configure geo-redundant backup storage with a retention of 90 days.

Why wrong: Geo-redundancy affects storage redundancy, not retention period; PITR retention still maxes at 35 days.

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

Why wrong: 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 wrong: 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 wrong: Active geo-replication replicates the live database, not backups; it doesn't provide backup retention.