August 29, 2008 at 10:36 am
I'm not really a SQL Server newbie, because I've done database design for years but now in a personal project where efficiency and so forth is paramount, I am realising just how tricky good database design can be.
My question today is, what is the difference between a sql server maintenance plan and a sql server job? Both essentially do the same thing. :S
Thank You!
Great forum
August 29, 2008 at 10:45 am
A Sql server maintenance plan is generated by a tool provided by MS, either Enterprise manager in 2000 or SSMS in 2005. It attempts to provide a standard set of tasks that should be performed if you are properly maintaining a Sql server database. the maintenance plan is scheduled using Sql server scheduling tool Sql Agent so the maintenance plans must appear there to run at the times designated by the user.
I think most people here have tried these plans and found that having your own Sql server tSQL based jobs and setting your own schedule easier. I have a standard set I used but from BOL it is pretty easy to assemble your own. These tasks will also have to be scheduled using Sql agent but will use TSQL instead of the maintenance plan.
Hope that helps
August 29, 2008 at 10:51 am
Alan (8/29/2008)
A Sql server maintenance plan is generated by a tool provided by MS, either Enterprise manager in 2000 or SSMS in 2005. It attempts to provide a standard set of tasks that should be performed if you are properly maintaining a Sql server database. the maintenance plan is scheduled using Sql server scheduling tool Sql Agent so the maintenance plans must appear there to run at the times designated by the user.I think most people here have tried these plans and found that having your own Sql server tSQL based jobs and setting your own schedule easier. I have a standard set I used but from BOL it is pretty easy to assemble your own. These tasks will also have to be scheduled using Sql agent but will use TSQL instead of the maintenance plan.
Hope that helps
So really it is preference and a series of design decisions which dictate which to use.
Also, I have noticed that jobs have a lot of power in that there's a lot of options in the window which pops up when creating a new job (not that I really need this power - yet).
Thanks for your help
August 29, 2008 at 10:58 am
Well as I understand it, there is a difference.
A Job can have multiple schedules included with it. A maintenance plan can have only one schedule. Also, I do not believe (although I am not positive) that you can use a maintenance plan within a job, but you can use a job within a maintenance plan. Essentially, a Job is like a chapter in a book, while the maintenance plan is the actual book (atleast that is how I understand it).
Fraggle
August 29, 2008 at 11:01 am
Also, I have noticed that jobs have a lot of power in that there's a lot of options in the window which pops up when creating a new job (not that I really need this power - yet).
If you look at it the one of the biggest differences, to me, between the pay versions of Sql Server and the free editions is Sql Agent with is a robust scheduler that keeps all of your logging and reporting in one server making it much easier to manage.
August 29, 2008 at 12:16 pm
Fraggle (8/29/2008)
Well as I understand it, there is a difference.A Job can have multiple schedules included with it. A maintenance plan can have only one schedule. Also, I do not believe (although I am not positive) that you can use a maintenance plan within a job, but you can use a job within a maintenance plan. Essentially, a Job is like a chapter in a book, while the maintenance plan is the actual book (at least that is how I understand it).
Fraggle
In 2005 and 2008 you can have multiple schedules based on the sub jobs in a single maintenance plan as a maintenance plan can have many sub jobs. If you are using 2005 make sure you are using SP2 or above as the maintenance plans are extremely flaky below that.
September 4, 2008 at 3:09 pm
when you create a maintenance plan it creates an associated job with it. Maintenance plans are just a graphical way of creating jobs in sql server!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply