SQLTreeo
SQLTreeo Compare · How-to

Compare SQL Agent jobs and logins between two servers

Check that two SQL Server instances have the same Agent jobs, operators and logins, for example availability group replicas, and script what is missing.

Agent jobs and logins belong to the instance, not to a database. A backup, a restore or an availability group moves the databases and leaves the jobs and logins behind (contained availability groups in SQL Server 2022 are the exception). After a failover or a migration the application cannot log in, or the nightly job does not exist on the new server.

So the question before a failover test or a migration is: do both servers have the same jobs and the same logins?

By hand

List the jobs on each server and compare the two lists:

SELECT j.name, j.enabled, SUSER_SNAME(j.owner_sid) AS owner, c.name AS category
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.syscategories c ON c.category_id = j.category_id
ORDER BY j.name;

And the logins:

SELECT name, type_desc, is_disabled, default_database_name
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G')
ORDER BY name;

This shows which jobs and logins exist. It does not show a job whose step command was edited on one server only, a schedule that moved from 01:00 to 03:00, or a login that is a member of sysadmin on one side. For that you also have to compare sysjobsteps, sysjobschedules, sysschedules and sys.server_role_members.

With SQLTreeo Compare

  1. Choose Compare server configuration on the start page and set the two servers on the source and target tiles.
  2. Under Object types to compare, press Select none and tick the types you are interested in: Agent jobs, Agent operators, Agent alerts, Agent proxies, Logins, Server roles and Server permissions.
  3. Press Compare.

Jobs and logins that are missing on one side appear under Only in source or Only in target. The ones that exist on both servers with a difference appear under Different, with the differing properties side by side.

What counts as a difference:

Item Compared
Agent job Enabled state, description, category, owner, start step, notifications, every step (name, subsystem, command, database, proxy, output file, success and failure actions, retries) and every schedule
Agent operator, alert, proxy Their properties, the operators an alert notifies, the subsystems and logins of a proxy
Login Type, disabled state, default database and language, password policy and expiration checks, server access and server role membership

Jobs that differ only because one replica has them disabled on purpose are easy to spot: the only differing property is the enabled state.

Script the missing jobs and logins

All differences are ticked. Untick what you do not want to bring over and press Script…. The script uses the same procedures you would use by hand: sp_add_job, sp_add_jobstep and sp_add_jobschedule for jobs, CREATE LOGIN and ALTER SERVER ROLE for logins.

Read these points before you run it:

  • A job that differs is re-created on the target, which removes its history there. A job that differs only in its enabled state is switched with sp_update_job and keeps its history.
  • Windows logins are created FROM WINDOWS and work straight away.
  • SQL logins are created with a placeholder password. SQLTreeo Compare does not read password hashes, and the new login gets a new SID. Fill in the password before running the script.
  • For the replicas of an availability group, or when databases are restored from the source, a SQL login needs the same SID as on the source, otherwise the database users become orphaned, and the same password, otherwise the application cannot log in. Use Microsoft’s sp_help_revlogin script to transfer those logins with their SID and password hash, then use the comparison to check role membership and the other properties.
  • Clear Include statements that remove objects and settings that only exist on the target unless you want jobs and logins that only exist on the target to be dropped.
  • Reading the script is free. Copying, saving or running it works during the 30-day trial or with a license, see Trial and licensing.

From the command line

sqlcompare server --source "Server=sql01;Integrated Security=true" --target "Server=sql02;Integrated Security=true" --include AgentJobs,AgentOperators,Logins,ServerRoles

The exit code is 0 when both servers match and 1 when they differ. Run it from a scheduled task after every change window, or daily, and the replicas cannot drift apart unnoticed. Add --script sync-sql02.sql --no-drops to get the script that creates what is missing on the target and changes what differs.