Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Stairway to SQL Server Agent - Level 12: Using MSX/TSX to scale out Job Management Expand / Collapse
Author
Message
Posted Tuesday, April 22, 2014 1:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:03 PM
Points: 361, Visits: 566
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
"Perhaps I'm not the only one that does not know what you are doing."
Post #1564004
Posted Tuesday, April 22, 2014 10:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 10:40 PM
Points: 21, Visits: 133
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.



Post #1564082
Posted Tuesday, April 22, 2014 10:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 10:40 PM
Points: 21, Visits: 133
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.



Post #1564083
Posted Wednesday, April 23, 2014 7:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:03 PM
Points: 361, Visits: 566
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
"Perhaps I'm not the only one that does not know what you are doing."
Post #1564487
Posted Wednesday, April 23, 2014 7:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:03 PM
Points: 361, Visits: 566
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
"Perhaps I'm not the only one that does not know what you are doing."
Post #1564488
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse