Maintenance Plans in SQL 2000 vs 2005

We're in the process of setting up some new SQL 2005 servers (previously all
our DB servers have been SQL 2000).
SQL 2000 had maintenance plans which consisted of a Database Backup Job,
Transaction Log Backup Job, Integrity Check Job, and an Optimization Job.
Each of these four jobs was managed by the parent maintenance plan and each
job could be run on different schedules. This made it simple for us to
manage DB maintenance.
However it seems the idea of a DB Maintenance Job is a bit different in SQL
2005:
- All the jobs inside a maintenance plan seem to have to run at the same
time? In our case we actually want these jobs to run on different schedules.
- Rather than adding each database to one maintenance plan, each database
has to be added to each job creating 4 times as much work to setup a
database.
The new functionality offers greater granularity which is great to have
available, but at the expense of ease of use. In SQL 2000 we used to have 5
maintenance plans per SQL instance to manage - in SQL 2005 that would equate
to 20 jobs per server to manage.
I'm praying we're missing something. Can anyone clue me in :-)
Thanks,
Brad BakerBrad Baker wrote:
> We're in the process of setting up some new SQL 2005 servers (previously all
> our DB servers have been SQL 2000).
> SQL 2000 had maintenance plans which consisted of a Database Backup Job,
> Transaction Log Backup Job, Integrity Check Job, and an Optimization Job.
> Each of these four jobs was managed by the parent maintenance plan and each
> job could be run on different schedules. This made it simple for us to
> manage DB maintenance.
> However it seems the idea of a DB Maintenance Job is a bit different in SQL
> 2005:
> - All the jobs inside a maintenance plan seem to have to run at the same
> time? In our case we actually want these jobs to run on different schedules.
> - Rather than adding each database to one maintenance plan, each database
> has to be added to each job creating 4 times as much work to setup a
> database.
> The new functionality offers greater granularity which is great to have
> available, but at the expense of ease of use. In SQL 2000 we used to have 5
> maintenance plans per SQL instance to manage - in SQL 2005 that would equate
> to 20 jobs per server to manage.
> I'm praying we're missing something. Can anyone clue me in :-)
> Thanks,
> Brad Baker
>
I would encourage you to move away from the maintenance plans and write
your own maintenance scripts. It's not difficult, and you have far
greater control over how things work.
See
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
and http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
for a couple of examples.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy -
Thanks for the links. I'll have a look.
We have on average 200 databases per SQL instance. So I'm afraid manual
scripting may not be totally practical. I'm assuming we would have to type
out the names of all 200 DBs in multiple locations?
To make things more fun our DBs are named like TZ0001-Y-clientname, so
imagine typing 200 of those without typos. :-) Not to mention managing
semi-weeekly additions and deletions.
To make this practical I think we'll need a GUI. Maybe wer can write our own
app that can generate these scripts auto-magically for us. I hope we don't
have to go that route though.
Brad Baker
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44FF106C.7020409@.realsqlguy.com...
> Brad Baker wrote:
>> We're in the process of setting up some new SQL 2005 servers (previously
>> all our DB servers have been SQL 2000).
>> SQL 2000 had maintenance plans which consisted of a Database Backup Job,
>> Transaction Log Backup Job, Integrity Check Job, and an Optimization Job.
>> Each of these four jobs was managed by the parent maintenance plan and
>> each job could be run on different schedules. This made it simple for us
>> to manage DB maintenance.
>> However it seems the idea of a DB Maintenance Job is a bit different in
>> SQL 2005:
>> - All the jobs inside a maintenance plan seem to have to run at the same
>> time? In our case we actually want these jobs to run on different
>> schedules.
>> - Rather than adding each database to one maintenance plan, each database
>> has to be added to each job creating 4 times as much work to setup a
>> database.
>> The new functionality offers greater granularity which is great to have
>> available, but at the expense of ease of use. In SQL 2000 we used to have
>> 5 maintenance plans per SQL instance to manage - in SQL 2005 that would
>> equate to 20 jobs per server to manage.
>> I'm praying we're missing something. Can anyone clue me in :-)
>> Thanks,
>> Brad Baker
> I would encourage you to move away from the maintenance plans and write
> your own maintenance scripts. It's not difficult, and you have far
> greater control over how things work.
> See
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
> and http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html for
> a couple of examples.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||One more thing I should probably have mentioned: because we have so many DBs
per SQL instance we we've previously done with SQL 2000 is something similar
to this;
Maintenance Plan 1
--
Integrity Check, Optimization Job, Full DB Backup
Runs Sunday around Midnight (each job is spaced out by 10-20 mins so that
they aren't colliding)
Transaction Log Backup
Runs every day - starts at 11PM
Contains DBs 1-50
Maintenance Plan 2
--
Integrity Check, Optimization Job, Full DB Backup
Runs Monday around Midnight (each job is spaced out by 10-20 mins so that
they aren't colliding)
Transaction Log Backup
Runs every day - starts at 11:30 PM
Contains DBs 51-100
Maintenance Plan 3
--
Integrity Check, Optimization Job, Full DB Backup
Runs Tuesday around Midnight (they're spaced out by 10-20 mins so that they
aren't colliding)
Transaction Log Backup
Runs every day - starts at 12:00 AM
Contains DBs 101-150
The details above aren't exactly right but you get the idea.
- Each database gets a full backup once a week but they are distributed
across different days to limit impact on server performance.
- Transaction log backups are made every day for every database but I
believe they start at different times - again to limit the impact on
performance.
Brad
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:ekgkAme0GHA.4920@.TK2MSFTNGP06.phx.gbl...
> Tracy -
> Thanks for the links. I'll have a look.
> We have on average 200 databases per SQL instance. So I'm afraid manual
> scripting may not be totally practical. I'm assuming we would have to type
> out the names of all 200 DBs in multiple locations?
> To make things more fun our DBs are named like TZ0001-Y-clientname, so
> imagine typing 200 of those without typos. :-) Not to mention managing
> semi-weeekly additions and deletions.
> To make this practical I think we'll need a GUI. Maybe wer can write our
> own app that can generate these scripts auto-magically for us. I hope we
> don't have to go that route though.
> Brad Baker
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:44FF106C.7020409@.realsqlguy.com...
>> Brad Baker wrote:
>> We're in the process of setting up some new SQL 2005 servers (previously
>> all our DB servers have been SQL 2000).
>> SQL 2000 had maintenance plans which consisted of a Database Backup Job,
>> Transaction Log Backup Job, Integrity Check Job, and an Optimization
>> Job. Each of these four jobs was managed by the parent maintenance plan
>> and each job could be run on different schedules. This made it simple
>> for us to manage DB maintenance.
>> However it seems the idea of a DB Maintenance Job is a bit different in
>> SQL 2005:
>> - All the jobs inside a maintenance plan seem to have to run at the same
>> time? In our case we actually want these jobs to run on different
>> schedules.
>> - Rather than adding each database to one maintenance plan, each
>> database has to be added to each job creating 4 times as much work to
>> setup a database.
>> The new functionality offers greater granularity which is great to have
>> available, but at the expense of ease of use. In SQL 2000 we used to
>> have 5 maintenance plans per SQL instance to manage - in SQL 2005 that
>> would equate to 20 jobs per server to manage.
>> I'm praying we're missing something. Can anyone clue me in :-)
>> Thanks,
>> Brad Baker
>> I would encourage you to move away from the maintenance plans and write
>> your own maintenance scripts. It's not difficult, and you have far
>> greater control over how things work.
>> See
>> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
>> and http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
>> for a couple of examples.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||Brad Baker wrote:
> Tracy -
> Thanks for the links. I'll have a look.
> We have on average 200 databases per SQL instance. So I'm afraid manual
> scripting may not be totally practical. I'm assuming we would have to type
> out the names of all 200 DBs in multiple locations?
> To make things more fun our DBs are named like TZ0001-Y-clientname, so
> imagine typing 200 of those without typos. :-) Not to mention managing
> semi-weeekly additions and deletions.
> To make this practical I think we'll need a GUI. Maybe wer can write our own
> app that can generate these scripts auto-magically for us. I hope we don't
> have to go that route though.
>
One of the links I gave you is for a backup script that will
automatically backup every database on the server, no need to type out
database names.
The second link is for an "intelligent" reindex routine that checks the
severity of fragmentation before acting on an index. This one accepts a
database name as a parameter, you should be able to easily take the
"loop through all databases" logic from the backup script and apply it
to the reindex script.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> One of the links I gave you is for a backup script that will automatically
> backup every database on the server, no need to type out database names.
We don't want to back all the databases up at the same time as it would take
too long.
Thus we do full backup on about 30 databases per day:
Sunday: Databases 1-30
Monday: Databases 31- 61
Tuesday: Databases 62 - 102
etc.|||Dear Brad,
From your description, I understand that:
You had 5 maintenance plans for backing up your 200 databases per your SQL
Server 2000 instance. Now you want to move the maintenance plans to your
SQL Server 2005.
If I have misunderstood, please let me know.
If your solution in SQL Server 2000 can run well, it should be also
available in SQL Server 2005. As you said:
SQL 2000 had maintenance plans which consisted of a Database Backup Job,
Transaction Log Backup Job, Integrity Check Job, and an Optimization Job.
Each of these four jobs was managed by the parent maintenance plan and each
job could be run on different schedules. This made it simple for us to
manage DB maintenance.
In SQL Server 2005, you can also create 4 maintenance plans which response
to Database Backup Job, Transaction Log Backup job, Integreity Check job,
and Optimization Job. If you are considering management convenience, you
can named the plans with a name specification such as
"instance1_db1to50_trans_backup".
I'm not sure whether or not your concerns are for this point. Unfortunately
SQL Server 2005's desgin doesn't include this function of SQL 2000.
I do know that the task in SQL Server 2005 is at least quadruple your work
in SQL Server 2000. I appreciate that you can give Microsoft feedback on
this issue, and hope the next release will include this helpful function.
You can submit a feedback via the link:
http://connect.microsoft.com/feedback/default.aspx?SiteID=68
Note: Login first before a submition of your feedback.
Also, I understand that your situation seems complex and if I have some
misunderstandings of your issue, welcome your pointing out and coming back
to newsgroup for further research. I'm very glad for your assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Brad Baker wrote:
>> One of the links I gave you is for a backup script that will automatically
>> backup every database on the server, no need to type out database names.
> We don't want to back all the databases up at the same time as it would take
> too long.
> Thus we do full backup on about 30 databases per day:
> Sunday: Databases 1-30
> Monday: Databases 31- 61
> Tuesday: Databases 62 - 102
> etc.
>
Ok, so you didn't look at the links I sent you. The backup script backs
up each database, one at a time, checking to see if
a. it's been more than 7 days since a full backup was taken
or
b. today is Friday after 9:00pm, or Saturday, or Sunday, and it's been
more than 3 days since a full backup was taken
If either of those is true, then it runs a full backup, otherwise it
runs a transaction log backup, or truncates the log.
The point is, it's a SCRIPT, and you can alter it to fit your needs,
unlike the maintenance plans.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||My problem is:
1) That jobs in the 2005 maintenance plans can't be run on separate
schedules. They have to be run at the same time which is not what we need.
In searching online I was able to finally find someone else with the same
problem:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124896
Apparently this scheduling problem is being addressed in SQl 2005 SP2 but
SP2 isn't due to be released until after Vista (Yuck!). Is there any way we
can call PSS and get a patch sooner? We're setting up 6 new SQL instances -
I really don't want to create 150 maintenance plans its bad enough to have
to create 30.
2) Instead of being able to associate a db with one maintenance plan and be
done with it. We now have to associate a db with each component of a plan -
Integrity Check, Optimization, Full Backup, Transaction Log backup. So in
our case we have to add the DB four separate times instead of one.
I'm quickly realizing that this isn't something we've just missed but rather
Microsoft seems to have dropped a lot of functionality that we used on a day
to day basis. It's frustrating and disappointing - We are not at all
impressed with SQL 2005.
Brad
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:8eoqAun0GHA.4280@.TK2MSFTNGXA01.phx.gbl...
> Dear Brad,
> From your description, I understand that:
> You had 5 maintenance plans for backing up your 200 databases per your SQL
> Server 2000 instance. Now you want to move the maintenance plans to your
> SQL Server 2005.
> If I have misunderstood, please let me know.
> If your solution in SQL Server 2000 can run well, it should be also
> available in SQL Server 2005. As you said:
> SQL 2000 had maintenance plans which consisted of a Database Backup Job,
> Transaction Log Backup Job, Integrity Check Job, and an Optimization Job.
> Each of these four jobs was managed by the parent maintenance plan and
> each
> job could be run on different schedules. This made it simple for us to
> manage DB maintenance.
> In SQL Server 2005, you can also create 4 maintenance plans which response
> to Database Backup Job, Transaction Log Backup job, Integreity Check job,
> and Optimization Job. If you are considering management convenience, you
> can named the plans with a name specification such as
> "instance1_db1to50_trans_backup".
> I'm not sure whether or not your concerns are for this point.
> Unfortunately
> SQL Server 2005's desgin doesn't include this function of SQL 2000.
> I do know that the task in SQL Server 2005 is at least quadruple your work
> in SQL Server 2000. I appreciate that you can give Microsoft feedback on
> this issue, and hope the next release will include this helpful function.
> You can submit a feedback via the link:
> http://connect.microsoft.com/feedback/default.aspx?SiteID=68
> Note: Login first before a submition of your feedback.
> Also, I understand that your situation seems complex and if I have some
> misunderstandings of your issue, welcome your pointing out and coming back
> to newsgroup for further research. I'm very glad for your assistance.
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Tracy -
I appreciate the links you sent - I review them both but admittedly I'm not
a DBA - I'm a sysadmin. I'm not familiar with TSQL (or whatever language
those scripts are written in) thus utilizing these scripts would probably
require a lot of time especially because the scripts don't do exactly what
we need already.
It doesn't seem like they handle Optimizations and Integrity Checks -
although maybe that's what the fragmentation script does? We also need email
notifications based on failures and logging.
Additionally it seems that the scripts take one of two approaches - 1)
Backup all databases or 2) specify the names of each database manually.
Neither approach is ideal. We don't want to backup all our DBs at once and
we don't want to type out the names of all the DBs on a server - which is
why we are looking for some way to do this through a GUI where we don't have
to worry about typos.
In any case it doesn't sound to me like this would be a good solution for
us. We might be better off setting up 150 maintenance plans through the GUI.
It would be incredibly tedious and time consuming but it would allow other
less technical people at our organization add new dbs without having to edit
scripts.
Brad
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45001512.4030505@.realsqlguy.com...
> Brad Baker wrote:
>> One of the links I gave you is for a backup script that will
>> automatically backup every database on the server, no need to type out
>> database names.
>> We don't want to back all the databases up at the same time as it would
>> take too long.
>> Thus we do full backup on about 30 databases per day:
>> Sunday: Databases 1-30
>> Monday: Databases 31- 61
>> Tuesday: Databases 62 - 102
>> etc.
> Ok, so you didn't look at the links I sent you. The backup script backs
> up each database, one at a time, checking to see if
> a. it's been more than 7 days since a full backup was taken
> or
> b. today is Friday after 9:00pm, or Saturday, or Sunday, and it's been
> more than 3 days since a full backup was taken
> If either of those is true, then it runs a full backup, otherwise it runs
> a transaction log backup, or truncates the log.
> The point is, it's a SCRIPT, and you can alter it to fit your needs,
> unlike the maintenance plans.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Brad,
I understand it can be frustrating when something changes in a way so it makes it more difficult for
your particular environment and needs. In many cases, I find the new Maint Plan architecture better
than the old one, but I definitely can see your point.
I strongly encourage you to spend some time to get some scripts going for your environment. I've
done this, and the level of customization, resilience to changes in the products etc. are
unbeatable. My guess is that it will take some 2-3 days for an experienced DBA TSQL programmer to
set up a scheme for you. I tend to have a table, with one row per database and columns for each type
of operation to be performed. This can easily be expanded with something like day to do the db
backup. the TSQL routine (the stored procedure kicked off by agent) reads off of this and performs
the operations based on what is in the table. This also makes it easy for a GUI to manage this
table, with drop-down boxes for the database names, and also database grouping and "all" support. In
our Db Maint product (not carried on to 2005), we also had a built-in group named something like
"exclude from all".
In short, IMO, consider shelling out 2-3 days worth of consulting fee to get this scheme going...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brad Baker" <brad@.nospam.nospam> wrote in message news:uQriuYp0GHA.4956@.TK2MSFTNGP04.phx.gbl...
> Tracy -
> I appreciate the links you sent - I review them both but admittedly I'm not a DBA - I'm a
> sysadmin. I'm not familiar with TSQL (or whatever language those scripts are written in) thus
> utilizing these scripts would probably require a lot of time especially because the scripts don't
> do exactly what we need already.
> It doesn't seem like they handle Optimizations and Integrity Checks - although maybe that's what
> the fragmentation script does? We also need email notifications based on failures and logging.
> Additionally it seems that the scripts take one of two approaches - 1) Backup all databases or 2)
> specify the names of each database manually. Neither approach is ideal. We don't want to backup
> all our DBs at once and we don't want to type out the names of all the DBs on a server - which is
> why we are looking for some way to do this through a GUI where we don't have to worry about typos.
> In any case it doesn't sound to me like this would be a good solution for us. We might be better
> off setting up 150 maintenance plans through the GUI. It would be incredibly tedious and time
> consuming but it would allow other less technical people at our organization add new dbs without
> having to edit scripts.
> Brad
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:45001512.4030505@.realsqlguy.com...
>> Brad Baker wrote:
>> One of the links I gave you is for a backup script that will automatically backup every
>> database on the server, no need to type out database names.
>> We don't want to back all the databases up at the same time as it would take too long.
>> Thus we do full backup on about 30 databases per day:
>> Sunday: Databases 1-30
>> Monday: Databases 31- 61
>> Tuesday: Databases 62 - 102
>> etc.
>> Ok, so you didn't look at the links I sent you. The backup script backs up each database, one at
>> a time, checking to see if
>> a. it's been more than 7 days since a full backup was taken
>> or
>> b. today is Friday after 9:00pm, or Saturday, or Sunday, and it's been more than 3 days since a
>> full backup was taken
>> If either of those is true, then it runs a full backup, otherwise it runs a transaction log
>> backup, or truncates the log.
>> The point is, it's a SCRIPT, and you can alter it to fit your needs, unlike the maintenance
>> plans.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||Dear Brad,
I can understand your concerns that SQL Server 2005 management tools still
have a lot of places to be improved. I am not sure if PSS has corrected
this by design issue now, but you can try.
You can contact Microsoft Product Support Services (PSS) via telephone so
that a dedicated Support Professional can assist you recover the server in
a more efficient manner. Please be advised that contacting phone support
will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support