We set up multi-server administration to create a job on the master which are executed on target servers, and we had this issue because the target server was shut down after a virus attack and we set up a new SQL Server with the same name and restore all data including the master database on the new server, all the job is functioning as it used to be but now the issue happened when I am trying to update them, I can update the job at master SQL Server to change the schedule however the target server is not updating since it stopped polling after the original old server shutdown because of the virus attack,
Currently, the new SQL Server Agent is not showing (tsx)
I tried to force poll but it's not working.
my trial to add it as target server is not working with a duplicate error too.
Also, my trial to update the job at the target server is not possible since it's not active to even update
Please advise how can I reconnect this master and target server with out losing all jobs.
Best Answer
Does the target server know that it is a target server? In SSMS, does Agent say that this is a TSX?
I have a feeling that the fact that you are in an MSX environment is stored in the registry instead of a system database.
I just looked on an TSX server, an SQL Server instance named "B". The master server is an instance named "A" (on the same machine, this is a test setup). I have a key named:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.B\SQLServerAgent
In above key, I have an entry named MSXServerName with the name of the master server.
Above is what I would fix first.
After fixing that, I would check the SQLAgent.out file on both MSX and TSX. And possibly resync the TSX server using sp_resync_targetserver. I blogged about this here:
http://sqlblog.karaszi.com/resynchronizing-a-target-server-msx-tsx/