11.1 Schedules for Regular Maintenance Jobs

Why SQL Server Agent Matters for DP-300

Domain 4 of the DP-300 exam covers "Configure and manage automation of tasks," worth roughly 15-20% of the exam. SQL Server Agent is the core engine behind that automation on two of the three Azure SQL deployment targets: SQL Server on Azure Virtual Machines and Azure SQL Managed Instance (SQL MI). On Azure SQL Database single databases and elastic pools, Agent does not exist at all; that platform automates backups, index tuning, and statistics, and you handle custom automation with elastic jobs. Remembering this platform split is the single most heavily tested fact in this area.

SQL Server Agent Architecture

SQL Server Agent is a Windows service running alongside the SQL Server database engine. The exam lists six building blocks you should be able to define:

All of these objects live in the msdb system database, which is why msdb health and its backup matter to Agent operations. The Agent service account (or, on Managed Instance, the equivalent platform-managed identity) needs permissions to do its work; job step execution then runs under either the Agent service account or, for elevated or isolated steps, under a proxy backed by a SQL Server credential.

Job Types and Subsystems

A job step targets one of several subsystems. The most common is T-SQL, which runs a Transact-SQL batch against a target database. Other subsystems include SQL Server Integration Services (SSIS) for ETL packages, PowerShell for scripting against the SqlServer and SqlServerSQLProvider modules, CmdExec for running operating-system executables, and Analysis Services (AS) commands and queries for AS processing tasks. The subsystem you pick on the step's Advanced page dictates which options are available; for example, T-SQL steps let you set the database and output file, while SSIS steps let you pick a package, configuration, and server. On the exam, the subsystem selection is often the answer when a scenario describes running a package, a script, or a native executable.

Job Steps and Flow Control

Each step has its own Advanced settings for flow control:

• On success action: Go to the next step (default), Quit the job reporting success, Quit the job reporting failure, or Go to a specific step (enabling branching and loops).
• On failure action: same four choices; the default is Quit the job reporting failure. A common pattern is to retry a transient failure by jumping to a logging step and then quitting.
• Retry attempts: an integer; the step is retried up to this many times before the on-failure action fires.
• Retry interval: seconds between retries.

A job can also have a target server (in multi-server environments using a master/target server (MSX/TSX) topology), but for DP-300 the scope is single-instance jobs. Job activity is visible in the Job Activity Monitor in SSMS and in the msdb.dbo.sysjobs, sysjobsteps, sysjobschedules, and sysjobhistory catalog views.

Schedule Types and Frequency

A schedule defines when a job starts. Agent supports four frequency types:

• One-time: runs once at a specific date and time.
• Daily: runs every N days (1 through 365), at a start time, optionally recurring every N hours or minutes within that day.
• Weekly: runs on selected weekdays, every N weeks, with optional intra-day recurrence.
• Monthly: runs on a specific day number of the month, or on the Nth weekday of the month (for example, the second Sunday), every N months.

Every schedule has a start date and time (the earliest the schedule can fire) and optionally an end date after which it stops firing. A schedule with no end date runs indefinitely. Within daily frequency you can also set an occurs every interval in hours or minutes, with a start and end time window, which is how you build a job that runs every 15 minutes during business hours. Exam traps here: a weekly schedule that recurs every 2 weeks with both Sunday and Wednesday selected runs on both days of the active week, not just one; and a daily schedule with an every-15-minutes recurrence ends at the daily end time, not at midnight.

Shared Schedules vs Job-Specific Schedules

A schedule can be job-specific (created inside the job's property page and used by that one job) or a shared schedule (created in the SQL Server Agent > Schedules folder and then attached to any number of jobs). Shared schedules are the right answer when many jobs must fire together in a maintenance window - change the shared schedule once and every attached job follows. Attaching or detaching a job to a shared schedule does not modify the schedule itself. A common scenario question: a DBA maintains 12 nightly maintenance jobs that must all run at 02:00; using one shared schedule is the correct answer rather than editing 12 jobs when the window moves.

Agent on Azure SQL Managed Instance: Limitations vs SQL Server

SQL MI supports most SQL Server Agent features, which is one of its differentiators from Azure SQL Database. There are caveats the exam probes:

• Subsystems: T-SQL, PowerShell, and SSIS job steps are supported; some external integrations may behave differently than on a VM.
• Service account: Agent on SQL MI runs under a platform-managed identity rather than a Windows service account you control, so proxy-based elevation works within the instance's permission model but cannot impersonate arbitrary Windows principals.
• Database Mail: supported and is the notification channel for operators (see Section 11.2).
• Operators and alerts: supported, with the alert types discussed in Section 11.2.
• File-based job output: paths must be reachable from the instance environment; for SQL MI you typically write job step output to a table instead of a local file.

The exam often contrasts these capabilities with Azure SQL Database, where you would use elastic jobs (a separate service that runs T-SQL across one or more databases) rather than Agent.

Scheduling Common Maintenance

The exam expects you to recommend sensible schedules for routine maintenance:

Use maintenance plans or hand-built Agent jobs for these. A common pattern is one nightly job per task, attached to a shared "NightlyMaintenance" schedule, so the maintenance window shifts in one place. On Azure SQL Database and SQL MI, automated backups are handled by the platform - you only need Agent backup jobs on SQL Server VMs or for custom backup strategies (for example, copying backups to a specific Azure storage account).