Configure and manage automation of tasks
Deployment of database resources and database tasks
A nightly SQL Server Agent index-maintenance job must change engines when its SQL Server database moves to Azure SQL Database, because SQL Agent (short for SQL Server Agent) is not available in Azure SQL Database, while it still automates tasks in SQL Server and is also included with Azure SQL Managed Instance. The DP-300 skills outline names two kinds of automation, "Automate deployment of database resources", named for an operation, and "Create and manage database tasks in Azure", named for the engines that run the work, so one requirement can involve both. Deployment of database resources means using a Bicep file or an Azure Resource Manager (ARM) JSON template, or running an Azure PowerShell or Azure CLI script that sends resource-management requests to Resource Manager, to create or change a resource such as a logical server, in Azure SQL Database a central administrative point for a collection of databases. For database tasks, identify the engine that runs the work; the outline uses the term for work that the Azure engines run, elastic jobs and Azure Automation runbooks (in that title, "in Azure" names those engines, not any database hosted in Azure), and a SQL Server Agent job, which defines an administrative task that schedules or alerts can run, is the same kind of work on SQL Server and Managed Instance. An Azure Automation runbook can manage databases with Az PowerShell cmdlets or issue SQL commands using PowerShell, and can start manually as well as on a schedule or external trigger; a runbook is not an alternative to resource deployment, but can perform it. SQL Server Agent also runs on SQL Server on Azure Virtual Machines (VMs), and Azure SQL Database uses elastic jobs, which execute custom jobs on one or many databases or Azure Automation runbooks.
SQL Server Agent jobs, deployment of database resources, and database tasks in Azure
Configure and manage automation of tasks carries 15–20% of the DP-300 exam. Create and manage SQL Server Agent jobs covers the skills outline bullets "Manage schedules for regular maintenance jobs", "Configure job alerts and notifications", and "Troubleshoot SQL Server Agent jobs"; reach for it for SQL Server Agent jobs on SQL Server, including on Azure VMs, and on Azure SQL Managed Instance, which also includes SQL Server Agent. Automate deployment of database resources covers "Automate deployment by using Azure Resource Manager (ARM) and Bicep templates", "Automate deployment by using Azure PowerShell", "Automate deployment by using Azure CLI", and "Monitor and troubleshoot deployments"; reach for it when the operation creates or changes a server, a database, an elastic pool (a group of databases that are on a single server and share a set number of resources), or a managed instance through Resource Manager, which receives requests sent through any of the Azure APIs, tools, or SDKs, or when such a deployment fails. Create and manage database tasks in Azure covers "Create and configure elastic jobs", "Create and configure database tasks by using automation", "Configure alerts and notifications on database tasks", and "Troubleshoot automated database tasks"; reach for it when the requirement names elastic jobs or Azure Automation runbooks. Identify the operation separately from the engine: when a runbook's Az PowerShell cmdlets create or change Azure resources through Resource Manager, those cmdlets belong to Automate deployment of database resources and the runbook to Create and manage database tasks in Azure. SQL Server Agent also runs PowerShell script job steps, so cmdlet use does not identify the engine, and neither does a manual or scheduled start, because both a runbook and a SQL Server Agent job can start either way. The word job differs by engine: in SQL Server Agent it is a specified series of actions that SQL Server Agent performs, in elastic jobs a unit of work composed of one or more job steps, and in Azure Automation a single execution instance of the runbook. An alert in SQL Server Agent is an automatic response to a specific event, which can notify operators or run a job, where operators are aliases for people or groups that can receive electronic notification, while an Azure Monitor alert rule combines the resources to be monitored, the signal, and conditions, and the alert it triggers initiates the associated action group, which defines who gets notified and what actions are taken when an alert fires; that is the mechanism Microsoft points to for alerts on elastic job execution.
Every automation runs as an identity, checked by the permission system it touches
Each engine runs its work as an identity that must hold its own access: in SQL Server Agent, every job step runs in a specific security context, and that user must have the permissions and configuration necessary for the job step. The permission system depends on what the work touches. In Azure SQL Database, creating databases through PowerShell, Azure CLI, or the REST API needs an Azure role-based access control (Azure RBAC) role, specifically Contributor, SQL DB Contributor, or SQL Server Contributor, while creating one with Transact-SQL (T-SQL) needs, for example, membership in the dbmanager database role in master. An elastic job agent that signs in with a user-assigned managed identity, the recommended option, still needs a user for that identity in every target database, granted the permissions needed to execute the jobs, and Azure Automation's Run As accounts retired on 30 September 2023 and were replaced with managed identities. Microsoft's guidance for Azure SQL is to add users to the role with the least privileges required to do their job function; for automation, that means that when two configurations both work, the task's identity gets only what its work needs, in the system that work touches.
Deployment of database resources and database tasks: platform, engine or service, identity, and how you know it worked
| Automation | Platform | Engine or service and what it runs | Runs as or authorized by | How you know it worked | Drill into |
|---|---|---|---|---|---|
| Database task run by SQL Server Agent | SQL Server, including on Azure VMs, and Azure SQL Managed Instance | SQL Server Agent jobs and job steps, started by schedules, alerts (SQL Server only), or a manual start | Each step's security context: the job owner, the Agent service account, or a proxy (a Windows identity in a credential; SQL Server only) | Job notifications that email operators through Database Mail; job activity and history in msdb (the system database holding SQL Server Agent jobs) | Create and manage SQL Server Agent jobs |
| Deployment of database resources | Azure SQL resources: logical servers, databases, elastic pools, and managed instances | Azure Resource Manager, using a template (Bicep or ARM JSON) or resource-management requests from an Azure PowerShell or Azure CLI script | An Azure RBAC role at the deployment scope, not a database role | For ARM/Bicep template deployments: what-if preview (predicts changes, makes none) before, deployment history and deployment operations after; the Activity log records resource-management operations | Automate deployment of database resources |
| Database task run by elastic jobs or Azure Automation | Elastic jobs: Azure SQL Database; runbooks: any resource their identity and network path reach | Elastic jobs run T-SQL; Azure Automation runbooks run PowerShell or Python | Elastic jobs: a user-assigned managed identity or database-scoped credentials; runbooks: the Automation account's managed identity, or credential assets when no identity fits | jobs.job_executions (execution history in the job database, the Azure SQL database you supply to the elastic job agent) and elastic job metrics; runbook JobLogs (job status) and JobStreams (output and error records); Azure Monitor alert rules with action groups | Create and manage database tasks in Azure |