References on SQL Server Jobs - Remote or Local and When & WHy

Question

References on SQL Server Jobs - Remote or Local and When & WHy

YSLGuru

SSC-Insane

Points: 21661
More actions
March 23, 2010 at 1:56 pm

#139549

Does anyone have any links or references to documentation or studies on the point of where to setup a SQL Job (i.e. on the local instance that the DB the job accesses is on or a remote server) as well as when and why and possibly when not to?
I'm looking for some good solid info on when best to setup a job on a remote serve and when best to not do so. For example if I have n SSIS that affects multiple DB's and the DB's are on SERVER A, should I setup the SQL job that calls the SSIS package on the same server as the 2 DB's or on a remote server?
If using a remote Server for SQL Jobs then should that SQL Server do nothing else? Is there any kind of metric to apply when determining when a SQL Server instance has too many jobs if there is such a thing.
Basically there's been some debate about scheduling jobs locally verses remote for non-SSIS jobs as well as SSIS jobs and whether or not to have a JObs only SQL Server. I realize there may be no formula that answers all of these but perhaps someone with a lot of experience in the SQL Jobs field has written something about these kinds of questions.
thanks
Kindest Regards,
Just say No to Facebook!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 1

Can't say that I have seen any white papers or anything, but when making this decision, I look at one factor as it is really all that matters all else being equal.

Which location is going to require the least amount of network traffic. All things are done on the same server. So all that ever matters from my perspective is how long something is waiting for network transfer.

Consider the following example for a job that hit server A and B.

Server A take 1 second to execute and the final output is 250 MB

Server B take 5 second to execute and the final output is 1MB.

Now if Server A has to wait on Server B before completing what it needs, it waits on 1MB to be transfered at what ever your rate of transfer is.

On the flip side, if Server B has to wait on Serve A before completing, it waits on 250MB to be transfered.

Now, I am not much on the network, but to me, it would make more sense to put the job on Server A, where it wouldn't have to wait on the network transfer as long, which, assuming that Server A and B are equal, then it is your only factor in how long things take to get done.

Hope this at least throws another argument in the mix for you guys/gals.

Fraggle

YSLGuru SSC-Insane Points: 21661 More actions · Answer 2

Fraggle,

I didn't think there was anything like this because I did not think there was any reason other then a scenario like you have listed that you would have to have a SQL JObs server but my boss who is familiar with SQL Server said he had read or heard somewhere that as of SQL Server 2005 there were reason now, straight froward reasons & examples where you'd run the job on a SQL Server instance that was different from the SQL Server that hosted the DB the job was doing something to.

As an example lets say i have a SQL Job that runs nghtly after hours and looks for INdex fragmentation and based on teh results it either Reorganizes or Rebuilds the indexes. We are already running this after hours and since the work (I assume) of the reorganize/rebuild will be done on the same SQL Server that hosts the database that conations the indexe being rebuilt, then what benefit would I get from moving that job to a different SQL Sever and thru Linked servers run the job remotely? I don't see a reason for doing that in this scenario but maybe I'm missing something.

Perhaps in SQL Server 2008 there is a change in the system that makes placing SQL jobs on a rmeote server make sense??

Thanks for replying.

Kindest Regards,

Just say No to Facebook!

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

It is perhaps possible your boss is referring to this BOL http://msdn.microsoft.com/en-us/library/ms345184%28SQL.90%29.aspx

However it seems to involve creating the jobs on the remote servers and running them from a parent SSIS package. You still have the upkeep of all of the child SSIS packages and it's a manual process. I guess the biggest question I have is what is the business reason for moving all of your jobs to a separate server? Is this just cause your boss is bored?

Executing a SQL agent job on server B to do some work on server a doesn't seem to make a whole lot of sense to me unless it's absolutely necessary. For no other reason than what happens if you need to reboot your 'jobs' server? What happens if you have a failure on that particular machine? Then none of your jobs fire at all. It's a single point of failure. None of your backups happen, none of your index maintenance, nothing.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

YSLGuru SSC-Insane Points: 21661 More actions · Answer 4

Luke,

I haven't read over the article you linked in your reply but the title alone and the fact its about SSIS is enough to make me think this just may be what my boss read or saw at some point because he used to be big into SSIS when 2005 first hit and the true frustration of trying to use SSIS 2005 had not yet been widely known.

Thanks for sharing and for confirming what I suspected that genreally speaking there's not any standard benefit from doing a 2 server setup for SQL Jobs.

Thanks

Kindest Regards,

Just say No to Facebook!