Hello.
I would like to know the point of view of the experts at this forum.
I have one IBM xSeries 236 Server with just one ServeRAID 7 controller and
six internal hot swapable SCSI hard drives of 73.4 Gb each.
I will setup that Server with MS Windows 2003 Server Standard Edition and MS
SQL Server 2000.
I will load the Server with 5 databases:
1.- MRP production database, very intensive in writing data and also on be
red for reports and other applications. Current size about 1.5 Gb.
2.- MRP test database, same than (1) but only to be used when testing
something new, not very often. Current size about 1.5 Gb.
3.- Payroll database, used ligthly about two days a week, just collecting
employee's checks ins and outs from employees the remaining time of the
week. Current size about 700 Mb
4. - Other small database, very few usage. Current size 100 Mb.
What would be the best option to setup this Server, number of logical
drives, RAID levels, stripe size, write cache settings?
I would like also to implement log shipping in this Server, do I need
exactly the same configuration for the secondary Server?
Thanks for your help...!
Alex.
The info you ahve given would not be sufficient to recommend 'the best option
to setup this server'. But if I have to make a decision, I'd probably use two
disks to create a mirror for the OS, and use the other four disks to create a
RAID5 set.
For log shipping, you don't absolutely need the same configurations for the
standby server. The key factor to consider is what/how you'll use your
standby server. If you don't want any performance degradation after failover,
use an identical server. If the standby server is intended for temporary
relief and your customers are okay with performance degradation while the
primary server is beign restored/recovered, you may consider a less powerful
server config. We typically use the same class of servers for both the
primary and the secondary.
Linchi
"Microsoft Newsgroups" wrote:
> Hello.
> I would like to know the point of view of the experts at this forum.
> I have one IBM xSeries 236 Server with just one ServeRAID 7 controller and
> six internal hot swapable SCSI hard drives of 73.4 Gb each.
> I will setup that Server with MS Windows 2003 Server Standard Edition and MS
> SQL Server 2000.
> I will load the Server with 5 databases:
> 1.- MRP production database, very intensive in writing data and also on be
> red for reports and other applications. Current size about 1.5 Gb.
> 2.- MRP test database, same than (1) but only to be used when testing
> something new, not very often. Current size about 1.5 Gb.
> 3.- Payroll database, used ligthly about two days a week, just collecting
> employee's checks ins and outs from employees the remaining time of the
> week. Current size about 700 Mb
> 4. - Other small database, very few usage. Current size 100 Mb.
> What would be the best option to setup this Server, number of logical
> drives, RAID levels, stripe size, write cache settings?
> I would like also to implement log shipping in this Server, do I need
> exactly the same configuration for the secondary Server?
> Thanks for your help...!
> Alex.
>
>
Showing posts with label setup. Show all posts
Showing posts with label setup. Show all posts
Friday, March 30, 2012
RAID setup.
Hello.
I would like to know the point of view of the experts at this forum.
I have one IBM xSeries 236 Server with just one ServeRAID 7 controller and
six internal hot swapable SCSI hard drives of 73.4 Gb each.
I will setup that Server with MS Windows 2003 Server Standard Edition and MS
SQL Server 2000.
I will load the Server with 5 databases:
1.- MRP production database, very intensive in writing data and also on be
red for reports and other applications. Current size about 1.5 Gb.
2.- MRP test database, same than (1) but only to be used when testing
something new, not very often. Current size about 1.5 Gb.
3.- Payroll database, used ligthly about two days a week, just collecting
employee's checks ins and outs from employees the remaining time of the
week. Current size about 700 Mb
4. - Other small database, very few usage. Current size 100 Mb.
What would be the best option to setup this Server, number of logical
drives, RAID levels, stripe size, write cache settings?
I would like also to implement log shipping in this Server, do I need
exactly the same configuration for the secondary Server?
Thanks for your help...!
Alex.The info you ahve given would not be sufficient to recommend 'the best option
to setup this server'. But if I have to make a decision, I'd probably use two
disks to create a mirror for the OS, and use the other four disks to create a
RAID5 set.
For log shipping, you don't absolutely need the same configurations for the
standby server. The key factor to consider is what/how you'll use your
standby server. If you don't want any performance degradation after failover,
use an identical server. If the standby server is intended for temporary
relief and your customers are okay with performance degradation while the
primary server is beign restored/recovered, you may consider a less powerful
server config. We typically use the same class of servers for both the
primary and the secondary.
Linchi
"Microsoft Newsgroups" wrote:
> Hello.
> I would like to know the point of view of the experts at this forum.
> I have one IBM xSeries 236 Server with just one ServeRAID 7 controller and
> six internal hot swapable SCSI hard drives of 73.4 Gb each.
> I will setup that Server with MS Windows 2003 Server Standard Edition and MS
> SQL Server 2000.
> I will load the Server with 5 databases:
> 1.- MRP production database, very intensive in writing data and also on be
> red for reports and other applications. Current size about 1.5 Gb.
> 2.- MRP test database, same than (1) but only to be used when testing
> something new, not very often. Current size about 1.5 Gb.
> 3.- Payroll database, used ligthly about two days a week, just collecting
> employee's checks ins and outs from employees the remaining time of the
> week. Current size about 700 Mb
> 4. - Other small database, very few usage. Current size 100 Mb.
> What would be the best option to setup this Server, number of logical
> drives, RAID levels, stripe size, write cache settings?
> I would like also to implement log shipping in this Server, do I need
> exactly the same configuration for the secondary Server?
> Thanks for your help...!
> Alex.
>
>
I would like to know the point of view of the experts at this forum.
I have one IBM xSeries 236 Server with just one ServeRAID 7 controller and
six internal hot swapable SCSI hard drives of 73.4 Gb each.
I will setup that Server with MS Windows 2003 Server Standard Edition and MS
SQL Server 2000.
I will load the Server with 5 databases:
1.- MRP production database, very intensive in writing data and also on be
red for reports and other applications. Current size about 1.5 Gb.
2.- MRP test database, same than (1) but only to be used when testing
something new, not very often. Current size about 1.5 Gb.
3.- Payroll database, used ligthly about two days a week, just collecting
employee's checks ins and outs from employees the remaining time of the
week. Current size about 700 Mb
4. - Other small database, very few usage. Current size 100 Mb.
What would be the best option to setup this Server, number of logical
drives, RAID levels, stripe size, write cache settings?
I would like also to implement log shipping in this Server, do I need
exactly the same configuration for the secondary Server?
Thanks for your help...!
Alex.The info you ahve given would not be sufficient to recommend 'the best option
to setup this server'. But if I have to make a decision, I'd probably use two
disks to create a mirror for the OS, and use the other four disks to create a
RAID5 set.
For log shipping, you don't absolutely need the same configurations for the
standby server. The key factor to consider is what/how you'll use your
standby server. If you don't want any performance degradation after failover,
use an identical server. If the standby server is intended for temporary
relief and your customers are okay with performance degradation while the
primary server is beign restored/recovered, you may consider a less powerful
server config. We typically use the same class of servers for both the
primary and the secondary.
Linchi
"Microsoft Newsgroups" wrote:
> Hello.
> I would like to know the point of view of the experts at this forum.
> I have one IBM xSeries 236 Server with just one ServeRAID 7 controller and
> six internal hot swapable SCSI hard drives of 73.4 Gb each.
> I will setup that Server with MS Windows 2003 Server Standard Edition and MS
> SQL Server 2000.
> I will load the Server with 5 databases:
> 1.- MRP production database, very intensive in writing data and also on be
> red for reports and other applications. Current size about 1.5 Gb.
> 2.- MRP test database, same than (1) but only to be used when testing
> something new, not very often. Current size about 1.5 Gb.
> 3.- Payroll database, used ligthly about two days a week, just collecting
> employee's checks ins and outs from employees the remaining time of the
> week. Current size about 700 Mb
> 4. - Other small database, very few usage. Current size 100 Mb.
> What would be the best option to setup this Server, number of logical
> drives, RAID levels, stripe size, write cache settings?
> I would like also to implement log shipping in this Server, do I need
> exactly the same configuration for the secondary Server?
> Thanks for your help...!
> Alex.
>
>
RAID setup.
Hello.
I would like to know the point of view of the experts at this forum.
I have one IBM xSeries 236 Server with just one ServeRAID 7 controller and
six internal hot swapable SCSI hard drives of 73.4 Gb each.
I will setup that Server with MS Windows 2003 Server Standard Edition and MS
SQL Server 2000.
I will load the Server with 5 databases:
1.- MRP production database, very intensive in writing data and also on be
red for reports and other applications. Current size about 1.5 Gb.
2.- MRP test database, same than (1) but only to be used when testing
something new, not very often. Current size about 1.5 Gb.
3.- Payroll database, used ligthly about two days a week, just collecting
employee's checks ins and outs from employees the remaining time of the
week. Current size about 700 Mb
4. - Other small database, very few usage. Current size 100 Mb.
What would be the best option to setup this Server, number of logical
drives, RAID levels, stripe size, write cache settings?
I would like also to implement log shipping in this Server, do I need
exactly the same configuration for the secondary Server?
Thanks for your help...!
Alex.The info you ahve given would not be sufficient to recommend 'the best optio
n
to setup this server'. But if I have to make a decision, I'd probably use tw
o
disks to create a mirror for the OS, and use the other four disks to create
a
RAID5 set.
For log shipping, you don't absolutely need the same configurations for the
standby server. The key factor to consider is what/how you'll use your
standby server. If you don't want any performance degradation after failover
,
use an identical server. If the standby server is intended for temporary
relief and your customers are okay with performance degradation while the
primary server is beign restored/recovered, you may consider a less powerful
server config. We typically use the same class of servers for both the
primary and the secondary.
Linchi
"Microsoft Newsgroups" wrote:
> Hello.
> I would like to know the point of view of the experts at this forum.
> I have one IBM xSeries 236 Server with just one ServeRAID 7 controller and
> six internal hot swapable SCSI hard drives of 73.4 Gb each.
> I will setup that Server with MS Windows 2003 Server Standard Edition and
MS
> SQL Server 2000.
> I will load the Server with 5 databases:
> 1.- MRP production database, very intensive in writing data and also on be
> red for reports and other applications. Current size about 1.5 Gb.
> 2.- MRP test database, same than (1) but only to be used when testing
> something new, not very often. Current size about 1.5 Gb.
> 3.- Payroll database, used ligthly about two days a week, just collecting
> employee's checks ins and outs from employees the remaining time of the
> week. Current size about 700 Mb
> 4. - Other small database, very few usage. Current size 100 Mb.
> What would be the best option to setup this Server, number of logical
> drives, RAID levels, stripe size, write cache settings?
> I would like also to implement log shipping in this Server, do I need
> exactly the same configuration for the secondary Server?
> Thanks for your help...!
> Alex.
>
>
I would like to know the point of view of the experts at this forum.
I have one IBM xSeries 236 Server with just one ServeRAID 7 controller and
six internal hot swapable SCSI hard drives of 73.4 Gb each.
I will setup that Server with MS Windows 2003 Server Standard Edition and MS
SQL Server 2000.
I will load the Server with 5 databases:
1.- MRP production database, very intensive in writing data and also on be
red for reports and other applications. Current size about 1.5 Gb.
2.- MRP test database, same than (1) but only to be used when testing
something new, not very often. Current size about 1.5 Gb.
3.- Payroll database, used ligthly about two days a week, just collecting
employee's checks ins and outs from employees the remaining time of the
week. Current size about 700 Mb
4. - Other small database, very few usage. Current size 100 Mb.
What would be the best option to setup this Server, number of logical
drives, RAID levels, stripe size, write cache settings?
I would like also to implement log shipping in this Server, do I need
exactly the same configuration for the secondary Server?
Thanks for your help...!
Alex.The info you ahve given would not be sufficient to recommend 'the best optio
n
to setup this server'. But if I have to make a decision, I'd probably use tw
o
disks to create a mirror for the OS, and use the other four disks to create
a
RAID5 set.
For log shipping, you don't absolutely need the same configurations for the
standby server. The key factor to consider is what/how you'll use your
standby server. If you don't want any performance degradation after failover
,
use an identical server. If the standby server is intended for temporary
relief and your customers are okay with performance degradation while the
primary server is beign restored/recovered, you may consider a less powerful
server config. We typically use the same class of servers for both the
primary and the secondary.
Linchi
"Microsoft Newsgroups" wrote:
> Hello.
> I would like to know the point of view of the experts at this forum.
> I have one IBM xSeries 236 Server with just one ServeRAID 7 controller and
> six internal hot swapable SCSI hard drives of 73.4 Gb each.
> I will setup that Server with MS Windows 2003 Server Standard Edition and
MS
> SQL Server 2000.
> I will load the Server with 5 databases:
> 1.- MRP production database, very intensive in writing data and also on be
> red for reports and other applications. Current size about 1.5 Gb.
> 2.- MRP test database, same than (1) but only to be used when testing
> something new, not very often. Current size about 1.5 Gb.
> 3.- Payroll database, used ligthly about two days a week, just collecting
> employee's checks ins and outs from employees the remaining time of the
> week. Current size about 700 Mb
> 4. - Other small database, very few usage. Current size 100 Mb.
> What would be the best option to setup this Server, number of logical
> drives, RAID levels, stripe size, write cache settings?
> I would like also to implement log shipping in this Server, do I need
> exactly the same configuration for the secondary Server?
> Thanks for your help...!
> Alex.
>
>
Raid Setup
We have just bought a new box for our SQL Database's.. ..We have three
database's, 1 is by far the most intensive, it's a 3rd party application
database and is around 20gb.. ..it uses the temp db heavily.. ..and the
transaction log gets up to about 750mb - 1gb before it's backed up every
15minutes. The other databases are 20gb and 10gb respectively, the 20gb
database contains 15gb of BLOB's.
The box we have bought has dual xeons and 4gb of RAM, and we have 18 disks
to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a Smart
Array.
My initial views for the RAID configuration were as follows:
Array 1, RAID 1 (2xSCSI) OS & Pagefile
Array 2, RAID 1 (2xSCSI) TempDB
Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
Array 4, RAID 10 (10xSATA) All Datafiles
Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
Although I have also looked at:
Array 1, RAID 1 (2xSCSI) OS & Pagefile
Array 2, RAID 1 (2xSCSI) TempDB
Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
Array 4, RAID 10 (6xSATA) All Datafiles
Array 5, RAID 10 (2xSATA) File containing BLOB's
Array 6, RAID 1 (2xSATA) Transaction Log 2
Array 7, RAID 1 (2xSATA) Transaction Log 3
And:
Array 1, RAID 1 (2xSCSI) OS
Array 2, RAID 1 (2xSCSI) PageFile
Array 3, RAID 1 (2xSCSI) TempDB
Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
Array 6, RAID 1 (2xSATA) File containing BLOB's
Array 7, RAID 1 (2xSATA) Transaction Log 1
Array 8, RAID 1 (2xSATA) Transaction Log 2
Array 9, RAID 1 (2xSATA) Transaction Log 3
...I'll be doing some further reading, but just wondered if anyone had any
input on this?
Thanks in advance BenHi Ben
Why did you buy so many disks & such a small amount of memory? Are you using
SQL 2000 or SQL 2005? Which version of Windows? These are actually all quite
important factors as they define your memory constraints & influence whether
you should configure any of your disks for specialist read / write activity.
The less memory you have, the more you push IO down to your disk
sub-system..
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
> We have just bought a new box for our SQL Database's.. ..We have three
> database's, 1 is by far the most intensive, it's a 3rd party application
> database and is around 20gb.. ..it uses the temp db heavily.. ..and the
> transaction log gets up to about 750mb - 1gb before it's backed up every
> 15minutes. The other databases are 20gb and 10gb respectively, the 20gb
> database contains 15gb of BLOB's.
> The box we have bought has dual xeons and 4gb of RAM, and we have 18 disks
> to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a Smart
> Array.
> My initial views for the RAID configuration were as follows:
> Array 1, RAID 1 (2xSCSI) OS & Pagefile
> Array 2, RAID 1 (2xSCSI) TempDB
> Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> Array 4, RAID 10 (10xSATA) All Datafiles
> Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
> Although I have also looked at:
> Array 1, RAID 1 (2xSCSI) OS & Pagefile
> Array 2, RAID 1 (2xSCSI) TempDB
> Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> Array 4, RAID 10 (6xSATA) All Datafiles
> Array 5, RAID 10 (2xSATA) File containing BLOB's
> Array 6, RAID 1 (2xSATA) Transaction Log 2
> Array 7, RAID 1 (2xSATA) Transaction Log 3
> And:
> Array 1, RAID 1 (2xSCSI) OS
> Array 2, RAID 1 (2xSCSI) PageFile
> Array 3, RAID 1 (2xSCSI) TempDB
> Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
> Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
> Array 6, RAID 1 (2xSATA) File containing BLOB's
> Array 7, RAID 1 (2xSATA) Transaction Log 1
> Array 8, RAID 1 (2xSATA) Transaction Log 2
> Array 9, RAID 1 (2xSATA) Transaction Log 3
> ...I'll be doing some further reading, but just wondered if anyone had any
> input on this?
> Thanks in advance Ben|||We're using Windows 2003 Standard Edition, and SQL Standard Edition... ...so
more memory wasn't really an option.. ..the cost of disks are cheap in
comparison to the upgrade to Enterprise Edition of SQL - we have to purchase
the processor licenses. We haven't actually purchased the disks yet, if
there's only a small benefit between say 12 and 18 disks we'll only buy the
12...
"Greg Linwood" wrote:
> Hi Ben
> Why did you buy so many disks & such a small amount of memory? Are you usi
ng
> SQL 2000 or SQL 2005? Which version of Windows? These are actually all qui
te
> important factors as they define your memory constraints & influence wheth
er
> you should configure any of your disks for specialist read / write activit
y.
> The less memory you have, the more you push IO down to your disk
> sub-system..
> Regards,
> Greg Linwood
> SQL Server MVP
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
>
>|||Hi Ben
This depends entirely on whether you're using SQL 2000 or SQL 2005 & 32 bit
vs 64 bit. For example, if you're using 64 bit Windows 2003 Server Standard
Edition and 64 bit SQL 2005 Standard Edition, you can access up to 32Gb
Memory.
That's why I asked whether you're using SQL 2000 or SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...[vbcol=seagreen]
> We're using Windows 2003 Standard Edition, and SQL Standard Edition...
> ...so
> more memory wasn't really an option.. ..the cost of disks are cheap in
> comparison to the upgrade to Enterprise Edition of SQL - we have to
> purchase
> the processor licenses. We haven't actually purchased the disks yet, if
> there's only a small benefit between say 12 and 18 disks we'll only buy
> the
> 12...
> "Greg Linwood" wrote:
>|||Memory isn't an option, it's 32bit SQL 2000 on 32bit windows... ...hence I'm
trying to compensate with disks and find the best raid config I can...
"Greg Linwood" wrote:
> Hi Ben
> This depends entirely on whether you're using SQL 2000 or SQL 2005 & 32 bi
t
> vs 64 bit. For example, if you're using 64 bit Windows 2003 Server Standar
d
> Edition and 64 bit SQL 2005 Standard Edition, you can access up to 32Gb
> Memory.
> That's why I asked whether you're using SQL 2000 or SQL 2005.
> Regards,
> Greg Linwood
> SQL Server MVP
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
>
>|||OK, I just wanted to check up on these points first, because they're often
missed with new SQL server implementations.
Back to your original qns then:
Firstly, drop the idea of a seperate Array for your page file b/c well
configured, dedicated database servers should perform very little IO against
page files. Page files are for machines where many processes need to share
virtual memory & dedicated servers generally do very little of this -
certainly not enought to warrant dedicating an array to. Far better to
dedicate this array to a tlog or some other useful purpose
Seperating objects down to the granularity you've outlined in the last
option. The problem with this approach is that you're making assumptions
about how your IO should be partitioned where it's often far easier to leave
these objects on a single array & let SQL Server spread the load accross
them. Another way of looking at this scenario is that if any one object type
experiences heavy IO, it has fewer disks to achieve its workload with.
Short of more specific information about the actual workload performed by
the various DB objects, I'd say that your initial configuration is a very
good starting point.
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:94A8EB85-786A-415D-BA47-8E75777744A1@.microsoft.com...[vbcol=seagreen]
> Memory isn't an option, it's 32bit SQL 2000 on 32bit windows... ...hence
> I'm
> trying to compensate with disks and find the best raid config I can...
> "Greg Linwood" wrote:
>|||Smashing thanks Greg, in the original configuration I have 2 transaction log
s
sharing an array... ...these logs don't grow above 250mb over 15minutes...
...but I'm wondering whether it's worth splitting them out onto seperate
arrays? - I'd have to drop the RAID 10 array from 10 to 8 disks to make
room... ...but I've read it's best to seperate Transaction Logs onto
seperate arrays so they can be sequential... ...I'm unsure if the benefit of
having them on seperate disks would be less than having the 10 disks in the
RAID 10 array... ...I guess it's one of those things I'll find out with some
testing :-) Anyway thanks again for the responses..
"Greg Linwood" wrote:
> OK, I just wanted to check up on these points first, because they're often
> missed with new SQL server implementations.
> Back to your original qns then:
> Firstly, drop the idea of a seperate Array for your page file b/c well
> configured, dedicated database servers should perform very little IO again
st
> page files. Page files are for machines where many processes need to share
> virtual memory & dedicated servers generally do very little of this -
> certainly not enought to warrant dedicating an array to. Far better to
> dedicate this array to a tlog or some other useful purpose
> Seperating objects down to the granularity you've outlined in the last
> option. The problem with this approach is that you're making assumptions
> about how your IO should be partitioned where it's often far easier to lea
ve
> these objects on a single array & let SQL Server spread the load accross
> them. Another way of looking at this scenario is that if any one object ty
pe
> experiences heavy IO, it has fewer disks to achieve its workload with.
> Short of more specific information about the actual workload performed by
> the various DB objects, I'd say that your initial configuration is a very
> good starting point.
> Regards,
> Greg Linwood
> SQL Server MVP
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:94A8EB85-786A-415D-BA47-8E75777744A1@.microsoft.com...
>
>|||Hi Ben
I suggest you don't seperate those logs out then, as 250Mb in 15 mins is
quite moderate & you could probably find better uses for those disks.
Speaking of which - one thing I missed when I first looked at your
suggestions is that you don't have a local drive for backups. Are you
planning to do these off onto a network or tape? If not, I suggest you don't
forget a backup volume in your plan & those extra disks might come in handy
for this purpose...
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:ADDF9C2C-FC93-447F-9F44-E554316C36F6@.microsoft.com...[vbcol=seagreen]
> Smashing thanks Greg, in the original configuration I have 2 transaction
> logs
> sharing an array... ...these logs don't grow above 250mb over 15minutes...
> ...but I'm wondering whether it's worth splitting them out onto seperate
> arrays? - I'd have to drop the RAID 10 array from 10 to 8 disks to make
> room... ...but I've read it's best to seperate Transaction Logs onto
> seperate arrays so they can be sequential... ...I'm unsure if the benefit
> of
> having them on seperate disks would be less than having the 10 disks in
> the
> RAID 10 array... ...I guess it's one of those things I'll find out with
> some
> testing :-) Anyway thanks again for the responses..
> "Greg Linwood" wrote:
>|||We currently backup over the network and then onto tape.. ..but it's worth
thinking about, thanks again for the help :-)
"Greg Linwood" wrote:
> Hi Ben
> I suggest you don't seperate those logs out then, as 250Mb in 15 mins is
> quite moderate & you could probably find better uses for those disks.
> Speaking of which - one thing I missed when I first looked at your
> suggestions is that you don't have a local drive for backups. Are you
> planning to do these off onto a network or tape? If not, I suggest you don
't
> forget a backup volume in your plan & those extra disks might come in hand
y
> for this purpose...
> Regards,
> Greg Linwood
> SQL Server MVP
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:ADDF9C2C-FC93-447F-9F44-E554316C36F6@.microsoft.com...
>
>sql
database's, 1 is by far the most intensive, it's a 3rd party application
database and is around 20gb.. ..it uses the temp db heavily.. ..and the
transaction log gets up to about 750mb - 1gb before it's backed up every
15minutes. The other databases are 20gb and 10gb respectively, the 20gb
database contains 15gb of BLOB's.
The box we have bought has dual xeons and 4gb of RAM, and we have 18 disks
to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a Smart
Array.
My initial views for the RAID configuration were as follows:
Array 1, RAID 1 (2xSCSI) OS & Pagefile
Array 2, RAID 1 (2xSCSI) TempDB
Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
Array 4, RAID 10 (10xSATA) All Datafiles
Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
Although I have also looked at:
Array 1, RAID 1 (2xSCSI) OS & Pagefile
Array 2, RAID 1 (2xSCSI) TempDB
Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
Array 4, RAID 10 (6xSATA) All Datafiles
Array 5, RAID 10 (2xSATA) File containing BLOB's
Array 6, RAID 1 (2xSATA) Transaction Log 2
Array 7, RAID 1 (2xSATA) Transaction Log 3
And:
Array 1, RAID 1 (2xSCSI) OS
Array 2, RAID 1 (2xSCSI) PageFile
Array 3, RAID 1 (2xSCSI) TempDB
Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
Array 6, RAID 1 (2xSATA) File containing BLOB's
Array 7, RAID 1 (2xSATA) Transaction Log 1
Array 8, RAID 1 (2xSATA) Transaction Log 2
Array 9, RAID 1 (2xSATA) Transaction Log 3
...I'll be doing some further reading, but just wondered if anyone had any
input on this?
Thanks in advance BenHi Ben
Why did you buy so many disks & such a small amount of memory? Are you using
SQL 2000 or SQL 2005? Which version of Windows? These are actually all quite
important factors as they define your memory constraints & influence whether
you should configure any of your disks for specialist read / write activity.
The less memory you have, the more you push IO down to your disk
sub-system..
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
> We have just bought a new box for our SQL Database's.. ..We have three
> database's, 1 is by far the most intensive, it's a 3rd party application
> database and is around 20gb.. ..it uses the temp db heavily.. ..and the
> transaction log gets up to about 750mb - 1gb before it's backed up every
> 15minutes. The other databases are 20gb and 10gb respectively, the 20gb
> database contains 15gb of BLOB's.
> The box we have bought has dual xeons and 4gb of RAM, and we have 18 disks
> to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a Smart
> Array.
> My initial views for the RAID configuration were as follows:
> Array 1, RAID 1 (2xSCSI) OS & Pagefile
> Array 2, RAID 1 (2xSCSI) TempDB
> Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> Array 4, RAID 10 (10xSATA) All Datafiles
> Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
> Although I have also looked at:
> Array 1, RAID 1 (2xSCSI) OS & Pagefile
> Array 2, RAID 1 (2xSCSI) TempDB
> Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> Array 4, RAID 10 (6xSATA) All Datafiles
> Array 5, RAID 10 (2xSATA) File containing BLOB's
> Array 6, RAID 1 (2xSATA) Transaction Log 2
> Array 7, RAID 1 (2xSATA) Transaction Log 3
> And:
> Array 1, RAID 1 (2xSCSI) OS
> Array 2, RAID 1 (2xSCSI) PageFile
> Array 3, RAID 1 (2xSCSI) TempDB
> Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
> Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
> Array 6, RAID 1 (2xSATA) File containing BLOB's
> Array 7, RAID 1 (2xSATA) Transaction Log 1
> Array 8, RAID 1 (2xSATA) Transaction Log 2
> Array 9, RAID 1 (2xSATA) Transaction Log 3
> ...I'll be doing some further reading, but just wondered if anyone had any
> input on this?
> Thanks in advance Ben|||We're using Windows 2003 Standard Edition, and SQL Standard Edition... ...so
more memory wasn't really an option.. ..the cost of disks are cheap in
comparison to the upgrade to Enterprise Edition of SQL - we have to purchase
the processor licenses. We haven't actually purchased the disks yet, if
there's only a small benefit between say 12 and 18 disks we'll only buy the
12...
"Greg Linwood" wrote:
> Hi Ben
> Why did you buy so many disks & such a small amount of memory? Are you usi
ng
> SQL 2000 or SQL 2005? Which version of Windows? These are actually all qui
te
> important factors as they define your memory constraints & influence wheth
er
> you should configure any of your disks for specialist read / write activit
y.
> The less memory you have, the more you push IO down to your disk
> sub-system..
> Regards,
> Greg Linwood
> SQL Server MVP
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
>
>|||Hi Ben
This depends entirely on whether you're using SQL 2000 or SQL 2005 & 32 bit
vs 64 bit. For example, if you're using 64 bit Windows 2003 Server Standard
Edition and 64 bit SQL 2005 Standard Edition, you can access up to 32Gb
Memory.
That's why I asked whether you're using SQL 2000 or SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...[vbcol=seagreen]
> We're using Windows 2003 Standard Edition, and SQL Standard Edition...
> ...so
> more memory wasn't really an option.. ..the cost of disks are cheap in
> comparison to the upgrade to Enterprise Edition of SQL - we have to
> purchase
> the processor licenses. We haven't actually purchased the disks yet, if
> there's only a small benefit between say 12 and 18 disks we'll only buy
> the
> 12...
> "Greg Linwood" wrote:
>|||Memory isn't an option, it's 32bit SQL 2000 on 32bit windows... ...hence I'm
trying to compensate with disks and find the best raid config I can...
"Greg Linwood" wrote:
> Hi Ben
> This depends entirely on whether you're using SQL 2000 or SQL 2005 & 32 bi
t
> vs 64 bit. For example, if you're using 64 bit Windows 2003 Server Standar
d
> Edition and 64 bit SQL 2005 Standard Edition, you can access up to 32Gb
> Memory.
> That's why I asked whether you're using SQL 2000 or SQL 2005.
> Regards,
> Greg Linwood
> SQL Server MVP
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
>
>|||OK, I just wanted to check up on these points first, because they're often
missed with new SQL server implementations.
Back to your original qns then:
Firstly, drop the idea of a seperate Array for your page file b/c well
configured, dedicated database servers should perform very little IO against
page files. Page files are for machines where many processes need to share
virtual memory & dedicated servers generally do very little of this -
certainly not enought to warrant dedicating an array to. Far better to
dedicate this array to a tlog or some other useful purpose
Seperating objects down to the granularity you've outlined in the last
option. The problem with this approach is that you're making assumptions
about how your IO should be partitioned where it's often far easier to leave
these objects on a single array & let SQL Server spread the load accross
them. Another way of looking at this scenario is that if any one object type
experiences heavy IO, it has fewer disks to achieve its workload with.
Short of more specific information about the actual workload performed by
the various DB objects, I'd say that your initial configuration is a very
good starting point.
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:94A8EB85-786A-415D-BA47-8E75777744A1@.microsoft.com...[vbcol=seagreen]
> Memory isn't an option, it's 32bit SQL 2000 on 32bit windows... ...hence
> I'm
> trying to compensate with disks and find the best raid config I can...
> "Greg Linwood" wrote:
>|||Smashing thanks Greg, in the original configuration I have 2 transaction log
s
sharing an array... ...these logs don't grow above 250mb over 15minutes...
...but I'm wondering whether it's worth splitting them out onto seperate
arrays? - I'd have to drop the RAID 10 array from 10 to 8 disks to make
room... ...but I've read it's best to seperate Transaction Logs onto
seperate arrays so they can be sequential... ...I'm unsure if the benefit of
having them on seperate disks would be less than having the 10 disks in the
RAID 10 array... ...I guess it's one of those things I'll find out with some
testing :-) Anyway thanks again for the responses..
"Greg Linwood" wrote:
> OK, I just wanted to check up on these points first, because they're often
> missed with new SQL server implementations.
> Back to your original qns then:
> Firstly, drop the idea of a seperate Array for your page file b/c well
> configured, dedicated database servers should perform very little IO again
st
> page files. Page files are for machines where many processes need to share
> virtual memory & dedicated servers generally do very little of this -
> certainly not enought to warrant dedicating an array to. Far better to
> dedicate this array to a tlog or some other useful purpose
> Seperating objects down to the granularity you've outlined in the last
> option. The problem with this approach is that you're making assumptions
> about how your IO should be partitioned where it's often far easier to lea
ve
> these objects on a single array & let SQL Server spread the load accross
> them. Another way of looking at this scenario is that if any one object ty
pe
> experiences heavy IO, it has fewer disks to achieve its workload with.
> Short of more specific information about the actual workload performed by
> the various DB objects, I'd say that your initial configuration is a very
> good starting point.
> Regards,
> Greg Linwood
> SQL Server MVP
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:94A8EB85-786A-415D-BA47-8E75777744A1@.microsoft.com...
>
>|||Hi Ben
I suggest you don't seperate those logs out then, as 250Mb in 15 mins is
quite moderate & you could probably find better uses for those disks.
Speaking of which - one thing I missed when I first looked at your
suggestions is that you don't have a local drive for backups. Are you
planning to do these off onto a network or tape? If not, I suggest you don't
forget a backup volume in your plan & those extra disks might come in handy
for this purpose...
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:ADDF9C2C-FC93-447F-9F44-E554316C36F6@.microsoft.com...[vbcol=seagreen]
> Smashing thanks Greg, in the original configuration I have 2 transaction
> logs
> sharing an array... ...these logs don't grow above 250mb over 15minutes...
> ...but I'm wondering whether it's worth splitting them out onto seperate
> arrays? - I'd have to drop the RAID 10 array from 10 to 8 disks to make
> room... ...but I've read it's best to seperate Transaction Logs onto
> seperate arrays so they can be sequential... ...I'm unsure if the benefit
> of
> having them on seperate disks would be less than having the 10 disks in
> the
> RAID 10 array... ...I guess it's one of those things I'll find out with
> some
> testing :-) Anyway thanks again for the responses..
> "Greg Linwood" wrote:
>|||We currently backup over the network and then onto tape.. ..but it's worth
thinking about, thanks again for the help :-)
"Greg Linwood" wrote:
> Hi Ben
> I suggest you don't seperate those logs out then, as 250Mb in 15 mins is
> quite moderate & you could probably find better uses for those disks.
> Speaking of which - one thing I missed when I first looked at your
> suggestions is that you don't have a local drive for backups. Are you
> planning to do these off onto a network or tape? If not, I suggest you don
't
> forget a backup volume in your plan & those extra disks might come in hand
y
> for this purpose...
> Regards,
> Greg Linwood
> SQL Server MVP
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:ADDF9C2C-FC93-447F-9F44-E554316C36F6@.microsoft.com...
>
>sql
Raid Setup
We have just bought a new box for our SQL Database's.. ..We have three
database's, 1 is by far the most intensive, it's a 3rd party application
database and is around 20gb.. ..it uses the temp db heavily.. ..and the
transaction log gets up to about 750mb - 1gb before it's backed up every
15minutes. The other databases are 20gb and 10gb respectively, the 20gb
database contains 15gb of BLOB's.
The box we have bought has dual xeons and 4gb of RAM, and we have 18 disks
to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a Smart
Array.
My initial views for the RAID configuration were as follows:
Array 1, RAID 1 (2xSCSI) OS & Pagefile
Array 2, RAID 1 (2xSCSI) TempDB
Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
Array 4, RAID 10 (10xSATA) All Datafiles
Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
Although I have also looked at:
Array 1, RAID 1 (2xSCSI) OS & Pagefile
Array 2, RAID 1 (2xSCSI) TempDB
Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
Array 4, RAID 10 (6xSATA) All Datafiles
Array 5, RAID 10 (2xSATA) File containing BLOB's
Array 6, RAID 1 (2xSATA) Transaction Log 2
Array 7, RAID 1 (2xSATA) Transaction Log 3
And:
Array 1, RAID 1 (2xSCSI) OS
Array 2, RAID 1 (2xSCSI) PageFile
Array 3, RAID 1 (2xSCSI) TempDB
Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
Array 6, RAID 1 (2xSATA) File containing BLOB's
Array 7, RAID 1 (2xSATA) Transaction Log 1
Array 8, RAID 1 (2xSATA) Transaction Log 2
Array 9, RAID 1 (2xSATA) Transaction Log 3
...I'll be doing some further reading, but just wondered if anyone had any
input on this?
Thanks in advance BenHi Ben
Why did you buy so many disks & such a small amount of memory? Are you using
SQL 2000 or SQL 2005? Which version of Windows? These are actually all quite
important factors as they define your memory constraints & influence whether
you should configure any of your disks for specialist read / write activity.
The less memory you have, the more you push IO down to your disk
sub-system..
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
> We have just bought a new box for our SQL Database's.. ..We have three
> database's, 1 is by far the most intensive, it's a 3rd party application
> database and is around 20gb.. ..it uses the temp db heavily.. ..and the
> transaction log gets up to about 750mb - 1gb before it's backed up every
> 15minutes. The other databases are 20gb and 10gb respectively, the 20gb
> database contains 15gb of BLOB's.
> The box we have bought has dual xeons and 4gb of RAM, and we have 18 disks
> to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a Smart
> Array.
> My initial views for the RAID configuration were as follows:
> Array 1, RAID 1 (2xSCSI) OS & Pagefile
> Array 2, RAID 1 (2xSCSI) TempDB
> Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> Array 4, RAID 10 (10xSATA) All Datafiles
> Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
> Although I have also looked at:
> Array 1, RAID 1 (2xSCSI) OS & Pagefile
> Array 2, RAID 1 (2xSCSI) TempDB
> Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> Array 4, RAID 10 (6xSATA) All Datafiles
> Array 5, RAID 10 (2xSATA) File containing BLOB's
> Array 6, RAID 1 (2xSATA) Transaction Log 2
> Array 7, RAID 1 (2xSATA) Transaction Log 3
> And:
> Array 1, RAID 1 (2xSCSI) OS
> Array 2, RAID 1 (2xSCSI) PageFile
> Array 3, RAID 1 (2xSCSI) TempDB
> Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
> Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
> Array 6, RAID 1 (2xSATA) File containing BLOB's
> Array 7, RAID 1 (2xSATA) Transaction Log 1
> Array 8, RAID 1 (2xSATA) Transaction Log 2
> Array 9, RAID 1 (2xSATA) Transaction Log 3
> ...I'll be doing some further reading, but just wondered if anyone had any
> input on this?
> Thanks in advance Ben|||We're using Windows 2003 Standard Edition, and SQL Standard Edition... ...so
more memory wasn't really an option.. ..the cost of disks are cheap in
comparison to the upgrade to Enterprise Edition of SQL - we have to purchase
the processor licenses. We haven't actually purchased the disks yet, if
there's only a small benefit between say 12 and 18 disks we'll only buy the
12...
"Greg Linwood" wrote:
> Hi Ben
> Why did you buy so many disks & such a small amount of memory? Are you using
> SQL 2000 or SQL 2005? Which version of Windows? These are actually all quite
> important factors as they define your memory constraints & influence whether
> you should configure any of your disks for specialist read / write activity.
> The less memory you have, the more you push IO down to your disk
> sub-system..
> Regards,
> Greg Linwood
> SQL Server MVP
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
> >
> > We have just bought a new box for our SQL Database's.. ..We have three
> > database's, 1 is by far the most intensive, it's a 3rd party application
> > database and is around 20gb.. ..it uses the temp db heavily.. ..and the
> > transaction log gets up to about 750mb - 1gb before it's backed up every
> > 15minutes. The other databases are 20gb and 10gb respectively, the 20gb
> > database contains 15gb of BLOB's.
> > The box we have bought has dual xeons and 4gb of RAM, and we have 18 disks
> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a Smart
> > Array.
> >
> > My initial views for the RAID configuration were as follows:
> >
> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> > Array 2, RAID 1 (2xSCSI) TempDB
> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> > Array 4, RAID 10 (10xSATA) All Datafiles
> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
> >
> > Although I have also looked at:
> >
> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> > Array 2, RAID 1 (2xSCSI) TempDB
> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> > Array 4, RAID 10 (6xSATA) All Datafiles
> > Array 5, RAID 10 (2xSATA) File containing BLOB's
> > Array 6, RAID 1 (2xSATA) Transaction Log 2
> > Array 7, RAID 1 (2xSATA) Transaction Log 3
> >
> > And:
> >
> > Array 1, RAID 1 (2xSCSI) OS
> > Array 2, RAID 1 (2xSCSI) PageFile
> > Array 3, RAID 1 (2xSCSI) TempDB
> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
> > Array 6, RAID 1 (2xSATA) File containing BLOB's
> > Array 7, RAID 1 (2xSATA) Transaction Log 1
> > Array 8, RAID 1 (2xSATA) Transaction Log 2
> > Array 9, RAID 1 (2xSATA) Transaction Log 3
> >
> > ...I'll be doing some further reading, but just wondered if anyone had any
> > input on this?
> >
> > Thanks in advance Ben
>
>|||Hi Ben
This depends entirely on whether you're using SQL 2000 or SQL 2005 & 32 bit
vs 64 bit. For example, if you're using 64 bit Windows 2003 Server Standard
Edition and 64 bit SQL 2005 Standard Edition, you can access up to 32Gb
Memory.
That's why I asked whether you're using SQL 2000 or SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
> We're using Windows 2003 Standard Edition, and SQL Standard Edition...
> ...so
> more memory wasn't really an option.. ..the cost of disks are cheap in
> comparison to the upgrade to Enterprise Edition of SQL - we have to
> purchase
> the processor licenses. We haven't actually purchased the disks yet, if
> there's only a small benefit between say 12 and 18 disks we'll only buy
> the
> 12...
> "Greg Linwood" wrote:
>> Hi Ben
>> Why did you buy so many disks & such a small amount of memory? Are you
>> using
>> SQL 2000 or SQL 2005? Which version of Windows? These are actually all
>> quite
>> important factors as they define your memory constraints & influence
>> whether
>> you should configure any of your disks for specialist read / write
>> activity.
>> The less memory you have, the more you push IO down to your disk
>> sub-system..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
>> >
>> > We have just bought a new box for our SQL Database's.. ..We have three
>> > database's, 1 is by far the most intensive, it's a 3rd party
>> > application
>> > database and is around 20gb.. ..it uses the temp db heavily.. ..and the
>> > transaction log gets up to about 750mb - 1gb before it's backed up
>> > every
>> > 15minutes. The other databases are 20gb and 10gb respectively, the
>> > 20gb
>> > database contains 15gb of BLOB's.
>> > The box we have bought has dual xeons and 4gb of RAM, and we have 18
>> > disks
>> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a
>> > Smart
>> > Array.
>> >
>> > My initial views for the RAID configuration were as follows:
>> >
>> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
>> > Array 2, RAID 1 (2xSCSI) TempDB
>> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
>> > Array 4, RAID 10 (10xSATA) All Datafiles
>> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
>> >
>> > Although I have also looked at:
>> >
>> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
>> > Array 2, RAID 1 (2xSCSI) TempDB
>> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
>> > Array 4, RAID 10 (6xSATA) All Datafiles
>> > Array 5, RAID 10 (2xSATA) File containing BLOB's
>> > Array 6, RAID 1 (2xSATA) Transaction Log 2
>> > Array 7, RAID 1 (2xSATA) Transaction Log 3
>> >
>> > And:
>> >
>> > Array 1, RAID 1 (2xSCSI) OS
>> > Array 2, RAID 1 (2xSCSI) PageFile
>> > Array 3, RAID 1 (2xSCSI) TempDB
>> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
>> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
>> > Array 6, RAID 1 (2xSATA) File containing BLOB's
>> > Array 7, RAID 1 (2xSATA) Transaction Log 1
>> > Array 8, RAID 1 (2xSATA) Transaction Log 2
>> > Array 9, RAID 1 (2xSATA) Transaction Log 3
>> >
>> > ...I'll be doing some further reading, but just wondered if anyone had
>> > any
>> > input on this?
>> >
>> > Thanks in advance Ben
>>|||Memory isn't an option, it's 32bit SQL 2000 on 32bit windows... ...hence I'm
trying to compensate with disks and find the best raid config I can...
"Greg Linwood" wrote:
> Hi Ben
> This depends entirely on whether you're using SQL 2000 or SQL 2005 & 32 bit
> vs 64 bit. For example, if you're using 64 bit Windows 2003 Server Standard
> Edition and 64 bit SQL 2005 Standard Edition, you can access up to 32Gb
> Memory.
> That's why I asked whether you're using SQL 2000 or SQL 2005.
> Regards,
> Greg Linwood
> SQL Server MVP
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
> > We're using Windows 2003 Standard Edition, and SQL Standard Edition...
> > ...so
> > more memory wasn't really an option.. ..the cost of disks are cheap in
> > comparison to the upgrade to Enterprise Edition of SQL - we have to
> > purchase
> > the processor licenses. We haven't actually purchased the disks yet, if
> > there's only a small benefit between say 12 and 18 disks we'll only buy
> > the
> > 12...
> >
> > "Greg Linwood" wrote:
> >
> >> Hi Ben
> >>
> >> Why did you buy so many disks & such a small amount of memory? Are you
> >> using
> >> SQL 2000 or SQL 2005? Which version of Windows? These are actually all
> >> quite
> >> important factors as they define your memory constraints & influence
> >> whether
> >> you should configure any of your disks for specialist read / write
> >> activity.
> >> The less memory you have, the more you push IO down to your disk
> >> sub-system..
> >>
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP
> >>
> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
> >> >
> >> > We have just bought a new box for our SQL Database's.. ..We have three
> >> > database's, 1 is by far the most intensive, it's a 3rd party
> >> > application
> >> > database and is around 20gb.. ..it uses the temp db heavily.. ..and the
> >> > transaction log gets up to about 750mb - 1gb before it's backed up
> >> > every
> >> > 15minutes. The other databases are 20gb and 10gb respectively, the
> >> > 20gb
> >> > database contains 15gb of BLOB's.
> >> > The box we have bought has dual xeons and 4gb of RAM, and we have 18
> >> > disks
> >> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a
> >> > Smart
> >> > Array.
> >> >
> >> > My initial views for the RAID configuration were as follows:
> >> >
> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> >> > Array 2, RAID 1 (2xSCSI) TempDB
> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> >> > Array 4, RAID 10 (10xSATA) All Datafiles
> >> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
> >> >
> >> > Although I have also looked at:
> >> >
> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> >> > Array 2, RAID 1 (2xSCSI) TempDB
> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> >> > Array 4, RAID 10 (6xSATA) All Datafiles
> >> > Array 5, RAID 10 (2xSATA) File containing BLOB's
> >> > Array 6, RAID 1 (2xSATA) Transaction Log 2
> >> > Array 7, RAID 1 (2xSATA) Transaction Log 3
> >> >
> >> > And:
> >> >
> >> > Array 1, RAID 1 (2xSCSI) OS
> >> > Array 2, RAID 1 (2xSCSI) PageFile
> >> > Array 3, RAID 1 (2xSCSI) TempDB
> >> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
> >> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
> >> > Array 6, RAID 1 (2xSATA) File containing BLOB's
> >> > Array 7, RAID 1 (2xSATA) Transaction Log 1
> >> > Array 8, RAID 1 (2xSATA) Transaction Log 2
> >> > Array 9, RAID 1 (2xSATA) Transaction Log 3
> >> >
> >> > ...I'll be doing some further reading, but just wondered if anyone had
> >> > any
> >> > input on this?
> >> >
> >> > Thanks in advance Ben
> >>
> >>
> >>
>
>|||OK, I just wanted to check up on these points first, because they're often
missed with new SQL server implementations.
Back to your original qns then:
Firstly, drop the idea of a seperate Array for your page file b/c well
configured, dedicated database servers should perform very little IO against
page files. Page files are for machines where many processes need to share
virtual memory & dedicated servers generally do very little of this -
certainly not enought to warrant dedicating an array to. Far better to
dedicate this array to a tlog or some other useful purpose
Seperating objects down to the granularity you've outlined in the last
option. The problem with this approach is that you're making assumptions
about how your IO should be partitioned where it's often far easier to leave
these objects on a single array & let SQL Server spread the load accross
them. Another way of looking at this scenario is that if any one object type
experiences heavy IO, it has fewer disks to achieve its workload with.
Short of more specific information about the actual workload performed by
the various DB objects, I'd say that your initial configuration is a very
good starting point.
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:94A8EB85-786A-415D-BA47-8E75777744A1@.microsoft.com...
> Memory isn't an option, it's 32bit SQL 2000 on 32bit windows... ...hence
> I'm
> trying to compensate with disks and find the best raid config I can...
> "Greg Linwood" wrote:
>> Hi Ben
>> This depends entirely on whether you're using SQL 2000 or SQL 2005 & 32
>> bit
>> vs 64 bit. For example, if you're using 64 bit Windows 2003 Server
>> Standard
>> Edition and 64 bit SQL 2005 Standard Edition, you can access up to 32Gb
>> Memory.
>> That's why I asked whether you're using SQL 2000 or SQL 2005.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
>> > We're using Windows 2003 Standard Edition, and SQL Standard Edition...
>> > ...so
>> > more memory wasn't really an option.. ..the cost of disks are cheap in
>> > comparison to the upgrade to Enterprise Edition of SQL - we have to
>> > purchase
>> > the processor licenses. We haven't actually purchased the disks yet,
>> > if
>> > there's only a small benefit between say 12 and 18 disks we'll only buy
>> > the
>> > 12...
>> >
>> > "Greg Linwood" wrote:
>> >
>> >> Hi Ben
>> >>
>> >> Why did you buy so many disks & such a small amount of memory? Are you
>> >> using
>> >> SQL 2000 or SQL 2005? Which version of Windows? These are actually all
>> >> quite
>> >> important factors as they define your memory constraints & influence
>> >> whether
>> >> you should configure any of your disks for specialist read / write
>> >> activity.
>> >> The less memory you have, the more you push IO down to your disk
>> >> sub-system..
>> >>
>> >> Regards,
>> >> Greg Linwood
>> >> SQL Server MVP
>> >>
>> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> >> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
>> >> >
>> >> > We have just bought a new box for our SQL Database's.. ..We have
>> >> > three
>> >> > database's, 1 is by far the most intensive, it's a 3rd party
>> >> > application
>> >> > database and is around 20gb.. ..it uses the temp db heavily.. ..and
>> >> > the
>> >> > transaction log gets up to about 750mb - 1gb before it's backed up
>> >> > every
>> >> > 15minutes. The other databases are 20gb and 10gb respectively, the
>> >> > 20gb
>> >> > database contains 15gb of BLOB's.
>> >> > The box we have bought has dual xeons and 4gb of RAM, and we have 18
>> >> > disks
>> >> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a
>> >> > Smart
>> >> > Array.
>> >> >
>> >> > My initial views for the RAID configuration were as follows:
>> >> >
>> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
>> >> > Array 2, RAID 1 (2xSCSI) TempDB
>> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
>> >> > Array 4, RAID 10 (10xSATA) All Datafiles
>> >> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
>> >> >
>> >> > Although I have also looked at:
>> >> >
>> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
>> >> > Array 2, RAID 1 (2xSCSI) TempDB
>> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
>> >> > Array 4, RAID 10 (6xSATA) All Datafiles
>> >> > Array 5, RAID 10 (2xSATA) File containing BLOB's
>> >> > Array 6, RAID 1 (2xSATA) Transaction Log 2
>> >> > Array 7, RAID 1 (2xSATA) Transaction Log 3
>> >> >
>> >> > And:
>> >> >
>> >> > Array 1, RAID 1 (2xSCSI) OS
>> >> > Array 2, RAID 1 (2xSCSI) PageFile
>> >> > Array 3, RAID 1 (2xSCSI) TempDB
>> >> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
>> >> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
>> >> > Array 6, RAID 1 (2xSATA) File containing BLOB's
>> >> > Array 7, RAID 1 (2xSATA) Transaction Log 1
>> >> > Array 8, RAID 1 (2xSATA) Transaction Log 2
>> >> > Array 9, RAID 1 (2xSATA) Transaction Log 3
>> >> >
>> >> > ...I'll be doing some further reading, but just wondered if anyone
>> >> > had
>> >> > any
>> >> > input on this?
>> >> >
>> >> > Thanks in advance Ben
>> >>
>> >>
>> >>
>>|||Smashing thanks Greg, in the original configuration I have 2 transaction logs
sharing an array... ...these logs don't grow above 250mb over 15minutes...
...but I'm wondering whether it's worth splitting them out onto seperate
arrays? - I'd have to drop the RAID 10 array from 10 to 8 disks to make
room... ...but I've read it's best to seperate Transaction Logs onto
seperate arrays so they can be sequential... ...I'm unsure if the benefit of
having them on seperate disks would be less than having the 10 disks in the
RAID 10 array... ...I guess it's one of those things I'll find out with some
testing :-) Anyway thanks again for the responses..
"Greg Linwood" wrote:
> OK, I just wanted to check up on these points first, because they're often
> missed with new SQL server implementations.
> Back to your original qns then:
> Firstly, drop the idea of a seperate Array for your page file b/c well
> configured, dedicated database servers should perform very little IO against
> page files. Page files are for machines where many processes need to share
> virtual memory & dedicated servers generally do very little of this -
> certainly not enought to warrant dedicating an array to. Far better to
> dedicate this array to a tlog or some other useful purpose
> Seperating objects down to the granularity you've outlined in the last
> option. The problem with this approach is that you're making assumptions
> about how your IO should be partitioned where it's often far easier to leave
> these objects on a single array & let SQL Server spread the load accross
> them. Another way of looking at this scenario is that if any one object type
> experiences heavy IO, it has fewer disks to achieve its workload with.
> Short of more specific information about the actual workload performed by
> the various DB objects, I'd say that your initial configuration is a very
> good starting point.
> Regards,
> Greg Linwood
> SQL Server MVP
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:94A8EB85-786A-415D-BA47-8E75777744A1@.microsoft.com...
> > Memory isn't an option, it's 32bit SQL 2000 on 32bit windows... ...hence
> > I'm
> > trying to compensate with disks and find the best raid config I can...
> >
> > "Greg Linwood" wrote:
> >
> >> Hi Ben
> >>
> >> This depends entirely on whether you're using SQL 2000 or SQL 2005 & 32
> >> bit
> >> vs 64 bit. For example, if you're using 64 bit Windows 2003 Server
> >> Standard
> >> Edition and 64 bit SQL 2005 Standard Edition, you can access up to 32Gb
> >> Memory.
> >>
> >> That's why I asked whether you're using SQL 2000 or SQL 2005.
> >>
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP
> >>
> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
> >> > We're using Windows 2003 Standard Edition, and SQL Standard Edition...
> >> > ...so
> >> > more memory wasn't really an option.. ..the cost of disks are cheap in
> >> > comparison to the upgrade to Enterprise Edition of SQL - we have to
> >> > purchase
> >> > the processor licenses. We haven't actually purchased the disks yet,
> >> > if
> >> > there's only a small benefit between say 12 and 18 disks we'll only buy
> >> > the
> >> > 12...
> >> >
> >> > "Greg Linwood" wrote:
> >> >
> >> >> Hi Ben
> >> >>
> >> >> Why did you buy so many disks & such a small amount of memory? Are you
> >> >> using
> >> >> SQL 2000 or SQL 2005? Which version of Windows? These are actually all
> >> >> quite
> >> >> important factors as they define your memory constraints & influence
> >> >> whether
> >> >> you should configure any of your disks for specialist read / write
> >> >> activity.
> >> >> The less memory you have, the more you push IO down to your disk
> >> >> sub-system..
> >> >>
> >> >> Regards,
> >> >> Greg Linwood
> >> >> SQL Server MVP
> >> >>
> >> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> >> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
> >> >> >
> >> >> > We have just bought a new box for our SQL Database's.. ..We have
> >> >> > three
> >> >> > database's, 1 is by far the most intensive, it's a 3rd party
> >> >> > application
> >> >> > database and is around 20gb.. ..it uses the temp db heavily.. ..and
> >> >> > the
> >> >> > transaction log gets up to about 750mb - 1gb before it's backed up
> >> >> > every
> >> >> > 15minutes. The other databases are 20gb and 10gb respectively, the
> >> >> > 20gb
> >> >> > database contains 15gb of BLOB's.
> >> >> > The box we have bought has dual xeons and 4gb of RAM, and we have 18
> >> >> > disks
> >> >> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a
> >> >> > Smart
> >> >> > Array.
> >> >> >
> >> >> > My initial views for the RAID configuration were as follows:
> >> >> >
> >> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> >> >> > Array 2, RAID 1 (2xSCSI) TempDB
> >> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> >> >> > Array 4, RAID 10 (10xSATA) All Datafiles
> >> >> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
> >> >> >
> >> >> > Although I have also looked at:
> >> >> >
> >> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> >> >> > Array 2, RAID 1 (2xSCSI) TempDB
> >> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> >> >> > Array 4, RAID 10 (6xSATA) All Datafiles
> >> >> > Array 5, RAID 10 (2xSATA) File containing BLOB's
> >> >> > Array 6, RAID 1 (2xSATA) Transaction Log 2
> >> >> > Array 7, RAID 1 (2xSATA) Transaction Log 3
> >> >> >
> >> >> > And:
> >> >> >
> >> >> > Array 1, RAID 1 (2xSCSI) OS
> >> >> > Array 2, RAID 1 (2xSCSI) PageFile
> >> >> > Array 3, RAID 1 (2xSCSI) TempDB
> >> >> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
> >> >> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
> >> >> > Array 6, RAID 1 (2xSATA) File containing BLOB's
> >> >> > Array 7, RAID 1 (2xSATA) Transaction Log 1
> >> >> > Array 8, RAID 1 (2xSATA) Transaction Log 2
> >> >> > Array 9, RAID 1 (2xSATA) Transaction Log 3
> >> >> >
> >> >> > ...I'll be doing some further reading, but just wondered if anyone
> >> >> > had
> >> >> > any
> >> >> > input on this?
> >> >> >
> >> >> > Thanks in advance Ben
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Hi Ben
I suggest you don't seperate those logs out then, as 250Mb in 15 mins is
quite moderate & you could probably find better uses for those disks.
Speaking of which - one thing I missed when I first looked at your
suggestions is that you don't have a local drive for backups. Are you
planning to do these off onto a network or tape? If not, I suggest you don't
forget a backup volume in your plan & those extra disks might come in handy
for this purpose...
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:ADDF9C2C-FC93-447F-9F44-E554316C36F6@.microsoft.com...
> Smashing thanks Greg, in the original configuration I have 2 transaction
> logs
> sharing an array... ...these logs don't grow above 250mb over 15minutes...
> ...but I'm wondering whether it's worth splitting them out onto seperate
> arrays? - I'd have to drop the RAID 10 array from 10 to 8 disks to make
> room... ...but I've read it's best to seperate Transaction Logs onto
> seperate arrays so they can be sequential... ...I'm unsure if the benefit
> of
> having them on seperate disks would be less than having the 10 disks in
> the
> RAID 10 array... ...I guess it's one of those things I'll find out with
> some
> testing :-) Anyway thanks again for the responses..
> "Greg Linwood" wrote:
>> OK, I just wanted to check up on these points first, because they're
>> often
>> missed with new SQL server implementations.
>> Back to your original qns then:
>> Firstly, drop the idea of a seperate Array for your page file b/c well
>> configured, dedicated database servers should perform very little IO
>> against
>> page files. Page files are for machines where many processes need to
>> share
>> virtual memory & dedicated servers generally do very little of this -
>> certainly not enought to warrant dedicating an array to. Far better to
>> dedicate this array to a tlog or some other useful purpose
>> Seperating objects down to the granularity you've outlined in the last
>> option. The problem with this approach is that you're making assumptions
>> about how your IO should be partitioned where it's often far easier to
>> leave
>> these objects on a single array & let SQL Server spread the load accross
>> them. Another way of looking at this scenario is that if any one object
>> type
>> experiences heavy IO, it has fewer disks to achieve its workload with.
>> Short of more specific information about the actual workload performed by
>> the various DB objects, I'd say that your initial configuration is a very
>> good starting point.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>>
>> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> news:94A8EB85-786A-415D-BA47-8E75777744A1@.microsoft.com...
>> > Memory isn't an option, it's 32bit SQL 2000 on 32bit windows...
>> > ...hence
>> > I'm
>> > trying to compensate with disks and find the best raid config I can...
>> >
>> > "Greg Linwood" wrote:
>> >
>> >> Hi Ben
>> >>
>> >> This depends entirely on whether you're using SQL 2000 or SQL 2005 &
>> >> 32
>> >> bit
>> >> vs 64 bit. For example, if you're using 64 bit Windows 2003 Server
>> >> Standard
>> >> Edition and 64 bit SQL 2005 Standard Edition, you can access up to
>> >> 32Gb
>> >> Memory.
>> >>
>> >> That's why I asked whether you're using SQL 2000 or SQL 2005.
>> >>
>> >> Regards,
>> >> Greg Linwood
>> >> SQL Server MVP
>> >>
>> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> >> news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
>> >> > We're using Windows 2003 Standard Edition, and SQL Standard
>> >> > Edition...
>> >> > ...so
>> >> > more memory wasn't really an option.. ..the cost of disks are cheap
>> >> > in
>> >> > comparison to the upgrade to Enterprise Edition of SQL - we have to
>> >> > purchase
>> >> > the processor licenses. We haven't actually purchased the disks
>> >> > yet,
>> >> > if
>> >> > there's only a small benefit between say 12 and 18 disks we'll only
>> >> > buy
>> >> > the
>> >> > 12...
>> >> >
>> >> > "Greg Linwood" wrote:
>> >> >
>> >> >> Hi Ben
>> >> >>
>> >> >> Why did you buy so many disks & such a small amount of memory? Are
>> >> >> you
>> >> >> using
>> >> >> SQL 2000 or SQL 2005? Which version of Windows? These are actually
>> >> >> all
>> >> >> quite
>> >> >> important factors as they define your memory constraints &
>> >> >> influence
>> >> >> whether
>> >> >> you should configure any of your disks for specialist read / write
>> >> >> activity.
>> >> >> The less memory you have, the more you push IO down to your disk
>> >> >> sub-system..
>> >> >>
>> >> >> Regards,
>> >> >> Greg Linwood
>> >> >> SQL Server MVP
>> >> >>
>> >> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> >> >> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
>> >> >> >
>> >> >> > We have just bought a new box for our SQL Database's.. ..We have
>> >> >> > three
>> >> >> > database's, 1 is by far the most intensive, it's a 3rd party
>> >> >> > application
>> >> >> > database and is around 20gb.. ..it uses the temp db heavily..
>> >> >> > ..and
>> >> >> > the
>> >> >> > transaction log gets up to about 750mb - 1gb before it's backed
>> >> >> > up
>> >> >> > every
>> >> >> > 15minutes. The other databases are 20gb and 10gb respectively,
>> >> >> > the
>> >> >> > 20gb
>> >> >> > database contains 15gb of BLOB's.
>> >> >> > The box we have bought has dual xeons and 4gb of RAM, and we have
>> >> >> > 18
>> >> >> > disks
>> >> >> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in
>> >> >> > a
>> >> >> > Smart
>> >> >> > Array.
>> >> >> >
>> >> >> > My initial views for the RAID configuration were as follows:
>> >> >> >
>> >> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
>> >> >> > Array 2, RAID 1 (2xSCSI) TempDB
>> >> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
>> >> >> > Array 4, RAID 10 (10xSATA) All Datafiles
>> >> >> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
>> >> >> >
>> >> >> > Although I have also looked at:
>> >> >> >
>> >> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
>> >> >> > Array 2, RAID 1 (2xSCSI) TempDB
>> >> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
>> >> >> > Array 4, RAID 10 (6xSATA) All Datafiles
>> >> >> > Array 5, RAID 10 (2xSATA) File containing BLOB's
>> >> >> > Array 6, RAID 1 (2xSATA) Transaction Log 2
>> >> >> > Array 7, RAID 1 (2xSATA) Transaction Log 3
>> >> >> >
>> >> >> > And:
>> >> >> >
>> >> >> > Array 1, RAID 1 (2xSCSI) OS
>> >> >> > Array 2, RAID 1 (2xSCSI) PageFile
>> >> >> > Array 3, RAID 1 (2xSCSI) TempDB
>> >> >> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
>> >> >> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
>> >> >> > Array 6, RAID 1 (2xSATA) File containing BLOB's
>> >> >> > Array 7, RAID 1 (2xSATA) Transaction Log 1
>> >> >> > Array 8, RAID 1 (2xSATA) Transaction Log 2
>> >> >> > Array 9, RAID 1 (2xSATA) Transaction Log 3
>> >> >> >
>> >> >> > ...I'll be doing some further reading, but just wondered if
>> >> >> > anyone
>> >> >> > had
>> >> >> > any
>> >> >> > input on this?
>> >> >> >
>> >> >> > Thanks in advance Ben
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||We currently backup over the network and then onto tape.. ..but it's worth
thinking about, thanks again for the help :-)
"Greg Linwood" wrote:
> Hi Ben
> I suggest you don't seperate those logs out then, as 250Mb in 15 mins is
> quite moderate & you could probably find better uses for those disks.
> Speaking of which - one thing I missed when I first looked at your
> suggestions is that you don't have a local drive for backups. Are you
> planning to do these off onto a network or tape? If not, I suggest you don't
> forget a backup volume in your plan & those extra disks might come in handy
> for this purpose...
> Regards,
> Greg Linwood
> SQL Server MVP
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:ADDF9C2C-FC93-447F-9F44-E554316C36F6@.microsoft.com...
> > Smashing thanks Greg, in the original configuration I have 2 transaction
> > logs
> > sharing an array... ...these logs don't grow above 250mb over 15minutes...
> > ...but I'm wondering whether it's worth splitting them out onto seperate
> > arrays? - I'd have to drop the RAID 10 array from 10 to 8 disks to make
> > room... ...but I've read it's best to seperate Transaction Logs onto
> > seperate arrays so they can be sequential... ...I'm unsure if the benefit
> > of
> > having them on seperate disks would be less than having the 10 disks in
> > the
> > RAID 10 array... ...I guess it's one of those things I'll find out with
> > some
> > testing :-) Anyway thanks again for the responses..
> >
> > "Greg Linwood" wrote:
> >
> >> OK, I just wanted to check up on these points first, because they're
> >> often
> >> missed with new SQL server implementations.
> >>
> >> Back to your original qns then:
> >>
> >> Firstly, drop the idea of a seperate Array for your page file b/c well
> >> configured, dedicated database servers should perform very little IO
> >> against
> >> page files. Page files are for machines where many processes need to
> >> share
> >> virtual memory & dedicated servers generally do very little of this -
> >> certainly not enought to warrant dedicating an array to. Far better to
> >> dedicate this array to a tlog or some other useful purpose
> >>
> >> Seperating objects down to the granularity you've outlined in the last
> >> option. The problem with this approach is that you're making assumptions
> >> about how your IO should be partitioned where it's often far easier to
> >> leave
> >> these objects on a single array & let SQL Server spread the load accross
> >> them. Another way of looking at this scenario is that if any one object
> >> type
> >> experiences heavy IO, it has fewer disks to achieve its workload with.
> >>
> >> Short of more specific information about the actual workload performed by
> >> the various DB objects, I'd say that your initial configuration is a very
> >> good starting point.
> >>
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP
> >>
> >>
> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> news:94A8EB85-786A-415D-BA47-8E75777744A1@.microsoft.com...
> >> > Memory isn't an option, it's 32bit SQL 2000 on 32bit windows...
> >> > ...hence
> >> > I'm
> >> > trying to compensate with disks and find the best raid config I can...
> >> >
> >> > "Greg Linwood" wrote:
> >> >
> >> >> Hi Ben
> >> >>
> >> >> This depends entirely on whether you're using SQL 2000 or SQL 2005 &
> >> >> 32
> >> >> bit
> >> >> vs 64 bit. For example, if you're using 64 bit Windows 2003 Server
> >> >> Standard
> >> >> Edition and 64 bit SQL 2005 Standard Edition, you can access up to
> >> >> 32Gb
> >> >> Memory.
> >> >>
> >> >> That's why I asked whether you're using SQL 2000 or SQL 2005.
> >> >>
> >> >> Regards,
> >> >> Greg Linwood
> >> >> SQL Server MVP
> >> >>
> >> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> >> news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
> >> >> > We're using Windows 2003 Standard Edition, and SQL Standard
> >> >> > Edition...
> >> >> > ...so
> >> >> > more memory wasn't really an option.. ..the cost of disks are cheap
> >> >> > in
> >> >> > comparison to the upgrade to Enterprise Edition of SQL - we have to
> >> >> > purchase
> >> >> > the processor licenses. We haven't actually purchased the disks
> >> >> > yet,
> >> >> > if
> >> >> > there's only a small benefit between say 12 and 18 disks we'll only
> >> >> > buy
> >> >> > the
> >> >> > 12...
> >> >> >
> >> >> > "Greg Linwood" wrote:
> >> >> >
> >> >> >> Hi Ben
> >> >> >>
> >> >> >> Why did you buy so many disks & such a small amount of memory? Are
> >> >> >> you
> >> >> >> using
> >> >> >> SQL 2000 or SQL 2005? Which version of Windows? These are actually
> >> >> >> all
> >> >> >> quite
> >> >> >> important factors as they define your memory constraints &
> >> >> >> influence
> >> >> >> whether
> >> >> >> you should configure any of your disks for specialist read / write
> >> >> >> activity.
> >> >> >> The less memory you have, the more you push IO down to your disk
> >> >> >> sub-system..
> >> >> >>
> >> >> >> Regards,
> >> >> >> Greg Linwood
> >> >> >> SQL Server MVP
> >> >> >>
> >> >> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> >> >> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
> >> >> >> >
> >> >> >> > We have just bought a new box for our SQL Database's.. ..We have
> >> >> >> > three
> >> >> >> > database's, 1 is by far the most intensive, it's a 3rd party
> >> >> >> > application
> >> >> >> > database and is around 20gb.. ..it uses the temp db heavily..
> >> >> >> > ..and
> >> >> >> > the
> >> >> >> > transaction log gets up to about 750mb - 1gb before it's backed
> >> >> >> > up
> >> >> >> > every
> >> >> >> > 15minutes. The other databases are 20gb and 10gb respectively,
> >> >> >> > the
> >> >> >> > 20gb
> >> >> >> > database contains 15gb of BLOB's.
> >> >> >> > The box we have bought has dual xeons and 4gb of RAM, and we have
> >> >> >> > 18
> >> >> >> > disks
> >> >> >> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in
> >> >> >> > a
> >> >> >> > Smart
> >> >> >> > Array.
> >> >> >> >
> >> >> >> > My initial views for the RAID configuration were as follows:
> >> >> >> >
> >> >> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> >> >> >> > Array 2, RAID 1 (2xSCSI) TempDB
> >> >> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> >> >> >> > Array 4, RAID 10 (10xSATA) All Datafiles
> >> >> >> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
> >> >> >> >
> >> >> >> > Although I have also looked at:
> >> >> >> >
> >> >> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> >> >> >> > Array 2, RAID 1 (2xSCSI) TempDB
> >> >> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> >> >> >> > Array 4, RAID 10 (6xSATA) All Datafiles
> >> >> >> > Array 5, RAID 10 (2xSATA) File containing BLOB's
> >> >> >> > Array 6, RAID 1 (2xSATA) Transaction Log 2
> >> >> >> > Array 7, RAID 1 (2xSATA) Transaction Log 3
> >> >> >> >
> >> >> >> > And:
> >> >> >> >
> >> >> >> > Array 1, RAID 1 (2xSCSI) OS
> >> >> >> > Array 2, RAID 1 (2xSCSI) PageFile
> >> >> >> > Array 3, RAID 1 (2xSCSI) TempDB
> >> >> >> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
> >> >> >> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
> >> >> >> > Array 6, RAID 1 (2xSATA) File containing BLOB's
> >> >> >> > Array 7, RAID 1 (2xSATA) Transaction Log 1
> >> >> >> > Array 8, RAID 1 (2xSATA) Transaction Log 2
> >> >> >> > Array 9, RAID 1 (2xSATA) Transaction Log 3
> >> >> >> >
> >> >> >> > ...I'll be doing some further reading, but just wondered if
> >> >> >> > anyone
> >> >> >> > had
> >> >> >> > any
> >> >> >> > input on this?
> >> >> >> >
> >> >> >> > Thanks in advance Ben
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
database's, 1 is by far the most intensive, it's a 3rd party application
database and is around 20gb.. ..it uses the temp db heavily.. ..and the
transaction log gets up to about 750mb - 1gb before it's backed up every
15minutes. The other databases are 20gb and 10gb respectively, the 20gb
database contains 15gb of BLOB's.
The box we have bought has dual xeons and 4gb of RAM, and we have 18 disks
to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a Smart
Array.
My initial views for the RAID configuration were as follows:
Array 1, RAID 1 (2xSCSI) OS & Pagefile
Array 2, RAID 1 (2xSCSI) TempDB
Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
Array 4, RAID 10 (10xSATA) All Datafiles
Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
Although I have also looked at:
Array 1, RAID 1 (2xSCSI) OS & Pagefile
Array 2, RAID 1 (2xSCSI) TempDB
Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
Array 4, RAID 10 (6xSATA) All Datafiles
Array 5, RAID 10 (2xSATA) File containing BLOB's
Array 6, RAID 1 (2xSATA) Transaction Log 2
Array 7, RAID 1 (2xSATA) Transaction Log 3
And:
Array 1, RAID 1 (2xSCSI) OS
Array 2, RAID 1 (2xSCSI) PageFile
Array 3, RAID 1 (2xSCSI) TempDB
Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
Array 6, RAID 1 (2xSATA) File containing BLOB's
Array 7, RAID 1 (2xSATA) Transaction Log 1
Array 8, RAID 1 (2xSATA) Transaction Log 2
Array 9, RAID 1 (2xSATA) Transaction Log 3
...I'll be doing some further reading, but just wondered if anyone had any
input on this?
Thanks in advance BenHi Ben
Why did you buy so many disks & such a small amount of memory? Are you using
SQL 2000 or SQL 2005? Which version of Windows? These are actually all quite
important factors as they define your memory constraints & influence whether
you should configure any of your disks for specialist read / write activity.
The less memory you have, the more you push IO down to your disk
sub-system..
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
> We have just bought a new box for our SQL Database's.. ..We have three
> database's, 1 is by far the most intensive, it's a 3rd party application
> database and is around 20gb.. ..it uses the temp db heavily.. ..and the
> transaction log gets up to about 750mb - 1gb before it's backed up every
> 15minutes. The other databases are 20gb and 10gb respectively, the 20gb
> database contains 15gb of BLOB's.
> The box we have bought has dual xeons and 4gb of RAM, and we have 18 disks
> to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a Smart
> Array.
> My initial views for the RAID configuration were as follows:
> Array 1, RAID 1 (2xSCSI) OS & Pagefile
> Array 2, RAID 1 (2xSCSI) TempDB
> Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> Array 4, RAID 10 (10xSATA) All Datafiles
> Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
> Although I have also looked at:
> Array 1, RAID 1 (2xSCSI) OS & Pagefile
> Array 2, RAID 1 (2xSCSI) TempDB
> Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> Array 4, RAID 10 (6xSATA) All Datafiles
> Array 5, RAID 10 (2xSATA) File containing BLOB's
> Array 6, RAID 1 (2xSATA) Transaction Log 2
> Array 7, RAID 1 (2xSATA) Transaction Log 3
> And:
> Array 1, RAID 1 (2xSCSI) OS
> Array 2, RAID 1 (2xSCSI) PageFile
> Array 3, RAID 1 (2xSCSI) TempDB
> Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
> Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
> Array 6, RAID 1 (2xSATA) File containing BLOB's
> Array 7, RAID 1 (2xSATA) Transaction Log 1
> Array 8, RAID 1 (2xSATA) Transaction Log 2
> Array 9, RAID 1 (2xSATA) Transaction Log 3
> ...I'll be doing some further reading, but just wondered if anyone had any
> input on this?
> Thanks in advance Ben|||We're using Windows 2003 Standard Edition, and SQL Standard Edition... ...so
more memory wasn't really an option.. ..the cost of disks are cheap in
comparison to the upgrade to Enterprise Edition of SQL - we have to purchase
the processor licenses. We haven't actually purchased the disks yet, if
there's only a small benefit between say 12 and 18 disks we'll only buy the
12...
"Greg Linwood" wrote:
> Hi Ben
> Why did you buy so many disks & such a small amount of memory? Are you using
> SQL 2000 or SQL 2005? Which version of Windows? These are actually all quite
> important factors as they define your memory constraints & influence whether
> you should configure any of your disks for specialist read / write activity.
> The less memory you have, the more you push IO down to your disk
> sub-system..
> Regards,
> Greg Linwood
> SQL Server MVP
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
> >
> > We have just bought a new box for our SQL Database's.. ..We have three
> > database's, 1 is by far the most intensive, it's a 3rd party application
> > database and is around 20gb.. ..it uses the temp db heavily.. ..and the
> > transaction log gets up to about 750mb - 1gb before it's backed up every
> > 15minutes. The other databases are 20gb and 10gb respectively, the 20gb
> > database contains 15gb of BLOB's.
> > The box we have bought has dual xeons and 4gb of RAM, and we have 18 disks
> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a Smart
> > Array.
> >
> > My initial views for the RAID configuration were as follows:
> >
> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> > Array 2, RAID 1 (2xSCSI) TempDB
> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> > Array 4, RAID 10 (10xSATA) All Datafiles
> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
> >
> > Although I have also looked at:
> >
> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> > Array 2, RAID 1 (2xSCSI) TempDB
> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> > Array 4, RAID 10 (6xSATA) All Datafiles
> > Array 5, RAID 10 (2xSATA) File containing BLOB's
> > Array 6, RAID 1 (2xSATA) Transaction Log 2
> > Array 7, RAID 1 (2xSATA) Transaction Log 3
> >
> > And:
> >
> > Array 1, RAID 1 (2xSCSI) OS
> > Array 2, RAID 1 (2xSCSI) PageFile
> > Array 3, RAID 1 (2xSCSI) TempDB
> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
> > Array 6, RAID 1 (2xSATA) File containing BLOB's
> > Array 7, RAID 1 (2xSATA) Transaction Log 1
> > Array 8, RAID 1 (2xSATA) Transaction Log 2
> > Array 9, RAID 1 (2xSATA) Transaction Log 3
> >
> > ...I'll be doing some further reading, but just wondered if anyone had any
> > input on this?
> >
> > Thanks in advance Ben
>
>|||Hi Ben
This depends entirely on whether you're using SQL 2000 or SQL 2005 & 32 bit
vs 64 bit. For example, if you're using 64 bit Windows 2003 Server Standard
Edition and 64 bit SQL 2005 Standard Edition, you can access up to 32Gb
Memory.
That's why I asked whether you're using SQL 2000 or SQL 2005.
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
> We're using Windows 2003 Standard Edition, and SQL Standard Edition...
> ...so
> more memory wasn't really an option.. ..the cost of disks are cheap in
> comparison to the upgrade to Enterprise Edition of SQL - we have to
> purchase
> the processor licenses. We haven't actually purchased the disks yet, if
> there's only a small benefit between say 12 and 18 disks we'll only buy
> the
> 12...
> "Greg Linwood" wrote:
>> Hi Ben
>> Why did you buy so many disks & such a small amount of memory? Are you
>> using
>> SQL 2000 or SQL 2005? Which version of Windows? These are actually all
>> quite
>> important factors as they define your memory constraints & influence
>> whether
>> you should configure any of your disks for specialist read / write
>> activity.
>> The less memory you have, the more you push IO down to your disk
>> sub-system..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
>> >
>> > We have just bought a new box for our SQL Database's.. ..We have three
>> > database's, 1 is by far the most intensive, it's a 3rd party
>> > application
>> > database and is around 20gb.. ..it uses the temp db heavily.. ..and the
>> > transaction log gets up to about 750mb - 1gb before it's backed up
>> > every
>> > 15minutes. The other databases are 20gb and 10gb respectively, the
>> > 20gb
>> > database contains 15gb of BLOB's.
>> > The box we have bought has dual xeons and 4gb of RAM, and we have 18
>> > disks
>> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a
>> > Smart
>> > Array.
>> >
>> > My initial views for the RAID configuration were as follows:
>> >
>> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
>> > Array 2, RAID 1 (2xSCSI) TempDB
>> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
>> > Array 4, RAID 10 (10xSATA) All Datafiles
>> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
>> >
>> > Although I have also looked at:
>> >
>> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
>> > Array 2, RAID 1 (2xSCSI) TempDB
>> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
>> > Array 4, RAID 10 (6xSATA) All Datafiles
>> > Array 5, RAID 10 (2xSATA) File containing BLOB's
>> > Array 6, RAID 1 (2xSATA) Transaction Log 2
>> > Array 7, RAID 1 (2xSATA) Transaction Log 3
>> >
>> > And:
>> >
>> > Array 1, RAID 1 (2xSCSI) OS
>> > Array 2, RAID 1 (2xSCSI) PageFile
>> > Array 3, RAID 1 (2xSCSI) TempDB
>> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
>> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
>> > Array 6, RAID 1 (2xSATA) File containing BLOB's
>> > Array 7, RAID 1 (2xSATA) Transaction Log 1
>> > Array 8, RAID 1 (2xSATA) Transaction Log 2
>> > Array 9, RAID 1 (2xSATA) Transaction Log 3
>> >
>> > ...I'll be doing some further reading, but just wondered if anyone had
>> > any
>> > input on this?
>> >
>> > Thanks in advance Ben
>>|||Memory isn't an option, it's 32bit SQL 2000 on 32bit windows... ...hence I'm
trying to compensate with disks and find the best raid config I can...
"Greg Linwood" wrote:
> Hi Ben
> This depends entirely on whether you're using SQL 2000 or SQL 2005 & 32 bit
> vs 64 bit. For example, if you're using 64 bit Windows 2003 Server Standard
> Edition and 64 bit SQL 2005 Standard Edition, you can access up to 32Gb
> Memory.
> That's why I asked whether you're using SQL 2000 or SQL 2005.
> Regards,
> Greg Linwood
> SQL Server MVP
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
> > We're using Windows 2003 Standard Edition, and SQL Standard Edition...
> > ...so
> > more memory wasn't really an option.. ..the cost of disks are cheap in
> > comparison to the upgrade to Enterprise Edition of SQL - we have to
> > purchase
> > the processor licenses. We haven't actually purchased the disks yet, if
> > there's only a small benefit between say 12 and 18 disks we'll only buy
> > the
> > 12...
> >
> > "Greg Linwood" wrote:
> >
> >> Hi Ben
> >>
> >> Why did you buy so many disks & such a small amount of memory? Are you
> >> using
> >> SQL 2000 or SQL 2005? Which version of Windows? These are actually all
> >> quite
> >> important factors as they define your memory constraints & influence
> >> whether
> >> you should configure any of your disks for specialist read / write
> >> activity.
> >> The less memory you have, the more you push IO down to your disk
> >> sub-system..
> >>
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP
> >>
> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
> >> >
> >> > We have just bought a new box for our SQL Database's.. ..We have three
> >> > database's, 1 is by far the most intensive, it's a 3rd party
> >> > application
> >> > database and is around 20gb.. ..it uses the temp db heavily.. ..and the
> >> > transaction log gets up to about 750mb - 1gb before it's backed up
> >> > every
> >> > 15minutes. The other databases are 20gb and 10gb respectively, the
> >> > 20gb
> >> > database contains 15gb of BLOB's.
> >> > The box we have bought has dual xeons and 4gb of RAM, and we have 18
> >> > disks
> >> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a
> >> > Smart
> >> > Array.
> >> >
> >> > My initial views for the RAID configuration were as follows:
> >> >
> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> >> > Array 2, RAID 1 (2xSCSI) TempDB
> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> >> > Array 4, RAID 10 (10xSATA) All Datafiles
> >> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
> >> >
> >> > Although I have also looked at:
> >> >
> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> >> > Array 2, RAID 1 (2xSCSI) TempDB
> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> >> > Array 4, RAID 10 (6xSATA) All Datafiles
> >> > Array 5, RAID 10 (2xSATA) File containing BLOB's
> >> > Array 6, RAID 1 (2xSATA) Transaction Log 2
> >> > Array 7, RAID 1 (2xSATA) Transaction Log 3
> >> >
> >> > And:
> >> >
> >> > Array 1, RAID 1 (2xSCSI) OS
> >> > Array 2, RAID 1 (2xSCSI) PageFile
> >> > Array 3, RAID 1 (2xSCSI) TempDB
> >> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
> >> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
> >> > Array 6, RAID 1 (2xSATA) File containing BLOB's
> >> > Array 7, RAID 1 (2xSATA) Transaction Log 1
> >> > Array 8, RAID 1 (2xSATA) Transaction Log 2
> >> > Array 9, RAID 1 (2xSATA) Transaction Log 3
> >> >
> >> > ...I'll be doing some further reading, but just wondered if anyone had
> >> > any
> >> > input on this?
> >> >
> >> > Thanks in advance Ben
> >>
> >>
> >>
>
>|||OK, I just wanted to check up on these points first, because they're often
missed with new SQL server implementations.
Back to your original qns then:
Firstly, drop the idea of a seperate Array for your page file b/c well
configured, dedicated database servers should perform very little IO against
page files. Page files are for machines where many processes need to share
virtual memory & dedicated servers generally do very little of this -
certainly not enought to warrant dedicating an array to. Far better to
dedicate this array to a tlog or some other useful purpose
Seperating objects down to the granularity you've outlined in the last
option. The problem with this approach is that you're making assumptions
about how your IO should be partitioned where it's often far easier to leave
these objects on a single array & let SQL Server spread the load accross
them. Another way of looking at this scenario is that if any one object type
experiences heavy IO, it has fewer disks to achieve its workload with.
Short of more specific information about the actual workload performed by
the various DB objects, I'd say that your initial configuration is a very
good starting point.
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:94A8EB85-786A-415D-BA47-8E75777744A1@.microsoft.com...
> Memory isn't an option, it's 32bit SQL 2000 on 32bit windows... ...hence
> I'm
> trying to compensate with disks and find the best raid config I can...
> "Greg Linwood" wrote:
>> Hi Ben
>> This depends entirely on whether you're using SQL 2000 or SQL 2005 & 32
>> bit
>> vs 64 bit. For example, if you're using 64 bit Windows 2003 Server
>> Standard
>> Edition and 64 bit SQL 2005 Standard Edition, you can access up to 32Gb
>> Memory.
>> That's why I asked whether you're using SQL 2000 or SQL 2005.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
>> > We're using Windows 2003 Standard Edition, and SQL Standard Edition...
>> > ...so
>> > more memory wasn't really an option.. ..the cost of disks are cheap in
>> > comparison to the upgrade to Enterprise Edition of SQL - we have to
>> > purchase
>> > the processor licenses. We haven't actually purchased the disks yet,
>> > if
>> > there's only a small benefit between say 12 and 18 disks we'll only buy
>> > the
>> > 12...
>> >
>> > "Greg Linwood" wrote:
>> >
>> >> Hi Ben
>> >>
>> >> Why did you buy so many disks & such a small amount of memory? Are you
>> >> using
>> >> SQL 2000 or SQL 2005? Which version of Windows? These are actually all
>> >> quite
>> >> important factors as they define your memory constraints & influence
>> >> whether
>> >> you should configure any of your disks for specialist read / write
>> >> activity.
>> >> The less memory you have, the more you push IO down to your disk
>> >> sub-system..
>> >>
>> >> Regards,
>> >> Greg Linwood
>> >> SQL Server MVP
>> >>
>> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> >> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
>> >> >
>> >> > We have just bought a new box for our SQL Database's.. ..We have
>> >> > three
>> >> > database's, 1 is by far the most intensive, it's a 3rd party
>> >> > application
>> >> > database and is around 20gb.. ..it uses the temp db heavily.. ..and
>> >> > the
>> >> > transaction log gets up to about 750mb - 1gb before it's backed up
>> >> > every
>> >> > 15minutes. The other databases are 20gb and 10gb respectively, the
>> >> > 20gb
>> >> > database contains 15gb of BLOB's.
>> >> > The box we have bought has dual xeons and 4gb of RAM, and we have 18
>> >> > disks
>> >> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a
>> >> > Smart
>> >> > Array.
>> >> >
>> >> > My initial views for the RAID configuration were as follows:
>> >> >
>> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
>> >> > Array 2, RAID 1 (2xSCSI) TempDB
>> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
>> >> > Array 4, RAID 10 (10xSATA) All Datafiles
>> >> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
>> >> >
>> >> > Although I have also looked at:
>> >> >
>> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
>> >> > Array 2, RAID 1 (2xSCSI) TempDB
>> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
>> >> > Array 4, RAID 10 (6xSATA) All Datafiles
>> >> > Array 5, RAID 10 (2xSATA) File containing BLOB's
>> >> > Array 6, RAID 1 (2xSATA) Transaction Log 2
>> >> > Array 7, RAID 1 (2xSATA) Transaction Log 3
>> >> >
>> >> > And:
>> >> >
>> >> > Array 1, RAID 1 (2xSCSI) OS
>> >> > Array 2, RAID 1 (2xSCSI) PageFile
>> >> > Array 3, RAID 1 (2xSCSI) TempDB
>> >> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
>> >> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
>> >> > Array 6, RAID 1 (2xSATA) File containing BLOB's
>> >> > Array 7, RAID 1 (2xSATA) Transaction Log 1
>> >> > Array 8, RAID 1 (2xSATA) Transaction Log 2
>> >> > Array 9, RAID 1 (2xSATA) Transaction Log 3
>> >> >
>> >> > ...I'll be doing some further reading, but just wondered if anyone
>> >> > had
>> >> > any
>> >> > input on this?
>> >> >
>> >> > Thanks in advance Ben
>> >>
>> >>
>> >>
>>|||Smashing thanks Greg, in the original configuration I have 2 transaction logs
sharing an array... ...these logs don't grow above 250mb over 15minutes...
...but I'm wondering whether it's worth splitting them out onto seperate
arrays? - I'd have to drop the RAID 10 array from 10 to 8 disks to make
room... ...but I've read it's best to seperate Transaction Logs onto
seperate arrays so they can be sequential... ...I'm unsure if the benefit of
having them on seperate disks would be less than having the 10 disks in the
RAID 10 array... ...I guess it's one of those things I'll find out with some
testing :-) Anyway thanks again for the responses..
"Greg Linwood" wrote:
> OK, I just wanted to check up on these points first, because they're often
> missed with new SQL server implementations.
> Back to your original qns then:
> Firstly, drop the idea of a seperate Array for your page file b/c well
> configured, dedicated database servers should perform very little IO against
> page files. Page files are for machines where many processes need to share
> virtual memory & dedicated servers generally do very little of this -
> certainly not enought to warrant dedicating an array to. Far better to
> dedicate this array to a tlog or some other useful purpose
> Seperating objects down to the granularity you've outlined in the last
> option. The problem with this approach is that you're making assumptions
> about how your IO should be partitioned where it's often far easier to leave
> these objects on a single array & let SQL Server spread the load accross
> them. Another way of looking at this scenario is that if any one object type
> experiences heavy IO, it has fewer disks to achieve its workload with.
> Short of more specific information about the actual workload performed by
> the various DB objects, I'd say that your initial configuration is a very
> good starting point.
> Regards,
> Greg Linwood
> SQL Server MVP
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:94A8EB85-786A-415D-BA47-8E75777744A1@.microsoft.com...
> > Memory isn't an option, it's 32bit SQL 2000 on 32bit windows... ...hence
> > I'm
> > trying to compensate with disks and find the best raid config I can...
> >
> > "Greg Linwood" wrote:
> >
> >> Hi Ben
> >>
> >> This depends entirely on whether you're using SQL 2000 or SQL 2005 & 32
> >> bit
> >> vs 64 bit. For example, if you're using 64 bit Windows 2003 Server
> >> Standard
> >> Edition and 64 bit SQL 2005 Standard Edition, you can access up to 32Gb
> >> Memory.
> >>
> >> That's why I asked whether you're using SQL 2000 or SQL 2005.
> >>
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP
> >>
> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
> >> > We're using Windows 2003 Standard Edition, and SQL Standard Edition...
> >> > ...so
> >> > more memory wasn't really an option.. ..the cost of disks are cheap in
> >> > comparison to the upgrade to Enterprise Edition of SQL - we have to
> >> > purchase
> >> > the processor licenses. We haven't actually purchased the disks yet,
> >> > if
> >> > there's only a small benefit between say 12 and 18 disks we'll only buy
> >> > the
> >> > 12...
> >> >
> >> > "Greg Linwood" wrote:
> >> >
> >> >> Hi Ben
> >> >>
> >> >> Why did you buy so many disks & such a small amount of memory? Are you
> >> >> using
> >> >> SQL 2000 or SQL 2005? Which version of Windows? These are actually all
> >> >> quite
> >> >> important factors as they define your memory constraints & influence
> >> >> whether
> >> >> you should configure any of your disks for specialist read / write
> >> >> activity.
> >> >> The less memory you have, the more you push IO down to your disk
> >> >> sub-system..
> >> >>
> >> >> Regards,
> >> >> Greg Linwood
> >> >> SQL Server MVP
> >> >>
> >> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> >> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
> >> >> >
> >> >> > We have just bought a new box for our SQL Database's.. ..We have
> >> >> > three
> >> >> > database's, 1 is by far the most intensive, it's a 3rd party
> >> >> > application
> >> >> > database and is around 20gb.. ..it uses the temp db heavily.. ..and
> >> >> > the
> >> >> > transaction log gets up to about 750mb - 1gb before it's backed up
> >> >> > every
> >> >> > 15minutes. The other databases are 20gb and 10gb respectively, the
> >> >> > 20gb
> >> >> > database contains 15gb of BLOB's.
> >> >> > The box we have bought has dual xeons and 4gb of RAM, and we have 18
> >> >> > disks
> >> >> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in a
> >> >> > Smart
> >> >> > Array.
> >> >> >
> >> >> > My initial views for the RAID configuration were as follows:
> >> >> >
> >> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> >> >> > Array 2, RAID 1 (2xSCSI) TempDB
> >> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> >> >> > Array 4, RAID 10 (10xSATA) All Datafiles
> >> >> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
> >> >> >
> >> >> > Although I have also looked at:
> >> >> >
> >> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> >> >> > Array 2, RAID 1 (2xSCSI) TempDB
> >> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> >> >> > Array 4, RAID 10 (6xSATA) All Datafiles
> >> >> > Array 5, RAID 10 (2xSATA) File containing BLOB's
> >> >> > Array 6, RAID 1 (2xSATA) Transaction Log 2
> >> >> > Array 7, RAID 1 (2xSATA) Transaction Log 3
> >> >> >
> >> >> > And:
> >> >> >
> >> >> > Array 1, RAID 1 (2xSCSI) OS
> >> >> > Array 2, RAID 1 (2xSCSI) PageFile
> >> >> > Array 3, RAID 1 (2xSCSI) TempDB
> >> >> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
> >> >> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
> >> >> > Array 6, RAID 1 (2xSATA) File containing BLOB's
> >> >> > Array 7, RAID 1 (2xSATA) Transaction Log 1
> >> >> > Array 8, RAID 1 (2xSATA) Transaction Log 2
> >> >> > Array 9, RAID 1 (2xSATA) Transaction Log 3
> >> >> >
> >> >> > ...I'll be doing some further reading, but just wondered if anyone
> >> >> > had
> >> >> > any
> >> >> > input on this?
> >> >> >
> >> >> > Thanks in advance Ben
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Hi Ben
I suggest you don't seperate those logs out then, as 250Mb in 15 mins is
quite moderate & you could probably find better uses for those disks.
Speaking of which - one thing I missed when I first looked at your
suggestions is that you don't have a local drive for backups. Are you
planning to do these off onto a network or tape? If not, I suggest you don't
forget a backup volume in your plan & those extra disks might come in handy
for this purpose...
Regards,
Greg Linwood
SQL Server MVP
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:ADDF9C2C-FC93-447F-9F44-E554316C36F6@.microsoft.com...
> Smashing thanks Greg, in the original configuration I have 2 transaction
> logs
> sharing an array... ...these logs don't grow above 250mb over 15minutes...
> ...but I'm wondering whether it's worth splitting them out onto seperate
> arrays? - I'd have to drop the RAID 10 array from 10 to 8 disks to make
> room... ...but I've read it's best to seperate Transaction Logs onto
> seperate arrays so they can be sequential... ...I'm unsure if the benefit
> of
> having them on seperate disks would be less than having the 10 disks in
> the
> RAID 10 array... ...I guess it's one of those things I'll find out with
> some
> testing :-) Anyway thanks again for the responses..
> "Greg Linwood" wrote:
>> OK, I just wanted to check up on these points first, because they're
>> often
>> missed with new SQL server implementations.
>> Back to your original qns then:
>> Firstly, drop the idea of a seperate Array for your page file b/c well
>> configured, dedicated database servers should perform very little IO
>> against
>> page files. Page files are for machines where many processes need to
>> share
>> virtual memory & dedicated servers generally do very little of this -
>> certainly not enought to warrant dedicating an array to. Far better to
>> dedicate this array to a tlog or some other useful purpose
>> Seperating objects down to the granularity you've outlined in the last
>> option. The problem with this approach is that you're making assumptions
>> about how your IO should be partitioned where it's often far easier to
>> leave
>> these objects on a single array & let SQL Server spread the load accross
>> them. Another way of looking at this scenario is that if any one object
>> type
>> experiences heavy IO, it has fewer disks to achieve its workload with.
>> Short of more specific information about the actual workload performed by
>> the various DB objects, I'd say that your initial configuration is a very
>> good starting point.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>>
>> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> news:94A8EB85-786A-415D-BA47-8E75777744A1@.microsoft.com...
>> > Memory isn't an option, it's 32bit SQL 2000 on 32bit windows...
>> > ...hence
>> > I'm
>> > trying to compensate with disks and find the best raid config I can...
>> >
>> > "Greg Linwood" wrote:
>> >
>> >> Hi Ben
>> >>
>> >> This depends entirely on whether you're using SQL 2000 or SQL 2005 &
>> >> 32
>> >> bit
>> >> vs 64 bit. For example, if you're using 64 bit Windows 2003 Server
>> >> Standard
>> >> Edition and 64 bit SQL 2005 Standard Edition, you can access up to
>> >> 32Gb
>> >> Memory.
>> >>
>> >> That's why I asked whether you're using SQL 2000 or SQL 2005.
>> >>
>> >> Regards,
>> >> Greg Linwood
>> >> SQL Server MVP
>> >>
>> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> >> news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
>> >> > We're using Windows 2003 Standard Edition, and SQL Standard
>> >> > Edition...
>> >> > ...so
>> >> > more memory wasn't really an option.. ..the cost of disks are cheap
>> >> > in
>> >> > comparison to the upgrade to Enterprise Edition of SQL - we have to
>> >> > purchase
>> >> > the processor licenses. We haven't actually purchased the disks
>> >> > yet,
>> >> > if
>> >> > there's only a small benefit between say 12 and 18 disks we'll only
>> >> > buy
>> >> > the
>> >> > 12...
>> >> >
>> >> > "Greg Linwood" wrote:
>> >> >
>> >> >> Hi Ben
>> >> >>
>> >> >> Why did you buy so many disks & such a small amount of memory? Are
>> >> >> you
>> >> >> using
>> >> >> SQL 2000 or SQL 2005? Which version of Windows? These are actually
>> >> >> all
>> >> >> quite
>> >> >> important factors as they define your memory constraints &
>> >> >> influence
>> >> >> whether
>> >> >> you should configure any of your disks for specialist read / write
>> >> >> activity.
>> >> >> The less memory you have, the more you push IO down to your disk
>> >> >> sub-system..
>> >> >>
>> >> >> Regards,
>> >> >> Greg Linwood
>> >> >> SQL Server MVP
>> >> >>
>> >> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
>> >> >> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
>> >> >> >
>> >> >> > We have just bought a new box for our SQL Database's.. ..We have
>> >> >> > three
>> >> >> > database's, 1 is by far the most intensive, it's a 3rd party
>> >> >> > application
>> >> >> > database and is around 20gb.. ..it uses the temp db heavily..
>> >> >> > ..and
>> >> >> > the
>> >> >> > transaction log gets up to about 750mb - 1gb before it's backed
>> >> >> > up
>> >> >> > every
>> >> >> > 15minutes. The other databases are 20gb and 10gb respectively,
>> >> >> > the
>> >> >> > 20gb
>> >> >> > database contains 15gb of BLOB's.
>> >> >> > The box we have bought has dual xeons and 4gb of RAM, and we have
>> >> >> > 18
>> >> >> > disks
>> >> >> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in
>> >> >> > a
>> >> >> > Smart
>> >> >> > Array.
>> >> >> >
>> >> >> > My initial views for the RAID configuration were as follows:
>> >> >> >
>> >> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
>> >> >> > Array 2, RAID 1 (2xSCSI) TempDB
>> >> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
>> >> >> > Array 4, RAID 10 (10xSATA) All Datafiles
>> >> >> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
>> >> >> >
>> >> >> > Although I have also looked at:
>> >> >> >
>> >> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
>> >> >> > Array 2, RAID 1 (2xSCSI) TempDB
>> >> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
>> >> >> > Array 4, RAID 10 (6xSATA) All Datafiles
>> >> >> > Array 5, RAID 10 (2xSATA) File containing BLOB's
>> >> >> > Array 6, RAID 1 (2xSATA) Transaction Log 2
>> >> >> > Array 7, RAID 1 (2xSATA) Transaction Log 3
>> >> >> >
>> >> >> > And:
>> >> >> >
>> >> >> > Array 1, RAID 1 (2xSCSI) OS
>> >> >> > Array 2, RAID 1 (2xSCSI) PageFile
>> >> >> > Array 3, RAID 1 (2xSCSI) TempDB
>> >> >> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
>> >> >> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
>> >> >> > Array 6, RAID 1 (2xSATA) File containing BLOB's
>> >> >> > Array 7, RAID 1 (2xSATA) Transaction Log 1
>> >> >> > Array 8, RAID 1 (2xSATA) Transaction Log 2
>> >> >> > Array 9, RAID 1 (2xSATA) Transaction Log 3
>> >> >> >
>> >> >> > ...I'll be doing some further reading, but just wondered if
>> >> >> > anyone
>> >> >> > had
>> >> >> > any
>> >> >> > input on this?
>> >> >> >
>> >> >> > Thanks in advance Ben
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||We currently backup over the network and then onto tape.. ..but it's worth
thinking about, thanks again for the help :-)
"Greg Linwood" wrote:
> Hi Ben
> I suggest you don't seperate those logs out then, as 250Mb in 15 mins is
> quite moderate & you could probably find better uses for those disks.
> Speaking of which - one thing I missed when I first looked at your
> suggestions is that you don't have a local drive for backups. Are you
> planning to do these off onto a network or tape? If not, I suggest you don't
> forget a backup volume in your plan & those extra disks might come in handy
> for this purpose...
> Regards,
> Greg Linwood
> SQL Server MVP
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:ADDF9C2C-FC93-447F-9F44-E554316C36F6@.microsoft.com...
> > Smashing thanks Greg, in the original configuration I have 2 transaction
> > logs
> > sharing an array... ...these logs don't grow above 250mb over 15minutes...
> > ...but I'm wondering whether it's worth splitting them out onto seperate
> > arrays? - I'd have to drop the RAID 10 array from 10 to 8 disks to make
> > room... ...but I've read it's best to seperate Transaction Logs onto
> > seperate arrays so they can be sequential... ...I'm unsure if the benefit
> > of
> > having them on seperate disks would be less than having the 10 disks in
> > the
> > RAID 10 array... ...I guess it's one of those things I'll find out with
> > some
> > testing :-) Anyway thanks again for the responses..
> >
> > "Greg Linwood" wrote:
> >
> >> OK, I just wanted to check up on these points first, because they're
> >> often
> >> missed with new SQL server implementations.
> >>
> >> Back to your original qns then:
> >>
> >> Firstly, drop the idea of a seperate Array for your page file b/c well
> >> configured, dedicated database servers should perform very little IO
> >> against
> >> page files. Page files are for machines where many processes need to
> >> share
> >> virtual memory & dedicated servers generally do very little of this -
> >> certainly not enought to warrant dedicating an array to. Far better to
> >> dedicate this array to a tlog or some other useful purpose
> >>
> >> Seperating objects down to the granularity you've outlined in the last
> >> option. The problem with this approach is that you're making assumptions
> >> about how your IO should be partitioned where it's often far easier to
> >> leave
> >> these objects on a single array & let SQL Server spread the load accross
> >> them. Another way of looking at this scenario is that if any one object
> >> type
> >> experiences heavy IO, it has fewer disks to achieve its workload with.
> >>
> >> Short of more specific information about the actual workload performed by
> >> the various DB objects, I'd say that your initial configuration is a very
> >> good starting point.
> >>
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP
> >>
> >>
> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> news:94A8EB85-786A-415D-BA47-8E75777744A1@.microsoft.com...
> >> > Memory isn't an option, it's 32bit SQL 2000 on 32bit windows...
> >> > ...hence
> >> > I'm
> >> > trying to compensate with disks and find the best raid config I can...
> >> >
> >> > "Greg Linwood" wrote:
> >> >
> >> >> Hi Ben
> >> >>
> >> >> This depends entirely on whether you're using SQL 2000 or SQL 2005 &
> >> >> 32
> >> >> bit
> >> >> vs 64 bit. For example, if you're using 64 bit Windows 2003 Server
> >> >> Standard
> >> >> Edition and 64 bit SQL 2005 Standard Edition, you can access up to
> >> >> 32Gb
> >> >> Memory.
> >> >>
> >> >> That's why I asked whether you're using SQL 2000 or SQL 2005.
> >> >>
> >> >> Regards,
> >> >> Greg Linwood
> >> >> SQL Server MVP
> >> >>
> >> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> >> news:12B8799F-C62C-4D25-BD05-2122305040A9@.microsoft.com...
> >> >> > We're using Windows 2003 Standard Edition, and SQL Standard
> >> >> > Edition...
> >> >> > ...so
> >> >> > more memory wasn't really an option.. ..the cost of disks are cheap
> >> >> > in
> >> >> > comparison to the upgrade to Enterprise Edition of SQL - we have to
> >> >> > purchase
> >> >> > the processor licenses. We haven't actually purchased the disks
> >> >> > yet,
> >> >> > if
> >> >> > there's only a small benefit between say 12 and 18 disks we'll only
> >> >> > buy
> >> >> > the
> >> >> > 12...
> >> >> >
> >> >> > "Greg Linwood" wrote:
> >> >> >
> >> >> >> Hi Ben
> >> >> >>
> >> >> >> Why did you buy so many disks & such a small amount of memory? Are
> >> >> >> you
> >> >> >> using
> >> >> >> SQL 2000 or SQL 2005? Which version of Windows? These are actually
> >> >> >> all
> >> >> >> quite
> >> >> >> important factors as they define your memory constraints &
> >> >> >> influence
> >> >> >> whether
> >> >> >> you should configure any of your disks for specialist read / write
> >> >> >> activity.
> >> >> >> The less memory you have, the more you push IO down to your disk
> >> >> >> sub-system..
> >> >> >>
> >> >> >> Regards,
> >> >> >> Greg Linwood
> >> >> >> SQL Server MVP
> >> >> >>
> >> >> >> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> >> >> >> news:5109C752-1078-4576-B5EF-F7929AB104DE@.microsoft.com...
> >> >> >> >
> >> >> >> > We have just bought a new box for our SQL Database's.. ..We have
> >> >> >> > three
> >> >> >> > database's, 1 is by far the most intensive, it's a 3rd party
> >> >> >> > application
> >> >> >> > database and is around 20gb.. ..it uses the temp db heavily..
> >> >> >> > ..and
> >> >> >> > the
> >> >> >> > transaction log gets up to about 750mb - 1gb before it's backed
> >> >> >> > up
> >> >> >> > every
> >> >> >> > 15minutes. The other databases are 20gb and 10gb respectively,
> >> >> >> > the
> >> >> >> > 20gb
> >> >> >> > database contains 15gb of BLOB's.
> >> >> >> > The box we have bought has dual xeons and 4gb of RAM, and we have
> >> >> >> > 18
> >> >> >> > disks
> >> >> >> > to play with, 6 SCSI's @.15krpm in the box and 12 @.7.2rpm SATA in
> >> >> >> > a
> >> >> >> > Smart
> >> >> >> > Array.
> >> >> >> >
> >> >> >> > My initial views for the RAID configuration were as follows:
> >> >> >> >
> >> >> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> >> >> >> > Array 2, RAID 1 (2xSCSI) TempDB
> >> >> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> >> >> >> > Array 4, RAID 10 (10xSATA) All Datafiles
> >> >> >> > Array 5, RAID 1 (2xSATA) Remaining 2 Transaction Logs
> >> >> >> >
> >> >> >> > Although I have also looked at:
> >> >> >> >
> >> >> >> > Array 1, RAID 1 (2xSCSI) OS & Pagefile
> >> >> >> > Array 2, RAID 1 (2xSCSI) TempDB
> >> >> >> > Array 3, RAID 1 (2xSCSI) The intensive database's transaction log
> >> >> >> > Array 4, RAID 10 (6xSATA) All Datafiles
> >> >> >> > Array 5, RAID 10 (2xSATA) File containing BLOB's
> >> >> >> > Array 6, RAID 1 (2xSATA) Transaction Log 2
> >> >> >> > Array 7, RAID 1 (2xSATA) Transaction Log 3
> >> >> >> >
> >> >> >> > And:
> >> >> >> >
> >> >> >> > Array 1, RAID 1 (2xSCSI) OS
> >> >> >> > Array 2, RAID 1 (2xSCSI) PageFile
> >> >> >> > Array 3, RAID 1 (2xSCSI) TempDB
> >> >> >> > Array 4, RAID 1 (2xSATA) Clustered Index's of all DB's
> >> >> >> > Array 5, RAID 1 (2xSATA) Non-Clustered Index's of all DB's
> >> >> >> > Array 6, RAID 1 (2xSATA) File containing BLOB's
> >> >> >> > Array 7, RAID 1 (2xSATA) Transaction Log 1
> >> >> >> > Array 8, RAID 1 (2xSATA) Transaction Log 2
> >> >> >> > Array 9, RAID 1 (2xSATA) Transaction Log 3
> >> >> >> >
> >> >> >> > ...I'll be doing some further reading, but just wondered if
> >> >> >> > anyone
> >> >> >> > had
> >> >> >> > any
> >> >> >> > input on this?
> >> >> >> >
> >> >> >> > Thanks in advance Ben
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Wednesday, March 28, 2012
Raid 10 with lots of disks. Where to place datafiles
I'm in setup new server mode so I'm just checking a few things.
Say you had 8, 12 or 16 disks purely for data (log on other disks).
Do you think it's best to create a single big raid 10 set for a single
filegroup with a single database file.
Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
indexes and data or even by tables that are hit heavy (not easy to get right
when you have 100's of tables)
Create 2 or more raid 10 sets and have a single filegroup that owns a file
on each raid 10 set.
I know this is application specific but when you have a complex system you
could spend forever trying to work out the details.
Raid 10 will give me the megs per second but I was thinking maybe I'd be
better splitting the data up to get the transactions per second.
Paul
On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
<xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>I'm in setup new server mode so I'm just checking a few things.
>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>Do you think it's best to create a single big raid 10 set for a single
>filegroup with a single database file.
Why so many disks? Is the database humongous?
>Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
>indexes and data or even by tables that are hit heavy (not easy to get right
>when you have 100's of tables)
>Create 2 or more raid 10 sets and have a single filegroup that owns a file
>on each raid 10 set.
>I know this is application specific
Right.
> but when you have a complex system you
>could spend forever trying to work out the details.
>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>better splitting the data up to get the transactions per second.
One big, fat RAID 10 will probably get you 80% of peak performance
with 20% of the bother. You can use SQLServer filegroups to optimize
wihin, if you like. Two more more smaller RAIDS might help, depending
on the app, depending on whether you NEED more help!
J.
|||Hi,
We should remember that the more heads/spindals you have for the RAID
the better the performance. With the disk technology today and the RPM
speeds I think you could get away with a large RAID and splice it up on
the OS level into different drives/devices.
Just a note from my SysAdmin days [those were the days ;-].
Shahryar
jxstern wrote:
>On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
><xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>
>Why so many disks? Is the database humongous?
>
>
>Right.
>
>
>One big, fat RAID 10 will probably get you 80% of peak performance
>with 20% of the bother. You can use SQLServer filegroups to optimize
>wihin, if you like. Two more more smaller RAIDS might help, depending
>on the app, depending on whether you NEED more help!
>J.
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Lot's of disks because we are i/o bound on our current system and because
they are relatively cheap compared to the price of staff for one thing.
I could spend days and days fine tuning what tables/indexes get hit, when
and by whom. There are over 500 users and their usage can be quite
heterogeneous at times.
I've always thought raid 5 should be avoided, therefore raid 10.
I have prepared the system with fat raid 10's but I was thinking maybe I was
getting obsessed with i/o throughput and not thinking about random access.
All the heads on an array will be rushing to the same stripe at the same
time and then on to the next requested block of data.
However, say I had two smaller raid 10 sets then maybe I'd get a higher
transaction throughput although a slighly lower data rate per transaction.
Shahryar, I know you are correct. These new disks are blindingly quick but
disks haven't come on as much as cpu and memory speeds in the last few
years. They have mainly got bigger which is not necessarily best for rdbms.
Paul
"Shahryar G. Hashemi" <shahryar.hashemi@.infospace.com> wrote in message
news:eDAnkYM4FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> We should remember that the more heads/spindals you have for the RAID the
> better the performance. With the disk technology today and the RPM speeds
> I think you could get away with a large RAID and splice it up on the OS
> level into different drives/devices.
> Just a note from my SysAdmin days [those were the days ;-].
> Shahryar
> jxstern wrote:
>
> --
> Shahryar G. Hashemi | Sr. DBA Consultant InfoSpace, Inc. 601 108th Ave NE
> | Suite 1200 | Bellevue, WA 98004 USA Mobile +1 206.459.6203 | Office
> +1 425.201.8853 | Fax +1 425.201.6150 shashem@.infospace.com |
> www.infospaceinc.com
> This e-mail and any attachments may contain confidential information that
> is legally privileged. The information is solely for the use of the
> intended recipient(s); any disclosure, copying, distribution, or other use
> of this information is strictly prohibited. If you have received this
> e-mail in error, please notify the sender by return e-mail and delete this
> message. Thank you.
sql
Say you had 8, 12 or 16 disks purely for data (log on other disks).
Do you think it's best to create a single big raid 10 set for a single
filegroup with a single database file.
Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
indexes and data or even by tables that are hit heavy (not easy to get right
when you have 100's of tables)
Create 2 or more raid 10 sets and have a single filegroup that owns a file
on each raid 10 set.
I know this is application specific but when you have a complex system you
could spend forever trying to work out the details.
Raid 10 will give me the megs per second but I was thinking maybe I'd be
better splitting the data up to get the transactions per second.
Paul
On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
<xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>I'm in setup new server mode so I'm just checking a few things.
>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>Do you think it's best to create a single big raid 10 set for a single
>filegroup with a single database file.
Why so many disks? Is the database humongous?
>Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
>indexes and data or even by tables that are hit heavy (not easy to get right
>when you have 100's of tables)
>Create 2 or more raid 10 sets and have a single filegroup that owns a file
>on each raid 10 set.
>I know this is application specific
Right.
> but when you have a complex system you
>could spend forever trying to work out the details.
>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>better splitting the data up to get the transactions per second.
One big, fat RAID 10 will probably get you 80% of peak performance
with 20% of the bother. You can use SQLServer filegroups to optimize
wihin, if you like. Two more more smaller RAIDS might help, depending
on the app, depending on whether you NEED more help!
J.
|||Hi,
We should remember that the more heads/spindals you have for the RAID
the better the performance. With the disk technology today and the RPM
speeds I think you could get away with a large RAID and splice it up on
the OS level into different drives/devices.
Just a note from my SysAdmin days [those were the days ;-].
Shahryar
jxstern wrote:
>On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
><xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>
>Why so many disks? Is the database humongous?
>
>
>Right.
>
>
>One big, fat RAID 10 will probably get you 80% of peak performance
>with 20% of the bother. You can use SQLServer filegroups to optimize
>wihin, if you like. Two more more smaller RAIDS might help, depending
>on the app, depending on whether you NEED more help!
>J.
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Lot's of disks because we are i/o bound on our current system and because
they are relatively cheap compared to the price of staff for one thing.
I could spend days and days fine tuning what tables/indexes get hit, when
and by whom. There are over 500 users and their usage can be quite
heterogeneous at times.
I've always thought raid 5 should be avoided, therefore raid 10.
I have prepared the system with fat raid 10's but I was thinking maybe I was
getting obsessed with i/o throughput and not thinking about random access.
All the heads on an array will be rushing to the same stripe at the same
time and then on to the next requested block of data.
However, say I had two smaller raid 10 sets then maybe I'd get a higher
transaction throughput although a slighly lower data rate per transaction.
Shahryar, I know you are correct. These new disks are blindingly quick but
disks haven't come on as much as cpu and memory speeds in the last few
years. They have mainly got bigger which is not necessarily best for rdbms.
Paul
"Shahryar G. Hashemi" <shahryar.hashemi@.infospace.com> wrote in message
news:eDAnkYM4FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> We should remember that the more heads/spindals you have for the RAID the
> better the performance. With the disk technology today and the RPM speeds
> I think you could get away with a large RAID and splice it up on the OS
> level into different drives/devices.
> Just a note from my SysAdmin days [those were the days ;-].
> Shahryar
> jxstern wrote:
>
> --
> Shahryar G. Hashemi | Sr. DBA Consultant InfoSpace, Inc. 601 108th Ave NE
> | Suite 1200 | Bellevue, WA 98004 USA Mobile +1 206.459.6203 | Office
> +1 425.201.8853 | Fax +1 425.201.6150 shashem@.infospace.com |
> www.infospaceinc.com
> This e-mail and any attachments may contain confidential information that
> is legally privileged. The information is solely for the use of the
> intended recipient(s); any disclosure, copying, distribution, or other use
> of this information is strictly prohibited. If you have received this
> e-mail in error, please notify the sender by return e-mail and delete this
> message. Thank you.
sql
Raid 10 with lots of disks. Where to place datafiles
I'm in setup new server mode so I'm just checking a few things.
Say you had 8, 12 or 16 disks purely for data (log on other disks).
Do you think it's best to create a single big raid 10 set for a single
filegroup with a single database file.
Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
indexes and data or even by tables that are hit heavy (not easy to get right
when you have 100's of tables)
Create 2 or more raid 10 sets and have a single filegroup that owns a file
on each raid 10 set.
I know this is application specific but when you have a complex system you
could spend forever trying to work out the details.
Raid 10 will give me the megs per second but I was thinking maybe I'd be
better splitting the data up to get the transactions per second.
PaulOn Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
<xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>I'm in setup new server mode so I'm just checking a few things.
>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>Do you think it's best to create a single big raid 10 set for a single
>filegroup with a single database file.
Why so many disks? Is the database humongous?
>Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
>indexes and data or even by tables that are hit heavy (not easy to get right
>when you have 100's of tables)
>Create 2 or more raid 10 sets and have a single filegroup that owns a file
>on each raid 10 set.
>I know this is application specific
Right.
> but when you have a complex system you
>could spend forever trying to work out the details.
>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>better splitting the data up to get the transactions per second.
One big, fat RAID 10 will probably get you 80% of peak performance
with 20% of the bother. You can use SQLServer filegroups to optimize
wihin, if you like. Two more more smaller RAIDS might help, depending
on the app, depending on whether you NEED more help!
J.|||Hi,
We should remember that the more heads/spindals you have for the RAID
the better the performance. With the disk technology today and the RPM
speeds I think you could get away with a large RAID and splice it up on
the OS level into different drives/devices.
Just a note from my SysAdmin days [those were the days ;-].
Shahryar
jxstern wrote:
>On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
><xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>
>>I'm in setup new server mode so I'm just checking a few things.
>>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>>Do you think it's best to create a single big raid 10 set for a single
>>filegroup with a single database file.
>>
>Why so many disks? Is the database humongous?
>
>>Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
>>indexes and data or even by tables that are hit heavy (not easy to get right
>>when you have 100's of tables)
>>Create 2 or more raid 10 sets and have a single filegroup that owns a file
>>on each raid 10 set.
>>I know this is application specific
>>
>Right.
>
>> but when you have a complex system you
>>could spend forever trying to work out the details.
>>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>>better splitting the data up to get the transactions per second.
>>
>One big, fat RAID 10 will probably get you 80% of peak performance
>with 20% of the bother. You can use SQLServer filegroups to optimize
>wihin, if you like. Two more more smaller RAIDS might help, depending
>on the app, depending on whether you NEED more help!
>J.
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Lot's of disks because we are i/o bound on our current system and because
they are relatively cheap compared to the price of staff for one thing.
I could spend days and days fine tuning what tables/indexes get hit, when
and by whom. There are over 500 users and their usage can be quite
heterogeneous at times.
I've always thought raid 5 should be avoided, therefore raid 10.
I have prepared the system with fat raid 10's but I was thinking maybe I was
getting obsessed with i/o throughput and not thinking about random access.
All the heads on an array will be rushing to the same stripe at the same
time and then on to the next requested block of data.
However, say I had two smaller raid 10 sets then maybe I'd get a higher
transaction throughput although a slighly lower data rate per transaction.
Shahryar, I know you are correct. These new disks are blindingly quick but
disks haven't come on as much as cpu and memory speeds in the last few
years. They have mainly got bigger which is not necessarily best for rdbms.
Paul
"Shahryar G. Hashemi" <shahryar.hashemi@.infospace.com> wrote in message
news:eDAnkYM4FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> We should remember that the more heads/spindals you have for the RAID the
> better the performance. With the disk technology today and the RPM speeds
> I think you could get away with a large RAID and splice it up on the OS
> level into different drives/devices.
> Just a note from my SysAdmin days [those were the days ;-].
> Shahryar
> jxstern wrote:
>>On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
>><xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>>I'm in setup new server mode so I'm just checking a few things.
>>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>>Do you think it's best to create a single big raid 10 set for a single
>>filegroup with a single database file.
>>
>>Why so many disks? Is the database humongous?
>>
>>Create 2 or more raid 10 sets and split the data in a controlled manner.
>>Eg indexes and data or even by tables that are hit heavy (not easy to get
>>right when you have 100's of tables)
>>Create 2 or more raid 10 sets and have a single filegroup that owns a
>>file on each raid 10 set.
>>I know this is application specific
>>Right.
>>
>> but when you have a complex system
>> you could spend forever trying to work out the details.
>>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>>better splitting the data up to get the transactions per second.
>>
>>One big, fat RAID 10 will probably get you 80% of peak performance
>>with 20% of the bother. You can use SQLServer filegroups to optimize
>>wihin, if you like. Two more more smaller RAIDS might help, depending
>>on the app, depending on whether you NEED more help!
>>J.
>>
>
> --
> Shahryar G. Hashemi | Sr. DBA Consultant InfoSpace, Inc. 601 108th Ave NE
> | Suite 1200 | Bellevue, WA 98004 USA Mobile +1 206.459.6203 | Office
> +1 425.201.8853 | Fax +1 425.201.6150 shashem@.infospace.com |
> www.infospaceinc.com
> This e-mail and any attachments may contain confidential information that
> is legally privileged. The information is solely for the use of the
> intended recipient(s); any disclosure, copying, distribution, or other use
> of this information is strictly prohibited. If you have received this
> e-mail in error, please notify the sender by return e-mail and delete this
> message. Thank you.
Say you had 8, 12 or 16 disks purely for data (log on other disks).
Do you think it's best to create a single big raid 10 set for a single
filegroup with a single database file.
Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
indexes and data or even by tables that are hit heavy (not easy to get right
when you have 100's of tables)
Create 2 or more raid 10 sets and have a single filegroup that owns a file
on each raid 10 set.
I know this is application specific but when you have a complex system you
could spend forever trying to work out the details.
Raid 10 will give me the megs per second but I was thinking maybe I'd be
better splitting the data up to get the transactions per second.
PaulOn Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
<xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>I'm in setup new server mode so I'm just checking a few things.
>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>Do you think it's best to create a single big raid 10 set for a single
>filegroup with a single database file.
Why so many disks? Is the database humongous?
>Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
>indexes and data or even by tables that are hit heavy (not easy to get right
>when you have 100's of tables)
>Create 2 or more raid 10 sets and have a single filegroup that owns a file
>on each raid 10 set.
>I know this is application specific
Right.
> but when you have a complex system you
>could spend forever trying to work out the details.
>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>better splitting the data up to get the transactions per second.
One big, fat RAID 10 will probably get you 80% of peak performance
with 20% of the bother. You can use SQLServer filegroups to optimize
wihin, if you like. Two more more smaller RAIDS might help, depending
on the app, depending on whether you NEED more help!
J.|||Hi,
We should remember that the more heads/spindals you have for the RAID
the better the performance. With the disk technology today and the RPM
speeds I think you could get away with a large RAID and splice it up on
the OS level into different drives/devices.
Just a note from my SysAdmin days [those were the days ;-].
Shahryar
jxstern wrote:
>On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
><xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>
>>I'm in setup new server mode so I'm just checking a few things.
>>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>>Do you think it's best to create a single big raid 10 set for a single
>>filegroup with a single database file.
>>
>Why so many disks? Is the database humongous?
>
>>Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
>>indexes and data or even by tables that are hit heavy (not easy to get right
>>when you have 100's of tables)
>>Create 2 or more raid 10 sets and have a single filegroup that owns a file
>>on each raid 10 set.
>>I know this is application specific
>>
>Right.
>
>> but when you have a complex system you
>>could spend forever trying to work out the details.
>>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>>better splitting the data up to get the transactions per second.
>>
>One big, fat RAID 10 will probably get you 80% of peak performance
>with 20% of the bother. You can use SQLServer filegroups to optimize
>wihin, if you like. Two more more smaller RAIDS might help, depending
>on the app, depending on whether you NEED more help!
>J.
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Lot's of disks because we are i/o bound on our current system and because
they are relatively cheap compared to the price of staff for one thing.
I could spend days and days fine tuning what tables/indexes get hit, when
and by whom. There are over 500 users and their usage can be quite
heterogeneous at times.
I've always thought raid 5 should be avoided, therefore raid 10.
I have prepared the system with fat raid 10's but I was thinking maybe I was
getting obsessed with i/o throughput and not thinking about random access.
All the heads on an array will be rushing to the same stripe at the same
time and then on to the next requested block of data.
However, say I had two smaller raid 10 sets then maybe I'd get a higher
transaction throughput although a slighly lower data rate per transaction.
Shahryar, I know you are correct. These new disks are blindingly quick but
disks haven't come on as much as cpu and memory speeds in the last few
years. They have mainly got bigger which is not necessarily best for rdbms.
Paul
"Shahryar G. Hashemi" <shahryar.hashemi@.infospace.com> wrote in message
news:eDAnkYM4FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> We should remember that the more heads/spindals you have for the RAID the
> better the performance. With the disk technology today and the RPM speeds
> I think you could get away with a large RAID and splice it up on the OS
> level into different drives/devices.
> Just a note from my SysAdmin days [those were the days ;-].
> Shahryar
> jxstern wrote:
>>On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
>><xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>>I'm in setup new server mode so I'm just checking a few things.
>>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>>Do you think it's best to create a single big raid 10 set for a single
>>filegroup with a single database file.
>>
>>Why so many disks? Is the database humongous?
>>
>>Create 2 or more raid 10 sets and split the data in a controlled manner.
>>Eg indexes and data or even by tables that are hit heavy (not easy to get
>>right when you have 100's of tables)
>>Create 2 or more raid 10 sets and have a single filegroup that owns a
>>file on each raid 10 set.
>>I know this is application specific
>>Right.
>>
>> but when you have a complex system
>> you could spend forever trying to work out the details.
>>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>>better splitting the data up to get the transactions per second.
>>
>>One big, fat RAID 10 will probably get you 80% of peak performance
>>with 20% of the bother. You can use SQLServer filegroups to optimize
>>wihin, if you like. Two more more smaller RAIDS might help, depending
>>on the app, depending on whether you NEED more help!
>>J.
>>
>
> --
> Shahryar G. Hashemi | Sr. DBA Consultant InfoSpace, Inc. 601 108th Ave NE
> | Suite 1200 | Bellevue, WA 98004 USA Mobile +1 206.459.6203 | Office
> +1 425.201.8853 | Fax +1 425.201.6150 shashem@.infospace.com |
> www.infospaceinc.com
> This e-mail and any attachments may contain confidential information that
> is legally privileged. The information is solely for the use of the
> intended recipient(s); any disclosure, copying, distribution, or other use
> of this information is strictly prohibited. If you have received this
> e-mail in error, please notify the sender by return e-mail and delete this
> message. Thank you.
Raid 10 with lots of disks. Where to place datafiles
I'm in setup new server mode so I'm just checking a few things.
Say you had 8, 12 or 16 disks purely for data (log on other disks).
Do you think it's best to create a single big raid 10 set for a single
filegroup with a single database file.
Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
indexes and data or even by tables that are hit heavy (not easy to get right
when you have 100's of tables)
Create 2 or more raid 10 sets and have a single filegroup that owns a file
on each raid 10 set.
I know this is application specific but when you have a complex system you
could spend forever trying to work out the details.
Raid 10 will give me the megs per second but I was thinking maybe I'd be
better splitting the data up to get the transactions per second.
PaulOn Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
<xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>I'm in setup new server mode so I'm just checking a few things.
>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>Do you think it's best to create a single big raid 10 set for a single
>filegroup with a single database file.
Why so many disks? Is the database humongous?
>Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
>indexes and data or even by tables that are hit heavy (not easy to get righ
t
>when you have 100's of tables)
>Create 2 or more raid 10 sets and have a single filegroup that owns a file
>on each raid 10 set.
>I know this is application specific
Right.
> but when you have a complex system you
>could spend forever trying to work out the details.
>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>better splitting the data up to get the transactions per second.
One big, fat RAID 10 will probably get you 80% of peak performance
with 20% of the bother. You can use SQLServer filegroups to optimize
wihin, if you like. Two more more smaller RAIDS might help, depending
on the app, depending on whether you NEED more help!
J.|||Hi,
We should remember that the more heads/spindals you have for the RAID
the better the performance. With the disk technology today and the RPM
speeds I think you could get away with a large RAID and splice it up on
the OS level into different drives/devices.
Just a note from my SysAdmin days [those were the days ;-].
Shahryar
jxstern wrote:
>On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
><xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>
>Why so many disks? Is the database humongous?
>
>
>Right.
>
>
>One big, fat RAID 10 will probably get you 80% of peak performance
>with 20% of the bother. You can use SQLServer filegroups to optimize
>wihin, if you like. Two more more smaller RAIDS might help, depending
>on the app, depending on whether you NEED more help!
>J.
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Lot's of disks because we are i/o bound on our current system and because
they are relatively cheap compared to the price of staff for one thing.
I could spend days and days fine tuning what tables/indexes get hit, when
and by whom. There are over 500 users and their usage can be quite
heterogeneous at times.
I've always thought raid 5 should be avoided, therefore raid 10.
I have prepared the system with fat raid 10's but I was thinking maybe I was
getting obsessed with i/o throughput and not thinking about random access.
All the heads on an array will be rushing to the same stripe at the same
time and then on to the next requested block of data.
However, say I had two smaller raid 10 sets then maybe I'd get a higher
transaction throughput although a slighly lower data rate per transaction.
Shahryar, I know you are correct. These new disks are blindingly quick but
disks haven't come on as much as cpu and memory speeds in the last few
years. They have mainly got bigger which is not necessarily best for rdbms.
Paul
"Shahryar G. Hashemi" <shahryar.hashemi@.infospace.com> wrote in message
news:eDAnkYM4FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> We should remember that the more heads/spindals you have for the RAID the
> better the performance. With the disk technology today and the RPM speeds
> I think you could get away with a large RAID and splice it up on the OS
> level into different drives/devices.
> Just a note from my SysAdmin days [those were the days ;-].
> Shahryar
> jxstern wrote:
>
>
> --
> Shahryar G. Hashemi | Sr. DBA Consultant InfoSpace, Inc. 601 108th Ave NE
> | Suite 1200 | Bellevue, WA 98004 USA Mobile +1 206.459.6203 | Office
> +1 425.201.8853 | Fax +1 425.201.6150 shashem@.infospace.com |
> www.infospaceinc.com
> This e-mail and any attachments may contain confidential information that
> is legally privileged. The information is solely for the use of the
> intended recipient(s); any disclosure, copying, distribution, or other use
> of this information is strictly prohibited. If you have received this
> e-mail in error, please notify the sender by return e-mail and delete this
> message. Thank you.
Say you had 8, 12 or 16 disks purely for data (log on other disks).
Do you think it's best to create a single big raid 10 set for a single
filegroup with a single database file.
Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
indexes and data or even by tables that are hit heavy (not easy to get right
when you have 100's of tables)
Create 2 or more raid 10 sets and have a single filegroup that owns a file
on each raid 10 set.
I know this is application specific but when you have a complex system you
could spend forever trying to work out the details.
Raid 10 will give me the megs per second but I was thinking maybe I'd be
better splitting the data up to get the transactions per second.
PaulOn Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
<xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>I'm in setup new server mode so I'm just checking a few things.
>Say you had 8, 12 or 16 disks purely for data (log on other disks).
>Do you think it's best to create a single big raid 10 set for a single
>filegroup with a single database file.
Why so many disks? Is the database humongous?
>Create 2 or more raid 10 sets and split the data in a controlled manner. Eg
>indexes and data or even by tables that are hit heavy (not easy to get righ
t
>when you have 100's of tables)
>Create 2 or more raid 10 sets and have a single filegroup that owns a file
>on each raid 10 set.
>I know this is application specific
Right.
> but when you have a complex system you
>could spend forever trying to work out the details.
>Raid 10 will give me the megs per second but I was thinking maybe I'd be
>better splitting the data up to get the transactions per second.
One big, fat RAID 10 will probably get you 80% of peak performance
with 20% of the bother. You can use SQLServer filegroups to optimize
wihin, if you like. Two more more smaller RAIDS might help, depending
on the app, depending on whether you NEED more help!
J.|||Hi,
We should remember that the more heads/spindals you have for the RAID
the better the performance. With the disk technology today and the RPM
speeds I think you could get away with a large RAID and splice it up on
the OS level into different drives/devices.
Just a note from my SysAdmin days [those were the days ;-].
Shahryar
jxstern wrote:
>On Thu, 3 Nov 2005 19:26:24 -0000, "Paul Cahill"
><xyzpaul.xyzcahill@.dsl.pipex.com> wrote:
>
>Why so many disks? Is the database humongous?
>
>
>Right.
>
>
>One big, fat RAID 10 will probably get you 80% of peak performance
>with 20% of the bother. You can use SQLServer filegroups to optimize
>wihin, if you like. Two more more smaller RAIDS might help, depending
>on the app, depending on whether you NEED more help!
>J.
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Lot's of disks because we are i/o bound on our current system and because
they are relatively cheap compared to the price of staff for one thing.
I could spend days and days fine tuning what tables/indexes get hit, when
and by whom. There are over 500 users and their usage can be quite
heterogeneous at times.
I've always thought raid 5 should be avoided, therefore raid 10.
I have prepared the system with fat raid 10's but I was thinking maybe I was
getting obsessed with i/o throughput and not thinking about random access.
All the heads on an array will be rushing to the same stripe at the same
time and then on to the next requested block of data.
However, say I had two smaller raid 10 sets then maybe I'd get a higher
transaction throughput although a slighly lower data rate per transaction.
Shahryar, I know you are correct. These new disks are blindingly quick but
disks haven't come on as much as cpu and memory speeds in the last few
years. They have mainly got bigger which is not necessarily best for rdbms.
Paul
"Shahryar G. Hashemi" <shahryar.hashemi@.infospace.com> wrote in message
news:eDAnkYM4FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> We should remember that the more heads/spindals you have for the RAID the
> better the performance. With the disk technology today and the RPM speeds
> I think you could get away with a large RAID and splice it up on the OS
> level into different drives/devices.
> Just a note from my SysAdmin days [those were the days ;-].
> Shahryar
> jxstern wrote:
>
>
> --
> Shahryar G. Hashemi | Sr. DBA Consultant InfoSpace, Inc. 601 108th Ave NE
> | Suite 1200 | Bellevue, WA 98004 USA Mobile +1 206.459.6203 | Office
> +1 425.201.8853 | Fax +1 425.201.6150 shashem@.infospace.com |
> www.infospaceinc.com
> This e-mail and any attachments may contain confidential information that
> is legally privileged. The information is solely for the use of the
> intended recipient(s); any disclosure, copying, distribution, or other use
> of this information is strictly prohibited. If you have received this
> e-mail in error, please notify the sender by return e-mail and delete this
> message. Thank you.
Subscribe to:
Posts (Atom)