SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to SQL Server Agent - Level 12: Using MSX/TSX to scale out Job Management


Stairway to SQL Server Agent - Level 12: Using MSX/TSX to scale out Job Management

Author
Message
rstone
rstone
SSChasing Mays
SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)

Group: General Forum Members
Points: 609 Visits: 824
If I either remove the target or delete the master job, the job on the target gets removed - as it should. I'm curious - is it possible to not delete the job and convert it to a local job instead? Thanks.

Randy
Helpdesk: "Perhaps I'm not the only one that does not know what you are doing." ;-)
David Korzennik
David Korzennik
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 155
Hi Randy

Yes there is a way to convert an MSX job to a local job. But remember, with great power comes great responsibility. ;-).

In the msdb db on the target server in the sysjobs table, is a column - originating_server_id. For jobs that originate from a Master server, these are a 1. Update the 1 to a 0 and the job becomes local.



David Korzennik
David Korzennik
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 155
Hi Randy

Re your average run time per server job = backup time. I would, as you intimated, rather directly query the backupset table in msdb and do a DATEDIFF on the backup_start_date and backup_end_date columns to get the backup run time. You could do this to each server individually or through a CMS distributed query or using powershell to loop through a list of servers and populate a central table. Just some ideas.



rstone
rstone
SSChasing Mays
SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)

Group: General Forum Members
Points: 609 Visits: 824
Thanks for all the advice. I'm still trying to figure out the "best" way to go. I'm also considering creating a group of "identical" master jobs that very only by schedule. Each would be a "schedule bin" of sorts to place instances. One master job for all servers sharing the same schedlue for that job. Might be too much clutter.

I think the idea of deploying a single job, coverting it to a local job, and then updating the schedule is interesting. Some of the targets will be production servers. A solution based on hacking system tables will not likely be used. I tried hacking the record on a local test instance. I had to also change the category_id to 0 to "unlock" it. A refresh of the jobs folder in SSMS allowed it to open without error. Sure enough, I was able to add a schedule and rename the job. I ended up enabling one of the master schedules, but it had to be also "localized".

After a call to sp_resync_targetserver on the master, the "converted" job gets replaced by the master copy. It uses the job_id for the sync, so it would not matter if the job was renamed.

If I also include a version number in the job name, I can effectively move the test servers first by removing them from the old bin and adding them to the new bin.

Here is the first step in my test job if you are curious. The master job has 5 schedules named with "Bin 0" to "Bin 4". Only "Bin 0" is enabled so that the job can run within 5 minutes - every 5 minutes. Only the first step is configured to run. The first step removes the "Bin 0" schedule, enables the correct schedule, and then removes the first step of the job. Then the second step is now the first step and will run with the new scheduled. Crazy, but it actually works. I thought the removal of the "Bin 0" would not work as coded, but it appears the @delete_unused_schedule=1 applies to MSX schedules.

-- This job step only exists because the job has not been localized.

declare @JobID uniqueidentifier
set @JobID = $(ESCAPE_SQUOTE(JOBID))

declare @StepID int
set @StepID = $(ESCAPE_SQUOTE(STEPID))

declare @LocalOpName nvarchar(128)
set @LocalOpName = 'RStone'

declare @LocalOpAddress nvarchar(128)
set @LocalOpAddress = 'rstone@marincounty.org'

declare @OperatorID int
select @OperatorID = id from msdb.dbo.sysoperators where name = @LocalOpName

-- 1. Determine which of the job schedules to enable.

declare @ScheduleBin int
/*
if @@SERVERNAME like '[a-g]%' set @ScheduleBin = 1
else if @@SERVERNAME like '[h-m]%' set @ScheduleBin = 2
else if @@SERVERNAME like '[n-s]%' set @ScheduleBin = 3
else set @ScheduleBin = 4
*/
if @@SERVICENAME in ('STG') set @ScheduleBin = 2
else if @@SERVICENAME in ('TST') set @ScheduleBin = 3
else if @@SERVICENAME in ('TRN') set @ScheduleBin = 4
else set @ScheduleBin = 1

declare @ScheduleID int
select @ScheduleID = s.schedule_id
from msdb.dbo.sysschedules s
inner join msdb.dbo.sysjobschedules js
on js.schedule_id = s.schedule_id
where job_id = @JobID and s.name like '% Bin ' + LTRIM(STR(@ScheduleBin )) + ' %'

-- 2. Create local operator to replace MSXOperator

if not exists(select * from msdb.dbo.sysoperators where name = @LocalOpName)
begin
EXEC msdb.dbo.sp_add_operator
@name=@LocalOpName,
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=@LocalOpAddress,
@category_name=N'[Uncategorized]'
end

-- 3. Localize this job and the schedule to be enabled

if exists(select * from msdb.dbo.sysjobs where job_id = @JobID and originating_server_id = 1)
begin

update msdb.dbo.sysjobs
set originating_server_id = 0,
category_id = 0,
owner_sid = 0x01
where job_id = @JobID

update msdb.dbo.sysschedules
set originating_server_id = 0
where schedule_id = @ScheduleID

end

-- 4. Change operator for email notification

if not exists(select * from msdb.dbo.sysjobs where job_id = @JobID and notify_email_operator_id = @OperatorID)
begin
execute msdb.dbo.sp_update_job
@job_id = @JobID,
@notify_email_operator_name = @LocalOpName
end

-- 5. The "Bin 0" schedule only exists to run this step once - remove it

declare @Bin0ScheduleID int

select @Bin0ScheduleID = s.schedule_id
from msdb.dbo.sysschedules s
inner join msdb.dbo.sysjobschedules js
on js.schedule_id = s.schedule_id
where job_id = @JobID and s.name like '% Bin 0 %'

EXEC msdb.dbo.sp_detach_schedule
@job_id=@JobID,
@schedule_id=@Bin0ScheduleID,
@delete_unused_schedule=1

-- 6. Enable the appropruate schedule

EXEC msdb.dbo.sp_update_schedule
@schedule_id=@ScheduleID,
@enabled=1

-- 7. Delete this step. The job is not configured to go to the next step.

execute msdb.dbo.sp_delete_jobstep @job_id = @JobID, @step_id = @StepID

Randy
Helpdesk: "Perhaps I'm not the only one that does not know what you are doing." ;-)
rstone
rstone
SSChasing Mays
SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)

Group: General Forum Members
Points: 609 Visits: 824
After all that, I am wondering if perhaps I can't just change the bit on the target schedule, edit the schedule, and then change the bit back.

Randy
Helpdesk: "Perhaps I'm not the only one that does not know what you are doing." ;-)
yhandz_21
yhandz_21
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 280
hi i have a problem there was no error but the job is not appearing on the target server? anyone encountered the same problem?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search