How many logical volumes for large database?

From: Gary Dom (domgATsutterhealthDOTorg_at_no.spam)
Date: 09/16/04 

Date: Thu, 16 Sep 2004 08:45:21 -0700

We are about to migrate a large database server (600+ GB data) onto new
hardware. It is currently on a 450 MHz quad CPU, 4 GB RAM box with local
SCSI storage, and will be moving to a new 3 GHz Quad CPU, 6 GB RAM box with
SAN attached storage. My question concerns how many logical volumes we
should have to support the various database components.

Currently, we have separate logical drives for the Data files, Indexes,
Logs, and TempDB. This was done for two reasons:

First, we had four available SCSI channels, so by creating four arrays each
on a different channel we increased disk I/O.

Second, we did it to reduce fragmentation. The database is used mainly for
running reports and analyses off of data from our main business application,
which is run on a VMS cluster. That system can't handle the performance hit
for all of the reporting, so once a week data is dumped from the mainframe
into the SQL Server. The rest of the week the SQL db is essentially
read-only. So the data volume is fairly static, growing on a weekly basis
but almost never shrinking. For performance reasons, the indexes are
dropped and recreated weekly, the logs are truncated and then grow, and of
course TempDB grows and shrinks as needed. By separating each component
onto a different logical drive, we should have less fragmentation.

Now that we are moving to a new architecture, the question has been raised
as to whether this is still the best setup, or if it just creates
unnecessary administrative overhead. Obviously we no longer get a
performance gain from multiple I/O channels, because all of the storage will
be accessed via the same fiber channel to the SAN.

As for the fragmentation issue, is it really that much of a problem? Would
it cause issues if we were to, say, combine data and indexes on the same
volume, or logs and TempDB?

The goal is to reduce administrative overhead. But since we have data
files, indexes, logs, and tempDB in any case, does it make a difference from
a management standpoint whether they are all on one volume or on four
separate ones? Would it be an issue either way from a backup/restore
standpoint?

Thanks,

Gary

Relevant Pages

  • Re: How many logical volumes for large database?
    ... You can still benefit from multiple volumes on a SAN, specifically for data, ... tempdb) usually provides some performance gain. ... > Logs, and TempDB. ... > performance gain from multiple I/O channels, ...
    (microsoft.public.sqlserver.server)
  • Re: Dynamic Firewall/IDS System
    ... some thoughts about event handling software. ... > is almost impossible to handle manually just reading logs. ... > This is how we fill our database. ... this is logging, way like acid does with snort logs. ...
    (FreeBSD-Security)
  • Re: Exchange - Huge (x10) mismatch between size of priv1.edb and actual size of mailboxes in mai
    ... about 40 transaction logs. ... I think client actions that read from the database are ... mailboxes and the system is not that old. ...
    (microsoft.public.windows.server.sbs)
  • Re: Error in Exchange
    ... Will you provide me some linkes to get more info about the role these logs ... play in .edb file or with database and how information is saved into .edb ... > backup on Sept 8. ... > transaction logs are saved in that backup. ...
    (microsoft.public.exchange.admin)
  • Re: Minimizing backup induced downtime
    ... With that magical RMAN thing, how will you be able to recover ... take a backup of an Oracle database without the need for having ... I am running the databases in archive log mode and I know what it is. ... that the logs aren't backed up. ...
    (comp.databases.oracle.server)