Jobs in availability groups

SQL Server Agent jobs belong to an instance, not to an availability group. Every replica has its own msdb with its own copy of a job. Contained availability groups, new in SQL Server 2022, are the exception: the group has its own msdb, and the jobs in it move with the group.

SQLTreeo Job Timeline shows both kinds, and says for a job on which replica it does its work.

Instance jobs: one row per replica

Add every replica by its own name. The Jobs view then lists a job once per replica, for example Nightly ETL on SQL01 and Nightly ETL on SQL02, each with its own schedule, enabled state and history.

The AG column says which availability group a job works for, and which role this server has in that group:

• SalesAG (primary here)
• SalesAG (secondary here)

That explains a copy that is disabled on the secondary, and it makes a copy that is enabled there stand out. A job is linked to a group when one of its steps runs in a database of that group. The server list shows the roles per server as well, for example SalesAG primary · HRAG secondary.

“Skips its work on this replica”

Many maintenance jobs are enabled on every replica and decide at run time whether this replica should do the work. SQLTreeo Job Timeline recognizes such a job by its step commands: a call to sys.fn_hadr_is_primary_replica or sys.fn_hadr_backup_is_preferred_replica, or the keywords AVAILABILITY_GROUP_DATABASES, USER_DATABASES and @AvailabilityGroups as used with Ola Hallengren’s maintenance solution. The check runs on the server; the command text is not transferred. A job of this kind without a step in a group database is linked to every group on the instance.

On the replica where it is not its turn, such a job starts on schedule and exits within seconds. The AG column then adds (skips its work on this replica). This is how the application decides:

1. A step that calls sys.fn_hadr_is_primary_replica works on the primary only. When this server is secondary for every group the job is linked to, the job skips its work here. When it is primary for every one, it works here.
2. Otherwise the job’s own successful runs decide. A job whose latest run was real work, works here. A job whose latest runs took seconds, after at least three real runs in a row, skips its work here: that is the shape a failover leaves.
3. When neither can tell, the job counts as working here.

What follows from it:

• Long-running. The seconds-long exits are left out of the job’s baseline when enough real runs remain. The baseline then carries the note baseline from active-replica runs, and the first real run after a failover is not reported as far too long.
• Rhythm. The scheduled runs of a job that skips its work here are drawn hollow. They take no part in the overlap analysis and are not reported as missed.

A step that uses sys.fn_hadr_is_primary_replica to do its work on the secondary is judged the wrong way round.

Contained availability groups

The jobs inside a contained availability group are stored in the group’s own msdb. From a replica that database is visible as <group name>_msdb.

• Connect to a replica by its own name. The application reads the instance’s jobs and, when the group’s msdb is readable there, the jobs of the group. The group’s jobs carry the group in the Server column: AG SalesAG (via SQL01).
• The group’s jobs are shown once. However many replicas of the group are in the workspace, the jobs of the group are read from one of them: the primary, when it is in the workspace and its last read worked.
• A replica that cannot read the group’s msdb shows a note, for example on a secondary that allows no connections: Contained availability group SalesAG: its msdb (SalesAG_msdb) is not readable on this replica (secondary), so its jobs are not shown from this server. With the primary in the workspace as well, nothing is missing.
• A connection through the listener of a contained group is inside the group. There, msdb is the group’s msdb. Such a connection shows the group’s jobs only, not the jobs of the instance behind it. The server list says so: Inside contained AG SalesAG (primary now on SQL01): shows the group’s jobs only. The listener and the replicas can be in the workspace together; the group’s jobs are still shown once.
• Jobs of a contained group always count as doing their work. The group’s primary runs them, whichever replica the msdb is read from.

The listener of a classic group

The listener of a group that is not contained leads to the instance that is primary now, and shows that instance’s jobs. When that replica is already in the workspace, the listener is refused as a second name for the same instance.

Add the replicas by their own names instead. A listener shows the jobs of one replica only, and after a failover the same name shows the jobs of another instance. The application then puts a dated note on the server: Since 2026-10-03 14:05 this name connects to SQL02 (was SQL01): it points at another replica, probably after a failover. The jobs shown are those of SQL02.

The connection never asks for read-only routing, so a listener does not send the application to a readable secondary.

After a failover

The light read, every 30 seconds by default, reads the role of this replica in each group. When a role changed, a group appeared or went, or the msdb of a contained group became readable or unreadable, the server is read in full at once. The jobs of a contained group are then taken from the new primary, without waiting for the next full read.

A connection through the listener of a contained group notes the failover: The availability group failed over at about 2026-10-03 14:05: its primary is now SQL02 (was SQL01).

Notes appear on the server in the server list, in the Notes column of the Servers view, and in the bar Not everything could be read above the views.

Permissions

Permission	What it gives	Without it
VIEW SERVER STATE	The role of this replica in each group	The role shows as unknown, with the note The replica role of availability groups is unknown: VIEW SERVER STATE is needed to read it. The AG column then names the group without the role
VIEW ANY DEFINITION	The groups themselves: names, databases, listeners	The groups are hidden from the login. The note says that the jobs of contained availability groups are not read and that replica roles are unknown
Read access to <group name>_msdb	The jobs inside a contained group, read from a replica	The note that the group’s msdb is not readable on this replica

On SQL Server 2022 and later, the groups are visible to a login with VIEW ANY DEFINITION together with VIEW SERVER STATE or VIEW SERVER PERFORMANCE STATE.

Inside a contained group, through its listener, a login without VIEW ANY DEFINITION in the group cannot see the group’s name. The jobs are read, and the group is shown as group- followed by the first characters of its id.

The script for a login with these permissions is in Permissions and what is read.

Add the other replicas

With one replica in the workspace, the application can find the others:

1. Open Add many servers and choose Availability groups.
2. Press Find replicas and listeners. Every server in the workspace that has Always On enabled is asked for the replicas and listeners of its groups.
3. The replicas are ticked. Listeners are listed but not ticked. Distributed availability groups are left out: their replicas are availability groups, not servers.
4. Choose how to sign in and press Add.

See Add many servers at once.

Limits

• The copies of a job are not compared. The same job is listed once per replica, and the application does not say how the copies relate: a job that is missing on a replica, a disabled copy that has to be enabled by hand after a failover, a copy with another schedule or other steps. To compare the jobs of two replicas, see Compare SQL Agent jobs and logins between two servers with SQLTreeo Compare.
• Which group a job targets is derived, not read. It comes from the databases of the job’s steps. A job that decides at run time where it works, and whose steps name no group database, is linked to every group on the instance.
• A login that cannot see the groups gets a note, not the jobs. From a replica, the jobs of a contained group are not read for such a login.
• The label of a contained group does not show the role of its reader. AG SalesAG (via SQL01) does not say whether SQL01 is the primary.
• Baselines are not kept per role. After a failover a job keeps one baseline per replica, with the seconds-long exits left out as described above.

So far the availability group handling has run against one real estate: a contained availability group with three replicas on SQL Server 2022, next to a failover cluster instance and stand-alone instances. All jobs there were instance jobs, defined on each replica. Jobs stored inside a contained group and a connection through the listener of a contained group are covered by automated tests with a simulated group, not yet by a real estate. A failover while the application is open has not been observed on a real estate either. If the application shows something else than this article describes, send the diagnostics report: it holds the group ids, the roles and which msdb each connection read.