Copy-DbaAgentJob and Copy-DbaAgentServer using -Force results in loss of links from alerts to jobs

[jameslegrand2]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Running Copy-DbaAgentJob (or other commands that call it) can result in the destination alert becoming unlinked from a job.
Source, Destination before:

Destination after:

This was originally observed when running Copy-DbaAgentServer using -Force but can also occur when running Copy-DbaAgentJob using -Force. When the agent job is dropped the alert loses its link to the job, and this does not get added back when the job is created again since it is not part of the job script and is part of the alert instead.

In Copy-DbaAgentServer -Force the alert is copied first and the link is created, but then the job is copied and wipes this out:

Steps to Reproduce

Copy-DbaAgentServer -Source localhost\SQL2019 -Destination localhost\SQL2022 -Force
Copy-DbaAgentJob -Source localhost\SQL2019 -Destination localhost\SQL2022 -Job 'Copy-DbaAgentJob Alert Bug' -Force

Please confirm that you are running the most recent version of dbatools

2.1.1
See "Other details or mentions" below - development branch seems affected

Other details or mentions

In Copy-DbaAgentServer this happens because the alert is copied before the job. It might be able to be fixed by changing the order of these commands:

That wouldn't fix Copy-DbaAgentJob, but that might be able to be handled for -Force by manipulating the script before running it, similarly to existing handling for missing logins:

There is some other interesting behavior related to this when NOT using -Force and trying to copy the job/alert to the destination server for the first time. These may or may not be issues, but would be good to consider when testing a fix.

1. Copy-DbaAgentServer will skip the alert on the first run since the job doesn't exist and will create the job. Running it again will then be able to create the alert and will skip the job because it already exists.
   
   
2. Copy-DbaAgentAlert when the job already exists will properly link the alert to the job
3. Copy-DbaAgentJob when the alert already exists will not create the link
4. Copy-DbaAgentJob when the alert does not exist will not create the alert or link, and provides no message

Presumably Start-DbaMigration and Sync-DbaAvailabilityGroup can also be affected via their calls to Copy-DbaAgentServer

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name Value

---

PSVersion 7.4.1
PSEdition Core
GitCommitId 7.4.1
OS Microsoft Windows 10.0.19045
Platform Win32NT
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0

SQL Server Edition and Build number

Initial observation: Enterprise edition, 15.0.4316.3
Replicating the problem for examples to put in this issue: Developer edition, source 15.0.2104.1 destination 16.0.1110.1

.NET Framework Version

.NET 8.0.1

[csommerolo]

This is not identical but similar to an issue we faced with the Copy-DbaAgentServer cmdlet. We noticed that none of our alerts copied from source to destination the first time we ran it. Everything else copied fine, just the alerts were missed. No errors were thrown and it appeared to be successful, but it was not. We re-ran it and the alerts were successfully copied over on the second run.

There are a lot of .Refresh() method calls in that cmdlet. And they even fully reconnect after copying the operators so I feel like they were trying to account for this weirdness.

# extra reconnect to force refresh
 $destServer = Connect-DbaInstance 

At first we thought it was an order of operations thing but this kinda feels like SMO being buggy. I have one more set of servers to upgrade so I'm going to try to capture more information when we do that.

Additional context. We are upgrading SQL 2016 -> SQL 2022.