Knowledge + Practice

CCNA Configure and manage automation of tasks Questions

75 of 163 questions · Page 1/3 · Configure and manage automation of tasks · Answers revealed

Practice these questions Domain overview All 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

SQL Agent jobs in Azure SQL Managed Instance can execute T-SQL scripts across databases using sp_foreachdb (or a cursor-based loop) to run compliance scripts on each database. Results can be inserted into a central database using three-part names (or cross-database queries). Email notifications can be sent using Database Mail (sp_send_dbmail).

Option A is incorrect because Logic Apps are external tools and not built-in Azure features for this purpose. Option B is incorrect because Azure Automation Runbooks require external connectivity and are not directly integrated with SQL Managed Instance for cross-database execution. Option D is incorrect because Elastic Jobs are designed for Azure SQL Database, not for SQL Managed Instance.

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

AnswerA

Correct. Elastic Database Jobs allow you to automate T-SQL scripts for index maintenance across Azure SQL databases, enabling scheduling of index rebuilds to reduce fragmentation without performance impact.

Why this answer

Elastic Database Jobs can be used to schedule and run T-SQL scripts for index maintenance across one or more databases in Azure SQL Database. This allows you to automate index rebuilds or reorganizations during off-peak hours to reduce fragmentation without impacting performance. Option B is incorrect because automatic tuning in Azure SQL Database focuses on creating and dropping indexes based on query patterns, not on rebuilding fragmented indexes.

Options C and D are incorrect: SQL Agent Jobs are not available in Azure SQL Database single databases, and while Azure Automation runbooks could be used, Elastic Database Jobs provide a more integrated and seamless solution for database-specific tasks.

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

The target group uses membershipType 'Exclude' with a members list containing only 'db1'. This means that the job will target all databases in the logical server except 'db1'. Option A is incorrect because the membership type is 'Exclude', not 'Include', so it does not target only 'db1'.

Option C is incorrect because the target group is at the database level within a server, not the entire server itself. Option D is incorrect because the target group excludes 'db1', so it targets all databases on the server, but not the server as a whole.

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 including the day of week (e.g., Sunday), start time (e.g., 2:00 AM local time), and a notification email for upcoming maintenance. Option C is incorrect because maintenance windows are instance-level, not per database. Option D is incorrect because it applies to 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

Azure SQL Managed Instance supports point-in-time restore (PITR) with a configurable retention period from 1 to 35 days; the default is 7 days, which satisfies the requirement to restore within 2 hours for the last 7 days (the 2-hour target is a recovery point objective, not retention). For long-term retention (LTR) of 5 years, Managed Instance supports LTR backup policies with yearly backups that can retain backups for up to 10 years. Therefore, setting PITR retention to 7 days (default) and configuring an LTR policy with yearly backups for 5 years meets both requirements.

Option A is wrong because Azure Backup for SQL Server in Azure VM is for SQL Server on Azure VMs, not for Managed Instance. Option B is wrong because "geo-redundant backup storage for LTR" is a storage redundancy option, not an LTR retention policy; LTR requires explicit backup frequency and retention configuration. Option C is wrong because PITR retention cannot be set to 2 hours (minimum is 1 day) and custom backup jobs using Elastic Database Jobs are unnecessary; Managed Instance automates backups natively.

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

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 ONE Azure service can be used to achieve automated backups with retention beyond the default 7-35 days?

Select 1 answer

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

AnswersE

Azure SQL Database long-term retention (LTR) backup policy is the built-in feature that allows configuring retention up to 10 years, beyond the default 7-35 days.

Why this answer

Only Azure SQL Database long-term retention (LTR) backup policy provides automated long-term backup retention for Azure SQL Database (PaaS), allowing retention beyond the default 7-35 days up to 10 years. Option C (Azure Backup for SQL Server in Azure VMs) is for SQL Server on Azure VMs (IaaS), not for Azure SQL Database, so it does not apply. The other options (A, B, D) do not provide automated backup with long-term retention for Azure SQL Database.

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

Azure Automation runbooks can use the Export-AzSqlDatabase cmdlet to export each database to BACPAC, and scheduling the runbook for Sundays at 2 AM meets the automation and off-hours requirement, minimizing performance impact. This is straightforward and cost-effective as it uses built-in Azure Automation without extra services. Option A (Elastic Database Jobs) is designed for running T-SQL scripts across databases, not for exporting BACPAC files.

Option B (Azure Data Factory) adds unnecessary complexity and cost for this simple export task. Option D is manual and 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

Bicep templates (B) and ARM templates (C) are both declarative Infrastructure as Code (IaC) tools for defining Azure resources, including Azure SQL Database, firewall rules, Entra ID admin, and vulnerability assessment baselines. An Azure DevOps release pipeline (D) can automate the deployment of these templates, ensuring idempotent and repeatable deployments. SQL Server Management Studio (A) is a management tool for SQL Server, not for IaC or automated deployment.

The Azure portal (E) is a manual GUI and not suitable for repeatable automation.

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 runbooks with Invoke-SqlCmd allow flexible scheduling and execution of T-SQL scripts for maintenance tasks like index rebuild and statistics update.

Why this answer

Azure Automation runbooks with Invoke-SqlCmd allow custom scheduling and execution of T-SQL scripts for index maintenance and statistics updates. Option A (Elastic Database Jobs) is designed for Azure SQL Database, not Managed Instance. Option C (Azure Logic Apps) can trigger but is not optimal for long-running maintenance tasks.

Option D (SQL Agent job) is available in Azure SQL Managed Instance, but it is less flexible for automated scheduling during low usage windows and may impact performance; Azure Automation provides better orchestration and monitoring capabilities. Therefore, the best approach is to use Azure Automation runbook.

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

A Self-Hosted Integration Runtime is required to access an on-premises SQL Server, and the Copy activity can be configured for incremental loading using a watermark column. This supports the requirement for incremental loads and schema changes (by using auto-create table). Option B is incorrect because Azure-SSIS IR is for SSIS package execution, not for data copy with incremental loading.

Option C is incorrect because Azure IR cannot access on-premises networks without a self-hosted IR. Option D is incorrect because a Stored Procedure activity is used for executing stored procedures, not for copying data with incremental loading.

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

Azure Logic Apps provide a no-code/low-code solution with a recurrence trigger to run weekly, the Azure Monitor connector to retrieve metrics (DTU consumption, storage) from Azure SQL Databases, and the Office 365 Outlook connector to send the email report. Option A is less ideal because Azure Automation Runbooks require custom scripting for email and are more complex. Option C is incorrect because Elastic Database Jobs are designed for T-SQL administration tasks across databases, not for gathering metrics via Azure Monitor.

Option D is incorrect because Azure Data Factory focuses on data movement and transformation, not on scheduling email reports with live metrics.

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 B, C, and E are correct. An Azure SQL Database deployment task (e.g., SqlAzureSqlDatabaseDeployment task) executes the schema change scripts. An Azure Resource Manager service connection to the SQL Database is required for authentication and permissions.

A YAML pipeline file defines the build and release stages including the deployment steps. Option A is incorrect because an Elastic Database Job agent is used for multi-database management, not for automated deployment from Git for a single database. Option D is incorrect because an Azure Automation Runbook is not part of Azure Pipelines; it is used for process automation outside of CI/CD.

Practice this question →

15

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

GitHub Actions with the Azure SQL Database deployment action (Option B) and Azure DevOps release pipeline with the SQL Server database project (DACPAC) deployment task (Option C) are both valid tools for automating schema deployments to Azure SQL Database in a CI/CD pipeline. Option A (Azure Data Studio SQLCMD mode) is manual and not designed for CI/CD integration. Option D (SQL Server Import and Export Wizard) is a data migration tool, not for schema changes.

Option E (SSMS) is a management tool without native CI/CD automation.

Practice this question →

16

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

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 →

17

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

SQL Agent Jobs are a built-in feature of Azure SQL Managed Instance, providing native scheduling for T-SQL jobs like executing a stored procedure every hour. Option B is less appropriate because Azure Logic Apps is an external service that adds complexity and latency. Option C is designed for executing tasks across multiple databases, not a single instance.

Option D is external and less integrated compared to the native SQL Agent Job.

Practice this question →

18

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

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 →

19

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

Azure Resource Manager (ARM) templates are the standard infrastructure-as-code tool for Azure, allowing declarative deployment of Azure SQL Database with specific settings like performance tier, collation, and firewall rules. Option A (Azure CLI) and Option C (PowerShell) are procedural scripts that can be used but do not provide true declarative IaC. Option B (SSMS) is a GUI tool, not suitable for automation.

Practice this question →

20

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 →

21

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 D and E are correct. Azure DevOps Services provides pipeline capabilities for CI/CD to automate deployment of schema changes. Azure Automation can be used with runbooks to execute T-SQL scripts on a schedule or event trigger.

Option A (Azure Portal) is a manual interface. Option B (SSMS) is a manual tool. Option C (Elastic Database Jobs) is for ad-hoc execution, not a full deployment pipeline.

Practice this question →

22

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 B and E are correct. Elastic Database Jobs in Azure SQL Database allow you to schedule and run T-SQL scripts across multiple databases, including those in an elastic pool. You can use them to schedule index rebuilding (B) and to update statistics via T-SQL (E).

Option A is incorrect because firewall rules are managed through Azure SQL Server firewall settings, not Elastic Database Jobs. Option C is incorrect because creating users in Microsoft Entra ID (formerly Azure AD) is done through Active Directory, not Elastic Database Jobs. Option D is incorrect because scaling service tiers is an administrative operation handled by Azure SQL Database scaling commands or automation, not by Elastic Database Jobs.

Practice this question →

23

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

Creating a logging table is necessary to record the results of the index maintenance operations. Option C is correct because SQL Agent jobs can be scheduled on a Managed Instance to run tasks at specific times, such as every Sunday at 2:00 AM. Option E is correct because a T-SQL script using sys.dm_db_index_physical_stats to identify fragmented indexes and ALTER INDEX REBUILD to rebuild them is the standard approach.

Option B is incorrect because Azure Automation runbooks require additional setup (like Hybrid Runbook Worker) to connect to SQL Managed Instance, and it is not the simplest way to schedule a recurring task on the instance. Option D is incorrect because Elastic Database Jobs is designed for Azure SQL Database and is not supported on Azure SQL Managed Instance.

Practice this question →

24

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

The ARM template specifies `requestedServiceObjectiveName: S2`, which corresponds to a provisioned tier (S2). The `autoPauseDelay` property is only applicable to serverless databases; when used with a provisioned tier, it is ignored and does not cause the deployment to fail. Therefore, the database is deployed as a provisioned tier S2 database.

Option A is correct. Option B is incorrect because the database is not serverless. Option C is incorrect because `zoneRedundant` is set to false.

Option D is incorrect because `maxSizeBytes` is 5 GB, which is less than 250 GB.

Practice this question →

25

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

Configuring a long-term retention policy is the correct built-in feature to retain automatic backups for longer periods, up to 10 years.

Why this answer

Azure SQL Database has automated backups enabled by default, which are managed by Microsoft. To retain backups beyond the default retention period (7-35 days), you can configure a long-term retention (LTR) policy to store backups for up to 10 years. Option A is incorrect because elastic jobs are used for performing administrative tasks across multiple databases, not for scheduling backups (backups are automatic and cannot be scheduled via elastic jobs).

Option B is incorrect because Azure Backup is designed for IaaS VMs and Azure Files, not for Azure SQL Database, which has its own built-in backup mechanism. Option C is incorrect because SQL Server Agent is not available in Azure SQL Database (it is a feature of SQL Server on-premises or on IaaS VMs); therefore, you cannot use SQL Server Agent jobs for backup automation. Option D is correct because a long-term retention policy is the built-in feature that allows you to keep automated backups for extended periods.

Practice this question →

26

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

Auto-pause functionality is only available for Azure SQL Database in the serverless compute tier. Without configuring the database as serverless (by setting the 'sku' property to include 'Serverless' tier), the auto-pause and auto-resume features will not work, regardless of other property settings. Option A is incorrect because the 'zoneRedundant' property does not affect auto-pause.

Option C is incorrect because the minimum 'autoPauseDelay' for serverless is 60 minutes, not 360. Option D is incorrect because the API version used supports serverless features.

Practice this question →

27

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

Azure Monitor metric alerts can be configured to trigger an Automation runbook when a metric (like CPU) crosses a threshold. Option A (Azure SQL Analytics) is for analyzing logs, not triggering actions. Option B (Azure Log Analytics) is for log analytics, not for direct metric alerts triggering runbooks.

Option C (Kusto Query Language) is a query language, not a service.

Practice this question →

28

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 →

29

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

Dynamic Data Masking (DDM) is the correct feature for masking a specific column for non-admin users. DDM can be configured on a column using a masking function (e.g., default, email, custom) and automatically obfuscates the data in query results for users without the UNMASK permission. Option A (Always Encrypted) encrypts the column but does not mask it selectively; non-admin users cannot see the data at all unless they have the key.

Option B (Row-Level Security) filters entire rows based on a predicate, not columns. Option D (Azure Policy) applies governance rules at the subscription/resource level and cannot mask database columns.

Practice this question →

30

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 →

31

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

Azure Automation Runbooks can execute the Start-AzSqlDatabaseFailover cmdlet with -AllowDataLoss:$false to perform a planned failover without data loss. Option A (Logic App with failover trigger) is designed for reacting to Azure Monitor alerts, not for scheduled failsafe testing. Option B (configuring automatic failover policy) is for unplanned failovers and would result in data loss if used for testing because automatic failover is asynchronous.

Option C (T-SQL ALTER DATABASE FAILOVER scheduled via SQL Server Agent) is invalid because SQL Server Agent is not available in Azure SQL Database (single database or elastic pool); it's only available in Azure SQL Managed Instance. Therefore, D is the correct choice for automated planned failover.

Practice this question →

32

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

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 →

33

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

It leverages Azure SQL Database's built-in automatic tuning feature while still allowing you to enforce a schedule. By setting the 'INDEX_CREATE' option to 'OFF' and using a script (e.g., via Azure Automation or SQL Server Agent) to toggle it on during off-peak hours and off during peak hours, you can automatically create missing indexes outside the prohibited window. This approach combines the intelligence of automatic tuning with your scheduling requirement.

Option A does not allow scheduling; Option C relies on manual scripts without the built-in tuning intelligence; Option D is similar to B but less integrated with automatic tuning.

Practice this question →

34

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

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 →

35

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 a built-in view of query performance metrics, including top queries by CPU, duration, and execution count, making it suitable for generating automated weekly reports.

Why this answer

Query Performance Insight in Azure SQL Database provides query-level performance metrics including CPU, duration, and execution count. It can be used to identify the top 10 queries by CPU consumption and automate report generation. Option D is correct.

Option A (SQL Server Profiler) is a traditional tool for capturing SQL Server events, not for automated reporting in Azure. Option B (Database Engine Tuning Advisor) focuses on index recommendations, not report generation. Option C (Azure Monitor Metrics) provides platform-level metrics but lacks query-level granularity.

Exam trap

Candidates may confuse Query Performance Insight with Azure Monitor Metrics, but Query Performance Insight is specifically designed for query-level analysis in Azure SQL Database.

Practice this question →

36

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, when moving a database to an elastic pool, you must not specify the RequestedServiceObjectiveName parameter because the service objective is determined by the pool. The script incorrectly sets both Edition and RequestedServiceObjectiveName, causing a conflict.

Option C correctly identifies this issue. Option A is incorrect because the pool not existing would cause an error, not a silent failure. Option B is incorrect because the ServerName parameter does not require a fully qualified domain name; the resource group and server name suffice.

Option D is incorrect because the -Force parameter is not required for pool moves.

Practice this question →

37

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

Azure SQL Managed Instance includes SQL Server Agent, which can natively schedule T-SQL jobs with minimal latency. Option A is incorrect because Elastic Database Jobs require a separate job database and are designed for multi-database scalability, adding overhead. Option B is incorrect because Azure Logic Apps introduce external orchestration, potentially increasing latency and cost.

Option D is incorrect because Azure Functions require external connectivity and are not as tightly integrated as SQL Agent.

Practice this question →

38

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 →

39

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

Azure DevOps CI/CD pipeline with SQL Database deployment tasks provides automated schema changes with built-in rollback via deployment history and script versioning.

Why this answer

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 Elastic Database Jobs are for ad-hoc scripts, not for complex deployments with rollback. Option C is wrong because SQL Server Management Studio (SSMS) is a manual tool.

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

Practice this question →

40

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

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

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

Practice this question →

41

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 E are correct because ARM/Bicep templates can define the entire infrastructure, Azure PowerShell can be used in scripts, and Azure CLI can also be used. Option B (Azure Migrate) is for migration, not automation. Option D (Azure Data Studio) is a client tool for querying, not automation.

Practice this question →

42

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

To automate monthly partition maintenance, you need to add a new partition for the next month (split the range) and remove the oldest partition (merge the oldest boundary). The T-SQL commands ALTER PARTITION FUNCTION ... SPLIT RANGE and ALTER PARTITION FUNCTION ...

MERGE RANGE accomplish this. Scheduling these via Elastic Database Jobs provides automation. Option A's SELECT INTO and TRUNCATE are not partition management operations.

Option B's ALTER INDEX REORGANIZE is for index maintenance, not partition boundary changes. Option D's SWITCH PARTITION moves data without altering the partition function/scheme.

Practice this question →

43

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

The 'az sql db update' command updates an existing database's service objective to S2. This immediate effect changes the performance level of the OrdersDB database. Option A is incorrect because the command targets the database, not the server.

Option C is incorrect because the command does not specify scaling direction; it simply sets the service objective, which could be an upgrade or downgrade depending on the current setting. Option D is incorrect because the update command does not create a new database; it modifies the existing one.

Practice this question →

44

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

The serverless compute tier in Azure SQL Database automatically pauses after a period of inactivity and resumes on the first connection. By setting the auto-pause delay to 10 hours (covering the 8 PM to 6 AM window), the database will pause during non-business hours and automatically resume when a connection is attempted. Options A and D are unnecessary because the serverless tier handles this natively, and Option B is incorrect because elastic jobs are not used for scaling to 0 vCores.

Practice this question →

45

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 →

46

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

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 →

47

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

LTR policies are applied at the database level in Azure SQL Database. Each database requires its own LTR policy to retain backups beyond the PITR retention period. Option A is wrong because policies are per database, not per elastic pool.

Option B is wrong because two policies per server (weekly and yearly) are not a minimal requirement; each database can have multiple LTR policies as needed. Option C is wrong because one LTR policy cannot cover all databases on a server; each database has its own retention requirements.

Exam trap

A common pitfall is confusing LTR policy scope with elastic pool or server level. LTR is per database, not per server or pool.

Practice this question →

48

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

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 D is correct because automatic tuning can handle index and statistics maintenance, reducing manual effort.

Option B is incorrect because SQL Agent jobs are not available in Azure SQL Database (only in Managed Instance) and cannot be used across multiple databases in an elastic pool. Option E is incorrect because auto-failover groups are for high availability and disaster recovery, not for automating maintenance tasks.

Practice this question →

49

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

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 →

50

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.

AnswerA

Correct. Azure Automation runbooks can execute PowerShell cmdlets such as Set-AzSqlDatabase to scale the database up or down based on metrics like CPU percentage. This is a common pattern for automating tier changes without built-in autoscale.

Why this answer

Azure SQL Database does not have built-in autoscale based on CPU thresholds to change service tiers. The correct approach is to use Azure Automation runbooks, which can be triggered by Azure Monitor alerts or schedules to execute PowerShell cmdlets like Set-AzSqlDatabase to modify the service objective. Option D is incorrect because autoscale settings in the Azure portal are not available for SQL Database.

Option B is for running jobs across databases, not for scaling. Option C can also work but is less direct than Azure Automation for this automation scenario.

Exam trap

Candidates may assume Azure SQL Database has built-in autoscale like other services, but it does not. Custom automation using Azure Automation or Logic Apps is required.

Practice this question →

51

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

Azure SQL Database elastic jobs automatically store job execution history in a dedicated job database, which is specified when the job agent is created. This database serves as the central repository for job logs, making it the appropriate location for auditing and troubleshooting. Option A is incorrect because elastic jobs log to the job database, not Azure Table Storage.

Option B is incorrect because SQL Server Audit is used for auditing database events, not for capturing job execution logs. Option D is incorrect because Azure Monitor diagnostic settings can be used for additional monitoring but are not the central logging mechanism for elastic jobs.

Practice this question →

52

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

The retention period of 7 days matches the backup interval of 7 days, meaning each backup is retained exactly until the next backup. If a backup fails, there is no overlap and the previous backup may have already been deleted, leading to data loss. Option A is wrong because the runbook is enabled (isEnabled: true).

Option B is wrong because Standard_LRS is suitable for backups. Option C is wrong because the description is not the issue.

Practice this question →

53

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 without connection drops when scaling within the same DTU tier (e.g., from S2 to S3). This is because the database remains online and the operation is a metadata change. Option D is correct.

Option A is incorrect: creating a new database and using point-in-time restore does not scale the existing database and causes downtime during restore. Option B is incorrect: ALTER DATABASE with ONLINE option is for file operations, not for scaling service tiers. Option C is incorrect: Elastic Database Jobs are for scheduling scripts across databases, not for scaling operations.

Practice this question →

54

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.

AnswerA

Correct. A service principal with a client secret stored in GitHub Secrets provides a secure, automated way to authenticate to Azure SQL Database for deployment, and meets the requirement of using a service principal with minimal permissions.

Why this answer

It uses a service principal with a client secret stored in GitHub Secrets, which meets the requirement of using a service principal with minimal permissions. Using a service principal is the appropriate authentication method for an automated CI/CD deployment with Azure SQL Database. Option B is incorrect because a user-assigned managed identity is not a service principal; it is a different type of identity that does not satisfy the requirement for a service principal.

Option C is incorrect because storing connection strings in environment variables is insecure and does not use a service principal. Option D is incorrect because SQL authentication does not involve a service principal and is less secure than Azure AD-based methods.

Practice this question →

55

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

Azure Automation Runbooks with the Stop-AzSqlDatabase cmdlet provide a native, straightforward way to stop an Azure SQL Database on a schedule.

Why this answer

Azure Automation Runbooks with the Suspend-AzSqlDatabase cmdlet provide a native, straightforward way to stop an Azure SQL Database on a schedule. Option B is incorrect because Azure Logic Apps with the SQL connector cannot execute ALTER DATABASE SET SUSPEND; suspension is not a T-SQL operation for Azure SQL Database. Option C is incorrect because Azure Functions can achieve this but require more custom code and setup compared to the simpler Automation Runbook approach.

Option D is incorrect because Elastic Database Jobs are designed for executing T-SQL scripts across multiple databases, not for stopping or starting databases.

Practice this question →

56

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

The three required components are: a diagnostic setting on the database to send logs and metrics to a Log Analytics workspace, a Log Analytics workspace to store the data, and an alert rule with an action group to notify when conditions are met. A SQL Agent Job (option D) is not required for automated monitoring and alerting; it is used for scheduled tasks. An Azure Automation account (option C) is also not required; it is used for runbooks and process automation, not for monitoring alerts.

Practice this question →

57

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

Azure SQL Database supports point-in-time restore (PITR) to a different server and region. Restore-AzSqlDatabase can automate this process using a production backup from the same day, which is cost-effective as it only uses existing backups without additional replication costs. Options A and C involve manual steps or extra services, while option B is for active geo-replication, which incurs ongoing costs and is not designed for nightly refresh.

Practice this question →

58

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

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 →

59

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.

AnswerD

Azure Data Factory is the correct choice. It can be scheduled to run daily, perform the copy to Blob Storage using a copy activity, and then execute a delete activity against the database. This minimizes impact and is cost-effective.

Why this answer

Azure Data Factory (ADF) with a scheduled trigger can copy data older than 90 days to Azure Blob Storage and then delete it from the table. This approach minimizes impact on the transactional workload because ADF runs outside the database, is cost-effective for scheduled workloads, and requires minimal manual intervention. Option A is not available in Azure SQL Database (SQL Agent is only for on-premises or Managed Instance).

Option B requires a Hybrid Runbook Worker if the database is not publicly accessible, adding complexity. Option C is incorrect because PolyBase in Azure SQL Database does not support writing to Blob Storage; it only reads external data.

Practice this question →

60

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 Automation with PowerShell runbooks is ideal for automating post-deployment tasks such as creating user databases. Azure Resource Manager templates deploy infrastructure but not subsequent database operations. SQL Agent jobs are available in Azure SQL Managed Instance, but Azure Automation provides more orchestration flexibility.

Elastic Database Jobs are supported in Azure SQL Managed Instance (in preview), but they are primarily designed for cross-database query scheduling and management, making Azure Automation a more appropriate choice for database creation automation.

Practice this question →

61

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

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

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

Practice this question →

62

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

In ARM templates for Azure SQL Database, the 'sku' property defines the service tier and performance level. For Hyperscale, the 'sku' name is 'HS_Gen5_2' (Gen5, 2 vCores) and the 'sku.tier' is 'Hyperscale'. The 'zoneRedundant' property is set under 'properties' to true for zone redundancy.

Option B is incorrect because 'requestedServiceObjectiveName' is used for DTU-based tiers, not vCore-based Hyperscale. Option C is incorrect because 'edition' is not a valid ARM template property; the tier is specified through 'sku.tier'. Option D is incorrect because 'maxSizeBytes' only sets the maximum database size, not the service tier.

Practice this question →

63

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

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 →

64

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 →

65

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

SQL Agent is available on Azure SQL Managed Instance, and you can create a T-SQL script that uses sys.dm_db_stats_properties to check modification counters and then executes UPDATE STATISTICS only on tables that have had more than 10% of rows modified. This meets the efficiency requirement without rebuilding statistics unnecessarily. Option B is incorrect because automatic tuning does not update statistics based on modification counters; it manages indexes and query plan issues.

Option C is incorrect because Azure Automation requires external dependencies like a hybrid worker and is not self-contained within the Managed Instance. Option D is incorrect because Elastic Database Jobs are not available for Azure SQL Managed Instance; they are for Azure SQL Database.

Practice this question →

66

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.

AnswerB

Correct. Elastic Database Jobs allows scheduling T-SQL scripts that can query sys.dm_db_index_physical_stats to check fragmentation and then rebuild or reorganize indexes based on thresholds and schedule.

Why this answer

Elastic Database Jobs can be used to run a T-SQL script on a schedule, checking fragmentation thresholds and performing rebuilds/reorganizations. This meets the requirement of weekly execution while checking thresholds (30% and 10%). Option D is incorrect because automatic index tuning in Azure SQL Database does not support a fixed schedule; it operates in real-time based on workload and cannot be set to run weekly.

Options A and C are not suitable: A uses a SQL Agent job which is not available in Azure SQL Database (only in SQL Server on VMs), and C pertains to SQL Server on VMs, not Azure SQL Database.

Practice this question →

67

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 →

68

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 SqlPackage.exe with /p:ExtractAllTableData=False 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

Correct. An Azure Automation runbook can invoke SqlPackage.exe with the /p:ExtractAllTableData=False option to create a schema-only BACPAC, and the runbook can be scheduled weekly.

Why this answer

Azure Automation runbooks can execute PowerShell scripts that use the SqlPackage.exe utility to export a schema-only BACPAC file. The command 'SqlPackage.exe /Action:Export /SourceServerName:<server> /SourceDatabaseName:<database> /TargetFile:<bacpac> /p:ExtractAllTableData=False' creates a BACPAC containing only the schema. The runbook can be scheduled weekly using an Azure Automation schedule.

Option A is incorrect because Azure Data Factory's Copy activity is designed for data movement, not schema exports. Option B is incorrect because Elastic Database Jobs runs T-SQL scripts and cannot produce BACPAC files. Option D is incorrect because SQL Agent is not available in Azure SQL Database.

Exam trap

Candidates may incorrectly assume that the New-AzSqlDatabaseExport cmdlet has a -SchemaOnly parameter, but it does not exist. The correct approach is to use SqlPackage.exe with /p:ExtractAllTableData=False inside a runbook.

Practice this question →

69

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, the resource type is 'backupShortTermRetentionPolicies', which only supports short-term retention properties like retentionDays. Long-term retention properties (weeklyRetention, monthlyRetention, yearlyRetention) belong to a different resource type ('backupLongTermRetentionPolicies').

Including them in the same resource will cause a deployment error. Option B correctly identifies this. Option A is incorrect because the JSON syntax is valid (camelCase is used in ARM templates).

Option C is incorrect because diffBackupIntervalInHours is not relevant to this issue. Option D is incorrect because 28 days is within the allowed range for PITR (up to 35 days).

Practice this question →

70

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 pause action is not supported for Azure SQL Database, making option D correct. Option A is incorrect because the connection authentication is not the issue here.

Option B is incorrect because the recurrence trigger schedule is valid. Option C is incorrect because the API version is not the primary reason; the operation itself is unsupported.

Practice this question →

71

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

The database must be in the same server as the elastic pool is correct because moving a database into an elastic pool requires the database to be on the same server as the pool.

Why this answer

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 elastic pool must be in the same server as the database, not a different server. Option C is wrong because the database can be any service tier (Basic, Standard, Premium, etc.) as long as it fits within the pool's resource limits.

Option D is wrong because the database size can be any size up to the pool's max size limit, which is not necessarily less than 10 GB.

Practice this question →

72

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.

AnswerB

Retention of 35 days exceeds 7 days needed.

Why this answer

The current configuration shows a backup retention period of only 5 days, which is less than the required 7 days for point-in-time restore. With a retention period shorter than 7 days, you cannot restore to a point within the last 7 days. The backup interval of 12 hours is for full backups and does not affect point-in-time restore granularity, as transaction log backups are taken every 5-10 minutes, enabling 1-minute granularity.

Exam trap

Candidates often confuse the purpose of backup intervals. The 12-hour interval shown is for full/differential backups, not transaction logs. Transaction log backups are taken frequently (every 5-10 minutes) and are responsible for point-in-time restore 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 →

73

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 build pipeline (A) produces artifacts like a .dacpac file. A release pipeline (B) deploys those artifacts to the target database. A variable group (E) stores configuration values such as connection strings.

Options C (Elastic job agent) and D (Azure Automation runbook) are not required for schema deployment via DevOps CI/CD. Elastic jobs are used for scheduled tasks across databases, not for deploying schema changes. Azure Automation runbooks automate processes outside of DevOps pipelines.

Practice this question →

74

Multi-Selectmedium

You are implementing automation for an Azure SQL Database. Which TWO scenarios are appropriate for using elastic database jobs? (Choose two.)

Select 2 answers

A.Scaling up a database when CPU usage is high

B.Checking data consistency across multiple databases

C.Creating logins and users across multiple databases

D.Rebuilding indexes across all databases in an elastic pool

E.Performing a full database backup

AnswersB, D

Data consistency checks can be run across databases.

Why this answer

Elastic database jobs are designed for executing administrative tasks across multiple databases in Azure SQL Database. Checking data consistency (B) and rebuilding indexes (D) are common maintenance tasks that benefit from automation across many databases. Option A is incorrect because scaling up a database is handled by Azure's automatic scaling features, not elastic jobs.

Option C is incorrect because creating logins and users is better managed using contained database users or Azure AD authentication for security and simplicity. Option E is incorrect because full database backups are automatically managed by Azure SQL Database, so a job is unnecessary.

Practice this question →

75

Multi-Selectmedium

You need to automate the backup strategy for an Azure SQL Database to meet a recovery point objective (RPO) of 5 minutes and a recovery time objective (RTO) of 1 hour. Which THREE features should you combine? (Choose three.)

Select 3 answers

A.Long-term retention (LTR) backup policy.

B.Active geo-replication.

C.Azure Backup service.

D.Auto-failover group.

E.Point-in-time restore (PITR).

AnswersA, B, D

LTR provides archival backups beyond the PITR retention.

Why this answer

Options A, B, and D are correct. Long-term retention (LTR) backup policy provides archival backups for compliance and point-in-time restore. Active geo-replication maintains a synchronized readable secondary database with a low RPO (typically less than 5 seconds).

Auto-failover groups automate failover and enable a low RTO (typically less than 1 hour). Together, these three features meet the RPO of 5 minutes and RTO of 1 hour. Option C (Azure Backup service) is not used for Azure SQL Database; it is for Azure VMs and on-premises workloads.

Option E (Point-in-time restore) can meet the RPO but does not provide automated failover and has a higher RTO, so it is insufficient alone.

Practice this question →

Page 1 of 3 · 163 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Configure and manage automation of tasks questions.

Start 20-question session