Repository navigation
Copy-DbaAgentJob: compare job definitions instead of date_modified for -UseLastModified - #10767
Conversation
Internal function. Builds a content fingerprint of a SQL Agent job so two jobs can be compared by definition rather than by date_modified. Normalises the job properties, steps and schedules into a deterministic string and returns a SHA256 hash of it, plus the per-section text so callers can report which part differs. Deliberately excluded because they differ between instances even when the definition is identical: job_id, date_created, date_modified, version_number, schedule_id, schedule_uid, originating server, run history/status, and the job name (the caller already matched by name and may be using -NewName). Job-level IsEnabled is part of the fingerprint but kept in its own section so the caller can align it in place rather than recreating the job when nothing else differs.
Compares the job definition on source and destination - job properties, enabled state, steps and schedules - and only copies when they actually differ. When the definitions differ, the direction is decided by each job's effective last-modified time: the later of msdb.dbo.sysjobs.date_modified and the date_modified of every schedule attached to the job (sp_update_schedule only touches sysschedules, not the job row). Both values are normalized to UTC using each server's own time zone offset so instances in different time zones compare correctly: - Job doesn't exist on destination: creates it - Definitions identical: skips, regardless of timestamps - Only the enabled state differs and source is not older: updates the flag in place without recreating the job - Definitions differ and source is newer (or equal): drops and recreates the job - Definitions differ and destination is newer: skips with a warning Job IDs, timestamps, version numbers, schedule IDs/UIDs and run history are excluded from the comparison, so jobs that are identical but were created independently (for example on AG replicas) are not needlessly recreated. Use this for incremental synchronization scenarios where you want to keep jobs up-to-date without unconditionally overwriting them.
$refreshedDestinations = @{} added at the end of begin {}, and the unconditional $destServer.JobServer.Jobs.Refresh() at the top of the destination loop is now wrapped in the if ($UseLastModified -and -not $refreshedDestinations.ContainsKey(...)) guard
|
Thanks for this - comparing the job definition instead of A few points before this can go in: Must fix
Smaller points
Style (see
This text was created by Claude and reviewed by Andreas Jordan. |
Schedule names are not unique per job and Sort-Object is not guaranteed to be stable in Windows PowerShell, so two schedules with the same name could produce different fingerprints for the same job and trigger a needless recreate. Sorting the normalised schedule text makes the fingerprint deterministic.
- Timestamp query now uses DATEDIFF against GETUTCDATE and falls back to sysjobs.date_modified on SQL Server 2000, restoring 2000/2005 support lost to SYSDATETIMEOFFSET - Identical and enabled-only paths fall through to the common tail so -DisableOnSource applies consistently; documented in help - Source job refreshed before dependency checks, not only at comparison - DST limitation of the UTC offset noted in help - Splatted the timestamp queries; double quotes for string literals
…ison The "skips job when dates are equal" test asserted the old "same modification date" note and faked equal timestamps with a direct UPDATE of sysjobs.date_modified. Jobs are now skipped because their definitions match, regardless of timestamps, so the test asserts that instead and the UPDATE is removed. Adds coverage for the new behaviours: identical definitions with differing timestamps, enabled-only change aligned in place (job_id unchanged), schedule-only change via sp_update_schedule, no churn with -DisableOnDestination, -DisableOnSource on an identical job, and skip with warning when the destination is newer.
|
The requested changes have been pushed. Please review now |
potatoqualitee
left a comment
potatoqualitee
left a comment
There was a problem hiding this comment.
Three blocking defects at this exact head:
-
public/Copy-DbaAgentJob.ps1:438-446,582-586: an enabled-state update failure can still disable the source. With -UseLastModified -DisableOnSource, an enabled source and otherwise identical disabled destination take the in-place Alter() path. If that Alter() throws, the catch emits a Failed result but sets $skipCreate and falls through to the unconditional source-disable block. The operation can leave both jobs disabled despite reporting migration failure, stopping the workload. Continue to the next job after the failed destination update, or gate source disabling on verified successful synchronization.
-
ests/Copy-DbaAgentJob.Tests.ps1:130-138: the new schedule fixture supplies neither StartDate nor Force. New-DbaAgentSchedule therefore stops with “Please enter a start date or use -Force to use defaults.” The real COPY CI lane fails all seven new UseLastModified cases in setup. Add Force = True or all required schedule fields, then rerun the SQL-backed lane.
-
ests/Copy-DbaAgentJob.Tests.ps1:318: the expected Notes glob is impossible for the implementation text. The test expects newer on destination, while the command emits Definition differs (...) but destination job is newer than source (...); the literal wildcard comparison is false. Align the assertion or the emitted Notes. Once the fixture is repaired, this otherwise-unreached assertion will still fail.
- Schedule fixture passes StartDate and -Force to New-DbaAgentSchedule - Destination-newer test asserts the Notes text the command emits - Cleanup pipes Get-DbaAgentJob into Remove-DbaAgentJob so a failed setup doesn't also fail AfterAll
- Enabled-only path skips to the next job when the in-place Alter() fails, so -DisableOnSource can no longer leave the job disabled on both instances after a reported failure - Schedule fixture passes StartDate and -Force to New-DbaAgentSchedule - Destination-newer test asserts the Notes text the command emits - Cleanup pipes Get-DbaAgentJob into Remove-DbaAgentJob so a failed setup doesn't also fail AfterAll
|
I have implemented the requested changes and tested them the best I can within my home environment. If any additional issues arise, I will need your assistance to help resolve them |
potatoqualitee
left a comment
potatoqualitee
left a comment
There was a problem hiding this comment.
Reviewed the exact current head. The prior blockers are addressed: an in-place enabled-state failure now exits before the source-disable tail, the SQL-backed schedule fixture supplies valid start/default inputs, and the newer-destination assertion now matches the emitted result. I also checked the definition fingerprint, refresh paths, effective schedule/job timestamps, multi-destination flow, and cleanup/error branches and found no remaining material defect. The SQL-backed ci-azure run is currently queued rather than failed; the completed validation and repository checks are green.
|
Thank you 💯 |
3 participants
Type of Change
Invoke-ManualPester -Path <command> -ScriptAnalyzer -Compliance)Purpose
Copy-DbaAgentJob -UseLastModifieddecides whether to copy a job by comparingmsdb.dbo.sysjobs.date_modifiedon source and destination. That column is a poor proxy for "has this job changed", for two reasons:date_modifiedis stamped withGETDATE()bysp_add_job/sp_update_joband cannot be set through the API, so two jobs with identical definitions that were created independently — the normal state for Availability Group replicas — always carry different timestamps. Upon AG failover, a sync run drops and recreates them, and this will continue to happen after every failover because the target will always have more recentdate_modifiedsp_update_schedule, which stampssysschedules.date_modifiedbut never touches the job row. After the first sync the destination'sdate_modifiedis always later than the source's, so the change is reported as "newer on destination" and skipped forever, unless something else happens to touch the job row.A secondary issue: the comparison used server-local
datetimevalues, so instances in different time zones compared wall-clock times rather than the same instant.Approach
-UseLastModifiednow compares the job definition first and only uses timestamps to decide direction when the definitions actually differ.Get-AgentJobFingerprint(private/functions/) normalises a job's properties, steps and schedules into a deterministic string and returns a SHA256 hash plus the per-section text. It deliberately excludes everything that legitimately differs between independently created copies:job_id,date_created,date_modified,version_number,schedule_id/schedule_uid, originating server, run history, and the job name (already matched by the caller; may differ under-NewName). Job-levelIsEnabledis included but kept in its own section.sysjobs.date_modifiedand thedate_modifiedof every attached schedule, computed server-side and normalised to UTC withDATEPART(TZOFFSET, SYSDATETIMEOFFSET())so cross-time-zone instances compare correctly.Alter()instead of drop-and-recreate, preservingjob_id, history and alert links.-DisableOnDestinationis folded into the comparison, so a job deliberately kept disabled on the destination is not reported as drift on every run.-Forcewith a missing owner is folded in the same way: the source fingerprint is computed with thesaowner the copy will actually produce.Notescolumn now states which section differed (job properties,enabled state,steps,schedules), making sync output reviewable at scale.-UseLastModifiedbehaviourThe equal-timestamp case changed because timestamps only tie in practice when the job was copied, and the definitions are now known to differ.
Commands to test
Copy-DbaAgentJobwith-UseLastModifiedCopy-DbaAgentJobwithout-UseLastModified(regression: behaviour should be identical to current)Tests
tests/Copy-DbaAgentJob.Tests.ps1, context "UseLastModified parameter", needs updating to accommodate the changed-UseLastModifiedbehaviour:It "skips job when dates are equal"assertsNotes -BeLike "*same modification date*"; the new skip reason is"Job definition is identical on source and destination". The context'sBeforeAllalso updatesmsdb.dbo.sysjobs.date_modifieddirectly on the destination to fake equal timestamps, which is no longer necessary — the job is skipped because the definitions match, regardless of timestamps.It "updates job when source is newer"passes as-is.job_id; schedule-only change viasp_update_scheduleon the source → Successful and schedule updated on destination; definition change on the destination only → Skipped with the "newer on destination" warning;-DisableOnDestinationwith-UseLastModified→ Skipped/identical on the second run.