Hardware configuration help for MS SQL server

This is what one of the tables looks like - they are all similar and there
are about 60 different tables:

CREATE TABLE SalesData1(
smalldatetime TimeStamp,
varchar(8) CustomerID,
numeric S1,
numeric S2,
numeric S3,
numeric S4,
numeric X
)

I'm not sure on the data types, S1 thru S4 are actually float values with
two decimal places. X is an unsigned long value.

Anyway, the bigger question is what do we need at *minimum* for hard drives
and RAM, if we are using SQL 7.0* under these circumstances:

1. Inserting up to 600,000 records every 10 minutes
2. Updating up to 70,000 records every 10 minutes

We can use BCP for inserting the data. There are many different tables with
an average of 10,000 records being inserted into each one every 10 minutes.

I nearly ordered a couple servers with dual 400GB SATA hard drives using
RAID1, but someone told me that with only 2 drives - it would cause SQL
server to work harder. So do we need a whole bunch of smaller 40GB drives??
Should they be SATA drives?

Could one server with dual Xeon CPUs do this, or do we need more than one
server?
And would 2GB of RAM be sufficient?

All I know is that we will be inserting 600k records, updating 70k records,
and reading 500,000 every 10 minutes.

Do I need to provide more info on anything specifically?

We should have an SQL consultant helping us in the near future - I'm just
trying to get a head start with the hardware. Our budget is small, in fact I
may have to build the servers myself out of spare parts and things on sale
at Fry's :)

Thanks for your help. I will provide more info if needed.

*Can't afford to upgrade to SQL 2000I highly recommend that you check out Kevin Kline's article Bare Metal
Tuning in SQL Server Magazine. He gives a step-by-step breakdown of
how changes in ahrdware affect performance; granted, it's written from
a SQL 2000 perspective, but it might give you some ideas.

http://www.windowsitpro.com/Article...46492.html?Ad=1

Note that the article requires a subscription, but it's a great
investment.

Stu|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1122484729.540188.299270@.f14g2000cwb.googlegr oups.com...
> I highly recommend that you check out Kevin Kline's article Bare Metal
> Tuning in SQL Server Magazine. He gives a step-by-step breakdown of
> how changes in ahrdware affect performance; granted, it's written from
> a SQL 2000 perspective, but it might give you some ideas.
> http://www.windowsitpro.com/Article...46492.html?Ad=1
> Note that the article requires a subscription, but it's a great
> investment.
> Stu

Based on that article, how does this look (if I understand correctly):

Dual 64bit AMD processors, 4GB fast RAM.

Drive C: is 20GB SATA drive using FAT32 without RAID. The OS goes here.

Drive E: is 40GB total storage RAID 1 (two 80GB SATA drives) with NTFS. SQL
program files and logs go on here.

Drive F: to Q: are 40GB drives using NTFS and RAID 5. Data storage goes
here.

Thanks|||Actually one thing he mentioned in his presentation was that AMD
Opterons actually performed worse than 32-bit XEON processors, although
he didn't mention it in his article. XEON processors act like dual
standard CPU, so two XEON's = 4 virtual processors.

If you have RAID 5, then you wouldn't have logical drives F through Q;
it would just show up as F. Be sure that you're using hardware RAID,
and not software-based RAID.

Other than that, it looks good. Get the biggest processors you can; fo
that much data, you'll need it.

Hardware Configuration - Need Advice

We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed. and
SQL Server 2000 Standard Edition. Our database is about 4GB and at most we
have 10 concurrent users hitting our server, with a mix of read and write
operations. Both the OS and our DB are on the same machine. I am aware that
having both IIS and SQL Server on the same box is not the ideal
configuration, but it has suited our needs given the load.
We are looking to private license our software to one other company, which
would entail the licensee having their own seperate db, but running in the
same instance of SQL Server. At a minimum, I'm thinking we would move SQL
Server onto its own dedicated machine, keeping the current RAID 5 config.
Perhaps this would not be sufficient?
What I'm looking for is some indication as to whether or not the proposed
platform could handle an increased user load, say 20 times what is now (200
concurrent users). This is primarily an OLTP db used for ACH processing,
with several reporting features. I'm fully aware that the application design
itself, along with query tuning, indexing, etc is equally important as the
hardware, but need some guidance on the hardware itself.
What I'm looking for are some general guidelines to follow given this
scenario. Thanks in advance.
Eric there is no way to answer that without knowing a lot more of what your
current system is doing and how the hardware is holding up now. Do you do 1
transaction per second or 1 thousand? What are the disk Queues, processor
queues etc. like now?
Andrew J. Kelly SQL MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3C2BC87F-5505-4DD6-9E79-A053B86527BA@.microsoft.com...
> We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
> RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed.
> and
> SQL Server 2000 Standard Edition. Our database is about 4GB and at most
> we
> have 10 concurrent users hitting our server, with a mix of read and write
> operations. Both the OS and our DB are on the same machine. I am aware
> that
> having both IIS and SQL Server on the same box is not the ideal
> configuration, but it has suited our needs given the load.
> We are looking to private license our software to one other company, which
> would entail the licensee having their own seperate db, but running in the
> same instance of SQL Server. At a minimum, I'm thinking we would move SQL
> Server onto its own dedicated machine, keeping the current RAID 5 config.
> Perhaps this would not be sufficient?
> What I'm looking for is some indication as to whether or not the proposed
> platform could handle an increased user load, say 20 times what is now
> (200
> concurrent users). This is primarily an OLTP db used for ACH processing,
> with several reporting features. I'm fully aware that the application
> design
> itself, along with query tuning, indexing, etc is equally important as the
> hardware, but need some guidance on the hardware itself.
> What I'm looking for are some general guidelines to follow given this
> scenario. Thanks in advance.

Hardware Configuration - Need Advice

We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed. and
SQL Server 2000 Standard Edition. Our database is about 4GB and at most we
have 10 concurrent users hitting our server, with a mix of read and write
operations. Both the OS and our DB are on the same machine. I am aware that
having both IIS and SQL Server on the same box is not the ideal
configuration, but it has suited our needs given the load.
We are looking to private license our software to one other company, which
would entail the licensee having their own seperate db, but running in the
same instance of SQL Server. At a minimum, I'm thinking we would move SQL
Server onto its own dedicated machine, keeping the current RAID 5 config.
Perhaps this would not be sufficient?
What I'm looking for is some indication as to whether or not the proposed
platform could handle an increased user load, say 20 times what is now (200
concurrent users). This is primarily an OLTP db used for ACH processing,
with several reporting features. I'm fully aware that the application design
itself, along with query tuning, indexing, etc is equally important as the
hardware, but need some guidance on the hardware itself.
What I'm looking for are some general guidelines to follow given this
scenario. Thanks in advance.Eric there is no way to answer that without knowing a lot more of what your
current system is doing and how the hardware is holding up now. Do you do 1
transaction per second or 1 thousand? What are the disk Queues, processor
queues etc. like now?
--
Andrew J. Kelly SQL MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3C2BC87F-5505-4DD6-9E79-A053B86527BA@.microsoft.com...
> We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
> RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed.
> and
> SQL Server 2000 Standard Edition. Our database is about 4GB and at most
> we
> have 10 concurrent users hitting our server, with a mix of read and write
> operations. Both the OS and our DB are on the same machine. I am aware
> that
> having both IIS and SQL Server on the same box is not the ideal
> configuration, but it has suited our needs given the load.
> We are looking to private license our software to one other company, which
> would entail the licensee having their own seperate db, but running in the
> same instance of SQL Server. At a minimum, I'm thinking we would move SQL
> Server onto its own dedicated machine, keeping the current RAID 5 config.
> Perhaps this would not be sufficient?
> What I'm looking for is some indication as to whether or not the proposed
> platform could handle an increased user load, say 20 times what is now
> (200
> concurrent users). This is primarily an OLTP db used for ACH processing,
> with several reporting features. I'm fully aware that the application
> design
> itself, along with query tuning, indexing, etc is equally important as the
> hardware, but need some guidance on the hardware itself.
> What I'm looking for are some general guidelines to follow given this
> scenario. Thanks in advance.

Hardware Configuration - Need Advice

We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed. and
SQL Server 2000 Standard Edition. Our database is about 4GB and at most we
have 10 concurrent users hitting our server, with a mix of read and write
operations. Both the OS and our DB are on the same machine. I am aware tha
t
having both IIS and SQL Server on the same box is not the ideal
configuration, but it has suited our needs given the load.
We are looking to private license our software to one other company, which
would entail the licensee having their own seperate db, but running in the
same instance of SQL Server. At a minimum, I'm thinking we would move SQL
Server onto its own dedicated machine, keeping the current RAID 5 config.
Perhaps this would not be sufficient?
What I'm looking for is some indication as to whether or not the proposed
platform could handle an increased user load, say 20 times what is now (200
concurrent users). This is primarily an OLTP db used for ACH processing,
with several reporting features. I'm fully aware that the application desig
n
itself, along with query tuning, indexing, etc is equally important as the
hardware, but need some guidance on the hardware itself.
What I'm looking for are some general guidelines to follow given this
scenario. Thanks in advance.Eric there is no way to answer that without knowing a lot more of what your
current system is doing and how the hardware is holding up now. Do you do 1
transaction per second or 1 thousand? What are the disk Queues, processor
queues etc. like now?
Andrew J. Kelly SQL MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3C2BC87F-5505-4DD6-9E79-A053B86527BA@.microsoft.com...
> We have a web app currently being hosted on a RAID 5 with four disks, 2 GB
> RAM, Xenon 3.2 processor. Also running Windows 2003 Server Standard Ed.
> and
> SQL Server 2000 Standard Edition. Our database is about 4GB and at most
> we
> have 10 concurrent users hitting our server, with a mix of read and write
> operations. Both the OS and our DB are on the same machine. I am aware
> that
> having both IIS and SQL Server on the same box is not the ideal
> configuration, but it has suited our needs given the load.
> We are looking to private license our software to one other company, which
> would entail the licensee having their own seperate db, but running in the
> same instance of SQL Server. At a minimum, I'm thinking we would move SQL
> Server onto its own dedicated machine, keeping the current RAID 5 config.
> Perhaps this would not be sufficient?
> What I'm looking for is some indication as to whether or not the proposed
> platform could handle an increased user load, say 20 times what is now
> (200
> concurrent users). This is primarily an OLTP db used for ACH processing,
> with several reporting features. I'm fully aware that the application
> design
> itself, along with query tuning, indexing, etc is equally important as the
> hardware, but need some guidance on the hardware itself.
> What I'm looking for are some general guidelines to follow given this
> scenario. Thanks in advance.

Hardware configuration

Hi Friends,
This is my first post here. Hope for a good response from the gurus ;-)
here it is...
We have a medium size database (upto 50 tables) out of which one table is a massive. It gets populated online at very high speed (> 20,000 records a day) so it grows quite large in month. Now the problem is when it grows to that extent in a month at the end of 2 months any query fired on it takes toooo long to execute (to the magnitude of 10 minutes ). We have following hardware configuration

1. 1.2 GHz Pentium 4
2. 40 GB HDD
3. 384 mb RAM.

What should be the server configuration to boost the performance of the database??

Regards,
Vishal.It may not be your hardware configuration. How often do you Reorg the database?|||Currently we only backup the table( The Big one) and trim it to achieve the speed. Is this what U R talking about?? Is there any thing else we need to do in order to reorg the db??|||after 2 months you should have something like 1200000 recrods yes?

what sort of data is in these records?

what indexes do you have on the table?

what are the searches being performed?

I imagine that if you applied some suitable indexes and possibly did some restructuring you should have not problems with this amount of data.

Yes, eventually it will slow as more data comes on board but no where near the 10 mins you are talking about....|||What rokslide mentioned will help greatly. But Reorg is a quick fix to boost performance for a db with large amount of reads and writes because it rearrange the data and index pages. To do a reorg just simply use Database Maintenance Plan Wizard and check the Reorganize Data and Index Page. Execute the plan once a month, it will greatly help the performance of your database.

Hardware configuration

Hi i'm doing mirroring on 2 servers

each has 6 disks.

what is the recommended configuration for each server?

Option 1: 2 disks (mirror) for OS and 4 disks (RAID5) for SQL

Option 2: 2 disks (mirror) for OS, 2 disks (mirror) for SQL & Data, 2 disks (mirror) for SQL Logs.

or maybe other suggestion ? maybe some logical partitions ... (TempDB ...)

thanks,

I'd say definitely option 2

every article I read suggests log/temp db on separate physical DRIVE (not partition) than data

|||

It is recommended that you separate log from data and other activities.

It all depends upon the throughput of your disks and the response requirements of your application.

Hardware Configuration

I'm currently rebuilding an IBM server that I'd like to use as a SQL 7.0
database server. Two items I'd like some feedback on: when creating a new
volume (it's Windows 2000 Server) to host the databases, how important is
cluster (i.e. allocation unit) size? The volume I've created is 25 Gb.
Second, I'm using a RAID controller with hard drives which support
write-caching, with the two options being write-through and write-back. I
understand write-caching isn't the most desirable thing with SQL? Any and
all input on this is much appreciated.
steve.Steve,
In general, if the hard disk of the computer has a write-caching disk
controller, disable it. Unless a write-caching disk controller is designed
specifically for a database (basically battery-backed), it can seriously
threaten SQL Server data integrity.
hth
Quentin
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:uKwLOyisEHA.2788@.TK2MSFTNGP09.phx.gbl...
> I'm currently rebuilding an IBM server that I'd like to use as a SQL 7.0
> database server. Two items I'd like some feedback on: when creating a new
> volume (it's Windows 2000 Server) to host the databases, how important is
> cluster (i.e. allocation unit) size? The volume I've created is 25 Gb.
> Second, I'm using a RAID controller with hard drives which support
> write-caching, with the two options being write-through and write-back. I
> understand write-caching isn't the most desirable thing with SQL? Any and
> all input on this is much appreciated.
> steve.
>

Hardware Configuration

I'm currently rebuilding an IBM server that I'd like to use as a SQL 7.0
database server. Two items I'd like some feedback on: when creating a new
volume (it's Windows 2000 Server) to host the databases, how important is
cluster (i.e. allocation unit) size? The volume I've created is 25 Gb.
Second, I'm using a RAID controller with hard drives which support
write-caching, with the two options being write-through and write-back. I
understand write-caching isn't the most desirable thing with SQL? Any and
all input on this is much appreciated.
steve.
Steve,
In general, if the hard disk of the computer has a write-caching disk
controller, disable it. Unless a write-caching disk controller is designed
specifically for a database (basically battery-backed), it can seriously
threaten SQL Server data integrity.
hth
Quentin
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:uKwLOyisEHA.2788@.TK2MSFTNGP09.phx.gbl...
> I'm currently rebuilding an IBM server that I'd like to use as a SQL 7.0
> database server. Two items I'd like some feedback on: when creating a new
> volume (it's Windows 2000 Server) to host the databases, how important is
> cluster (i.e. allocation unit) size? The volume I've created is 25 Gb.
> Second, I'm using a RAID controller with hard drives which support
> write-caching, with the two options being write-through and write-back. I
> understand write-caching isn't the most desirable thing with SQL? Any and
> all input on this is much appreciated.
> steve.
>

Hardware Configuration

I'm currently rebuilding an IBM server that I'd like to use as a SQL 7.0
database server. Two items I'd like some feedback on: when creating a new
volume (it's Windows 2000 Server) to host the databases, how important is
cluster (i.e. allocation unit) size? The volume I've created is 25 Gb.
Second, I'm using a RAID controller with hard drives which support
write-caching, with the two options being write-through and write-back. I
understand write-caching isn't the most desirable thing with SQL? Any and
all input on this is much appreciated.
steve.Steve,
In general, if the hard disk of the computer has a write-caching disk
controller, disable it. Unless a write-caching disk controller is designed
specifically for a database (basically battery-backed), it can seriously
threaten SQL Server data integrity.
hth
Quentin
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:uKwLOyisEHA.2788@.TK2MSFTNGP09.phx.gbl...
> I'm currently rebuilding an IBM server that I'd like to use as a SQL 7.0
> database server. Two items I'd like some feedback on: when creating a new
> volume (it's Windows 2000 Server) to host the databases, how important is
> cluster (i.e. allocation unit) size? The volume I've created is 25 Gb.
> Second, I'm using a RAID controller with hard drives which support
> write-caching, with the two options being write-through and write-back. I
> understand write-caching isn't the most desirable thing with SQL? Any and
> all input on this is much appreciated.
> steve.
>

Hard drives

Can anybody suggest a hdd configuration that offers the best performance and
reliability for a SQL server?
I just finished deploying SQL 2000 Cluster.
I spent a solid week researching RAID configurations along with Microsoft
recommended cofigurations.
The settings that I came up with for the Drives are as follows.
Database is written across a RAID 1+0 across six drives. Each pair is being
written to by 2 different controllers.
Transaction Logs are written across a RAID 0+1 across 4 drives. Again each
pair is written to by 2 different controllers.
The RAID 10 provides the highest Availability for the database, since
multiple drive failures across mirrored sets will not effect the Virtual Disk
as much as a RAID 0+1.
The RAID 0+1 will provide a slightly faster write to disk then the RAID10
due to striping of the spanned disks.
Depending on the hardware of course this should be a pretty optimal setting
for your cluster...again I am running in a clustered SQL environment
therefore I am looking for MAX reliability first, then Good performance.
Cheers
John
"Peter Downes" wrote:

> Can anybody suggest a hdd configuration that offers the best performance and
> reliability for a SQL server?
>
>
|||Hi Downes -
Please find the following link :-
http://www.sql-server-performance.co...are_tuning.asp
Hope this will help you to get the solution you looking for
Regards,
"Peter Downes" wrote:

> Can anybody suggest a hdd configuration that offers the best performance and
> reliability for a SQL server?
>
>
|||John E Davis wrote:
> I just finished deploying SQL 2000 Cluster.
> I spent a solid week researching RAID configurations along with
> Microsoft recommended cofigurations.
> The settings that I came up with for the Drives are as follows.
> Database is written across a RAID 1+0 across six drives. Each pair is
> being written to by 2 different controllers.
> Transaction Logs are written across a RAID 0+1 across 4 drives. Again
> each pair is written to by 2 different controllers.
> The RAID 10 provides the highest Availability for the database, since
> multiple drive failures across mirrored sets will not effect the
> Virtual Disk as much as a RAID 0+1.
> The RAID 0+1 will provide a slightly faster write to disk then the
> RAID10 due to striping of the spanned disks.
> Depending on the hardware of course this should be a pretty optimal
> setting for your cluster...again I am running in a clustered SQL
> environment therefore I am looking for MAX reliability first, then
> Good performance.
> Cheers
> John
John,
Can you explain the difference between RAID 1+0 and RAID 10. I thought
they were one and the same. That is, a mirrored, striped set.
David
|||Ouch - expensive!
It's all a trade-off, the better the performance the more it'll cost.
If you're using a SAN (as opposed to direct attached storage), chances
are the RAID group will be used by more than just the SQL box (that's
what usually happens in most companies anyway), even by multiple SQL
boxes, so getting the best disk config is usually less of a concern than
getting LUNs on RAID groups that aren't used by other busy servers (busy
in terms of IO).
So unless you can afford RAID groups dedicated just to your SQL box then
I wouldn't worry too much about it. But as John say, yes, RAID 0+1 is
sweet but RAID 5 usually will suffice as the read performance is pretty
good. The write performance on RAID 5 is not so good but usually
there's much more read activity than write activity on SQL DBs - you've
got to see what's more common/important (read vs write) and it's a trade
off between performance, cost & available space (RAID 5 allows much more
usable disk space than RAID 0+1 for the money you spend). Typically,
RAID 5 for data & RAID 0+1 for transaction logs works well.
If you're after the best performance, check out the Microsoft TPC-C
benchmark reports on their high-end configurations. The ones I've read
all use RAID 0 (striping) to get the best read & write performance for
the number of disks (and therefore raw $ price) in their arrays but, of
course, RAID 0 offer exactly zero fault tolerance.
Cheers,
Mike.
John E Davis wrote:[vbcol=seagreen]
> I just finished deploying SQL 2000 Cluster.
> I spent a solid week researching RAID configurations along with Microsoft
> recommended cofigurations.
> The settings that I came up with for the Drives are as follows.
> Database is written across a RAID 1+0 across six drives. Each pair is being
> written to by 2 different controllers.
> Transaction Logs are written across a RAID 0+1 across 4 drives. Again each
> pair is written to by 2 different controllers.
> The RAID 10 provides the highest Availability for the database, since
> multiple drive failures across mirrored sets will not effect the Virtual Disk
> as much as a RAID 0+1.
> The RAID 0+1 will provide a slightly faster write to disk then the RAID10
> due to striping of the spanned disks.
> Depending on the hardware of course this should be a pretty optimal setting
> for your cluster...again I am running in a clustered SQL environment
> therefore I am looking for MAX reliability first, then Good performance.
> Cheers
> John
> "Peter Downes" wrote:
>
|||David Gugick wrote:
> John E Davis wrote:
>
[snip]
[vbcol=seagreen]
[snip]

>
> John,
> Can you explain the difference between RAID 1+0 and RAID 10. I thought
> they were one and the same. That is, a mirrored, striped set.
>
Webopedia (http://www.webopedia.com/TERM/R/RAID.html) provides a
reasonable summary of RAID levels (although I notice they don't have
RAID 50). There are probably better resources, but that was a 10 second
google search. Essentially, it's the difference between a stripe of
mirrors (RAID 10) or a mirror of stripes (RAID 0+1). Pretty much the
same in my book but there are probably subtle differences. As I
mentioned in my other post in this thread, I think getting to that level
of detail for this subject is really splitting straws since other things
like server contention is going to play a bigger role in the IO
performance. That's my 2c worth.
Cheers,
Mike.
|||Another (more graphical) description of RAID levels is:
RAID 10: http://www.acnc.com/04_01_10.html
RAID 0+1: http://www.acnc.com/04_01_0_1.html
It's pretty nice actually.
Cheers,
Mike.
Mike Hodgson wrote:
> David Gugick wrote:
>
> [snip]
>
> [snip]
>
> Webopedia (http://www.webopedia.com/TERM/R/RAID.html) provides a
> reasonable summary of RAID levels (although I notice they don't have
> RAID 50). There are probably better resources, but that was a 10 second
> google search. Essentially, it's the difference between a stripe of
> mirrors (RAID 10) or a mirror of stripes (RAID 0+1). Pretty much the
> same in my book but there are probably subtle differences. As I
> mentioned in my other post in this thread, I think getting to that level
> of detail for this subject is really splitting straws since other things
> like server contention is going to play a bigger role in the IO
> performance. That's my 2c worth.
> Cheers,
> Mike.
|||Peter
Were I work we have very strict standards on how the disk are used. We set
up the following disks for the following uses
'C' drive this is used by the windows operating system
'D' drive this is where we install SQL Server and have the system databases
'E' drive is where we have the user databases
'F' drive is where we have the transaction logs
'G' drive is where we keep our backups
'H' drive is available for application use
The way those logical drives are mapped to phyisical drives varies depending
on how many drives are available, what size databases you are going to have
etc, but we always try to have at least three seperate arrays. that way you
can keep the user databases, transaction logs and backups seperate from each
other.
We insist that all disks be 1+ 0 RAID unless write activity is less than 10%
of disk I/O in which case RAID 5 can be used.
Hope this helps
John
"surajits" wrote:
[vbcol=seagreen]
> Hi Downes -
> Please find the following link :-
> http://www.sql-server-performance.co...are_tuning.asp
> Hope this will help you to get the solution you looking for
> Regards,
> "Peter Downes" wrote:

Hard drives

Can anybody suggest a hdd configuration that offers the best performance and
reliability for a SQL server?I just finished deploying SQL 2000 Cluster.
I spent a solid week researching RAID configurations along with Microsoft
recommended cofigurations.
The settings that I came up with for the Drives are as follows.
Database is written across a RAID 1+0 across six drives. Each pair is being
written to by 2 different controllers.
Transaction Logs are written across a RAID 0+1 across 4 drives. Again each
pair is written to by 2 different controllers.
The RAID 10 provides the highest Availability for the database, since
multiple drive failures across mirrored sets will not effect the Virtual Disk
as much as a RAID 0+1.
The RAID 0+1 will provide a slightly faster write to disk then the RAID10
due to striping of the spanned disks.
Depending on the hardware of course this should be a pretty optimal setting
for your cluster...again I am running in a clustered SQL environment
therefore I am looking for MAX reliability first, then Good performance.
Cheers
John
"Peter Downes" wrote:
> Can anybody suggest a hdd configuration that offers the best performance and
> reliability for a SQL server?
>
>|||Hi Downes -
Please find the following link :-
http://www.sql-server-performance.com/hardware_tuning.asp
Hope this will help you to get the solution you looking for
Regards,
"Peter Downes" wrote:
> Can anybody suggest a hdd configuration that offers the best performance and
> reliability for a SQL server?
>
>|||John E Davis wrote:
> I just finished deploying SQL 2000 Cluster.
> I spent a solid week researching RAID configurations along with
> Microsoft recommended cofigurations.
> The settings that I came up with for the Drives are as follows.
> Database is written across a RAID 1+0 across six drives. Each pair is
> being written to by 2 different controllers.
> Transaction Logs are written across a RAID 0+1 across 4 drives. Again
> each pair is written to by 2 different controllers.
> The RAID 10 provides the highest Availability for the database, since
> multiple drive failures across mirrored sets will not effect the
> Virtual Disk as much as a RAID 0+1.
> The RAID 0+1 will provide a slightly faster write to disk then the
> RAID10 due to striping of the spanned disks.
> Depending on the hardware of course this should be a pretty optimal
> setting for your cluster...again I am running in a clustered SQL
> environment therefore I am looking for MAX reliability first, then
> Good performance.
> Cheers
> John
John,
Can you explain the difference between RAID 1+0 and RAID 10. I thought
they were one and the same. That is, a mirrored, striped set.
David|||Ouch - expensive!
It's all a trade-off, the better the performance the more it'll cost.
If you're using a SAN (as opposed to direct attached storage), chances
are the RAID group will be used by more than just the SQL box (that's
what usually happens in most companies anyway), even by multiple SQL
boxes, so getting the best disk config is usually less of a concern than
getting LUNs on RAID groups that aren't used by other busy servers (busy
in terms of IO).
So unless you can afford RAID groups dedicated just to your SQL box then
I wouldn't worry too much about it. But as John say, yes, RAID 0+1 is
sweet but RAID 5 usually will suffice as the read performance is pretty
good. The write performance on RAID 5 is not so good but usually
there's much more read activity than write activity on SQL DBs - you've
got to see what's more common/important (read vs write) and it's a trade
off between performance, cost & available space (RAID 5 allows much more
usable disk space than RAID 0+1 for the money you spend). Typically,
RAID 5 for data & RAID 0+1 for transaction logs works well.
If you're after the best performance, check out the Microsoft TPC-C
benchmark reports on their high-end configurations. The ones I've read
all use RAID 0 (striping) to get the best read & write performance for
the number of disks (and therefore raw $ price) in their arrays but, of
course, RAID 0 offer exactly zero fault tolerance.
Cheers,
Mike.
John E Davis wrote:
> I just finished deploying SQL 2000 Cluster.
> I spent a solid week researching RAID configurations along with Microsoft
> recommended cofigurations.
> The settings that I came up with for the Drives are as follows.
> Database is written across a RAID 1+0 across six drives. Each pair is being
> written to by 2 different controllers.
> Transaction Logs are written across a RAID 0+1 across 4 drives. Again each
> pair is written to by 2 different controllers.
> The RAID 10 provides the highest Availability for the database, since
> multiple drive failures across mirrored sets will not effect the Virtual Disk
> as much as a RAID 0+1.
> The RAID 0+1 will provide a slightly faster write to disk then the RAID10
> due to striping of the spanned disks.
> Depending on the hardware of course this should be a pretty optimal setting
> for your cluster...again I am running in a clustered SQL environment
> therefore I am looking for MAX reliability first, then Good performance.
> Cheers
> John
> "Peter Downes" wrote:
>
>>Can anybody suggest a hdd configuration that offers the best performance and
>>reliability for a SQL server?
>>|||David Gugick wrote:
> John E Davis wrote:
>
[snip]
>> The RAID 10 provides the highest Availability for the database, since
>> multiple drive failures across mirrored sets will not effect the
>> Virtual Disk as much as a RAID 0+1.
>> The RAID 0+1 will provide a slightly faster write to disk then the
>> RAID10 due to striping of the spanned disks.
[snip]
>
> John,
> Can you explain the difference between RAID 1+0 and RAID 10. I thought
> they were one and the same. That is, a mirrored, striped set.
>
Webopedia (http://www.webopedia.com/TERM/R/RAID.html) provides a
reasonable summary of RAID levels (although I notice they don't have
RAID 50). There are probably better resources, but that was a 10 second
google search. Essentially, it's the difference between a stripe of
mirrors (RAID 10) or a mirror of stripes (RAID 0+1). Pretty much the
same in my book but there are probably subtle differences. As I
mentioned in my other post in this thread, I think getting to that level
of detail for this subject is really splitting straws since other things
like server contention is going to play a bigger role in the IO
performance. That's my 2c worth.
Cheers,
Mike.|||Another (more graphical) description of RAID levels is:
RAID 10: http://www.acnc.com/04_01_10.html
RAID 0+1: http://www.acnc.com/04_01_0_1.html
It's pretty nice actually. :)
Cheers,
Mike.
Mike Hodgson wrote:
> David Gugick wrote:
>> John E Davis wrote:
> [snip]
>> The RAID 10 provides the highest Availability for the database, since
>> multiple drive failures across mirrored sets will not effect the
>> Virtual Disk as much as a RAID 0+1.
>> The RAID 0+1 will provide a slightly faster write to disk then the
>> RAID10 due to striping of the spanned disks.
>
> [snip]
>>
>> John,
>> Can you explain the difference between RAID 1+0 and RAID 10. I thought
>> they were one and the same. That is, a mirrored, striped set.
> Webopedia (http://www.webopedia.com/TERM/R/RAID.html) provides a
> reasonable summary of RAID levels (although I notice they don't have
> RAID 50). There are probably better resources, but that was a 10 second
> google search. Essentially, it's the difference between a stripe of
> mirrors (RAID 10) or a mirror of stripes (RAID 0+1). Pretty much the
> same in my book but there are probably subtle differences. As I
> mentioned in my other post in this thread, I think getting to that level
> of detail for this subject is really splitting straws since other things
> like server contention is going to play a bigger role in the IO
> performance. That's my 2c worth.
> Cheers,
> Mike.|||Peter
Were I work we have very strict standards on how the disk are used. We set
up the following disks for the following uses
'C' drive this is used by the windows operating system
'D' drive this is where we install SQL Server and have the system databases
'E' drive is where we have the user databases
'F' drive is where we have the transaction logs
'G' drive is where we keep our backups
'H' drive is available for application use
The way those logical drives are mapped to phyisical drives varies depending
on how many drives are available, what size databases you are going to have
etc, but we always try to have at least three seperate arrays. that way you
can keep the user databases, transaction logs and backups seperate from each
other.
We insist that all disks be 1+ 0 RAID unless write activity is less than 10%
of disk I/O in which case RAID 5 can be used.
Hope this helps
John
"surajits" wrote:
> Hi Downes -
> Please find the following link :-
> http://www.sql-server-performance.com/hardware_tuning.asp
> Hope this will help you to get the solution you looking for
> Regards,
> "Peter Downes" wrote:
> > Can anybody suggest a hdd configuration that offers the best performance and
> > reliability for a SQL server?
> >
> >
> >

Hard disk configuration question

I just acquire a new server with 8 hard drives and 2 RAID controllers. The
objective is to load SQL SERVER 2005 on this server to run CRM and one other
database centric application. In addition rerporting services and analysis
services will also be installed. I have configured two of the RAID hard
disks as RAID1 and created the System partition on it as Drive C (30GB) and
the remaining as Drive D.
I configured another 2 as RAID1 (67GB) which I intend to use for the
Databases log files.
I now have 4 hard disks left. I can either configure this drives as 1 RAID5
Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an issue
here, I am trying to achieve the best possible performance. Which RAID
configuration is better for the data files? Any suggestions or
recommendations?
Thank you,
ODMake the 4 hard drives RAID 10 (mirror then striping).
Don't even touch RAID 5 with a barge poll now-a-days.
Put tempdb on the D: on its own RAID 1 array away from the second and third
arrays, tempdb in SQL 2005 is more heavily used but even in 2000 I'm
recommending it has its own uncontended array and even use a RAM disk if a)
the RAM disk fully supports SQL Server IO requirements and b) you have spare
memory in the box and c) if your application pattern after testing benefits
that approach (which stuff with heaving sorting, hashing, temp table usage
will) - see my blog entry: Put TEMPDB on a RAM DRIVE; Short lived # tables -
do they get written to disk?
(http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx)
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"OD" <oludan@.hotmail.com> wrote in message
news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>I just acquire a new server with 8 hard drives and 2 RAID controllers. The
>objective is to load SQL SERVER 2005 on this server to run CRM and one
>other database centric application. In addition rerporting services and
>analysis services will also be installed. I have configured two of the RAID
>hard disks as RAID1 and created the System partition on it as Drive C
>(30GB) and the remaining as Drive D.
> I configured another 2 as RAID1 (67GB) which I intend to use for the
> Databases log files.
> I now have 4 hard disks left. I can either configure this drives as 1
> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an
> issue here, I am trying to achieve the best possible performance. Which
> RAID configuration is better for the data files? Any suggestions or
> recommendations?
> Thank you,
> OD
>|||With 8 drives, my suggestion for best performance is:
Two Drives -RAID 1 (log files)
Three Drives RAID 0
Three Drives RAID 0
Mirror the above two RAID 0 sets, making a RAID 10.
Use the RAID 10 for OS and datafiles.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"OD" <oludan@.hotmail.com> wrote in message
news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>I just acquire a new server with 8 hard drives and 2 RAID controllers. The
>objective is to load SQL SERVER 2005 on this server to run CRM and one
>other database centric application. In addition rerporting services and
>analysis services will also be installed. I have configured two of the RAID
>hard disks as RAID1 and created the System partition on it as Drive C
>(30GB) and the remaining as Drive D.
> I configured another 2 as RAID1 (67GB) which I intend to use for the
> Databases log files.
> I now have 4 hard disks left. I can either configure this drives as 1
> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an
> issue here, I am trying to achieve the best possible performance. Which
> RAID configuration is better for the data files? Any suggestions or
> recommendations?
> Thank you,
> OD
>|||Thats RAID 01 which isn't as fault tolerant as RAID 10.
01 is striping and then mirroring the stripe; if a disk in the stripe fails
then your whole array is exposed and a single drive failure on the second
mirrored striped can cause the set to be unavailable.
10 is mirror the drives and then stripe across, if a disk in the stripe
fails then so long as a disk in the mirror stripe isn't the mirror for the
one that failed then you are ok.
Basically, you can lose more drives RAID 10 then with RAID 01.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:u3zHWl3yGHA.4596@.TK2MSFTNGP02.phx.gbl...
> With 8 drives, my suggestion for best performance is:
> Two Drives -RAID 1 (log files)
> Three Drives RAID 0
> Three Drives RAID 0
> Mirror the above two RAID 0 sets, making a RAID 10.
> Use the RAID 10 for OS and datafiles.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers. The
>>objective is to load SQL SERVER 2005 on this server to run CRM and one
>>other database centric application. In addition rerporting services and
>>analysis services will also be installed. I have configured two of the
>>RAID hard disks as RAID1 and created the System partition on it as Drive C
>>(30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an
>> issue here, I am trying to achieve the best possible performance. Which
>> RAID configuration is better for the data files? Any suggestions or
>> recommendations?
>> Thank you,
>> OD
>|||Thanks for the recomendation. Drive C and D are on the same RAID 1 set but
they are 2 different OS partition on the RAID Vitual Disk. Is it ok to
locate the Data files for Tempdb on the RAID 10 set with other user create
data files?
here is what I will have:
-RAID 1: partitioned to DRIVE C (for System) and D (May be backup and other
media files).
-RAID 1: Partioned to E (for logs) and F: for backup
-RAID 10: Partioned to G and H for different data files
what do you think?
OD
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
> Make the 4 hard drives RAID 10 (mirror then striping).
> Don't even touch RAID 5 with a barge poll now-a-days.
> Put tempdb on the D: on its own RAID 1 array away from the second and
> third arrays, tempdb in SQL 2005 is more heavily used but even in 2000 I'm
> recommending it has its own uncontended array and even use a RAM disk if
> a) the RAM disk fully supports SQL Server IO requirements and b) you have
> spare memory in the box and c) if your application pattern after testing
> benefits that approach (which stuff with heaving sorting, hashing, temp
> table usage will) - see my blog entry: Put TEMPDB on a RAM DRIVE; Short
> lived # tables - do they get written to disk?
> (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx)
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers. The
>>objective is to load SQL SERVER 2005 on this server to run CRM and one
>>other database centric application. In addition rerporting services and
>>analysis services will also be installed. I have configured two of the
>>RAID hard disks as RAID1 and created the System partition on it as Drive C
>>(30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an
>> issue here, I am trying to achieve the best possible performance. Which
>> RAID configuration is better for the data files? Any suggestions or
>> recommendations?
>> Thank you,
>> OD
>|||Hi OD,
> Is it ok to locate the Data files for Tempdb on the RAID 10 set with
> other user create data files?
Why? Your system should rarely ever page so disk performance to the RAID 1
array containing pagefile.sys should not be a problem - better to utilise
it, if your box is paging (thereby causing IO to that RAID 1 array) then you
have other memory problems to solve first anyway, by using D: you aren't
touching the logical system drive so you will have enough space to put the
pagefile.sys (fix this rather than allow OS to grow it).
Think of tempdb in the same way you think of a database transaction log.
Why have RAID 10 partitioned as G and H? Just make it one logical drive -
drive G:
Again, why partition RAID 1 as E: and F: - just make it one logical drive -
drive E:
When you format the partitions in NT, format them NTFS but give it an
allocation size that matches your RAID stripe size, its usually 64K.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"OD" <oludan@.hotmail.com> wrote in message
news:eWT2923yGHA.4232@.TK2MSFTNGP05.phx.gbl...
> Thanks for the recomendation. Drive C and D are on the same RAID 1 set but
> they are 2 different OS partition on the RAID Vitual Disk. Is it ok to
> locate the Data files for Tempdb on the RAID 10 set with other user create
> data files?
> here is what I will have:
> -RAID 1: partitioned to DRIVE C (for System) and D (May be backup and
> other media files).
> -RAID 1: Partioned to E (for logs) and F: for backup
> -RAID 10: Partioned to G and H for different data files
> what do you think?
> OD
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Make the 4 hard drives RAID 10 (mirror then striping).
>> Don't even touch RAID 5 with a barge poll now-a-days.
>> Put tempdb on the D: on its own RAID 1 array away from the second and
>> third arrays, tempdb in SQL 2005 is more heavily used but even in 2000
>> I'm recommending it has its own uncontended array and even use a RAM disk
>> if a) the RAM disk fully supports SQL Server IO requirements and b) you
>> have spare memory in the box and c) if your application pattern after
>> testing benefits that approach (which stuff with heaving sorting,
>> hashing, temp table usage will) - see my blog entry: Put TEMPDB on a RAM
>> DRIVE; Short lived # tables - do they get written to disk?
>> (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx)
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
>> SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting services
>>and analysis services will also be installed. I have configured two of
>>the RAID hard disks as RAID1 and created the System partition on it as
>>Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of
>> an issue here, I am trying to achieve the best possible performance.
>> Which RAID configuration is better for the data files? Any suggestions
>> or recommendations?
>> Thank you,
>> OD
>>
>|||Thanks Tony, for the additional information.
However, as I was apt to do in my many Econ classes, I love to challenge the
assumptions. Anytime there is an 'IF', as in:
"if a disk in the stripe
fails then so long as a disk in the mirror stripe isn't the mirror for
the
one that failed then you are ok"
It seems mirrored stripes AND stripped mirrors are both vulnerable to
multiple drive failures. However, as you indicated, the stripped mirrors
seem to provide a little bit more of a element of chance to which mirrored
drive fails, whereas with mirrored stripes, failure in the mirror is certain
'death'.
One could posit that all of society survives on the element of chance.
Another day, another useful bit of information gleaned.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uoxYHr3yGHA.3656@.TK2MSFTNGP04.phx.gbl...
> Thats RAID 01 which isn't as fault tolerant as RAID 10.
> 01 is striping and then mirroring the stripe; if a disk in the stripe
> fails then your whole array is exposed and a single drive failure on the
> second mirrored striped can cause the set to be unavailable.
> 10 is mirror the drives and then stripe across, if a disk in the stripe
> fails then so long as a disk in the mirror stripe isn't the mirror for the
> one that failed then you are ok.
> Basically, you can lose more drives RAID 10 then with RAID 01.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:u3zHWl3yGHA.4596@.TK2MSFTNGP02.phx.gbl...
>> With 8 drives, my suggestion for best performance is:
>> Two Drives -RAID 1 (log files)
>> Three Drives RAID 0
>> Three Drives RAID 0
>> Mirror the above two RAID 0 sets, making a RAID 10.
>> Use the RAID 10 for OS and datafiles.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting services
>>and analysis services will also be installed. I have configured two of
>>the RAID hard disks as RAID1 and created the System partition on it as
>>Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of
>> an issue here, I am trying to achieve the best possible performance.
>> Which RAID configuration is better for the data files? Any suggestions
>> or recommendations?
>> Thank you,
>> OD
>>
>|||Thank you all for the assistance. In that case, I will stick with your
earlier advice as so:
First RAID 1
--C: ->System and Binary
--D: -> TempDB
Second Raid 1
--E: -> LOGS + Backup
RAID 10
--F: Data files
Now that the DB spaces have been figured out will it be ok (performance
wise) for one of Drives D, E or F to also hold other data such as Word and
Excel files that are generated by one of the appliations?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uzfHy73yGHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hi OD,
>> Is it ok to locate the Data files for Tempdb on the RAID 10 set with
>> other user create data files?
> Why? Your system should rarely ever page so disk performance to the RAID 1
> array containing pagefile.sys should not be a problem - better to utilise
> it, if your box is paging (thereby causing IO to that RAID 1 array) then
> you have other memory problems to solve first anyway, by using D: you
> aren't touching the logical system drive so you will have enough space to
> put the pagefile.sys (fix this rather than allow OS to grow it).
> Think of tempdb in the same way you think of a database transaction log.
> Why have RAID 10 partitioned as G and H? Just make it one logical drive -
> drive G:
> Again, why partition RAID 1 as E: and F: - just make it one logical
> drive - drive E:
> When you format the partitions in NT, format them NTFS but give it an
> allocation size that matches your RAID stripe size, its usually 64K.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:eWT2923yGHA.4232@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the recomendation. Drive C and D are on the same RAID 1 set
>> but they are 2 different OS partition on the RAID Vitual Disk. Is it ok
>> to locate the Data files for Tempdb on the RAID 10 set with other user
>> create data files?
>> here is what I will have:
>> -RAID 1: partitioned to DRIVE C (for System) and D (May be backup and
>> other media files).
>> -RAID 1: Partioned to E (for logs) and F: for backup
>> -RAID 10: Partioned to G and H for different data files
>> what do you think?
>> OD
>>
>> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
>> news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Make the 4 hard drives RAID 10 (mirror then striping).
>> Don't even touch RAID 5 with a barge poll now-a-days.
>> Put tempdb on the D: on its own RAID 1 array away from the second and
>> third arrays, tempdb in SQL 2005 is more heavily used but even in 2000
>> I'm recommending it has its own uncontended array and even use a RAM
>> disk if a) the RAM disk fully supports SQL Server IO requirements and b)
>> you have spare memory in the box and c) if your application pattern
>> after testing benefits that approach (which stuff with heaving sorting,
>> hashing, temp table usage will) - see my blog entry: Put TEMPDB on a RAM
>> DRIVE; Short lived # tables - do they get written to disk?
>> (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx)
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
>> SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting services
>>and analysis services will also be installed. I have configured two of
>>the RAID hard disks as RAID1 and created the System partition on it as
>>Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of
>> an issue here, I am trying to achieve the best possible performance.
>> Which RAID configuration is better for the data files? Any suggestions
>> or recommendations?
>> Thank you,
>> OD
>>
>>
>|||You raise a good point.
Something else I've seen is that all the onboard SATA RAID controllers I've
seen so far implement RAID 01, so if you want RAID 10 you must RAID 1 the
drives first and then use Windows to stripe across the drives.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:e9%23BGC4yGHA.4968@.TK2MSFTNGP05.phx.gbl...
> Thanks Tony, for the additional information.
> However, as I was apt to do in my many Econ classes, I love to challenge
> the assumptions. Anytime there is an 'IF', as in:
> "if a disk in the stripe
> fails then so long as a disk in the mirror stripe isn't the mirror for
> the
> one that failed then you are ok"
> It seems mirrored stripes AND stripped mirrors are both vulnerable to
> multiple drive failures. However, as you indicated, the stripped mirrors
> seem to provide a little bit more of a element of chance to which mirrored
> drive fails, whereas with mirrored stripes, failure in the mirror is
> certain 'death'.
> One could posit that all of society survives on the element of chance.
> Another day, another useful bit of information gleaned.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uoxYHr3yGHA.3656@.TK2MSFTNGP04.phx.gbl...
>> Thats RAID 01 which isn't as fault tolerant as RAID 10.
>> 01 is striping and then mirroring the stripe; if a disk in the stripe
>> fails then your whole array is exposed and a single drive failure on the
>> second mirrored striped can cause the set to be unavailable.
>> 10 is mirror the drives and then stripe across, if a disk in the stripe
>> fails then so long as a disk in the mirror stripe isn't the mirror for
>> the one that failed then you are ok.
>> Basically, you can lose more drives RAID 10 then with RAID 01.
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
>> SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:u3zHWl3yGHA.4596@.TK2MSFTNGP02.phx.gbl...
>> With 8 drives, my suggestion for best performance is:
>> Two Drives -RAID 1 (log files)
>> Three Drives RAID 0
>> Three Drives RAID 0
>> Mirror the above two RAID 0 sets, making a RAID 10.
>> Use the RAID 10 for OS and datafiles.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting services
>>and analysis services will also be installed. I have configured two of
>>the RAID hard disks as RAID1 and created the System partition on it as
>>Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of
>> an issue here, I am trying to achieve the best possible performance.
>> Which RAID configuration is better for the data files? Any suggestions
>> or recommendations?
>> Thank you,
>> OD
>>
>>
>|||I am running a DELL PERC 5 RAID controller and it explains this same thing.
However, it only allows configuration for RAIDs 0, 1, 5, and 10. So I have
configured the last 4 drives as RAID 10 since it's the only option supported
on the controller between RAID 01 and RAID 10.
thank you for your assistance.
OD
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:e9%23BGC4yGHA.4968@.TK2MSFTNGP05.phx.gbl...
> Thanks Tony, for the additional information.
> However, as I was apt to do in my many Econ classes, I love to challenge
> the assumptions. Anytime there is an 'IF', as in:
> "if a disk in the stripe
> fails then so long as a disk in the mirror stripe isn't the mirror for
> the
> one that failed then you are ok"
> It seems mirrored stripes AND stripped mirrors are both vulnerable to
> multiple drive failures. However, as you indicated, the stripped mirrors
> seem to provide a little bit more of a element of chance to which mirrored
> drive fails, whereas with mirrored stripes, failure in the mirror is
> certain 'death'.
> One could posit that all of society survives on the element of chance.
> Another day, another useful bit of information gleaned.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uoxYHr3yGHA.3656@.TK2MSFTNGP04.phx.gbl...
>> Thats RAID 01 which isn't as fault tolerant as RAID 10.
>> 01 is striping and then mirroring the stripe; if a disk in the stripe
>> fails then your whole array is exposed and a single drive failure on the
>> second mirrored striped can cause the set to be unavailable.
>> 10 is mirror the drives and then stripe across, if a disk in the stripe
>> fails then so long as a disk in the mirror stripe isn't the mirror for
>> the one that failed then you are ok.
>> Basically, you can lose more drives RAID 10 then with RAID 01.
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
>> SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:u3zHWl3yGHA.4596@.TK2MSFTNGP02.phx.gbl...
>> With 8 drives, my suggestion for best performance is:
>> Two Drives -RAID 1 (log files)
>> Three Drives RAID 0
>> Three Drives RAID 0
>> Mirror the above two RAID 0 sets, making a RAID 10.
>> Use the RAID 10 for OS and datafiles.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting services
>>and analysis services will also be installed. I have configured two of
>>the RAID hard disks as RAID1 and created the System partition on it as
>>Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of
>> an issue here, I am trying to achieve the best possible performance.
>> Which RAID configuration is better for the data files? Any suggestions
>> or recommendations?
>> Thank you,
>> OD
>>
>>
>|||> Now that the DB spaces have been figured out will it be ok (performance
> wise) for one of Drives D, E or F to also hold other data such as Word and
> Excel files that are generated by one of the appliations?
Depends on the volume, if you have an application that is continuously
knocking out files then I'd advise something else, but if its just a file
server then you need to ask yourself how often are files read or written
to/from and that will answer your question, I'd probably put the files on
the RAID 10 array because you really need the IO bandwidth for the
checkpoint process or when dirty pages are being written out.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"OD" <oludan@.hotmail.com> wrote in message
news:u%23XUZD4yGHA.3552@.TK2MSFTNGP02.phx.gbl...
> Thank you all for the assistance. In that case, I will stick with your
> earlier advice as so:
> First RAID 1
> --C: ->System and Binary
> --D: -> TempDB
> Second Raid 1
> --E: -> LOGS + Backup
> RAID 10
> --F: Data files
> Now that the DB spaces have been figured out will it be ok (performance
> wise) for one of Drives D, E or F to also hold other data such as Word and
> Excel files that are generated by one of the appliations?
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uzfHy73yGHA.5048@.TK2MSFTNGP05.phx.gbl...
>> Hi OD,
>> Is it ok to locate the Data files for Tempdb on the RAID 10 set with
>> other user create data files?
>> Why? Your system should rarely ever page so disk performance to the RAID
>> 1 array containing pagefile.sys should not be a problem - better to
>> utilise it, if your box is paging (thereby causing IO to that RAID 1
>> array) then you have other memory problems to solve first anyway, by
>> using D: you aren't touching the logical system drive so you will have
>> enough space to put the pagefile.sys (fix this rather than allow OS to
>> grow it).
>> Think of tempdb in the same way you think of a database transaction log.
>> Why have RAID 10 partitioned as G and H? Just make it one logical drive -
>> drive G:
>> Again, why partition RAID 1 as E: and F: - just make it one logical
>> drive - drive E:
>> When you format the partitions in NT, format them NTFS but give it an
>> allocation size that matches your RAID stripe size, its usually 64K.
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
>> SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:eWT2923yGHA.4232@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the recomendation. Drive C and D are on the same RAID 1 set
>> but they are 2 different OS partition on the RAID Vitual Disk. Is it ok
>> to locate the Data files for Tempdb on the RAID 10 set with other user
>> create data files?
>> here is what I will have:
>> -RAID 1: partitioned to DRIVE C (for System) and D (May be backup and
>> other media files).
>> -RAID 1: Partioned to E (for logs) and F: for backup
>> -RAID 10: Partioned to G and H for different data files
>> what do you think?
>> OD
>>
>> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
>> news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Make the 4 hard drives RAID 10 (mirror then striping).
>> Don't even touch RAID 5 with a barge poll now-a-days.
>> Put tempdb on the D: on its own RAID 1 array away from the second and
>> third arrays, tempdb in SQL 2005 is more heavily used but even in 2000
>> I'm recommending it has its own uncontended array and even use a RAM
>> disk if a) the RAM disk fully supports SQL Server IO requirements and
>> b) you have spare memory in the box and c) if your application pattern
>> after testing benefits that approach (which stuff with heaving sorting,
>> hashing, temp table usage will) - see my blog entry: Put TEMPDB on a
>> RAM DRIVE; Short lived # tables - do they get written to disk?
>> (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx)
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from
>> a SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting services
>>and analysis services will also be installed. I have configured two of
>>the RAID hard disks as RAID1 and created the System partition on it as
>>Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of
>> an issue here, I am trying to achieve the best possible performance.
>> Which RAID configuration is better for the data files? Any suggestions
>> or recommendations?
>> Thank you,
>> OD
>>
>>
>>
>|||Thank you Annie and Tony. You have helped a lot and saved me sometime. I
will go ahead and drop the Excel and Word files on the RAID 10 temporarily
and if there is a performance issue I will add more hard drives and move
them.
Thanks again!.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:O4VKpL4yGHA.1256@.TK2MSFTNGP04.phx.gbl...
>> Now that the DB spaces have been figured out will it be ok (performance
>> wise) for one of Drives D, E or F to also hold other data such as Word
>> and Excel files that are generated by one of the appliations?
> Depends on the volume, if you have an application that is continuously
> knocking out files then I'd advise something else, but if its just a file
> server then you need to ask yourself how often are files read or written
> to/from and that will answer your question, I'd probably put the files on
> the RAID 10 array because you really need the IO bandwidth for the
> checkpoint process or when dirty pages are being written out.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:u%23XUZD4yGHA.3552@.TK2MSFTNGP02.phx.gbl...
>> Thank you all for the assistance. In that case, I will stick with your
>> earlier advice as so:
>> First RAID 1
>> --C: ->System and Binary
>> --D: -> TempDB
>> Second Raid 1
>> --E: -> LOGS + Backup
>> RAID 10
>> --F: Data files
>> Now that the DB spaces have been figured out will it be ok (performance
>> wise) for one of Drives D, E or F to also hold other data such as Word
>> and Excel files that are generated by one of the appliations?
>>
>> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
>> news:uzfHy73yGHA.5048@.TK2MSFTNGP05.phx.gbl...
>> Hi OD,
>> Is it ok to locate the Data files for Tempdb on the RAID 10 set with
>> other user create data files?
>> Why? Your system should rarely ever page so disk performance to the RAID
>> 1 array containing pagefile.sys should not be a problem - better to
>> utilise it, if your box is paging (thereby causing IO to that RAID 1
>> array) then you have other memory problems to solve first anyway, by
>> using D: you aren't touching the logical system drive so you will have
>> enough space to put the pagefile.sys (fix this rather than allow OS to
>> grow it).
>> Think of tempdb in the same way you think of a database transaction log.
>> Why have RAID 10 partitioned as G and H? Just make it one logical
>> drive - drive G:
>> Again, why partition RAID 1 as E: and F: - just make it one logical
>> drive - drive E:
>> When you format the partitions in NT, format them NTFS but give it an
>> allocation size that matches your RAID stripe size, its usually 64K.
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
>> SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:eWT2923yGHA.4232@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the recomendation. Drive C and D are on the same RAID 1 set
>> but they are 2 different OS partition on the RAID Vitual Disk. Is it ok
>> to locate the Data files for Tempdb on the RAID 10 set with other user
>> create data files?
>> here is what I will have:
>> -RAID 1: partitioned to DRIVE C (for System) and D (May be backup and
>> other media files).
>> -RAID 1: Partioned to E (for logs) and F: for backup
>> -RAID 10: Partioned to G and H for different data files
>> what do you think?
>> OD
>>
>> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
>> news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Make the 4 hard drives RAID 10 (mirror then striping).
>> Don't even touch RAID 5 with a barge poll now-a-days.
>> Put tempdb on the D: on its own RAID 1 array away from the second and
>> third arrays, tempdb in SQL 2005 is more heavily used but even in 2000
>> I'm recommending it has its own uncontended array and even use a RAM
>> disk if a) the RAM disk fully supports SQL Server IO requirements and
>> b) you have spare memory in the box and c) if your application pattern
>> after testing benefits that approach (which stuff with heaving
>> sorting, hashing, temp table usage will) - see my blog entry: Put
>> TEMPDB on a RAM DRIVE; Short lived # tables - do they get written to
>> disk?
>> (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx)
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from
>> a SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting
>>services and analysis services will also be installed. I have
>>configured two of the RAID hard disks as RAID1 and created the System
>>partition on it as Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much
>> of an issue here, I am trying to achieve the best possible
>> performance. Which RAID configuration is better for the data files?
>> Any suggestions or recommendations?
>> Thank you,
>> OD
>>
>>
>>
>>
>

Hard disk configuration question

I just acquire a new server with 8 hard drives and 2 RAID controllers. The
objective is to load SQL SERVER 2005 on this server to run CRM and one other
database centric application. In addition rerporting services and analysis
services will also be installed. I have configured two of the RAID hard
disks as RAID1 and created the System partition on it as Drive C (30GB) and
the remaining as Drive D.
I configured another 2 as RAID1 (67GB) which I intend to use for the
Databases log files.
I now have 4 hard disks left. I can either configure this drives as 1 RAID5
Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an issue
here, I am trying to achieve the best possible performance. Which RAID
configuration is better for the data files? Any suggestions or
recommendations?
Thank you,
ODMake the 4 hard drives RAID 10 (mirror then striping).
Don't even touch RAID 5 with a barge poll now-a-days.
Put tempdb on the D: on its own RAID 1 array away from the second and third
arrays, tempdb in SQL 2005 is more heavily used but even in 2000 I'm
recommending it has its own uncontended array and even use a RAM disk if a)
the RAM disk fully supports SQL Server IO requirements and b) you have spare
memory in the box and c) if your application pattern after testing benefits
that approach (which stuff with heaving sorting, hashing, temp table usage
will) - see my blog entry: Put TEMPDB on a RAM DRIVE; Short lived # tables -
do they get written to disk?
(http://sqlblogcasts.com/blogs/tonyr.../08/24/958.aspx)
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"OD" <oludan@.hotmail.com> wrote in message
news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>I just acquire a new server with 8 hard drives and 2 RAID controllers. The
>objective is to load SQL SERVER 2005 on this server to run CRM and one
>other database centric application. In addition rerporting services and
>analysis services will also be installed. I have configured two of the RAID
>hard disks as RAID1 and created the System partition on it as Drive C
>(30GB) and the remaining as Drive D.
> I configured another 2 as RAID1 (67GB) which I intend to use for the
> Databases log files.
> I now have 4 hard disks left. I can either configure this drives as 1
> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an
> issue here, I am trying to achieve the best possible performance. Which
> RAID configuration is better for the data files? Any suggestions or
> recommendations?
> Thank you,
> OD
>|||With 8 drives, my suggestion for best performance is:
Two Drives -RAID 1 (log files)
Three Drives RAID 0
Three Drives RAID 0
Mirror the above two RAID 0 sets, making a RAID 10.
Use the RAID 10 for OS and datafiles.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"OD" <oludan@.hotmail.com> wrote in message
news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>I just acquire a new server with 8 hard drives and 2 RAID controllers. The
>objective is to load SQL SERVER 2005 on this server to run CRM and one
>other database centric application. In addition rerporting services and
>analysis services will also be installed. I have configured two of the RAID
>hard disks as RAID1 and created the System partition on it as Drive C
>(30GB) and the remaining as Drive D.
> I configured another 2 as RAID1 (67GB) which I intend to use for the
> Databases log files.
> I now have 4 hard disks left. I can either configure this drives as 1
> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an
> issue here, I am trying to achieve the best possible performance. Which
> RAID configuration is better for the data files? Any suggestions or
> recommendations?
> Thank you,
> OD
>|||Thats RAID 01 which isn't as fault tolerant as RAID 10.
01 is striping and then mirroring the stripe; if a disk in the stripe fails
then your whole array is exposed and a single drive failure on the second
mirrored striped can cause the set to be unavailable.
10 is mirror the drives and then stripe across, if a disk in the stripe
fails then so long as a disk in the mirror stripe isn't the mirror for the
one that failed then you are ok.
Basically, you can lose more drives RAID 10 then with RAID 01.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:u3zHWl3yGHA.4596@.TK2MSFTNGP02.phx.gbl...
> With 8 drives, my suggestion for best performance is:
> Two Drives -RAID 1 (log files)
> Three Drives RAID 0
> Three Drives RAID 0
> Mirror the above two RAID 0 sets, making a RAID 10.
> Use the RAID 10 for OS and datafiles.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>|||Thanks for the recomendation. Drive C and D are on the same RAID 1 set but
they are 2 different OS partition on the RAID Vitual Disk. Is it ok to
locate the Data files for Tempdb on the RAID 10 set with other user create
data files?
here is what I will have:
-RAID 1: partitioned to DRIVE C (for System) and D (May be backup and other
media files).
-RAID 1: Partioned to E (for logs) and F: for backup
-RAID 10: Partioned to G and H for different data files
what do you think?
OD
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
> Make the 4 hard drives RAID 10 (mirror then striping).
> Don't even touch RAID 5 with a barge poll now-a-days.
> Put tempdb on the D: on its own RAID 1 array away from the second and
> third arrays, tempdb in SQL 2005 is more heavily used but even in 2000 I'm
> recommending it has its own uncontended array and even use a RAM disk if
> a) the RAM disk fully supports SQL Server IO requirements and b) you have
> spare memory in the box and c) if your application pattern after testing
> benefits that approach (which stuff with heaving sorting, hashing, temp
> table usage will) - see my blog entry: Put TEMPDB on a RAM DRIVE; Short
> lived # tables - do they get written to disk?
> (http://sqlblogcasts.com/blogs/tonyr.../08/24/958.aspx)
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>|||Hi OD,

> Is it ok to locate the Data files for Tempdb on the RAID 10 set with
> other user create data files?
Why? Your system should rarely ever page so disk performance to the RAID 1
array containing pagefile.sys should not be a problem - better to utilise
it, if your box is paging (thereby causing IO to that RAID 1 array) then you
have other memory problems to solve first anyway, by using D: you aren't
touching the logical system drive so you will have enough space to put the
pagefile.sys (fix this rather than allow OS to grow it).
Think of tempdb in the same way you think of a database transaction log.
Why have RAID 10 partitioned as G and H? Just make it one logical drive -
drive G:
Again, why partition RAID 1 as E: and F: - just make it one logical drive -
drive E:
When you format the partitions in NT, format them NTFS but give it an
allocation size that matches your RAID stripe size, its usually 64K.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"OD" <oludan@.hotmail.com> wrote in message
news:eWT2923yGHA.4232@.TK2MSFTNGP05.phx.gbl...
> Thanks for the recomendation. Drive C and D are on the same RAID 1 set but
> they are 2 different OS partition on the RAID Vitual Disk. Is it ok to
> locate the Data files for Tempdb on the RAID 10 set with other user create
> data files?
> here is what I will have:
> -RAID 1: partitioned to DRIVE C (for System) and D (May be backup and
> other media files).
> -RAID 1: Partioned to E (for logs) and F: for backup
> -RAID 10: Partioned to G and H for different data files
> what do you think?
> OD
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
>|||Thanks Tony, for the additional information.
However, as I was apt to do in my many Econ classes, I love to challenge the
assumptions. Anytime there is an 'IF', as in:
"if a disk in the stripe
fails then so long as a disk in the mirror stripe isn't the mirror for
the
one that failed then you are ok"
It seems mirrored stripes AND stripped mirrors are both vulnerable to
multiple drive failures. However, as you indicated, the stripped mirrors
seem to provide a little bit more of a element of chance to which mirrored
drive fails, whereas with mirrored stripes, failure in the mirror is certain
'death'.
One could posit that all of society survives on the element of chance.
Another day, another useful bit of information gleaned.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uoxYHr3yGHA.3656@.TK2MSFTNGP04.phx.gbl...
> Thats RAID 01 which isn't as fault tolerant as RAID 10.
> 01 is striping and then mirroring the stripe; if a disk in the stripe
> fails then your whole array is exposed and a single drive failure on the
> second mirrored striped can cause the set to be unavailable.
> 10 is mirror the drives and then stripe across, if a disk in the stripe
> fails then so long as a disk in the mirror stripe isn't the mirror for the
> one that failed then you are ok.
> Basically, you can lose more drives RAID 10 then with RAID 01.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:u3zHWl3yGHA.4596@.TK2MSFTNGP02.phx.gbl...
>|||Thank you all for the assistance. In that case, I will stick with your
earlier advice as so:
First RAID 1
--C: ->System and Binary
--D: -> TempDB
Second Raid 1
--E: -> LOGS + Backup
RAID 10
--F: Data files
Now that the DB spaces have been figured out will it be ok (performance
wise) for one of Drives D, E or F to also hold other data such as Word and
Excel files that are generated by one of the appliations?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uzfHy73yGHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hi OD,
>
> Why? Your system should rarely ever page so disk performance to the RAID 1
> array containing pagefile.sys should not be a problem - better to utilise
> it, if your box is paging (thereby causing IO to that RAID 1 array) then
> you have other memory problems to solve first anyway, by using D: you
> aren't touching the logical system drive so you will have enough space to
> put the pagefile.sys (fix this rather than allow OS to grow it).
> Think of tempdb in the same way you think of a database transaction log.
> Why have RAID 10 partitioned as G and H? Just make it one logical drive -
> drive G:
> Again, why partition RAID 1 as E: and F: - just make it one logical
> drive - drive E:
> When you format the partitions in NT, format them NTFS but give it an
> allocation size that matches your RAID stripe size, its usually 64K.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:eWT2923yGHA.4232@.TK2MSFTNGP05.phx.gbl...
>|||You raise a good point.
Something else I've seen is that all the onboard SATA RAID controllers I've
seen so far implement RAID 01, so if you want RAID 10 you must RAID 1 the
drives first and then use Windows to stripe across the drives.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:e9%23BGC4yGHA.4968@.TK2MSFTNGP05.phx.gbl...
> Thanks Tony, for the additional information.
> However, as I was apt to do in my many Econ classes, I love to challenge
> the assumptions. Anytime there is an 'IF', as in:
> "if a disk in the stripe
> fails then so long as a disk in the mirror stripe isn't the mirror for
> the
> one that failed then you are ok"
> It seems mirrored stripes AND stripped mirrors are both vulnerable to
> multiple drive failures. However, as you indicated, the stripped mirrors
> seem to provide a little bit more of a element of chance to which mirrored
> drive fails, whereas with mirrored stripes, failure in the mirror is
> certain 'death'.
> One could posit that all of society survives on the element of chance.
> Another day, another useful bit of information gleaned.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uoxYHr3yGHA.3656@.TK2MSFTNGP04.phx.gbl...
>|||I am running a DELL PERC 5 RAID controller and it explains this same thing.
However, it only allows configuration for RAIDs 0, 1, 5, and 10. So I have
configured the last 4 drives as RAID 10 since it's the only option supported
on the controller between RAID 01 and RAID 10.
thank you for your assistance.
OD
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:e9%23BGC4yGHA.4968@.TK2MSFTNGP05.phx.gbl...
> Thanks Tony, for the additional information.
> However, as I was apt to do in my many Econ classes, I love to challenge
> the assumptions. Anytime there is an 'IF', as in:
> "if a disk in the stripe
> fails then so long as a disk in the mirror stripe isn't the mirror for
> the
> one that failed then you are ok"
> It seems mirrored stripes AND stripped mirrors are both vulnerable to
> multiple drive failures. However, as you indicated, the stripped mirrors
> seem to provide a little bit more of a element of chance to which mirrored
> drive fails, whereas with mirrored stripes, failure in the mirror is
> certain 'death'.
> One could posit that all of society survives on the element of chance.
> Another day, another useful bit of information gleaned.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uoxYHr3yGHA.3656@.TK2MSFTNGP04.phx.gbl...
>