Configure and manage automation of tasks

Your team maintains an ARM template that deploys an Azure SQL logical server and database across dev, test, and prod environments. The administrator login name, database SKU, and server name must change per environment when the pipeline runs, but the resource API versions and a computed database resource ID used in an output must stay consistent regardless of environment. Which approach correctly separates the values that vary at deployment time from the values that should be defined internally?

You created an Azure Automation account with a system-assigned managed identity to run a PowerShell runbook that executes maintenance T-SQL against an Azure SQL Database each night. The runbook authenticates using the managed identity, but every execution fails with a login error stating the token principal cannot connect. What must you do so the runbook can authenticate and run T-SQL successfully?

Your company runs a provisioned vCore Azure SQL Database that experiences heavy batch processing every weekend and light activity on weekdays. Management wants to automatically scale the database up to a higher service objective on Friday evening and scale it back down on Monday morning to reduce cost, with no dedicated server to host SQL Server Agent. The solution must run unattended and require minimal ongoing administration. Which approach should you implement?

Your team maintains an Azure DevOps pipeline that provisions a new Azure SQL Database in an existing logical server for each feature branch. The pipeline uses a Linux agent and authenticates with a service principal that has Contributor rights on the resource group. You need to add a single command-line step that creates the database at the S1 service objective and is safe to re-run without error if the database already exists. Which approach should you use?

You are automating the setup of Elastic Jobs to run scheduled index maintenance across 40 Azure SQL databases. Your deployment pipeline uses the Azure CLI. Before you can create an Elastic Job agent named 'jobagent1', which prerequisite must the Azure CLI command reference, and how is it satisfied?

Your team maintains Azure SQL Database infrastructure using Bicep files stored in a Git repository. Before applying changes to the production environment through an Azure DevOps pipeline, the change-management policy requires that reviewers see exactly which resources would be created, modified, or deleted—without actually deploying anything. Which action should you add to the pipeline to satisfy this requirement?

You are designing an automated maintenance solution for 40 Azure SQL databases spread across three logical servers. You decide to use the Elastic Jobs feature to run parameterized T-SQL index maintenance against all targets on a nightly schedule. Before you can create the Elastic Job agent, what prerequisite resource must you provision?

You are configuring an Elastic Job agent in Azure SQL Database to run a nightly index maintenance script across 40 databases in a target group. The job must connect to each target database and also write execution output to a central logging database. The job repeatedly fails with authentication errors when connecting to the target databases. You confirm the job's database-scoped credential exists in the job database. What must you also do to allow the job to authenticate successfully against every target database?

You manage 40 Azure SQL databases spread across three logical servers. You have configured an Elastic Job agent with a target group covering all databases and a T-SQL job that rebuilds indexes. The maintenance job must run automatically every night at 02:00. In the Elastic Job agent, which action correctly automates this recurring execution?

You are configuring an Elastic Job agent to run maintenance T-SQL against a group of Azure SQL databases. When creating the job step, you need to specify where the T-SQL command text and job metadata are stored and managed. Which resource holds the elastic job definitions, steps, and execution history?

You manage an Elastic Job agent that runs an index maintenance T-SQL script against all databases on a logical SQL server. A new database named StagingRefresh was added to the same server, but it must NOT be included in the maintenance job because it is dropped and recreated nightly by a separate pipeline. You want the existing job to continue targeting every other database on the server without needing to update the target group each time a new production database is added. What should you configure in the target group?

You manage 40 Azure SQL databases spread across three logical servers that host tenant data for a SaaS application. You need to run the same index maintenance T-SQL script against all 40 databases every night using the Elastic Jobs feature. You have created an Elastic Job agent and a job. Which action correctly defines WHICH databases the job step executes against?

You manage 40 Azure SQL databases on a single logical server. New databases are provisioned weekly, and you need an Elastic Job that runs a maintenance T-SQL script against every database on the server, automatically including databases added in the future. You are configuring the target group. Which target group member configuration meets this requirement with the least ongoing maintenance?

You manage an Elastic Job agent that runs a nightly index rebuild against a target group containing 40 Azure SQL databases spread across three logical servers. Each database has a different name, and you need the same T-SQL job step to execute successfully against every database without hardcoding names. The job step currently fails on some databases because they use different schema names for the target table. You want the job step to reliably enumerate and act on each database's local objects at runtime. Which characteristic of Elastic Jobs must you rely on to make the single job step work across all targets?

You manage a SQL Server 2022 instance running on an Azure VM. During recent month-end processing, the server experienced sustained high workload that went unnoticed until users complained. You want SQL Server Agent to automatically send you an email whenever the 'Batch Requests/sec' counter exceeds 5,000. What type of SQL Server Agent alert should you create?

You manage a SQL Server 2022 instance running on an Azure VM. Your team wants to be notified automatically whenever a fatal error occurs that indicates possible database corruption or hardware-related resource failures (system errors with severity levels 19 through 25). You need to configure SQL Server Agent so that an operator receives an email whenever any such error is raised. What is the most efficient way to accomplish this?

A SQL Server Agent job that runs a PowerShell step to export data to a network share completes successfully when you manually start it from SSMS while logged in as a domain administrator. However, when the job runs on its schedule, the step fails with an 'access is denied' error writing to the share. The job step is configured to run under the SQL Server Agent Service Account. What is the most likely cause and appropriate fix?

You maintain a SQL Server 2022 instance on an Azure VM. A SQL Server Agent job contains a step of type 'PowerShell' that imports the SqlServer module and runs a maintenance cmdlet. The step succeeds when you run the same script manually in a 64-bit PowerShell console on the server, but the job step fails with an error indicating the module cannot be loaded. What is the most likely cause?

You administer a SQL Server on an Azure VM that runs dozens of SQL Server Agent jobs every few minutes. Developers complain that when they open a job's history in SQL Server Management Studio, older execution records disappear and they cannot troubleshoot failures from earlier in the day. The msdb database is not full and the jobs themselves succeed. What is the MOST likely cause, and how should you correct it?

You manage a SQL Server 2022 instance on an Azure VM. A nightly SQL Server Agent job that runs a critical ETL package occasionally fails, and the on-call DBA team is not being alerted. You need to ensure that the responsible DBA receives an email whenever this specific job fails, with minimal configuration changes. Database Mail is already configured and functional. What should you do?