Location of MDF / LDF / Backup / Log

For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your advice
Stephen wrote:
> For a finance application, we use a Windows 2003 Server
> with C and D are local HD (They are mirrored as RAID 1).
> On the other hand, it is connected to a SAN with drive
> letter J.
> We find that the consultant has installed the SQLS Server
> 2000 DB MDF and LDF in the D drive. The finance
> application is installed on the J drive.
> We will backup both C,D and J drive daily. They haven't
> setup the database maintenance plan yet.
> For both performance purpose, we would like to get your
> advie where is the best place to put the backup files and
> log files ? Is it D or J drive ? Should we suggest them
> to put the transaction log file from D to J as well ?
> Thank you for your advice
If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored set (so that makes four local hard drives)? You don't have to
back up the drives themselves for SQL Server backup and recovery. What
you need to back up are the full, differential, and t-log backups you
make throughout the day. Generally, you want to put the backup files on
different spindles than the drive on which the data originates to
maximize bandwidth. So if the data is on the SAN, you can back up to one
of the local arrays.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> Stephen wrote:
> If you've paid for a SAN, it seems silly not use it for the data and
> t-logs. Are you saying that C is a mirrored set and drive D is a mirrored
> set (so that makes four local hard drives)? You don't have to back up the
> drives themselves for SQL Server backup and recovery. What you need to
> back up are the full, differential, and t-log backups you make throughout
> the day. Generally, you want to put the backup files on different spindles
> than the drive on which the data originates to maximize bandwidth. So if
> the data is on the SAN, you can back up to one of the local arrays.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Dear David,
> Thank you for your advice. Yes, both C and D are mirrored set.
> Someone suggest to put the transaction log file in the RAID 1 for recovery
> and data in RAID 5. Is it OK from your point of view ?
> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> Thanks
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
mirrored[vbcol=seagreen]
the[vbcol=seagreen]
throughout[vbcol=seagreen]
spindles
>
|||Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Stephen
> Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
> buy RAID 10&1) put the log on
> RAID1 device and data on RAID-5
> (this option is appropriate if the writes activites are moderate)
>
> Note: The appropriate decision depends on your perfomance charctiristics
> and fault tolerance needs.
>
>
> "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> mirrored
> the
> throughout
> spindles
>
|||Stepfen
I'd recommend you buy "Inside SQL Server 2000" written by Kalen Delaney (See
topic about RAID solutions)
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:e2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would like
to
> know the underlying reason to put the log on RAID 1 and data on RAID 5.
> On the other hand, should we put the Database and Transaction log backup
on[vbcol=seagreen]
> RAID 5 array ?
> Thank you again.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
charctiristics[vbcol=seagreen]
to
>
|||Stephen wrote:
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would
> like to know the underlying reason to put the log on RAID 1 and data
> on RAID 5.
> On the other hand, should we put the Database and Transaction log
> backup on RAID 5 array ?
>
I didn't think RAID 5 was one of your installed options given you only
had 4 local drives...
In any case, for small and medium sized databases with a low percentage
of writes, RAID 5 is an inexpensive solution. RAID 5 writes data very
slowly, so it's only appropriate for data files. Transaction logs and
tempdb should reside on a mirrored set (which you have). It's best for
performance to keep data and t-logs on different drives, which are also
separate from the OS. In your environment, you have a SAN (J), and 2
local mirrored sets (C and D).
I would recommend you use C for the OS and applications like SQL Server.
Use D for the t-logs and tempdb, and use the SAN for the data.
Everything needs to backed up in some fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||This website has a good intro to RAID levels and the basic pros & cons
(even if they are basically trying to sell you their products):
http://www.acnc.com/raid.html
(for those that can't afford Kalen's book _Inside SQL Server_, which in
an excellent book that I thoroughly recommend)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Stephen wrote:

>Dear Uri,
>Thank you for your advice. However, I am new to RAID array. I would like to
>know the underlying reason to put the log on RAID 1 and data on RAID 5.
>On the other hand, should we put the Database and Transaction log backup on
>RAID 5 array ?
>Thank you again.
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>
>
>