February 11, 2011 at 9:00 am
My company has 55 satellite locations that collect data. This data is replicated to the main database by a home-grown (so not SS replication) replication system. To assimilate this outside data to the production data, a job is run with set of location IDs staggered every 10 minutes, like so:
job 1 is set to run every 10 minutes
exec usp_MigrateDataFromLocation 1, 13
job 2 runs every 10 minutes starting 2 minutes after job 1 with these IDs
exec usp_MigrateDataFromLocation 14, 26
job 3 runs every 10 minutes starting 2 minutes after job 2 with these IDs
exec usp_MigrateDataFromLocation 27, 40
etc.
I want to replace those 5 jobs with this setup:
create table tblReplication_Migration_History (
ReplicationLocationID int not null,
Migration_Start datetime not null,
Migration_End datetime);
go;
--create indexes...
create procedure usp_Migration_Controller
as
set nocount on
--if there is any job that started but not finished, then don't start new job
if exists(
select 1 from tblReplication_Migration_History
where Migration_End is null)
return;
declare @ReplicationLocationID int;
--get the next rep ID to replicate, new ones first
select top 1 @ReplicationLocationID = rc.ReplicationLocationID
from tblReplicationLocations rl
left join
(
select ReplicationLocationID, max(Migration_Start) latest_migrate_date
from tblReplication_Migration_History
group by ReplicationLocationID
) md on rl.ReplicationLocationID = md.ReplicationLocationID
where rl.blnInActive = 0
and rl.LocationTypeID = (select LocationTypeID from tblLocationTypes where ObjectType = 'OutsideLocation')
order by isnull(md.latest_migrate_date, '1/1/1900'), rl.ReplicationLocationID;
insert into tblReplication_Migration_History(ReplicationLocationID,Migration_Start)
values (@ReplicationLocationID, getdate());
exec usp_MigrateDataFromLocation @ReplicationLocationID, @ReplicationLocationID;
update tblReplication_Migration_History set Migration_End = getdate()
where ReplicationLocationID = @ReplicationLocationID and Migration_End is null;
go;
So now my SS agent job will be to simply call usp_Migration_Controller every 15 seconds.
The benefits I get from this are 1) it only runs one location at a time taking less CPU time and tying up records for less time, and 2) if a job is still running, a new job will not start and pile on to the CPU, and 3) I won't have to alter any jobs if locations are added or removed.
My questions are:
Is this new setup advisable? Are there any caveats I'm not considering?
TIA
*Edit: misspellings
*edit 2: misspelled misspellings
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
February 11, 2011 at 9:13 am
What you're doing is essentially a "mutex". I've done pretty much the same thing for several jobs in several shops, and it has worked out just fine for me.
Look up mutex online. It'll help to understand the subject.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2011 at 9:36 am
Yes, I remember mutex from OS class oh so long ago. Couldn't think of the term. Thanks for that.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply