Courseiva

CCNA Automation Tasks Questions

75 of 172 questions · Page 1/3 · Automation Tasks topic · Answers revealed

Practice these questionsExam hubAll questions
1
Multi-Selecthard

Which THREE actions can be performed by using Elastic Database Jobs in Azure SQL Database? (Choose three.)

Select 3 answers
A.Run a T-SQL script to update statistics across multiple databases.
B.Collect metadata about databases and store it in a table.
C.Create a new Azure SQL Database.
D.Change the service tier objective (SLO) of a database.
E.Rebuild indexes on all databases in an elastic pool.
AnswersA, B, E

Elastic Database Jobs can execute any T-SQL script, including updating statistics.

Why this answer

Elastic Database Jobs in Azure SQL Database are designed to automate administrative tasks across multiple databases. Running a T-SQL script to update statistics is a common maintenance operation that can be executed in parallel across a target group of databases, making option A correct.

Exam trap

The trap here is that candidates may assume Elastic Database Jobs can perform any administrative task, but they are strictly limited to executing T-SQL scripts and cannot perform resource-level operations like creating databases or changing service tiers.

Practice this question →
2
MCQmedium

You are a senior database administrator for a financial services company that uses Azure SQL Managed Instance to host multiple customer databases. The company has a requirement to automatically execute a series of compliance scripts every Sunday at 2:00 AM against all databases in the instance. The scripts include checking for orphaned users, verifying data encryption, and auditing login attempts. The solution must log the execution results in a central table and send an email summary to the compliance team. You have been asked to implement this automation using built-in Azure features without relying on external tools like Power Automate or custom schedulers. What should you use?

A.Schedule a Logic App that connects to each database via the SQL connector and runs the scripts.
B.Use Azure Automation Runbooks with the PowerShell module for SQL Server to execute scripts on each database.
C.Create a SQL Agent job that runs a T-SQL script using sp_foreachdb to execute the compliance scripts on each database, store results in a central database, and send an email using Database Mail.
D.Create an Elastic Job agent with a job that runs the scripts against each database and logs results to a central database.
AnswerC

SQL Agent is built-in and supports cross-database execution and email.

Why this answer

Option D is correct because SQL Agent jobs in Managed Instance can execute T-SQL scripts across databases using sp_send_dbmail for email, and cross-database queries can log results to a central database. Option A is wrong because elastic jobs target Azure SQL Database, not Managed Instance. Option B is wrong because Azure Automation Runbooks require external connectivity and cannot directly execute T-SQL across databases.

Option C is wrong because Logic Apps are external and require additional configuration.

Practice this question →
3
MCQmedium

You have an Azure SQL Database that runs a critical workload. You need to automate index maintenance to reduce fragmentation without impacting performance. What should you use?

A.Elastic Database Jobs with T-SQL script
B.Automatic tuning for index management
C.SQL Agent Job with OLE Automation
D.Azure Automation runbook with T-SQL script
AnswerB

Automatic tuning can automatically create, drop, and rebuild indexes based on workload patterns.

Why this answer

Option D is correct because Azure SQL Database's automatic tuning includes index management that can be configured to fix fragmentation automatically. Option A is wrong because Azure Automation with runbooks requires custom scripts and may not be as efficient. Option B is wrong because Elastic Database Jobs are for multi-database tasks, not single-database index maintenance.

Option C is wrong because SQL Agent Jobs are not available in Azure SQL Database single databases.

Practice this question →
4
MCQhard

Refer to the exhibit. You are reviewing an Elastic Database Job definition for Azure SQL Database. The job is scheduled to run once on January 15, 2026. Which statement about the job's target is correct?

A.The job will target only the database named 'db1'.
B.The job will target all databases in the server except 'db1'.
C.The job will target the entire logical server.
D.The job will target the server and all its databases.
AnswerB

Exclude membership with a list excludes those databases from the server.

Why this answer

Option D is correct because the target group uses membershipType 'Exclude' with members list containing one database, meaning all databases in the logical server except 'db1' will be targeted. Option A is wrong because the membership type is 'Exclude', not 'Include'. Option B is wrong because the target group is for a single database, but it excludes that database, so it targets all others.

Option C is wrong because the target group is at the server level, not database level.

Practice this question →
5
Multi-Selectmedium

Which THREE components are part of an Azure SQL Managed Instance automated maintenance window configuration? (Choose three.)

Select 3 answers
A.Start time (e.g., 2:00 AM local time)
B.Day of week (e.g., Sunday)
C.Database-level maintenance schedule
D.Maximum number of concurrent databases
E.Notification email for upcoming maintenance
AnswersA, B, E

You can specify the start time of the maintenance window.

Why this answer

Azure SQL Managed Instance allows you to configure a maintenance window (day of week, time, and duration). You can also set a notification email for upcoming maintenance. Option A, Option C, and Option E are correct.

Option B is not configurable per database. Option D is for Azure SQL Database, not Managed Instance.

Practice this question →
6
MCQhard

You are designing an automated backup strategy for Azure SQL Managed Instance. The solution must ensure point-in-time restore (PITR) within 2 hours for the last 7 days and long-term retention (LTR) for 5 years. Which configuration should you use?

A.Use Azure Backup for SQL Server in Azure VM to back up the managed instance.
B.Set PITR retention to 7 days and use geo-redundant backup storage for LTR.
C.Set PITR retention to 2 hours and configure a custom backup job using Elastic Database Jobs.
D.Set PITR retention to 7 days (default) and configure LTR backup policy with yearly backups for 5 years.
AnswerD

Managed Instance supports both PITR and LTR.

Why this answer

Option A is correct because Managed Instance supports PITR retention up to 35 days (7 days is fine) and LTR policies for up to 10 years. Option B is wrong because LTR is not available for Azure SQL Database single database with just Backup Storage Redundancy. Option C is wrong because Azure Backup for SQL Server in Azure VM is a different scenario.

Option D is wrong because PITR retention can be up to 35 days, not 2 hours.

Practice this question →
7
MCQeasy

You need to automatically scale an Azure SQL Database based on workload patterns. Which Azure feature should you use?

A.Serverless compute tier
B.Provisioned tier with auto-scale setting
C.Azure Automation runbook that modifies the service tier
D.Elastic Database Jobs to add replicas
AnswerA

Serverless automatically scales compute resources based on workload.

Why this answer

Option A is correct because Azure SQL Database's serverless compute tier automatically scales based on workload. Option B is wrong because auto-scaling is not available in provisioned tier without manual intervention. Option C is wrong because Azure Automation with runbooks can perform scaling but is less efficient than serverless.

Option D is wrong because Elastic Database Jobs are for multi-database operations, not scaling.

Practice this question →
8
Multi-Selecteasy

You are tasked with automating the backups of multiple Azure SQL Databases to ensure long-term retention. Which TWO Azure services can be used to achieve automated backups with retention beyond the default 7-35 days?

Select 2 answers
A.Azure Site Recovery
B.Azure Blob Storage snapshots
C.Azure Backup for SQL Server in Azure VMs
D.Azure Storage lifecycle management
E.Azure SQL Database long-term retention (LTR) backup policy
AnswersC, E

Supports long-term retention for SQL Server on VMs.

Why this answer

Options A and B are correct because Azure Backup for SQL Server in Azure VMs supports long-term retention (LTR) up to 10 years, and Azure SQL Database's built-in long-term retention (LTR) policy can be configured for up to 10 years. Options C, D, and E do not provide long-term backup retention for SQL databases.

Practice this question →
9
MCQeasy

You are responsible for managing a fleet of 20 Azure SQL Databases used by different departments. Each database has its own schema and data. You need to automate the creation of weekly exports of each database to a BACPAC file stored in a specific Azure Storage container. The exports should be done outside business hours (Sundays at 2 AM). You also need to ensure that the export process does not impact production performance. What is the most straightforward and cost-effective solution?

A.Use Elastic Database Jobs to run a SQL script that exports the database using the BACPAC export command.
B.Use Azure Data Factory with a copy activity to export the database to BACPAC via the SQL Server Import/Export service.
C.Create an Azure Automation runbook that uses the Export-AzSqlDatabase cmdlet for each database, and schedule the runbook to run weekly on Sundays at 2 AM.
D.Manually export each database using Azure Portal once a week.
AnswerC

Simple, cost-effective, and uses built-in cmdlets.

Why this answer

Option A is correct because Azure SQL Database's export to BACPAC can be automated using Azure Automation runbooks with the Export-AzSqlDatabase cmdlet, and can be scheduled. Options B and C require additional services and complexity. Option D is not automated.

Practice this question →
10
Multi-Selecthard

You are responsible for automating the deployment of an Azure SQL Database with associated firewall rules, a Microsoft Entra ID admin, and a vulnerability assessment baseline. The deployment must be idempotent and repeatable. Which THREE tools or approaches should you use? (Choose three.)

Select 3 answers
A.SQL Server Management Studio.
B.Bicep template.
C.ARM template.
D.Azure DevOps release pipeline.
E.Azure portal.
AnswersB, C, D

Bicep is a declarative language that compiles to ARM templates.

Why this answer

Options A, B, and D are correct. ARM templates define infrastructure, Azure DevOps pipelines automate deployment, and Bicep is a declarative language for ARM. Option C is wrong because the Azure portal is manual.

Option E is wrong because SQL Server Management Studio is not for infrastructure deployment.

Practice this question →
11
MCQhard

A company uses Azure SQL Managed Instance and needs to automatically rebuild fragmented indexes weekly during low usage. They also need to update statistics. Which approach should they take?

A.Use Elastic Database Jobs to run index maintenance
B.Use Azure Automation runbook with Invoke-SqlCmd to run maintenance scripts
C.Use Azure Logic Apps with SQL connector
D.Create a SQL Agent job on the managed instance
AnswerB

Azure Automation can schedule PowerShell runbooks that connect to the managed instance and run T-SQL maintenance.

Why this answer

Azure Automation runbooks can run maintenance scripts but require custom coding. SQL Agent is not available in managed instance. Elastic Database Jobs cannot be used with managed instance.

Azure Logic Apps can trigger but are not ideal for long-running T-SQL. The correct approach is to use Azure Automation with PowerShell runbooks executing T-SQL.

Practice this question →
12
MCQhard

You are designing an automated data movement solution using Azure Data Factory. Data must be copied from an on-premises SQL Server to Azure SQL Database. The solution must support incremental loads and handle schema changes. Which approach should you use?

A.Use Self-Hosted Integration Runtime with a Copy activity configured with incremental loading
B.Use Azure-SSIS Integration Runtime with a Data Flow activity
C.Use Azure Integration Runtime with a Copy activity
D.Use a Stored Procedure activity in Data Factory
AnswerA

Self-hosted IR enables on-premises access, and Copy activity supports incremental loads.

Why this answer

Option C is correct because a self-hosted integration runtime is required for on-premises access, and the copy activity can be configured for incremental loads using watermarks. Option A lacks incremental loading. Option B uses the wrong IR (Azure IR cannot access on-premises).

Option D is not a Data Factory activity.

Practice this question →
13
MCQeasy

You need to automatically send a weekly email report summarizing the performance metrics (DTU consumption, storage used) of all Azure SQL Databases in a subscription. Which Azure service should you use?

A.Azure Automation Runbook with PowerShell to collect metrics and send email via Send-MailMessage.
B.Azure Logic App with a recurrence trigger, using Azure Monitor connector to get metrics and Office 365 Outlook connector to send email.
C.Elastic Database Job that queries sys.dm_db_resource_stats and sends email via sp_send_dbmail.
D.Azure Data Factory pipeline that runs weekly and sends an email with the report.
AnswerB

Logic Apps have built-in connectors for metrics and email.

Why this answer

Option D is correct because Azure Logic Apps can query metrics via the Azure Monitor connector and send emails via Office 365 connector, all in a scheduled workflow. Option A is wrong because Azure Automation Runbooks can do it but require more scripting for email. Option B is wrong because Elastic Database Jobs are for T-SQL, not metrics.

Option C is wrong because Azure Data Factory is for data movement, not reporting.

Practice this question →
14
Multi-Selecthard

Which THREE configurations are required to automate the deployment of database schema changes from a Git repository to Azure SQL Database using Azure Pipelines?

Select 3 answers
A.An Elastic Database Job agent to run the deployment scripts.
B.An Azure SQL Database deployment task in the pipeline.
C.An Azure Resource Manager service connection to the SQL Database.
D.An Azure Automation Runbook to execute the deployment.
E.A YAML pipeline file that defines the build and release stages.
AnswersB, C, E

The task runs the SQL scripts against the database.

Why this answer

Options A, B, and E are correct. A service connection to Azure SQL Database is needed to authenticate. A YAML pipeline definition specifies the steps.

The SQL task (e.g., Azure SQL Database deployment task) runs the scripts. Option C is wrong because an Azure Automation Runbook is not part of Azure Pipelines. Option D is wrong because an Elastic Database Job agent is not required for a single database deployment.

Practice this question →
15
Multi-Selectmedium

Which TWO services can be used to automate the backup of Azure SQL Database to a storage account? (Choose two.)

Select 2 answers
A.Elastic Database Jobs
B.SQL Agent jobs
C.Azure Backup
D.Azure Automation with PowerShell runbooks
E.Azure Data Factory
AnswersC, D

Azure Backup supports Azure SQL Database long-term retention.

Why this answer

Azure Automation with PowerShell runbooks can execute backup commands. Azure Data Factory can copy backups but not native backup. Azure Backup service supports Azure SQL Database backups.

SQL Agent is not available. Elastic jobs are for T-SQL, not backup.

Practice this question →
16
Multi-Selecteasy

Which TWO tools can be used to automate the deployment of database schema changes to Azure SQL Database as part of a CI/CD pipeline? (Choose two.)

Select 2 answers
A.Azure Data Studio with SQLCMD mode
B.GitHub Actions with the Azure SQL Database deployment action
C.Azure DevOps release pipeline with SQL Server database project (DACPAC) deployment task
D.SQL Server Import and Export Wizard
E.SQL Server Management Studio (SSMS)
AnswersB, C

GitHub Actions marketplace offers actions to deploy DACPAC or run SQL scripts.

Why this answer

Azure DevOps with SQL Server database projects (DACPAC) is a standard approach for schema deployment. GitHub Actions also supports deploying DACPACs or running SQL scripts. Option A and Option C are correct.

Option B is for SQL Server on-premises. Option D is for running T-SQL, not CI/CD. Option E is for data migration, not schema changes.

Practice this question →
17
MCQhard

You have an Azure SQL Database that uses a failover group for high availability. You need to automate the failover to the secondary region during a planned maintenance window. What is the best approach?

A.Use Azure CLI or PowerShell to invoke planned failover
B.Create an Azure Automation runbook that uses REST API
C.Schedule an Elastic Database Job to execute a failover script
D.Configure Azure Traffic Manager to route traffic to secondary
AnswerA

Failover groups support automated planned failover via CLI/PowerShell.

Why this answer

Option A is correct because Azure SQL Database failover groups support planned failover via Azure CLI or PowerShell, which can be automated. Option B is wrong because Azure Automation runbooks can also be used, but the CLI is more direct. Option C is wrong because Elastic Jobs are not for failover.

Option D is wrong because Azure Traffic Manager is for DNS-level traffic routing, not database failover.

Practice this question →
18
MCQmedium

You are responsible for automating database maintenance on multiple Azure SQL Databases in an elastic pool. You need to rebuild fragmented indexes weekly. The solution should minimize resource contention. What should you implement?

A.Enable automatic tuning and set 'INDEX_REBUILD' option.
B.Create a SQL Agent job on each database to rebuild indexes during off-peak.
C.Use Elastic Database Jobs with a T-SQL script that rebuilds indexes, and schedule it weekly.
D.Write a PowerShell script that connects to each database sequentially and rebuilds indexes.
AnswerC

Elastic Jobs are designed for this scenario and can be scheduled to run at low-usage times.

Why this answer

Option C is correct because Elastic Database Jobs can run T-SQL scripts across databases in a pool with concurrency control. Option A is wrong because SQL Agent is not available in Azure SQL Database. Option B is wrong because manual scripts are not automated.

Option D is wrong because automatic tuning does not handle index rebuilds; it manages index creation/drop.

Practice this question →
19
MCQmedium

You have an Azure SQL Managed Instance. You need to automate the execution of a stored procedure every hour to clean up historical data. What is the most appropriate solution?

A.SQL Agent Job
B.Azure Logic Apps with SQL connector
C.Elastic Database Job
D.Azure Automation runbook with T-SQL
AnswerA

SQL Agent Jobs are native to SQL Managed Instance and ideal for scheduled tasks.

Why this answer

Option B is correct because SQL Agent Jobs are available in Azure SQL Managed Instance. Option A is wrong because Azure Automation runbooks can be used but are external. Option C is wrong because Elastic Database Jobs are for multiple databases.

Option D is wrong because it is not a built-in automation feature.

Practice this question →
20
MCQmedium

You have an Azure SQL Database that uses a managed identity to access Azure Key Vault for storing column master keys. You need to automate the rotation of the column master key using Azure Automation. Which steps should you include in the runbook?

A.Use PowerShell cmdlets without authentication; the runbook runs under the Automation account's identity.
B.Use the SQL Server Management Objects (SMO) to rotate the key directly from the runbook without Key Vault.
C.Authenticate using Connect-AzAccount -Identity, then use Set-AzKeyVaultKey to create a new key version, and update the column encryption key using Invoke-SqlCmd.
D.Use the Key Vault REST API with a client secret stored in the runbook.
AnswerC

Managed identity authentication is secure and automated.

Why this answer

Option C is correct because the runbook must authenticate to Key Vault using the managed identity, create a new key, update the column encryption key, and then clean up. Options A and B miss authentication steps. Option D incorrectly uses connection strings.

Practice this question →
21
MCQeasy

You need to automate the deployment of a new Azure SQL Database with a specific performance tier, collation, and firewall rules using infrastructure as code. Which technology should you use?

A.Azure CLI script with az sql db create command.
B.SQL Server Management Studio (SSMS) with a deployment script.
C.PowerShell script with New-AzSqlDatabase cmdlet.
D.ARM template with Microsoft.Sql/servers/databases resource definition.
AnswerD

ARM templates are declarative and the standard for infrastructure as code.

Why this answer

Option B is correct because Azure Resource Manager (ARM) templates are the standard infrastructure-as-code tool for Azure resources. Option A is wrong because PowerShell can be used but is procedural rather than declarative. Option C is wrong because Azure CLI is also procedural.

Option D is wrong because SQL Server Management Studio is a GUI tool, not for automation.

Practice this question →
22
Multi-Selecteasy

Which TWO actions should you perform to enable automatic tuning for an Azure SQL Database?

Select 2 answers
A.Set the database compatibility level to 150
B.Enable automatic tuning at the Azure SQL Database server level
C.Enable automatic tuning at the database level
D.Enable Query Store
E.Configure performance recommendations
AnswersB, C

Server-level inheritance can apply settings to all databases.

Why this answer

Automatic tuning in Azure SQL Database requires enabling it at both the server level (to set the inheritance policy) and the database level (to apply tuning options like FORCE_LAST_GOOD_PLAN or CREATE_INDEX). Option B is correct because enabling at the server level allows you to define a default tuning policy that can be inherited by all databases, which is a prerequisite for per-database tuning. Option C is correct because you must also explicitly enable automatic tuning at the database level to override or confirm the inherited settings for that specific database.

Exam trap

The trap here is that candidates often think enabling Query Store alone (Option D) is sufficient for automatic tuning, but Query Store is merely a data collection mechanism—automatic tuning requires explicit enablement at both the server and database levels to apply corrective actions.

Practice this question →
23
Multi-Selectmedium

Which TWO Azure services can be used to automate the deployment of schema changes to Azure SQL Database?

Select 2 answers
A.Azure Portal
B.SQL Server Management Studio (SSMS)
C.Elastic Database Jobs
D.Azure DevOps Services
E.Azure Automation
AnswersD, E

Supports CI/CD pipelines for database deployments.

Why this answer

Options A and C are correct. Azure DevOps Services provides pipeline capabilities for CI/CD. Azure Automation can be used with runbooks to execute T-SQL scripts.

Option B is wrong because SQL Server Management Studio (SSMS) is a manual tool. Option D is wrong because Elastic Database Jobs are for ad-hoc execution, not a full deployment pipeline. Option E is wrong because Azure Portal is manual.

Practice this question →
24
Multi-Selectmedium

Which TWO actions can you perform using Elastic Database Jobs in Azure SQL Database?

Select 2 answers
A.Add a firewall rule to allow access from a specific IP address.
B.Schedule a job to rebuild indexes on a set of databases.
C.Create users in Microsoft Entra ID for database access.
D.Automatically scale the service tier of a database based on CPU usage.
E.Run a T-SQL script to update statistics on all databases in an elastic pool.
AnswersB, E

Elastic Database Jobs can be scheduled to run T-SQL maintenance.

Why this answer

Options A and D are correct. Elastic Database Jobs can run T-SQL scripts across multiple databases and can be scheduled. Option B is wrong because Elastic Database Jobs cannot scale databases.

Option C is wrong because Elastic Database Jobs cannot configure firewall rules. Option E is wrong because Microsoft Entra ID user creation is not a typical use case for Elastic Database Jobs.

Practice this question →
25
Multi-Selecthard

Your company uses Azure SQL Managed Instance for a critical application. You need to automate the process of rebuilding all indexes that have fragmentation above 30% across all databases on the instance. The automation must run every Sunday at 2:00 AM and must log the results to a table. Which three steps should you take to implement this automation?

Select 3 answers
A.Create a logging table in a database to record start time, end time, and status.
B.Use an Azure Automation runbook with the PowerShell module SqlServer to connect and run the maintenance.
C.Create a SQL Agent job on the Managed Instance scheduled for Sunday at 2:00 AM.
D.Use Elastic Database Jobs to run the script across all databases.
E.Write a T-SQL script that uses sys.dm_db_index_physical_stats and ALTER INDEX REBUILD.
AnswersA, C, E

Logging is required for tracking the automation.

Why this answer

Option A is correct because SQL Agent can run T-SQL jobs on Managed Instance. Option C is correct because creating a stored procedure to check fragmentation and rebuild indexes is a standard approach. Option D is correct because logging to a table is necessary for tracking.

Option B is incorrect because Azure Automation runbooks cannot directly connect to SQL Managed Instance easily without hybrid workers. Option E is incorrect because Elastic Jobs is for Azure SQL Database, not Managed Instance.

Practice this question →
26
MCQmedium

Refer to the exhibit. You deploy this ARM template for an Azure SQL Database. The deployment succeeds. Which statement about the deployed database is true?

A.The database is provisioned tier
B.The database is in serverless tier
C.The database is zone redundant
D.The database maximum size is 250 GB
AnswerA

S2 is a provisioned tier service objective.

Why this answer

Option C is correct because the autoPauseDelay property is only valid for serverless databases, but the requestedServiceObjectiveName is S2 (provisioned tier). The deployment will fail because autoPauseDelay is not applicable. But the stem says deployment succeeds, so we must assume the property is ignored or the database is serverless? Actually, autoPauseDelay is ignored for provisioned tier, and the database will be provisioned.

So option C is false. Option A is correct because the property is ignored. Option B is wrong because zoneRedundant is false.

Option D is wrong because maxSizeBytes is 5 GB, which is larger than 250 GB. Wait, 5368709120 bytes = 5 GB, which is less than 250 GB. So D is false.

The correct answer: The database is provisioned tier S2, autoPauseDelay is ignored.

Practice this question →
27
MCQeasy

You need to automate the backup of an Azure SQL Database to a storage account. Which built-in feature should you configure?

A.Use elastic jobs to schedule backups.
B.Use Azure Backup to create a backup policy.
C.Configure a SQL Server Agent job to perform backups.
D.Configure a long-term retention policy for the database.
AnswerD

Long-term retention policy automates backup retention beyond the default 7-35 days.

Why this answer

Azure SQL Database has automated backups enabled by default. Long-term retention (LTR) policies can be configured to keep backups for up to 10 years. Option A is wrong because SQL Server Agent is not available in Azure SQL Database.

Option C is wrong because Azure Backup is for IaaS VMs, not PaaS databases. Option D is wrong because elastic jobs are for scheduling tasks across multiple databases, not for backup automation.

Practice this question →
28
MCQmedium

You are reviewing an ARM template snippet for an Azure SQL Database. The database should be configured to automatically pause after 60 minutes of inactivity and resume with a minimum capacity of 0.5 vCores. However, the database is not pausing as expected. What is the most likely cause?

A.The 'zoneRedundant' property is set to false, which prevents auto-pause.
B.The database is not configured with the 'Serverless' compute tier.
C.The 'autoPauseDelay' value is too low; it must be at least 360 minutes.
D.The API version does not support auto-pause.
AnswerB

Auto-pause is only available in the serverless compute tier.

Why this answer

The 'autoPauseDelay' property is used for serverless databases. For serverless, 'minCapacity' is also required. The snippet is missing the 'sku' property with 'Serverless' tier.

Without the serverless tier, auto-pause is not available. Option A is wrong because zone redundancy does not affect auto-pause. Option B is wrong because the properties are correct but the tier is missing.

Option D is wrong because the API version supports serverless.

Practice this question →
29
MCQmedium

You need to automate the scaling of an Azure SQL Database in response to CPU usage using Azure Automation. Which Azure service should you use to monitor CPU metrics and trigger the runbook?

A.Azure SQL Analytics
B.Azure Log Analytics
C.Kusto Query Language (KQL)
D.Azure Monitor metric alerts
AnswerD

Metric alerts can trigger Automation runbooks when conditions are met.

Why this answer

Option B is correct because Azure Monitor alerts can be configured to trigger an Automation runbook when a metric (like CPU) crosses a threshold. Option A is for analyzing logs, not triggering actions. Option C is for server monitoring, not directly triggering runbooks.

Option D is a query language, not a service.

Practice this question →
30
MCQmedium

Refer to the exhibit. You are reviewing an ARM template snippet for an Azure SQL Database backup long-term retention policy. The template is deployed successfully. Which statement is true about the resulting backup retention?

A.Monthly backups are retained for 24 months.
B.Weekly backups are retained for 8 weeks.
C.Yearly backups are retained for 1 year.
D.Weekly backups are retained for 4 weeks.
AnswerD

The weeklyRetention property is set to P4W, which is 4 weeks.

Why this answer

The ARM template snippet defines a backup long-term retention policy for Azure SQL Database. The `weeklyRetention` property is set to `P4W`, which in ISO 8601 duration format means a period of 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks.

Option D correctly reflects this configuration.

Exam trap

The trap here is that candidates often misread the ISO 8601 duration values (e.g., confusing P4W with 4 months or assuming P12M means 24 months) or incorrectly associate the retention period with a different backup frequency (e.g., thinking weekly retention applies to monthly backups).

How to eliminate wrong answers

Option A is wrong because the `monthlyRetention` property is set to `P12M`, which retains monthly backups for 12 months, not 24. Option B is wrong because the `weeklyRetention` property is set to `P4W`, which retains weekly backups for 4 weeks, not 8. Option C is wrong because the `yearlyRetention` property is set to `P1Y`, which retains yearly backups for 1 year, but the question asks for the resulting backup retention based on the template, and the correct statement is about weekly retention, not yearly; additionally, the yearly retention is indeed 1 year, but that is not the correct answer because the question expects the specific true statement among the options, and D is the only one that matches the template's weekly retention value.

Practice this question →
31
MCQmedium

You have an Azure SQL Database that stores sensitive customer data. You need to automate the masking of a specific column for non-admin users. Which feature should you use?

A.Always Encrypted with a column encryption key.
B.Row-Level Security to filter rows for non-admin users.
C.Dynamic Data Masking with a masking function configured on the column.
D.Azure Policy to enforce masking rules on the database.
AnswerC

Dynamic Data Masking masks data at query time based on user permissions.

Why this answer

Option A is correct because Dynamic Data Masking can be configured via T-SQL or Azure portal and masks data automatically for non-admin users. Option B is wrong because Always Encrypted encrypts data at rest and in transit, but does not mask for non-admins. Option C is wrong because Row-Level Security filters rows, not columns.

Option D is wrong because Azure Policy cannot mask columns.

Practice this question →
32
MCQmedium

A company has multiple Azure SQL Databases and wants to automate the deployment of schema changes using a CI/CD pipeline. They need to ensure that changes are automatically rolled back if any step fails. Which Azure service should they use?

A.Azure Automation with PowerShell runbooks
B.Azure SQL Database elastic jobs
C.Azure Machine Learning
D.Azure DevOps with Azure SQL Database deployment task
AnswerD

Azure DevOps provides CI/CD pipelines with built-in support for Azure SQL Database deployment, including transactional deployment and rollback.

Why this answer

Azure SQL Database elastic jobs are designed for automating administrative tasks, not CI/CD. Azure Automation with PowerShell runbooks can orchestrate rollbacks but requires custom scripting. Azure DevOps with Azure SQL Deployment task supports transactional deployments with built-in rollback on failure.

Azure Machine Learning is irrelevant.

Practice this question →
33
MCQhard

Your Azure SQL Database uses a failover group for disaster recovery. You need to automate a planned failover for disaster recovery testing without data loss. What should you use?

A.Set up an Azure Logic App with a failover trigger from Azure Monitor.
B.Configure automatic failover policy in the failover group.
C.Use a T-SQL script with ALTER DATABASE FAILOVER scheduled via SQL Server Agent.
D.Create an Azure Automation Runbook that uses the Start-AzSqlDatabaseFailover command with the -AllowDataLoss parameter set to false.
AnswerD

Runbook can automate planned failover without data loss.

Why this answer

Option C is correct because Azure Automation Runbooks can execute the Start-AzSqlDatabaseFailover cmdlet which supports planned failover without data loss. Option A is wrong because failover group automatic failover is for unplanned events. Option B is wrong because T-SQL ALTER DATABASE FAILOVER is for manual failover but not automated.

Option D is wrong because Logic Apps can call REST API but Runbooks are more appropriate.

Practice this question →
34
MCQeasy

You need to automate the backup of an on-premises SQL Server database to Azure Blob Storage using the latest Microsoft recommended method. Which approach should you use?

A.Use SQL Server Management Studio to create a maintenance plan that uses the 'Back Up Database' task with destination = URL.
B.Use PowerShell with the Backup-SqlDatabase cmdlet and the -BackupContainer parameter.
C.Use Azure Backup for SQL Server in Azure VMs.
D.Use the 'Backup to Windows Azure' feature in SQL Server 2012.
AnswerA

Maintenance plans support URL backup with SAS token.

Why this answer

Option A is correct because SQL Server Backup to URL using the BACKUP TO URL command with a shared access signature is the recommended method for backing up to Azure Blob Storage. Options B and C are outdated or less secure. Option D requires additional software.

Practice this question →
35
MCQhard

You are configuring automated performance tuning for an Azure SQL Database. You want to automatically create missing indexes and drop unused indexes, but you need to prevent automatic index creation during peak business hours (9 AM - 5 PM). What is the best approach?

A.Enable automatic indexing in Azure SQL Database and rely on the system to decide when to create indexes.
B.Enable automatic tuning but set the 'INDEX_CREATE' option to 'OFF' and use a schedule to run a script that enables it during off-peak hours and disables during peak hours.
C.Use SQL Server Agent to schedule index creation scripts that check for missing indexes.
D.Use Azure Automation to run index analysis scripts only during off-peak hours.
AnswerB

You can toggle the setting using ALTER DATABASE SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = OFF) and schedule changes.

Why this answer

Option D is correct because Azure SQL Database's automatic tuning can be enabled but with a schedule that restricts index creation to off-peak hours using the 'SET AUTOMATIC_TUNING' command with a schedule. Options A and B do not provide scheduling. Option C is not available.

Practice this question →
36
Multi-Selecthard

You are automating index maintenance for an Azure SQL Database. Which THREE of the following should you consider to minimize performance impact?

Select 3 answers
A.Use the ONLINE = ON option when rebuilding indexes.
B.Always use SORT_IN_TEMPDB = ON to reduce tempdb contention.
C.Set MAXDOP to 0 to use all CPUs for faster execution.
D.Reorganize indexes with fragmentation below 30% instead of rebuilding.
E.Use a fill factor of 70-80% for indexes with frequent insert/update operations.
AnswersA, D, E

Allows concurrent access.

Why this answer

Option A is correct because using ONLINE = ON when rebuilding indexes in Azure SQL Database allows concurrent user access to the underlying table during the index rebuild operation. This minimizes blocking and performance impact on production workloads, as the index rebuild is performed as a series of short-term locks rather than a single long-term schema modification lock.

Exam trap

The trap here is that candidates often assume SORT_IN_TEMPDB = ON always improves performance, but in Azure SQL Database it can increase tempdb contention, and MAXDOP = 0 is mistakenly thought to be optimal for maintenance tasks when it actually risks resource starvation.

Practice this question →
37
MCQeasy

You need to automate the generation of a weekly report that shows the top 10 queries by CPU consumption in an Azure SQL Database. The report should be emailed to the DBA team. Which service should you use to gather the query performance data?

A.SQL Server Profiler
B.Database Engine Tuning Advisor
C.Azure Monitor Metrics
D.Query Performance Insight
AnswerD

Query Performance Insight provides aggregated query performance data and can be queried via DMVs like sys.dm_db_query_store_stats.

Why this answer

Query Performance Insight in Azure SQL Database provides query-level performance metrics including CPU, duration, and execution count. It can be queried via DMVs or the Azure portal. Option C is correct.

Option A is for database schema. Option B is for query hints. Option D is not for Azure SQL Database.

Practice this question →
38
MCQmedium

Refer to the exhibit. A PowerShell script is used to update an Azure SQL Database. The script runs without errors, but the database is not moved to the specified elastic pool. What is the most likely cause?

A.The elastic pool 'pool-prod' does not exist.
B.The ServerName parameter is missing the fully qualified domain name.
C.The script sets both Edition and RequestedServiceObjectiveName, which is invalid when moving to an elastic pool.
D.The cmdlet requires the -Force parameter to move a database.
AnswerC

When moving a database to an elastic pool, you should not specify RequestedServiceObjectiveName; the pool's tier determines the service objective.

Why this answer

The Set-AzSqlDatabase cmdlet can move a database into an elastic pool by specifying the ElasticPoolName parameter. However, the Edition parameter must match the pool's edition (e.g., Standard for Standard pool). The script sets Edition to 'Standard', which is correct.

But the cmdlet also requires that the database is not already in a pool; if it is, you need to specify -RemoveElasticPool first. However, the most common issue is that the Edition parameter conflicts if the database is in a different service tier. Option D is correct: the script sets Edition and RequestedServiceObjectiveName simultaneously, which can cause a conflict because when moving to a pool, you should not specify RequestedServiceObjectiveName.

Option A is incorrect because version is not a parameter. Option B is incorrect because the pool exists. Option C is incorrect because -Force is not required.

Practice this question →
39
MCQhard

Your company uses Azure SQL Managed Instance. You need to automate the execution of a stored procedure that processes sales data every night at 2 AM. The solution must use native capabilities and minimize latency. What should you do?

A.Create an Elastic Database Job to run the stored procedure on the target database.
B.Use Azure Logic Apps with a recurrence trigger to call the stored procedure via a connector.
C.Create a SQL Agent job with a schedule to execute the stored procedure.
D.Deploy an Azure Function with a timer trigger to invoke the stored procedure via the SQL binding.
AnswerC

SQL Agent is fully supported in Managed Instance and provides native scheduling.

Why this answer

Option B is correct because Azure SQL Managed Instance supports SQL Agent, which can schedule jobs natively. Option A is wrong because Elastic Database Jobs require creating a job database and agent, adding overhead. Option C is wrong because Azure Functions would need to connect externally and may have higher latency.

Option D is wrong because Logic Apps add complexity and cost.

Practice this question →
40
MCQmedium

You need to automate the execution of a T-SQL script against all user databases in an Azure SQL Database elastic pool. The script should run on a schedule and results should be logged to a table. Which feature should you use?

A.SQL Agent jobs
B.Azure Data Factory
C.Azure Automation with PowerShell runbooks
D.Elastic Database Jobs
AnswerD

Elastic jobs can target a group of databases and execute T-SQL scripts.

Why this answer

Elastic Database Jobs are designed for executing T-SQL across many databases in a pool. SQL Agent is not available in Azure SQL Database. Azure Automation runbooks can run T-SQL but require more setup.

Azure Data Factory is for data movement, not T-SQL execution.

Practice this question →
41
MCQhard

You manage a fleet of 100 Azure SQL Databases used by different departments. You need to automate the deployment of schema changes across all databases with rollback capability. What is the best approach?

A.Create an Elastic Database Job that runs the schema script
B.Implement a CI/CD pipeline using Azure DevOps with Azure SQL Database deployment tasks
C.Use SQL Server Management Studio (SSMS) to run scripts against each database
D.Use Azure Automation with PowerShell to run Invoke-SqlCmd
AnswerB

Provides automated, repeatable deployments with rollback support.

Why this answer

Option B is correct because Azure SQL Database supports deployment pipelines with CI/CD using Azure DevOps or GitHub Actions, allowing automated schema changes with rollback. Option A is wrong because SQL Server Management Studio (SSMS) is a manual tool. Option C is wrong because Elastic Database Jobs are for ad-hoc scripts, not for complex deployments with rollback.

Option D is wrong because it lacks full automation and rollback.

Practice this question →
42
Drag & Dropmedium

Drag and drop the steps to migrate an on-premises SQL Server database to Azure SQL Database using the Data Migration Assistant (DMA) in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps
Order

Why this order

First assess and fix issues, then deploy the target, then migrate schema, then data.

Practice this question →
43
Multi-Selecthard

You need to automate the deployment of Azure SQL Database with a specific configuration across multiple environments (dev, test, prod). The deployment must include firewall rules, auditing settings, and threat detection policies. Which THREE tools can be used to implement this automation?

Select 3 answers
A.ARM templates with Bicep
B.Azure Migrate
C.Azure PowerShell
D.Azure Data Studio
E.Azure CLI
AnswersA, C, E

Declarative infrastructure as code.

Why this answer

Options A, C, and D are correct because ARM/Bicep templates can define the entire infrastructure, Azure CLI can be used in scripts, and Azure PowerShell can also be used. Option B (Azure Data Studio) is a client tool for querying, not automation. Option E (Azure Migrate) is for migration.

Practice this question →
44
MCQmedium

You are implementing automated table partitioning maintenance for a large Azure SQL Database. The partitioning function uses a monthly range. You need to add a new partition for the next month and remove the oldest partition. What is the best way to automate this?

A.Create a stored procedure that uses SELECT INTO to copy old data to a history table and then TRUNCATE the partition.
B.Use ALTER INDEX REORGANIZE to compress old partitions.
C.Schedule a T-SQL script via Elastic Database Jobs that uses ALTER PARTITION FUNCTION to SPLIT the range and MERGE the oldest boundary.
D.Use the SWITCH PARTITION operation to move the oldest partition to a staging table and then drop the staging table.
AnswerC

ALTER PARTITION FUNCTION with SPLIT and MERGE is the standard way to add and remove partitions.

Why this answer

Splitting and merging partition ranges is done with T-SQL ALTER PARTITION FUNCTION and ALTER PARTITION SCHEME. The SWITCH operation moves data to a staging table, but splitting does not require it. Option A is correct.

Option B is for columnstore indexes. Option C is for data archiving. Option D is for data movement, not partition management.

Practice this question →
45
MCQeasy

Refer to the exhibit. An administrator runs this Azure CLI command. What is the immediate effect?

A.The server's performance tier is changed to S2.
B.The OrdersDB database's service objective is updated to S2.
C.The database is scaled down from a higher tier to S2.
D.A new database named OrdersDB is created with S2 tier.
AnswerB

The command updates the database's service tier to S2.

Why this answer

Option C is correct because the command changes the service objective to S2, which updates the performance level of the database. Option A is wrong because the command only updates the current database, not the server. Option B is wrong because it's an update, not a creation.

Option D is wrong because S2 is a higher tier than S0? Actually S2 is higher than S0, but the command does not specify previous tier; it sets to S2.

Practice this question →
46
MCQmedium

You have an Azure SQL Database that uses a serverless compute tier. You want to automate the process of pausing the database during non-business hours (8 PM to 6 AM) to save costs. The database should automatically resume when the first connection is attempted. What should you configure?

A.Create a SQL Agent job to run ALTER DATABASE PAUSE and ALTER DATABASE RESUME.
B.Configure an elastic job to scale down to 0 vCores during off-hours.
C.Set the auto-pause delay to 10 hours in the serverless configuration.
D.Use Azure Automation to run a script that calls the REST API to pause/resume the database.
AnswerC

The auto-pause delay can be configured to a maximum of 24 hours.

Why this answer

Option A is correct because serverless databases automatically pause after a period of inactivity and resume on first connection; you can set the auto-pause delay to 10 hours (from 8 PM to 6 AM). Options B and C are not applicable. Option D is for elastic pools.

Practice this question →
47
MCQmedium

Refer to the exhibit. You are reviewing the JSON configuration for an elastic job. The job is expected to run every week on Sunday at midnight UTC. However, the job is not executing as scheduled. What is the most likely cause?

A.The retention policy is set too low, causing the job to be pruned before execution.
B.The startTime is in the past; the job should have a future start time.
C.The job definition is missing the 'scheduleType' property.
D.The interval and frequency properties are conflicting; interval should be removed for weekly frequency.
AnswerD

For weekly frequency, interval is not used; it causes ambiguity.

Why this answer

The job configuration includes both an 'interval' property (e.g., 'PT1H' for every hour) and a 'frequency' property set to 'Weekly'. In Azure Elastic Jobs, when 'frequency' is specified, the 'interval' property is ignored or causes a conflict because the schedule is defined by the cron-like recurrence pattern. Removing the 'interval' property resolves the conflict and allows the weekly schedule to execute correctly.

Exam trap

Microsoft often tests the misconception that both 'interval' and 'frequency' must always be specified together, when in fact for weekly or monthly frequencies, 'interval' is not applicable and its presence causes a scheduling conflict.

How to eliminate wrong answers

Option A is wrong because the retention policy controls how long job history is kept, not whether the job executes; a low retention policy would prune history but not prevent execution. Option B is wrong because Azure Elastic Jobs allow startTime in the past; the job will run at the next scheduled recurrence after the startTime. Option C is wrong because the 'scheduleType' property is not required for elastic job schedules; the schedule is defined by 'frequency' and 'interval' properties.

Practice this question →
48
MCQhard

Your company has a critical Azure SQL Database named SalesDB in the West US region. To meet disaster recovery requirements, you configured active geo-replication with a readable secondary replica in East US. You need to automate failover in the event of a regional outage. The solution must ensure that the failover is initiated automatically based on health metrics and that the application connection string is updated to point to the new primary. Currently, the application uses a connection string with the primary server name. You have the following requirements: minimize manual intervention, use built-in Azure features, and ensure that failover is tested regularly. You consider the following options: A) Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover. B) Configure auto-failover group with read-write failover policy and grace period. C) Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS. D) Use Azure Automation with a runbook that checks the database status and initiates failover via REST API. Which option best meets the requirements?

A.Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover.
B.Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS.
C.Configure auto-failover group with read-write failover policy and grace period.
D.Use Azure Automation with a runbook that checks the database status and initiates failover via REST API.
AnswerC

Auto-failover groups automatically fail over based on health metrics and provide a listener endpoint that updates automatically.

Why this answer

Option C is correct because auto-failover groups are the built-in Azure feature designed specifically for automating failover of Azure SQL Database geo-replication. They support a read-write failover policy with a configurable grace period that automatically triggers failover based on health metrics, and they automatically update the connection string endpoint for applications using the failover group listener name, not the individual server name. This minimizes manual intervention and allows regular testing via manual failover without affecting the production environment.

Exam trap

The trap here is that candidates may think custom automation (Logic App or Automation runbook) is needed for automatic failover, but they overlook that auto-failover groups already provide built-in, policy-driven automatic failover with connection string transparency, which is the simplest and most reliable solution for this scenario.

How to eliminate wrong answers

Option A is wrong because Azure Traffic Manager operates at the DNS level and does not natively understand Azure SQL Database geo-replication health metrics; it cannot automatically trigger a geo-failover or update the database replication state, and it would require custom endpoint monitoring and manual failover scripts. Option B is wrong because creating a Logic App that runs a PowerShell script introduces custom code, manual maintenance, and potential latency, which does not minimize manual intervention or use built-in Azure features as effectively as auto-failover groups. Option D is wrong because Azure Automation with a runbook that checks database status and initiates failover via REST API is a custom solution that requires development, testing, and ongoing management, and it does not provide automatic connection string updates or the built-in grace period and health monitoring of auto-failover groups.

Practice this question →
49
MCQhard

You are designing an automated backup strategy for Azure SQL Database. The compliance policy requires point-in-time restore (PITR) for the last 35 days, and long-term retention (LTR) for 7 years. What is the minimal number of LTR policies needed?

A.One LTR policy per elastic pool.
B.Two LTR policies per server (weekly and yearly).
C.One LTR policy per server.
D.One LTR policy per database.
AnswerD

Each database requires its own LTR policy to define retention periods.

Why this answer

Option C is correct because LTR policies are per database, so you need one policy for each database. Option A is wrong because one policy per server is not possible; policies are database-level. Option B is wrong because two policies per server is incorrect.

Option D is wrong because one policy per elastic pool is incorrect.

Practice this question →
50
Multi-Selecthard

You are responsible for automating index maintenance and statistics updates across multiple Azure SQL Databases in an elastic pool. Each database has varying workloads and sizes. The automation must run weekly during low-traffic periods and should not degrade performance for critical applications. Which three actions should you take to implement a reliable and efficient automation solution?

Select 3 answers
A.Create an Azure Automation account and schedule a PowerShell runbook that connects to each database and runs index maintenance scripts.
B.Configure SQL Agent jobs in each database to run the maintenance scripts.
C.Use Elastic Database Jobs to run T-SQL scripts against all databases in the elastic pool.
D.Enable automatic tuning for index management and statistics update in each database.
E.Enable auto-failover groups for all databases to automate failover during maintenance.
AnswersA, C, D

Azure Automation can execute T-SQL scripts on a schedule.

Why this answer

Option A is correct because Azure Automation with PowerShell runbooks allows scheduling and executing T-SQL scripts across databases. Option C is correct because Elastic Database Jobs can target a group of databases in an elastic pool. Option E is correct because Azure SQL Database's automatic tuning can handle index and statistics maintenance, reducing manual effort.

Option B is incorrect because enabling auto-failover groups does not automate maintenance tasks. Option D is incorrect because SQL Agent jobs are not available in Azure SQL Database (only in Managed Instance).

Practice this question →
51
Multi-Selectmedium

You are configuring automated backups for an Azure SQL Database. Which TWO settings can you configure?

Select 2 answers
A.Backup compression.
B.Backup frequency (full, differential, log).
C.Point-in-time restore interval.
D.Backup retention period (in days).
E.Geo-redundant storage (GRS) for backups.
AnswersD, E

Configurable from 7 to 35 days.

Why this answer

Option D is correct because the backup retention period (in days) is a configurable setting for Azure SQL Database automated backups. You can set the retention period for point-in-time restore (PITR) backups between 1 and 35 days, and for long-term retention (LTR) backups up to 10 years. This directly controls how far back you can restore your database.

Exam trap

The trap here is that candidates confuse the configurable retention period with the non-configurable backup frequency or point-in-time restore interval, assuming they can adjust the schedule of full/differential/log backups or directly set the restore window, when in fact Azure SQL Database manages these automatically based on the retention policy.

Practice this question →
52
MCQeasy

You need to automatically scale up an Azure SQL Database to the next service tier when CPU usage exceeds 80% for 10 minutes, then scale back down when CPU drops below 30% for 30 minutes. Which Azure feature should you use?

A.Azure Automation runbook with PowerShell cmdlets to modify the service objective.
B.Elastic Database Jobs to run ALTER DATABASE MODIFY.
C.Azure Logic Apps with a recurrence trigger and SQL DB REST API.
D.Configure autoscale settings in the Azure portal for the SQL database server.
AnswerD

Azure SQL Database supports autoscale based on metrics.

Why this answer

Option D is correct because autoscale settings in Azure SQL Database can be configured to scale based on metrics like CPU percentage. Options A and B are for different purposes. Option C is not a built-in feature.

Practice this question →
53
MCQmedium

You manage an Azure SQL Database that uses Azure SQL Database elastic jobs to execute maintenance scripts across multiple databases. You need to ensure that job execution is logged in a central database for auditing and troubleshooting. What should you configure?

A.Configure the job to write logs to Azure Table Storage using a custom output.
B.Enable SQL Server Audit on the target databases to capture job execution.
C.Ensure the Elastic Job agent uses a dedicated job database to store job execution logs.
D.Set up Azure Monitor diagnostic settings for the elastic job agent.
AnswerC

Elastic jobs log execution details in the job database by default.

Why this answer

Option A is correct because elastic jobs automatically log job execution history in the job database (which is a user-defined database). Option B is wrong because the elastic job agent uses a job database, not Azure Table Storage. Option C is wrong because Azure Monitor logs require separate configuration.

Option D is wrong because SQL Server Audit is for database auditing, not job logging.

Practice this question →
54
MCQmedium

Refer to the exhibit. You are reviewing an Azure Automation runbook configuration that performs backups. What is the most likely issue with this configuration?

A.The runbook is disabled (isEnabled: false).
B.The storage account type Standard_LRS is not suitable for backups.
C.The backup description is missing required information.
D.The retention period (7 days) equals the backup interval (7 days), which could cause data loss if a backup fails.
AnswerD

If a backup fails, the previous backup might be deleted before a new one succeeds.

Why this answer

Option B is correct because the backup schedule frequency is set to every 7 days, but the retentionDays is only 7, meaning each backup will be retained exactly until the next backup, leaving no overlap and potentially causing gaps. Option A is wrong because Standard_LRS is fine for backups. Option C is wrong because isEnabled is true.

Option D is wrong because the description is not an issue.

Practice this question →
55
MCQhard

You are automating the scaling of an Azure SQL Database based on workload patterns. The database uses the DTU purchasing model. You need to ensure that scaling actions do not cause connection drops. Which scaling method should you use?

A.Create a new database at the target size and use point-in-time restore to copy data.
B.Scale using the ALTER DATABASE T-SQL command with the ONLINE option.
C.Use Elastic Database Jobs to schedule scaling during maintenance windows.
D.Use the Azure portal or PowerShell to change the service tier within the same DTU tier (e.g., S2 to S3) to avoid downtime.
AnswerD

Within the same DTU tier (Standard, Premium), scaling is online and does not drop connections.

Why this answer

Azure SQL Database supports online scaling (no connection drops) for both DTU and vCore models. However, when scaling up or down, a brief failover may occur if the target service objective is different enough. For DTU, scaling between tiers (e.g., Standard to Premium) causes a failover.

Within the same tier, scaling is typically online. Option A is correct. Option B is incorrect because PITR does not scale.

Option C is not necessary. Option D is incorrect because scaling is online for same-tier changes.

Practice this question →
56
MCQmedium

Your team uses GitHub Actions for CI/CD. You need to automatically deploy schema changes to an Azure SQL Database after a pull request merge. The solution must use a service principal with minimal permissions. Which authentication method should the workflow use?

A.Service principal with a client secret stored in GitHub Secrets.
B.Azure CLI action using a user-assigned managed identity for the GitHub runner.
C.Environment variables containing the connection string.
D.SQL authentication using a user name and password stored in GitHub Secrets.
AnswerB

Managed identities eliminate credential management and are securely tied to Azure resources.

Why this answer

Option B is correct because Azure CLI with az sql db deploy action and managed identity integrates securely with GitHub Actions. Option A is wrong because SQL authentication requires credentials in secrets, which is less secure. Option C is wrong because connection strings in variables are not recommended.

Option D is wrong because service principal with secret is acceptable but managed identity is more secure.

Practice this question →
57
MCQeasy

You have an Azure SQL Database that must be automatically stopped during weekends to save costs. Which combination of Azure services can achieve this automation?

A.Azure Automation Runbook with PowerShell script using Stop-AzSqlDatabase cmdlet, scheduled via Automation schedule.
B.Azure Logic Apps with a recurrence trigger and the SQL connector to execute ALTER DATABASE SET SUSPEND.
C.Azure Functions with a timer trigger and the Azure SDK to stop the database.
D.Elastic Database Jobs with a T-SQL script to ALTER DATABASE SET SUSPEND.
AnswerA

Automation Runbooks can directly manage Azure resources with PowerShell.

Why this answer

Option B is correct because Azure Automation with PowerShell can stop and start Azure SQL Database using the Stop-AzSqlDatabase cmdlet, scheduled for weekends. Option A is wrong because Azure Logic Apps can stop/start via REST API but require more configuration; Automation Runbooks are simpler. Option C is wrong because Elastic Database Jobs cannot stop databases.

Option D is wrong because Azure Functions can do it but require more custom setup.

Practice this question →
58
Multi-Selecthard

Which THREE components are required to set up automated monitoring and alerting for an Azure SQL Database using Azure Monitor?

Select 3 answers
A.Diagnostic setting for the database
B.Log Analytics workspace
C.Azure Automation account
D.SQL Agent Job
E.Alert rule with action group
AnswersA, B, E

Sends logs and metrics to Log Analytics.

Why this answer

Options B, C, and D are correct. A Log Analytics workspace is needed for storing and querying metrics and logs. A diagnostic setting sends logs and metrics to the workspace.

An alert rule defines conditions and actions. Option A is wrong because a SQL Agent Job is not required. Option E is wrong because Azure Automation is not required.

Practice this question →
59
MCQeasy

You need to automate the process of refreshing a development copy of an Azure SQL Database from a production backup every night. The production database is in the West US region, and the dev database is in East US. What is the most cost-effective method?

A.Export the production database to a BACPAC file, store it in Azure Blob Storage, and import it to dev using a Data Factory pipeline.
B.Configure active geo-replication between production and dev, then initiate a planned failover each night.
C.Use Azure Data Studio to export the database as a script and run it on the dev server.
D.Use the Restore-AzSqlDatabase PowerShell cmdlet to restore the production database to the dev server using a point-in-time backup from the same day.
AnswerD

Point-in-time restore is cost-effective and can be automated.

Why this answer

Option B is correct because Azure SQL Database supports point-in-time restore to a different server and region, which is cost-effective and automated via PowerShell or CLI. Options A and D require manual steps. Option C is for cross-region replication, which is not designed for nightly refresh.

Practice this question →
60
Multi-Selecthard

You need to automate monitoring and alerting for an Azure SQL Database. Which THREE actions can you achieve using Azure Monitor and SQL Insights?

Select 3 answers
A.Automatically rebuild fragmented indexes.
B.Automatically scale the database to a higher service tier.
C.Stream diagnostic logs to a Log Analytics workspace.
D.Set up an alert when DTU consumption exceeds 80% for 5 minutes.
E.Create a custom metric alert based on a KQL query.
AnswersC, D, E

Diagnostic settings allow log streaming.

Why this answer

Option C is correct because Azure Monitor and SQL Insights allow you to configure diagnostic settings to stream SQL Database metrics and resource logs (e.g., query store runtime statistics, wait statistics) to a Log Analytics workspace. This enables centralized log analysis, custom KQL queries, and long-term retention for auditing and troubleshooting.

Exam trap

The trap here is that candidates confuse monitoring and alerting capabilities (Azure Monitor/SQL Insights) with automated remediation actions (like index rebuilds or auto-scaling), which are separate Azure services or manual tasks.

Practice this question →
61
MCQmedium

You are a database administrator for a large e-commerce company. The company uses Azure SQL Database in a Business Critical tier for its transactional systems. The database is part of an elastic pool with multiple databases. You need to automate the process of purging old data from a specific table (OrderHistory) that grows by 5 GB per day. The data must be retained for 90 days. After 90 days, the data should be moved to an Azure Blob Storage archive for long-term storage. The automation must run daily at midnight and must minimize impact on the transactional workload. Additionally, the solution should be cost-effective and require minimal manual intervention. What should you do?

A.Create a SQL Agent job on the database to run a cleanup script at midnight.
B.Use Azure Automation with a PowerShell runbook that connects to the database, deletes old data, and uploads it to Blob Storage using AzCopy.
C.Create a stored procedure that deletes data older than 90 days and exports it to Blob Storage using PolyBase. Schedule the procedure using Elastic Database Jobs.
D.Use Azure Data Factory with a scheduled trigger to copy data older than 90 days to Blob Storage and then delete it from the table.
AnswerC

Elastic Database Jobs can efficiently run T-SQL on a schedule with minimal overhead.

Why this answer

Option B is the correct answer. Elastic Database Jobs can run a stored procedure that deletes data older than 90 days and exports it to Blob Storage using BULK INSERT or PolyBase. This approach is designed for minimal impact and uses Azure's PaaS capabilities.

Option A (Azure Data Factory) is more suitable for complex ETL, but for simple purge and archive, Elastic Jobs is simpler and more cost-effective. Option C (Azure Automation runbook) would require additional components like Hybrid Runbook Worker and may introduce latency. Option D (SQL Agent) is not available in Azure SQL Database.

Practice this question →
62
MCQeasy

You are tasked with automating the creation of user databases in an Azure SQL Managed Instance after deployment. Which tool should you use?

A.Elastic Database Jobs
B.Azure Automation with PowerShell runbooks
C.Azure Resource Manager templates
D.SQL Agent jobs
AnswerB

PowerShell runbooks can connect to the managed instance and run CREATE DATABASE.

Why this answer

Azure Resource Manager templates can deploy the managed instance itself but are not ideal for subsequent database creation. Azure Automation with PowerShell runbooks can execute T-SQL to create databases. SQL Agent jobs are not available in managed instance.

Azure SQL Database elastic jobs are for multiple databases, but simpler for single database creation is Automation.

Practice this question →
63
Drag & Dropmedium

Drag and drop the steps to configure an Azure SQL Database elastic pool in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps
Order

Why this order

First create the pool, then add databases, configure per-database limits, monitor, and scale.

Practice this question →
64
MCQmedium

You are automating the creation of Azure SQL Databases using ARM templates. You need to ensure that the databases are created with the 'Hyperscale' service tier and with 'ZoneRedundant' enabled. Which ARM template property should you set?

A.Set 'sku' to 'HS_Gen5_2' and 'properties.zoneRedundant' to true.
B.Set 'requestedServiceObjectiveName' to 'HS_Gen5_2' and 'zoneRedundant' to true.
C.Set 'edition' to 'Hyperscale' and 'zoneRedundant' to true.
D.Set 'maxSizeBytes' to a value and 'zoneRedundant' to true.
AnswerA

The 'sku' property specifies the service tier, and 'zoneRedundant' is a property of the database.

Why this answer

The 'sku' property in ARM templates defines the service tier. For Hyperscale, the name is 'HS', and tier is 'Hyperscale'. The 'zoneRedundant' property is a separate property under 'properties'.

Option B is wrong because 'edition' is not used in ARM templates for Azure SQL Database. Option C is wrong because 'requestedServiceObjectiveName' is for DTU-based tiers. Option D is wrong because 'maxSizeBytes' does not define the tier.

Practice this question →
65
MCQhard

You are designing an automation strategy for deploying schema changes to 50 Azure SQL Databases in a elastic pool. You need to ensure that changes are applied in a consistent order and rolled back if any database fails. Which approach should you use?

A.Use Azure Data Studio with the Schema Compare extension and apply changes manually.
B.Create an Elastic Job that executes the schema change script within a transaction and uses target group error handling to roll back on failure.
C.Deploy the schema changes using Azure Resource Manager templates with SQL extensions.
D.Use PowerShell to loop through each database and execute the script, catching exceptions.
AnswerB

Elastic Jobs support transactions and error handling.

Why this answer

Option B is correct because Azure SQL Database Elastic Jobs can run T-SQL scripts across multiple databases with transactional control and error handling. Options A and C do not provide rollback capability. Option D is for infrastructure deployment, not schema changes.

Practice this question →
66
Multi-Selectmedium

Which THREE components are required to set up elastic jobs in Azure SQL Database?

Select 3 answers
A.Azure Automation account
B.SQL Server Agent
C.Target groups
D.An elastic job agent
E.Job credentials
AnswersC, D, E

Target groups specify which databases to run the job on.

Why this answer

Target groups (C) are required because they define the set of databases (or elastic pools) against which an elastic job will execute. Without target groups, the job agent has no scope of execution. The elastic job agent (D) orchestrates job scheduling and execution, and job credentials (E) are needed to authenticate to the target databases for running T-SQL scripts.

Exam trap

The trap here is that candidates confuse SQL Server Agent (an on-premises/IaaS tool) with the cloud-native elastic job agent, or assume an Azure Automation account is needed for scheduling, when in fact elastic jobs have their own built-in scheduling and agent service.

Practice this question →
67
MCQmedium

You are a database administrator for a logistics company that uses Azure SQL Managed Instance. The instance hosts a database that tracks shipments. You need to automate the process of updating statistics for all tables in the database every night. The update should only include tables that have had more than 10% of rows modified since the last statistics update. The automation must be efficient and not rebuild statistics unnecessarily. Additionally, the solution should be self-contained within the Managed Instance and not rely on external services. What should you do?

A.Create a SQL Agent job with a T-SQL script that uses sys.dm_db_stats_properties to identify tables needing statistics updates based on modification counters, then executes UPDATE STATISTICS only on those tables.
B.Enable automatic tuning for the database to automatically update statistics.
C.Use Azure Automation with a PowerShell runbook that connects to the instance and runs sp_updatestats.
D.Use Elastic Database Jobs to schedule a T-SQL script that updates all statistics unconditionally.
AnswerA

SQL Agent is available and this approach is efficient.

Why this answer

Option B is correct. SQL Agent is available on Managed Instance and can execute a stored procedure that checks modification counters (sys.dm_db_stats_properties) and updates only those statistics that meet the threshold. Option A is incorrect because Elastic Database Jobs is not available for Managed Instance.

Option C is incorrect because Azure Automation would require a hybrid worker and is less efficient. Option D is incorrect because automatic tuning does not update statistics based on modification percentage; it only manages indexes.

Practice this question →
68
MCQmedium

You are responsible for automating index maintenance in Azure SQL Database. You need to ensure that index rebuilds and reorganizations are performed only when fragmentation exceeds 30% and 10%, respectively, and that the job runs weekly. Which approach should you use?

A.Create a SQL Agent job using T-SQL script with sys.dm_db_index_physical_stats.
B.Use Elastic Database Jobs to run a script that rebuilds/reorganizes indexes.
C.Schedule a maintenance task using SQL Server Agent in a VM running SQL Server.
D.Enable automatic index tuning in Azure SQL Database with the desired fragmentation thresholds.
AnswerD

Automatic index tuning can manage index rebuilds and reorganizations based on fragmentation.

Why this answer

Option C is correct because Azure SQL Database's built-in automatic index tuning can be configured to handle index maintenance based on fragmentation thresholds without custom scripting. Options A and B manage indexes at the database level but require manual job creation. Option D is for on-premises or SQL Server on VM, not Azure SQL Database managed instance or single database.

Practice this question →
69
MCQmedium

A company uses Azure SQL Database and wants to automatically send an email notification when an index fragmentation exceeds 30% for any database. Which solution should they implement?

A.Create an Azure Monitor alert based on fragmentation
B.Use Elastic Database Jobs to check fragmentation and send email
C.Configure SQL Agent job to send email
D.Use Azure Automation runbook to query sys.dm_db_index_physical_stats and send email
AnswerD

PowerShell runbooks can query the DMV and use SendGrid to send email notifications.

Why this answer

Azure Automation runbooks can query fragmentation and send emails via SendGrid. Elastic Database Jobs can check fragmentation but cannot send emails directly. Azure Monitor alerts can trigger on metrics like DTU but not on index fragmentation.

Azure Logic Apps can receive data but require custom setup.

Practice this question →
70
MCQmedium

You are a database administrator for an e-learning platform that uses Azure SQL Database with the General Purpose tier. The database stores course content and user progress. You need to automate the process of exporting the database schema (without data) to a BACPAC file every week for version control. The BACPAC file must be stored in an Azure Blob Storage container with a retention policy of 30 days. The automation must use Azure native services and should not require manual intervention. What should you do?

A.Use Azure Data Factory with a Copy activity to export the database schema to a BACPAC file in Blob Storage.
B.Use Elastic Database Jobs to run a T-SQL script that uses the BACKUP DATABASE command to a URL.
C.Create an Azure Automation runbook with PowerShell that uses the New-AzSqlDatabaseExport cmdlet to export the schema-only BACPAC to a Blob Storage container. Schedule the runbook to run weekly.
D.Create a SQL Agent job on the database to run SqlPackage.exe.
AnswerC

New-AzSqlDatabaseExport can export schema only with the -SchemaOnly parameter.

Why this answer

Option A is correct. Azure Automation runbooks can use the SqlPackage.exe utility (or the New-AzSqlDatabaseExport cmdlet) to export a BACPAC and save it to Blob Storage. You can schedule the runbook weekly.

Option B is incorrect because Data Factory is more complex than needed. Option C is incorrect because Elastic Database Jobs cannot export BACPAC. Option D is incorrect because SQL Agent is not available in Azure SQL Database.

Practice this question →
71
MCQhard

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup retention policy. The database is in a business-critical environment that requires point-in-time restore (PITR) for the last 28 days. What is the issue with this configuration?

A.The JSON syntax is invalid because property names use camelCase instead of PascalCase.
B.Long-term retention properties (weeklyRetention, monthlyRetention, yearlyRetention) are not valid in a backupShortTermRetentionPolicies resource.
C.The diffBackupIntervalInHours should be 12 for a business-critical database.
D.The retentionDays value of 28 exceeds the maximum allowed for PITR.
AnswerB

LTR properties belong to a separate resource type: backupLongTermRetentionPolicies.

Why this answer

The ARM template shows both short-term retention (PITR) and long-term retention (LTR) properties. However, PITR retention is set to 28 days (retentionDays), which is valid. The LTR properties (weeklyRetention, monthlyRetention, yearlyRetention) are part of a different resource (backupLongTermRetentionPolicies).

Mixing them in the same resource will cause a deployment error. Option C is correct. Option A is wrong because 28 days is within limits.

Option B is wrong because LTR is not part of this resource. Option D is wrong because the JSON is syntactically correct.

Practice this question →
72
MCQmedium

Refer to the exhibit. A Logic App is configured to pause an Azure SQL Database named AdventureWorks every Friday at 8 PM. However, the database is not pausing. What is the most likely reason?

A.The SQL connection is not authenticated.
B.The recurrence trigger uses a schedule that is not valid.
C.The API version specified is outdated.
D.The pause action is not supported for Azure SQL Database.
AnswerD

Pause/resume is only available for Azure Synapse dedicated SQL pools, not Azure SQL Database.

Why this answer

Azure SQL Database does not support pause/resume; only Azure SQL Data Warehouse (now Azure Synapse Analytics dedicated SQL pool) supports it. Therefore, the API operation is invalid for Azure SQL Database. Option B is correct.

Option A is incorrect because the connection is properly referenced. Option C is incorrect because the recurrence is valid. Option D is incorrect because API version is not the primary issue.

Practice this question →
73
MCQhard

Refer to the exhibit. A PowerShell script is used to move an Azure SQL Database into an elastic pool. The script runs without error. Which condition must be true before the script runs?

A.The elastic pool must be in a different server
B.The database must be in the same server as the elastic pool
C.The database must be in the Basic tier
D.The database size must be less than 10 GB
AnswerB

Required for moving into a pool.

Why this answer

Option B is correct because moving a database into an elastic pool requires the database to be in the same server as the pool. Option A is wrong because the database can be any tier. Option C is wrong because the database can be any size as long as it fits in the pool.

Option D is wrong because the pool must be in the same server.

Practice this question →
74
MCQeasy

You are reviewing an Azure SQL Database automated backup retention policy. The exhibit shows the current configuration. You need to ensure that the database can be restored to a point in time within the last 7 days at a granularity of 1 minute. Which of the following is a limitation of the current configuration?

A.The backup interval of 12 hours does not allow 1-minute granularity for point-in-time restore.
B.The retention period is too short to support point-in-time restore.
C.The policy must specify a minimum retention of 30 days for point-in-time restore.
D.Geo-redundant backup must be disabled for point-in-time restore to work.
AnswerA

PITR granularity is limited by backup frequency.

Why this answer

The current configuration uses a backup interval of 12 hours, which means automated backups are taken only every 12 hours. Point-in-time restore (PITR) in Azure SQL Database relies on full, differential, and transaction log backups to allow restoration to any second within the retention period. With a 12-hour backup interval, transaction log backups are not frequent enough to achieve 1-minute granularity; the minimum granularity is limited by the frequency of log backups, which defaults to every 5-10 minutes but can be configured.

A 12-hour interval indicates that log backups are not being taken frequently enough, thus preventing 1-minute restore granularity.

Exam trap

The trap here is that candidates often confuse the backup interval (which controls full/differential backups) with transaction log backup frequency, assuming that a 12-hour interval still allows fine-grained point-in-time restore, but in reality, log backup frequency is the key factor for granularity.

How to eliminate wrong answers

Option B is wrong because the retention period (e.g., 7 days) is sufficient for point-in-time restore; PITR requires a minimum retention of 1 day for Basic tier and 7 days for other tiers, so 7 days meets the requirement. Option C is wrong because Azure SQL Database does not mandate a minimum retention of 30 days for point-in-time restore; the default retention is 7 days, and it can be configured up to 35 days, but 7 days is valid. Option D is wrong because geo-redundant backup storage (RA-GRS) does not need to be disabled for point-in-time restore; PITR works with both locally redundant (LRS) and geo-redundant (RA-GRS) backup storage, and disabling it is not a requirement.

Practice this question →
75
Multi-Selectmedium

You need to automate the deployment of schema changes to an Azure SQL Database using Azure DevOps. Which THREE components are required? (Choose three.)

Select 3 answers
A.Build pipeline
B.Release pipeline
C.Elastic job agent
D.Azure Automation runbook
E.Variable group
AnswersA, B, E

The build pipeline compiles the schema changes into artifacts.

Why this answer

A release pipeline defines the deployment steps, a build pipeline produces the artifacts (e.g., .dacpac), and a variable group stores configuration values like connection strings. Option B is wrong because an Azure Automation runbook is not part of DevOps CI/CD. Option D is wrong because elastic jobs are not used for schema deployment.

Practice this question →
Page 1 of 3 · 172 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Automation Tasks questions.

Start 20-question session