Re: Admin question about backups
From: mountain man (hobbit_at_southern_seaweed.com.op)
Date: 03/29/04
- Next message: JB: "Query Help"
- Previous message: hkvats_1999_at_yahoo.com: "Re: Can't KILL Process"
- In reply to: DTJ: "Admin question about backups"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 29 Mar 2004 09:07:26 GMT
"DTJ" <dtj@comcast.net> wrote in message
news:7u4f60dksdsnsoa6icl6cnjnvo7udgiroh@4ax.com...
> I am a developer, not an admin. I am currently employed as a system
> analyst in charge of a number of applications we have purchased from
> major software vendors. Our admin is not real comfortable with a
> number of tasks, and has trouble thinking "outside the box". If there
> is something he has done before, he can duplicate it, but can not do
> it any differently.
>
> The issue we have is that some of our databases are backed up
> significantly differently than others. He has no idea how or why they
> were configured the way they were. We are seeing transaction logs
> that are up to 10 times as large as the database, for systems that are
> about 70% read, 30% write. The system that is the worst has been up
> since the beginning of the year, yet is one of our worst.
>
> I have done some research, and coupled with my (admittedly limited)
> admin knowledge from previous jobs, I believe the issue is that we are
> not truncating the transaction logs. However on SQL Server 2000 I
> read in some places that you should NOT truncate, while in others I
> read you should. I recognize that there are a number of issues that
> come into play.
>
> In general, for a system that is backed up to tape on a nightly basis,
> and that the users expect to be recoverable to the previous nights
> tape backup, how would you configure SQL Server 2000 to back up?
Assuming that you do not have huge queues of transactions filtering
down to the database after the daily transaction entry is over, you
might say that the database as at close of business has no open
transactions, and that the database needs no logfile data to be
complete as at that time. It is under these circumstances that
the logfile is truncated, and the database backed up. Lets
call this scenario 1.
OTOH, if you have problematics applications that for some
reason are holding up the comit of transactions, you will indeed
have open transactions as at this (standard) COB timeslot on
a daily basis, and the logfile would be needed to recover the
database plus queued transactions. Lets call this scenario 2.
Under scenario 1, the best approach IMO would be to
1) backup the database to disk (overwriting last nights backup).
2) backup the backup to tape.
This has no other advantage other than the difference in restore time
should you need to restore to COB backup data in the morning due
to an overnight corruption of some form. Restore from disk is
mostly quicker than from tape.
> How
> would you do this differently if the users want to be able to recover
> to a point in time during the business day?
Another type of scenario 2 is during the business day when the transaction
queue from user entry and maintenance could be deep due to response times
on the network, the database , the application(s), rogue apps or many users
hammering your machine concurrently.
The way to approach the scenario 2 backup, by the book, is via
incremental backups, that can be set throughout the day and
finally culminate with the full backup.
There is adequate documentation concerning "incremental backups",
how these are established, and how these are recovered from.
> The system is Raid 5, one physical disk array with 30GB storage. The
> transaction log, backups and database are all on the same drive of
> course, due to the physical configuration. So if we need to recover
> the database from SQL Server backups, the backup is no more likely to
> be there than the actual database, and the transaction logs are also
> questionable.
My advice to you is based on the fact that raid 5 gear is not cheap.
You have one expensive disk and a tape unit. This might be fine for
all your days. However IMO I would invest in an additional disk
drive, which in Oz costs about $300 for 80Gb (IDE drive), and
a drive housing ($100) and connect it via the USB to the server.
The point is you can through automation have a third level of
redundancy for very little cost, and exceeding little ongoing effort.
This may not seem worthwhile to many people, but Murphy's Murphy.
> Feel free to point me to articles, books online, or whatever resource,
> if you do not want to type out what could be a lengthy response!!! I
> have read quite a lot already, though, and am most interested in
> opinions, as there is only so much the literature can recommend before
> real life has to take over.
Additionally, all backups (full or incremental, with or without logs) can
be and should be automated of course.
The documentation MS Books Online provides reasonable guidelines
on all the foregoing, but there is no better method of learning than by
practice. Start with the demo databases such as pubs and northwind
and get the feel to do backups and restores (under another name is
safe as houses).
Good luck.
-- Pete Brown Winluck P/L IT Managers & Engineers Falls Creek Australia www.mountainman.com.au/software
- Next message: JB: "Query Help"
- Previous message: hkvats_1999_at_yahoo.com: "Re: Can't KILL Process"
- In reply to: DTJ: "Admin question about backups"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
