Recovery model problem; db properities

Hello,


I've follow problem - thing to consider.

SQLServer 200 sp3a, ms win 2003 server
db simple recovery

There is a production database, wich is around 20gb big. Db is backed
up each day completely, but it takes up to 30 minutes.
Because there is a simple recovery model, there is no transaction log
backup (it fails anyway), and we do not have up-to-point recovery.

I'm considering to switch to full recovery model, but ....
The problem is, I do not want to affect performance (when the backup is
running, database is hardly avalible).

So my question will be: does the full recovery model, will be better
for db performance (for acces and blocking db; means, does it will take
shorter?)
Strategy will be (I hope ok) to back up during the week only
transaction log (incremental), and once at the weekend, full database
backup.

Generaly, which one is better for performance?
Which strategy will be the best, to keep performance at high level, but
also have the possibility to restore data (in case of emergency) from
the newest possible backup.

Thanks for help

Matik

Re: Recovery model problem; db properities

"Matik" wrote in message
news:1140084783.803830.233070@g14g2000cwa.googlegroups.com.. .
> Hello,
>
>
> I've follow problem - thing to consider.
>
> SQLServer 200 sp3a, ms win 2003 server
> db simple recovery
>
> There is a production database, wich is around 20gb big. Db is backed
> up each day completely, but it takes up to 30 minutes.

What's the form of backup? disk, NAS, tape?

> Because there is a simple recovery model, there is no transaction log
> backup (it fails anyway), and we do not have up-to-point recovery.
>
> I'm considering to switch to full recovery model, but ....
> The problem is, I do not want to affect performance (when the backup is
> running, database is hardly avalible).

That's unusual. Generally a full-backup shouldn't hurt performance that
much.

If it really is, I'd do some more digging and try to find out why.

>
> So my question will be: does the full recovery model, will be better
> for db performance (for acces and blocking db; means, does it will take
> shorter?)

We do backups of similar size databases every 20 minutes with no noticable
impact on performance. I may eventually move to 10 minute backup windows.
Generally the backups shouldn't be blocking the db at all, the biggest cost
is generally disk I/O.

> Strategy will be (I hope ok) to back up during the week only
> transaction log (incremental), and once at the weekend, full database
> backup.
>

Only problem with this may be that in the event of a failure, say right
before you do your weekly full, you'll have a LOT of transaction logs to
restore. This will impact your time to recovery, so take that into account.
You may also want to use differential backups during the week to speed
things up in the event of having to recovery.


> Generaly, which one is better for performance?
> Which strategy will be the best, to keep performance at high level, but
> also have the possibility to restore data (in case of emergency) from
> the newest possible backup.
>
> Thanks for help
>
> Matik
>

Re: Recovery model problem; db properities

Matik (marzec@sauron.xo.pl) writes:
> I'm considering to switch to full recovery model, but ....
> The problem is, I do not want to affect performance (when the backup is
> running, database is hardly avalible).

Then you have something investigate. The footprint of a backup should
be light. Database should certainly be available during this time.

> So my question will be: does the full recovery model, will be better
> for db performance (for acces and blocking db; means, does it will take
> shorter?)
> Strategy will be (I hope ok) to back up during the week only
> transaction log (incremental), and once at the weekend, full database
> backup.

Daily differential backups would be a better choice in that case.

Most of our customers run a daily full backup, and then translog backups
at least once an hour, some every ten or fifteen minutes.

Depending on your activity in the database, the log can grow big it
you back it up once a day. If your run a maintenance job, it could
grow bigger than the data file itself.

> Generaly, which one is better for performance?
> Which strategy will be the best, to keep performance at high level, but
> also have the possibility to restore data (in case of emergency) from
> the newest possible backup.

The backup strategy should be determined of your requirements for
disaster recovery. If you are content with restoring a backup in
case of failure, continue with simple recovery. Switch to full if
you want up-to-the-point recovery.

A tip is that if you go for full recovery, is that you should allocate
a couple of GB for the log file on the spot, to prevent autogrow
growing the file piece by piece, which could lead to fragmentation
on file-syste level.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx