Re: Installing SQL Server 2005 on Windows 2003 Server (RAID 5) - need advice

I'm not a RAID expert however here's my opinion:

As far as I know, the idea behind best practice of putting the Log files on a different RAID Array is to dedicate the disk spindles to only one Transaction Log file. (Besides not losing all your data at once, this is another reason.)

So everytime a transaction is processed and move the spindles to write it to the disk, the spindles would keep their position after the operation completed and would be ready for the new one. Because there would not be anything to change their positions for some another operation and keep them busy with other I\O operations. When a new transaction occurs and needs be to written to the disk, spindles would be ready to be used at the right position.

Your C: drive is RAID1 and its write performance is very good theorically however it's not dedicated to your Transaction Log. So, it's gonna be used for other operations as your OS and your SQL Server binaries (and maybe some other stuff) would be working on it. Spindles most probably would be busy and change their positions according to the operations performed on that RAID Array by that stuff.

We are talking about miliseconds here.

--
Ekrem Önsoy



"Learner" <pradev@xxxxxxxxx> wrote in message news:1195056462.754266.138290@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Nov 13, 3:51 pm, Learner <pra...@xxxxxxxxx> wrote:
HI,

Thanks for the response. I appreciate your lengthly answer. But I
was just going through an article

http://www.sql-server-performance.com/faq/raid_1_raid_5_p1.aspx

and it tells me that (if I understand it right) in my scenario with
available disks and set up I can put the data files (.mdf) on D: drive
on RAID 5 which has a greater read performance
and put the .ldf files (Log files, by their nature, are mostly written
to, which means that often RAID 1 is your best choice for
performance.) on C: drive as RAID 1 has grater write performance over
RAID 5. And the other point is

'In addition, it is a good idea to locate log files on an array that
does not have data files because you don't want to risk losing both
your data files and your log files should an entire array fail. '

But as you mentioned

> I know, you do not have enough disks to set another RAID1 for your log
> files. However this is the preferred method locating log files. In your
> situation, I would locate my data and log files on that RAID5 > desperately .

putting the data and log files on the RAID 5 (D: drive) kinda differs
to it says in the above article

I am not sure if I am missing some thing here will you please respond
one more time.

I guess our budget is limited and we can't efford to for any hard
disks.

Thanks,

-L

On Nov 13, 3:27 pm, Ekrem Önsoy <ek...@xxxxxxxxxxxx> wrote:



> Answer in lines...

> --
> Ekrem Önsoy

> "Learner" <pra...@xxxxxxxxx> wrote in message

>news:1194980139.488765.137430@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

> > Hi,

> > We are currently running our databases on Sql server 2000. As an
> > upgrade to move our databases we bought a brand new machine and unlike
> > the regular machines with just one hard disk this machine has the
> > following hard disk configuration.

> > It has 6 physical hard disks of 72 GB each (is it called RAID
> > machine?)
> > 2 of the hard disks are mirrored (I guess it is called RAID1 please
> > correct me if I am wrong) and these two made C: drive.

> I've never heard something "RAID machine". RAID = Redundant Array of
> Independent disks. There are different versions of RAIDs. You'll be > familiar
> with them in time. Mirrored Disks called RAID1. Which gives you > redundancy
> and performance. (Performance depends on the hardware and environment, > you
> should test)

> > The remaining 4 hard disks made D: drive and are setup at RAID 5.

> RAID5 needs at least 3 disks. It gives you redundancy and performance
> according to some tests. There are lots of tests about RAIDs and they > all
> confuse but help to understand which one is the best fit for certain
> situations. So you better test your own stuff.

> > We just installed Windows 2003 Server OS on C: drive (on RAID 1 or
> > mirrored hard disks) and I guess this good. Please advise if not.

> This is a standard. Windows and SQL Server binaries are installed on C:
> (which is RAID1 usually)

> > Now the next step is to install SQL Server 2005 and here is our idea
> > on how where to install it on:

> > 1).Install the Sql Server 2005 Enterprise version on D: drive (RAID 5)
> > 2).Put the .mdf fiile on D: drvie (RAID 5) itself
> > 3).Put the .ldf file on C: drive (RAID 1)

> As I mentioned above, you better locate your SQL binaries on C:. You > should
> think of your databases' data and log files. If you are looking for
> performance and redundancy then you can plan locating your log file on > RAID1
> and locate your data files on RAID5\RAID10

> I know, you do not have enough disks to set another RAID1 for your log
> files. However this is the preferred method locating log files. In your
> situation, I would locate my data and log files on that RAID5 > desperately .

> If you have a chance, buy another disk and set up a RAID5 from that 3 > disks
> and set up a RAID1 from the other 2 disks. Put your data files on the > RAID5
> and put your log file on the RAID5 and locate your Windows and SQL > binaries
> on the other RAID1. (Totally 7 disks)

> However, I'm not sure if all of this performance fear is rightful? Test > your
> needs and go for them.

> And the last thing, I'd probably setup a RAID10 instead of RAID5 if I > had to
> use these disks and could not be allowed to buy another one for another
> RAID1. RAID10 is more appropriate for mixed situations (according to > tests
> I've seen...) Test all the possibilities for best performance.

> > I need to mention here that I am new to this RAID stuff so would like
> > to take advice on where to install the Sql server 2005 as a software,
> > and where to pu the .mdf files (fiile groups), and where to put
> > the .ldf files.

> > Hope my explanation above gives a clear idea of what we have and what
> > we are trying to do

> > Please take a moment to advise me in the right direction.

> > Thanks,

> > -L- Hide quoted text -

> - Show quoted text -- Hide quoted text -

- Show quoted text -

Thank you and for your deatailed posts. Basically my boss a strong net
work guy and set up the machine and asked me to install the SQL Server
2005 Enterprise on it. His idea was to put the .ldf files on C: drive
(RAID1) and .mdf files on D: drive RAID 5. So just wanted to know the
generally idea on how to install the SQL Server as a software when we
have hard is configure in RAID levels. But thanks again.

Thanks,

-L

.

Relevant Pages

  • Re: Disk Partitioning on exch 2003
    ... > Putting the log files on the same drive partition is not a best ... > really should consider getting some other disks to put the log files. ... same RAID controller. ...
    (microsoft.public.exchange.design)
  • Re: Installing SQL Server 2005 on Windows 2003 Server (RAID 5) - need advice
    ... available disks and set up I can put the data files on D: ... on RAID 5 which has a greater read performance ... and put the .ldf files (Log files, by their nature, are mostly written ...
    (microsoft.public.sqlserver.setup)
  • Re: Another Filegroup question
    ... I would use 2 disks in a RAID 1 for the log files and ... add those other two disks to the other Raid 1+0 and place all the files ... to the Raid 1+0 and separating the logs. ...
    (microsoft.public.sqlserver.server)
  • Re: Building New Server - What RAID should be used and how to divi
    ... different drives. ... Never use Raid 0 if you are concerned with data loss. ... Raid 10 is a better choice for data files. ... RAID 1 is a starting point for Log files. ...
    (microsoft.public.sqlserver.setup)
  • Re: Building New Server - What RAID should be used and how to divide t
    ... Creating multiple Logical drives such as C:, D: & E: on one physical array does nothing for performance but gives false impressions of files residing on different drives. ... Raid 5 should only be considered if there is mostly Read activity or very low write activity. ... Raid 10 is a better choice for data files. ... RAID 1 is a starting point for Log files. ...
    (microsoft.public.sqlserver.setup)
Loading