Repository navigation
Add drift detection to Sync-DbaAvailabilityGroup #10726
Description
Activity
Thanks for the detailed write-up. Your description of the two modes is correct. Without
-Force, every Copy command skips objects that already exist. With it, jobs and logins are dropped and recreated, and a dropped job loses its history.Some of this already exists, and there's one trap to know about:
Jobs:
Copy-DbaAgentJobhas had-UseLastModifiedsince 2.7.22 (#9956). It is your "option 2" for jobs: it comparessysjobs.date_modifiedon source and destination and only drops and recreates when the source is newer.Sync-DbaAvailabilityGroupjust doesn't pass it through yet, and that part is a small change. Two limits:- A job that changed is still dropped and recreated, so it loses its history. Only unchanged jobs keep theirs.
date_modifiedis set withGETDATE(), which is server local time, not UTC. With replicas in different time zones (for example a DR replica in another region), a timestamp comparison fails by hours, and a 60 s tolerance doesn't help. If the secondary's clock is behind, every job looks newer on the primary and gets recreated on every run. If it is ahead, changes never get copied. The existing-UseLastModifiedhas the same weakness. We should fix that there rather than build a second date comparison.
Logins: Here we don't need dates at all. For SQL logins, the password hash in
sys.sql_loginscan be compared directly, andSet-DbaLogin -PasswordHashupdates it in place withALTER LOGIN ... HASHED. There is no drop, and SID and ownerships stay as they are. Permissions and role memberships are already synced on every run bySync-DbaLoginPermission, whether or not-Forceis used. So for logins the better design is "update in place when the definition differs", not "drop and recreate when the date differs".Linked servers and credentials: Their secrets can only be compared after decrypting them through the dedicated admin connection, so a date comparison (
sys.servers.modify_date,sys.credentials.modify_date) is the realistic option. The time zone caveat applies there too. Dropping and recreating these objects loses nothing like job history, so per-type selection costs less there. Until something is built, you can already runCopy-DbaLinkedServer -ForceorCopy-DbaCredential -Forceon their own for just those types.Suggested order:
- Add a
-UseLastModifiedpass-through toSync-DbaAvailabilityGroupfor jobs. - Make the date comparison in
Copy-DbaAgentJobtime-zone safe. - Update logins in place in
Copy-DbaLogin, triggered by a hash comparison instead of drop and recreate.
Each can be its own PR. Would you like to take any of them?
This comment was created by Claude and reviewed by Andreas Jordan.
I can handle 1 and 2
Put in the Pull Request with improvements to
Copy-DbaAgentJob -UseLastModifiedbehavior: #10767Items 1 and 2 are done, thanks @mrahman-DBA:
- Copy-DbaAgentJob: compare job definitions instead of date_modified for -UseLastModified #10767:
Copy-DbaAgentJob -UseLastModifiednow compares the job definitions first and uses the timestamps, converted to UTC, only to decide which side is newer. That goes further than planned: identical jobs are no longer recreated, schedule-only changes now reach the destination, and an enabled-only change is fixed in place. - Sync-DbaAvailabilityGroup: add -UseJobLastModified pass-through to Copy-DbaAgentJob #10768:
Sync-DbaAvailabilityGroup -UseJobLastModifiedpasses this through.
Both ship with the next release after 2.9.0.
Item 3 (update logins in place instead of dropping them) moved to #10772, so it can be picked up on its own. For linked servers and credentials,
Copy-DbaLinkedServer -ForceandCopy-DbaCredential -Forceremain the way to refresh them for now.Closing as completed.
This text was created by Claude and reviewed by Andreas Jordan.
Reacted by mrahman-DBA- Copy-DbaAgentJob: compare job definitions instead of date_modified for -UseLastModified #10767:
Summarize Functionality
Sync-DbaAvailabilityGroup has two modes and nothing between them:
Without -Force, the underlying Copy-Dba* commands skip objects that already exist on the secondary. An object modified on the primary never propagates, the secondary silently holds a stale copy indefinitely.
With -Force, everything in scope is dropped and recreated, including objects that didn't change. For Agent jobs that wipes sysjobhistory on every secondary on every run (history is keyed to job_id, and a recreated job gets a new one). For SQL logins, password history could be lost for all logins
Proposal is to have per-type opt-in switches: -SyncModifiedJobs, -SyncModifiedLogins, -SyncModifiedLinkedServers, -SyncModifiedCredentials, that compare the primary's copy against each destination and force-refresh only where the primary's is newer
Another option to make changes to the underlying Copy-Dba* commands themselves to add a -UpdateModified parameter. This might be the better approach
Comparison is source-vs-destination, not a time window. The copy updates the destination's timestamp, so a synced object stops looking drifted; a missed run doesn't lose changes the way a "modified in the last N hours" filter would. A small tolerance (60s) absorbs clock skew.
Is there a command that is similiar or close to what you are looking for?
Yes
Technical Details
No response