ADSM-L

Re: SQL server backups

2003-09-23 14:57:20
Subject: Re: SQL server backups
From: Todd Lundstedt <Todd_Lundstedt AT VIA-CHRISTI DOT ORG>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Tue, 23 Sep 2003 13:55:47 -0500
One option would be to redirect those backups to storage pools that don't
reclaim.  Simply let the retention expire the data, and empty the volumes.
 It is what I had to do to prevent reclaiming three 280+GB striped files.




Thomas Denier <Thomas.Denier AT MAIL.TJU DOT EDU>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
09/23/2003 01:11 PM
Please respond to "ADSM: Dist Stor Manager"


        To:     ADSM-L AT VM.MARIST DOT EDU
        cc:
        Fax to:
        Subject:        Re: SQL server backups


> >My company has a Windows server supporting a very large SQL Server
database.
> >Before the last hardware upgrade, the database was backed up with the
SQL
> >Server Connect Agent. Since the upgrade the database has been dumped to
a
> >flat file which is then backed up by the backup/archive client. Either
way
> >the backup arrives at the TSM server as a single file containing about
> >20 gigabytes of data. A single file this size causes a number of
problems.
> >The recovery log gets pinned for hours. Tape reclamation tends to
perform
> >poorly. The system administrator is now considering installing TDP for
> >SQL Server. Would this software still send the backup as a single huge
> >file?
>
> Without product, level, and tape drive details, it's hard to give a lot
of
> advice; but it sounds like you have significant tape drive technology
issues
> slowing everything down.  Large files are usually the optimal type of
objects
> for backup systems, allowing streaming, minimal tracking updates, etc.
> I would not approach TDP until you address underlying issues.  I'd begin
by
> getting benchmark numbers on your tape drive and other subsystem
throughput
> rates to isolate the bottleneck and address that.

I didn't give details about the tape configuration because it is
clearly not the problem. We used to be able to back up the
database to a disk storage pool initially (the database is slowly but
steadily growing) and had the same kind of log pinning issues. The
worst form of the tape reclamation pathology occurs with offsite tapes.
We tend to get one of the huge files starting at or near the beginning
of a 3590 J volume, filling the rest of that volume, and spilling on
to the first few percent of another volume. The next offsite reclamation
process regards the second volume as a prime candidate for reclamation
and recopies the entire file to two new volumes. In some cases we have
seen this phenomenon repeatedly for several days running. Onsite tape
reclamation handles big spanned files more gracefully, but even there
such files result in a poor trade-off between the amount of data
movement and the amount of free space generated. The pathalogical
behavior results from the relationship between file size and volume
capacity, and has absolutely no relation to streaming, tracking
updates, or whatever.

<Prev in Thread] Current Thread [Next in Thread>