1

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.

2
  • I assume the target server(newly created) is having same IP, hotsname and instance name of SQL server along with same TCP port, please confirm. Commented Dec 30, 2020 at 6:33
  • correct, the newly created target server have the same IP, Hostname and instance name and the old job are also running fine. the only issue is i am not able to edit any multi server job since the servers are out of sync and target server is not showing tsx on agent
    – Liza
    Commented Dec 30, 2020 at 23:55

1 Answer 1

0

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/

5
  • thank you so much for answering my question. i also review your blog which is very helpful however my issue is different and i am still not able to fix because after we restore the new target server the agent is not showing TSX and when i tried to add as target server its failing because it's been registered with the same server name before.
    – Liza
    Commented Dec 30, 2020 at 23:59
  • let me know if you need more information,
    – Liza
    Commented Dec 31, 2020 at 0:00
  • Did you modify the registry, as per my recommendation? Commented Dec 31, 2020 at 17:32
  • can you please tell me how to update the registry, i need more explanation ?
    – Liza
    Commented Jan 1, 2021 at 0:32
  • Modifying the registry was more of a qualified guess from my side. Not tested by me. Most likely not supported. If you don't feel confident doing this (possibly in a test environment first) etc, then I suggest you go the supported routs and force defection of the target server from the master server, delete the jobs on the target server (if any such exist and re-register the target as a target. Commented Jan 1, 2021 at 11:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.