May 18, 2011 at 4:58 pm
We are migrating our database from SQL 2005 to SQL 2008 R2.
The old server (set up before I arrived) hosts about dozen databases. Each DB has it's own backup, log backup, index reorganization and other jobs, which means there are 60 jobs in SQL Agent, carefully scheduled to try not to overlap each other too much.
In setting up the new server, it makes more sense to me to create a fewer number of Maintenance Plans/Agent Jobs which perform these tasks on multiple databases. I can make one Backup job to backup all the DBs connected to a particular application, then a backup log job which backups the transaction logs for the same DBs, saving job build time, and also reducing overlap to some extent since the steps with a job will be executing consecutively with no overlap, or gaps between steps.
What are the pros & cons of creating separate jobs for everything verses combining them?
Dan
May 19, 2011 at 6:30 am
the downside of multiple step jobs or multiple commands in a step is how you handle an error or failure.
Let's say you take the approach of 1 step for each backup:- what do you on error - abort the whole job, carry on, and then how do you know if a job has a failure.
Some errors will end a batch so no matter how carefully you code an error could stop your whole job.
I'm not saying one approach is right or wrong, I think it depends upon your environment.
How you handle this type of thing is up to you, I have mostly my own procs to handle everything, many other dbas do much the same, so for example I have a proc i can call which will backup all user databases sequentially - because it's a series of proc calls - the backup all proc calls another proc to do the backup for each database which in turn has calls to a messaging proc which can log or mail or both success or failure messages. etc. etc.
But it all depends - backups are critical don't introduce complexity and potential errors just for the sake of reducing the number of jobs.
Make sure you test for as many types of errors as you can . e.g. put a database offline and see if the backup still runs.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 19, 2011 at 6:38 am
I have faced some problems with this approach-combining all db's in one backup job, especially on servers where there is disk space shortage on the backup drives. During backup job failures during disk shortage, the entire job would get aborted and all db's would get affected.
It is better we take note of the size of the databases while considering clubbing all the db's into one job.
M&M
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply