The Target of Your Defection

I was a bit nervous this morning. I had been tasked with adding two SQL instances back into a master/target (MSX/TSX) relationship so that they would receive updated job code that had a new DFS share backup maintenance plans. Why was I nervous? Well, nervous might not be the right word. I was challenged because I had not to this point in my career dealt with much MSX/TSX SQL Agent jobs but I knew enough about the process to have previously “enlisted” several servers. That was straightforward and easy. But I had not dealt with orphaned target servers previously.

Surely the Internet would know what to do. It didn’t.

I found a total of 3 pages on target servers being re-enlisted and not a single link dealt with my particular scenario, to wit, no evidence existing on the master that the targets were ever enlisted and the targets definitely showed that they at one time legitimate and polled target servers. Due to the lack of info out there and not because I think this is a modern issue that people encounter every day, I decided to post a few notes on what I did to overcome the issue so that if someone searches for “orphaned TSX server enlist” they may hit this page and follow the easy steps I did to overcome the issue without having to rebuild any jobs manually.

I knew from the research I did that there was a stored procedure that was part of the enlistment process and it is sp_msx_enlist. There is a wizard that will run this for you when you try to add the orphaned server back as a target to the master server. I tried this by RDPing (yes that is a gerund of an acronym) to the target server and in SSMS, connected to the master server, right clicked on the SQL Server Agent and went to multi server administration\add target servers.

Adding the target server in this way failed with: “Error: 14364, Severity: 16, The TSX server is not currently enlisted.”

I then tried to execute the stored procedure directly in a query window on the target server. The stored procedure lives in the MSDB database. It takes several parameters, but you only need one, I found and that is the MSX server name The command is:

sp_msx_enlist N’MasterServerName’

I received the same error. I scripted out all of the existing target server SQL Agent Jobs just in case my next steps totally blew everything up and I had to put them back to make sure at the very least transaction logs were backing up regularly while I assessed my next move. By the way it is easy to script out multiple SQL Agent Jobs using the Object Explorer Details windows, control-clicking multiple jobs and right-clicking\Script Jobs. Easy trick and I am sure there are MANY other ways.

The key to success on adding the orphaned target server back to the master is to defect the target server first. Seems logical right? I right clicked on the SQL Server Agent and selected Multi-Server Administration and Defect. This failed with a generic “Defect operation failed” error message, so I chose the option to Force Defection. This also failed. However, even though these steps reported failure, it must have removed whatever obstacle was preventing me from re-enlisting the target server because when I ran sp_msx_enlist again, I received a “operation succeeded” message.

Yes!

All that was left was to go back to the master server and push down the required jobs, which I did and within a few minutes everything was back the way it should be. As a point of interest, I used sp_helptext to uncover the logic in the sp_msx_enlist stored procedure. There are all kinds of interesting nuggets of info in there, like the extended stored procedure that is really run to make this enlistment called xp_msx_enlist that takes its own parameters.

Now let’s see if this shows up on a search for those of us still using this very helpful but somewhat dated SQL feature.