I’m working on setting up an upgrade/migration, and one of the tools I have embraced of late is Multiserver jobs for easy(ier) job administration, especially when it comes to Availability Groups. I was growing impatient and frustrated with trying to keep multiple jobs in sync in just one Availability Group (AG), let alone several.
Anyway, it’s been a while since I worked on this AG and I need to get the migration/upgrade done. As I was working on configuring jobs, I ran into a problem where the AG node (as a target node) wasn’t downloading jobs from the Master node, in fact, the last poll was in July.
When I tried to force a poll, I was met with an error message that the server wasn’t registered, never mind it was clearly listed as a target server. Google didn’t find anything useful, other than some questions from 2013. I did come across the syntax to forcefully eject the server as a target with SQL.
Use MSDB; go exec sp_resync_targetserver '<SERVERNAME>';
Running the code on the master server removed it from the list of managed nodes, however, the target server still saw itself as a target. A force defection on the target server resolved that.
Afterward, I was able to successfully re-add the server as a target node and re-add the jobs to the node. Although I had to “one at a time it” with the re-add, so if anyone has a method to make that easier, please let me know.
Anyway, hope this helps someone else when they google it.