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 a
ll
> 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 eac
h
> 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 SQ
L
> 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 schedule
s.
> - 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 equa
te
> 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/a...-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:
> 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/a.....htm
l
> and http://realsqlguy.com/serendipity/a...-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:
> 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 o
wn
> 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/feedba...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:
> We don't want to back all the databases up at the same time as it would ta
ke
> 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/SQLSer...=12489
6
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/feedba...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:
> 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