Skip to content

Commit f3280a3

Browse files
mrahman-DBAmrahman-DBA
authored
Sync-DbaAvailabilityGroup: add -UseJobLastModified pass-through to Copy-DbaAgentJob (#10768)
1 parent 4391a34 commit f3280a3

2 files changed

Lines changed: 137 additions & 2 deletions

File tree

‎public/Sync-DbaAvailabilityGroup.ps1‎

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -90,6 +90,16 @@ function Sync-DbaAvailabilityGroup {
9090
Disables all synchronized jobs on secondary replicas after copying them from the primary.
9191
Use this when jobs should only run on the primary replica or when you need to manually control which jobs run on each replica after failover.
9292
93+
.PARAMETER UseJobLastModified
94+
Passed through as -UseLastModified to Copy-DbaAgentJob for the AgentJob sync only; no other object type is affected.
95+
Instead of skipping every job that already exists on a secondary (or recreating all of them with -Force), jobs are compared
96+
by definition - properties, enabled state, steps and schedules - and only those that actually differ are updated, with the
97+
primary winning when it is not older than the secondary. Jobs that differ only in enabled state are updated in place.
98+
When combined with -Force, this switch takes precedence for jobs: existing jobs are still only updated when their definition
99+
differs, and -Force only suppresses prompts and remaps missing job owners to sa, while -Force continues to drop and recreate
100+
all other object types as usual.
101+
See Copy-DbaAgentJob for the full behaviour.
102+
93103
.PARAMETER InputObject
94104
Accepts availability group objects from Get-DbaAvailabilityGroup for pipeline processing.
95105
Use this to sync multiple availability groups at once or to process specific AGs returned by filtering commands.
@@ -102,6 +112,7 @@ function Sync-DbaAvailabilityGroup {
102112
.PARAMETER Force
103113
Drops and recreates existing objects on secondary replicas instead of skipping them.
104114
Use this when you need to update objects that already exist on secondaries or when objects have configuration differences that need to be synchronized.
115+
For agent jobs, -UseJobLastModified takes precedence when both are specified.
105116
106117
.PARAMETER WhatIf
107118
Shows what would happen if the command were to run. No actions are actually performed.
@@ -154,6 +165,12 @@ function Sync-DbaAvailabilityGroup {
154165
PS C:\> Get-DbaAvailabilityGroup -SqlInstance sql2016a | Sync-DbaAvailabilityGroup -WhatIf
155166
156167
Shows what would happen if the command were to run but doesn't actually perform the action.
168+
169+
.EXAMPLE
170+
PS C:\> Sync-DbaAvailabilityGroup -Primary sql2016a -AvailabilityGroup db3 -UseJobLastModified
171+
172+
Syncs all object types to the replicas in the db3 AG. Agent jobs that already exist on a secondary are only updated when their
173+
definition differs from the primary; identical jobs are left untouched. All other object types sync as they normally would.
157174
#>
158175
[CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'Medium')]
159176
param (
@@ -171,6 +188,7 @@ function Sync-DbaAvailabilityGroup {
171188
[string[]]$Job,
172189
[string[]]$ExcludeJob,
173190
[switch]$DisableJobOnDestination,
191+
[switch]$UseJobLastModified,
174192
[parameter(ValueFromPipeline)]
175193
[Microsoft.SqlServer.Management.Smo.AvailabilityGroup[]]$InputObject,
176194
[switch]$ExcludePassword,
@@ -419,6 +437,7 @@ function Sync-DbaAvailabilityGroup {
419437
Destination = $secondaries
420438
Force = $force
421439
DisableOnDestination = $DisableJobOnDestination
440+
UseLastModified = $UseJobLastModified
422441
InputObject = $jobsToSync
423442
}
424443
Copy-DbaAgentJob @splatCopyJob
@@ -431,4 +450,4 @@ function Sync-DbaAvailabilityGroup {
431450
Write-ProgressHelper -Activity $activity -Completed
432451
}
433452
}
434-
}
453+
}

‎tests/Sync-DbaAvailabilityGroup.Tests.ps1‎

Lines changed: 117 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@ Describe $CommandName -Tag UnitTests {
2323
"Job",
2424
"ExcludeJob",
2525
"DisableJobOnDestination",
26+
"UseJobLastModified",
2627
"InputObject",
2728
"ExcludePassword",
2829
"Force",
@@ -380,6 +381,8 @@ Describe $CommandName -Tag UnitTests {
380381
$Destination,
381382
[switch]$Force,
382383
[switch]$DisableOnDestination,
384+
# Sync-DbaAvailabilityGroup always passes -UseLastModified from -UseJobLastModified
385+
[switch]$UseLastModified,
383386
$InputObject
384387
)
385388

@@ -418,4 +421,117 @@ Describe $CommandName -Tag UnitTests {
418421
}
419422
}
420423
}
421-
}
424+
}
425+
426+
Describe $CommandName -Tag IntegrationTests {
427+
BeforeAll {
428+
# We want to run all commands in the BeforeAll block with EnableException to ensure that the test fails if the setup fails.
429+
$PSDefaultParameterValues["*-Dba*:EnableException"] = $true
430+
431+
# Sync-DbaAvailabilityGroup accepts -Primary and -Secondary without an availability group, so two standalone
432+
# instances are enough to run the real AgentJob sync, including Copy-DbaAgentJob, against SQL Server.
433+
$jobName = "dbatoolsci_syncag_job_$(Get-Random)"
434+
435+
$null = New-DbaAgentJob -SqlInstance $TestConfig.InstanceCopy1 -Job $jobName
436+
$splatStep = @{
437+
SqlInstance = $TestConfig.InstanceCopy1
438+
Job = $jobName
439+
StepName = "step1"
440+
Subsystem = "TransactSql"
441+
Database = "master"
442+
Command = "SELECT 1"
443+
}
444+
$null = New-DbaAgentJobStep @splatStep
445+
446+
# Only the test job, and every object type except AgentJob, so nothing else is synced and no DAC is opened
447+
$splatSync = @{
448+
Primary = $TestConfig.InstanceCopy1
449+
Secondary = $TestConfig.InstanceCopy2
450+
Job = $jobName
451+
UseJobLastModified = $true
452+
Exclude = @(
453+
"AgentAlert",
454+
"AgentCategory",
455+
"AgentOperator",
456+
"AgentProxy",
457+
"AgentSchedule",
458+
"Credentials",
459+
"CustomErrors",
460+
"DatabaseMail",
461+
"DatabaseOwner",
462+
"LinkedServers",
463+
"LoginPermissions",
464+
"Logins",
465+
"SpConfigure",
466+
"SystemTriggers"
467+
)
468+
}
469+
470+
# Read msdb on the secondary directly so assertions don't depend on cached SMO objects
471+
$splatDestJob = @{
472+
SqlInstance = $TestConfig.InstanceCopy2
473+
Database = "msdb"
474+
Query = "
475+
SELECT j.job_id, j.date_modified, s.command
476+
FROM dbo.sysjobs AS j
477+
INNER JOIN dbo.sysjobsteps AS s ON s.job_id = j.job_id
478+
WHERE j.name = @jobName AND s.step_id = 1"
479+
SqlParameter = @{ jobName = $jobName }
480+
}
481+
482+
# We want to run all commands outside of the BeforeAll block without EnableException to be able to test for specific warnings.
483+
$PSDefaultParameterValues.Remove("*-Dba*:EnableException")
484+
}
485+
486+
AfterAll {
487+
# We want to run all commands in the AfterAll block with EnableException to ensure that the test fails if the cleanup fails.
488+
$PSDefaultParameterValues["*-Dba*:EnableException"] = $true
489+
490+
# Pipe from Get so a job that never reached the secondary doesn't turn cleanup into a second failure
491+
$null = Get-DbaAgentJob -SqlInstance $TestConfig.InstanceCopy1, $TestConfig.InstanceCopy2 -Job $jobName | Remove-DbaAgentJob
492+
493+
$PSDefaultParameterValues.Remove("*-Dba*:EnableException")
494+
}
495+
496+
Context "Agent job sync with -UseJobLastModified" {
497+
It "creates the job on the secondary when it does not exist" {
498+
$null = Sync-DbaAvailabilityGroup @splatSync
499+
500+
$WarnVar | Should -BeNullOrEmpty
501+
$destJob = Invoke-DbaQuery @splatDestJob
502+
$destJob.command | Should -Be "SELECT 1"
503+
}
504+
505+
It "leaves an identical job untouched on a repeated sync" {
506+
$before = Invoke-DbaQuery @splatDestJob
507+
$before.job_id | Should -Not -BeNullOrEmpty
508+
509+
$null = Sync-DbaAvailabilityGroup @splatSync
510+
511+
$WarnVar | Should -BeNullOrEmpty
512+
$after = Invoke-DbaQuery @splatDestJob
513+
$after.job_id | Should -Be $before.job_id
514+
$after.date_modified | Should -Be $before.date_modified
515+
}
516+
517+
It "propagates a newer changed job definition from the primary" {
518+
$PSDefaultParameterValues["*-Dba*:EnableException"] = $true
519+
# Guarantee the primary's change lands after the secondary's copy was made
520+
Start-Sleep -Seconds 2
521+
$splatChangeStep = @{
522+
SqlInstance = $TestConfig.InstanceCopy1
523+
Job = $jobName
524+
StepName = "step1"
525+
Command = "SELECT 2"
526+
}
527+
$null = Set-DbaAgentJobStep @splatChangeStep
528+
$PSDefaultParameterValues.Remove("*-Dba*:EnableException")
529+
530+
$null = Sync-DbaAvailabilityGroup @splatSync
531+
532+
$WarnVar | Should -BeNullOrEmpty
533+
$destJob = Invoke-DbaQuery @splatDestJob
534+
$destJob.command | Should -Be "SELECT 2"
535+
}
536+
}
537+
}

0 commit comments

Comments
 (0)