September 9, 2015 at 4:30 pm
I'm looking for a way to list the target servers associated with a master server. The reason is that we're moving to another master server, and I'd prefer not to move the targets manually.
I've got most of the T-SQL already (sp_msx_enlist, sp_add_jobserver), but I'd like a scripted solution instead of a wizard. I'm open to other languages, if that's the way.
TIA
Andre
September 19, 2015 at 1:38 pm
I believe the dbo.systargetservers table in the msdb database has what you are looking for?
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
September 21, 2015 at 8:42 am
Yeah, I found a lot more since I first posted. For example, from the master server:
selectserver_name, *
frommsdb.dbo.systargetservers
or this, to help identify target servers that are failing:
-- http://mikehillwig.com/tag/multi-server-administration/
create table #tmp_sp_help_targetserver(
server_id int null,
server_name sysname null,
location nvarchar(200) null,
time_zone_adjustment int null,
enlist_date datetime null,
last_poll_date datetime null,
status int null,
unread_instructions int null,
local_time datetime null,
enlisted_by_nt_user nvarchar(200) null,
poll_interval int null)
insert into #tmp_sp_help_targetserver
exec msdb.dbo.sp_help_targetserver
SELECT*--SERVER_NAME
FROM#tmp_sp_help_targetserver
wherestatus = 5
drop table #tmp_sp_help_targetserver
or this to create a master server:
-- https://msdn.microsoft.com/en-us/library/ms188926(v=sql.120).aspx
-- https://msdn.microsoft.com/en-us/library/ms175104.aspx#TsqlProcedure
-- do this on the master server
declare @locSrv nvarchar(50)
set @locSrv = @@SERVERNAME
EXEC msdb.dbo.sp_msx_enlist @locSrv,
N'comment goes here' ;
to create a target server:
-- run this on the target server
declare @masterSrv nvarchar(50)
EXEC msdb.dbo.sp_msx_enlist @masterSrv,
N'comment goes here' ;
To add jobs to the master server that would be available to the target server, running from the master server:
declare @locSrv nvarchar(50)
set @locSrv = @@SERVERNAME
EXEC [msdb].[dbo].[sp_add_jobserver] @job_name = N'YourJobNameHere', @server_name = @locSrv
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply