Showing posts with label box. Show all posts
Showing posts with label box. Show all posts

Friday, March 30, 2012

RAID1 vs 3-disk RAID5 for DB

I'm making whatever hardware upgrades I can very easily do on this box.

Due to only having 6 HDD slots in my IBM x235 tower's backplane, I can't have 3 volumes AND have one of them as RAID 5. Also; a 3 volume RAID 5 just seems silly. Wouldn't it have worse performance than mirrored (both read and write)? Well; I don't claim any knowledge on all this so should stop on the wild guesses.

My DB is 3GB (+ 2GB log file). It's looking like it may grow up to 5 or 10 GB per year on the very outside, so 140GB for DB should be plenty for years to come.

I just want to keep this upgrade small and simple with zero exposure to upgrade heartache and regret. Therefore; I plan to avoid an O/S or Software rebuild.

My hardware / network guy says upgrading the O/S drive from 10K RPM to a larger 15K RPM drive requires OS reload. He's not entirely sure since he's never done it using this IBM RAID card, but 10K RPM should be fine for the O/S.

Existing:
Internal IBM PCI RAID Controller (not sure model, but decent). All RAID is via hardware controller.

Volume 1 = 10K RPM mirrored SCSI.
Volume 2 = 10K RPM RAID5 with 3 volumes
Slot 6 empty.

Solution 1:
1. 2 volume solution that includes a small RAID 5.

Volume 1 = 10K RPM RAID1 SCSI. (O/S, Log, and DB Backup files)
Volume 2 = 15K RPM RAID6 or RAID5 with 4 disks

2. 3 volume solution

Volume 1 = 10K RPM RAID1 SCSI. (O/S only)
Volume 2 = 15K RPM RAID1 SCSI (for DB only)
Volume 3 = 15K RPM RAID1 SCSI (for Log files only)

I have heard it's better to have log files on a mirrored disk because the I/O tends to be write intensive and contiguous. I don't know how intensive DB I/O is vs. Log File I/O vs. OS I/O. Maybe someone who does could advise me in this matter.

Additional question: Are higher capacity HDDs faster for read/write? Say; comparing 32GB vs. 300GB, the 300GB must be more compressed, so more data would travel over the heads. Yet; the specs don't seem to imply that there's much difference (unless I'm missing something).

Thanks!Hi vich

others will be able to answer - I'm pretty hopeless on hardware. Every so often I try to work my way through this and pick up a little more each time:
http://sql-server-performance.com/Community/forums/t/2337.aspx|||Let me take a stab at this and then let the experts chew me apart...

I would recommend the 3 RAID-1 volumes with a big caveat: make sure you have adequate space on the main data volume. If you don't, and you end up "spilling over" data files onto either the O/S volume or the log volume, you've negated any advantage.

Also, check your channel configuration carefully; you'd like the IO from the data LUNs on a separate channel from the IO for the log files (but definitely separate from the backup LUNs). Usually one SCSI card supports two channels, so you'll probably end up having to compromise something here.

My reasoning:

The OS volume should have the paging file; segregating the I/O for the paging file onto a separate LUN should be a plus;
Having the log files write to a RAID 1 LUN should be a plus; RAID-1 does sequential writes better (when compared to RAID-5);
Provided there's sufficient room, having a RAID-1 LUN for your data should be good; better would be to have more physical spindles for data access (ie, reads), but everything's a compromise in the end.

You did not specify where you were going to write the db backup files in the 3-drive scenario: I would probably go with the OS LUN (again, if it's big enough). Alternatively, if you're network is big enough and reliable enough (and the target is reliable enough), you can write your backups over the network. A LOT of people recommend against this (and with good reason). But if you understand the risks and you are SURE that the network is adequate and the target is reliable, then over the network backups can be a big boost in terms of space saved and IO performance.

Higher capacity drives do not directly affect IO performance. The key metrics for IO performance are RPM and latency. Indirectly however, they do have an impact. Since the new drives are higher cap, the budget guys tend to think you need fewer drives. Fewer drives means fewer spindles. Fewer spindles means slower (read) performance.

Recognizing that not every organization can afford them, I keep pushing SANs, even if it means using some of the lower end systems like the AX 1000 from EMC. Once you break into this world, you'll never want to go back.

Okay, so now everyone can rip into me and show me how ignorant I am!

Regards,

hmscott|||Okay, so now everyone can rip into me and show me how ignorant I am!I had to google LUN :o|||Sounds pretty good to me, hmscott.

In an ideal world, I would want separate spindles for the following objects split off in order of importance:
1) OS/pagefile
2) data filegroups
3) logs
4) tempdb
5) index filegroups

If you have a system that does not do a lot of writing, and mainly reading joined tables, or sorting output, items 3 and 4 invert. Reporting systems love tempdb. If you can not get 5 physical spindles, indexes collapse into the data. If you can not get 4 spindles, try to get tempdb in with the transaction logs. If you only have 2 spindles, hope you have a lightly used system.

Tempdb and transaction logs like having mirror arrays. Data/index filegroups can live with RAID 5, since the write I/O is asynchronous.

EDIT: Cleared up some of the ambiguous pasages.|||Coo - why would you make logs a higher priority on a read heavy system?|||DOH! I renumbered things when I added OS. That should be that tempdb and logs invert, not datafiles and logs invert. Perhaps I should do some editing...|||Sounds pretty good to me, hmscott.

In an ideal world, I would want separate spindles for the following objects split off in order of importance:
1) OS/pagefile
2) data filegroups
3) logs
4) tempdb
5) index filegroups

If you have a system that does not do a lot of writing, and mainly reading joined tables, or sorting output, items 3 and 4 invert. Reporting systems love tempdb. If you can not get 5 physical spindles, indexes collapse into the data. If you can not get 4 spindles, try to get tempdb in with the transaction logs. If you only have 2 spindles, hope you have a lightly used system.

Tempdb and transaction logs like having mirror arrays. Data/index filegroups can live with RAID 5, since the write I/O is asynchronous.

EDIT: Cleared up some of the ambiguous pasages.Great info. Thanks.

So; would I be correct to interpret this as ... having separate spindles for logs + tempdb (ie: 3 spindles rather than just two) should take precedence over putting RAID5 on the Data?

Also; for heavy reporting (I think that's my environment) you clearly say that putting TempDB on a separate spindle is better. But should the Log collapse onto the DB (+ Index) or TempDB spindle? Of any given hour; I would say that only 10 minutes of it actually has a report running somewhere in background. Most of those reports are heavy index users that do not do sorts (Crystal Reports does the sorting - separate processor) so I don't think they use TempDB much. Is there a stock-software simple way to analyze TempDB activity vs. Log activity? My thinking is to combine TempDB and Log files.

I should note: Although we run a fair number of report queries, the non-reporting aspect of the system is more performance critical. Users here wouldn't notice a 100% gain in reporting speed but would find a 200% update / random access slow-down unacceptable.

Your wording almost implies that you don't mind putting Data on a mirror if space allows. My DB is relatively tiny (3GB) so mirrored 72GB drives would be ample. I thought striping actually added performance, albeit hampered by extra processing so only applicable if it's an adequate controller, ergo the popular recommendation of RAID-0 on speed hungry systems not requiring fault tolerance (hard to imagine). Are you saying it's a minor consideration compared with the advantage of spindle separation?

Note: As a humble suggestion, in a high budget system; wouldn't you make a 6th spindle for the OS's swap file? It could be small and cheap, but placing it with the OS would thrash some. Having it's own dedicated spindle would keep the heads in one place and, since it's like-memory storage, it should be as fast as humanly possible.|||If the users are willing to pay the extra $X,000 dollars for the extra spindle, I would certainly not mind using it. Part of this job, however, is to deliver maximum performance at minimum cost. If the existing system shows no signs of disk queues, then most of this is moot. At 3GB most of this database is going to fit in memory, especially if only the last 2 weeks of data are accessed 90% of the time.|||If the users are willing to pay the extra $X,000 dollars for the extra spindle, I would certainly not mind using it. Part of this job, however, is to deliver maximum performance at minimum cost. If the existing system shows no signs of disk queues, then most of this is moot. At 3GB most of this database is going to fit in memory, especially if only the last 2 weeks of data are accessed 90% of the time.
Thanks. They don't mind paying $2.5K for 4 new disks ($650 ea) and $700 for two 1GB RAM sticks. It'll make this box last until next year. It's a safety net for the largely unknown extra load of upcoming MRP - going live next month, and for a $30M company, really, $3K is nothing for a little insurance. They could easily pay the $30K for a new box if it were warranted, but it's not. The larger expense is drawing my time from my larger project, MRP Implementation.

Lots of other hardware boost paths make more sense, but this is the easiest (I think).

My hardware guy will do the actual upgrade. The day before, I'll simply move the DB from E: drive (the RAID 5 volume) to D: drive (Mirrored volume shared by D and C: drives). It'll be a little slow for a day. If it's a disaster; I'll take it down and move back (15 minutes). Then, the next evening (Friday night) he can remove the RAID 5 volume and rebuild those 4 bays however I request.

That box's backplane only has 6 disk bays.

I can either have one 4-disk RAID 5 volume or two RAID 1 volumes.

So; the question on this thread is which one?

With more memory (4GB) plus the boot.ine 3GB switch on; you have a good point that (for the time being) nearly all of the popular pages will always be in memory. So; (DB writes being asynchronous), having slower DB drives will be fine.

Since, in an effort to keep this risk free and simple, we are NOT upgrading the OS volume (C/D), if I go with just a single RAID-5 volume, the more time-critical (synchronous) LOG files will still be on the slower volume.

Going the 3-RAID1-volume route; for the shorter more ongoing index-heavy random IO I'm gambling that they'll usually be using in-memory-pages since the short random reads is where striped volumes help (correct? still fuzzy for me).

Solution 2 is to migrate the DB to our Standy Server, a non-production machine (I use for development). It's the same model (IBM x235) but by a fluke it has 2 processors that are faster (2.6 GHz vs 2.0 GHz), and a faster backplane (although purchased only a month later, IBM had just increased the specs from 400K to 530K). But; it currently only has a single Windows Mirrored volume.

This weekend, when remoted in and fooling around with all this upgrade-option stuff, I discovered it's RAID controller isn't the one my hardware guy thought. It's the 4Lx (see link (http://www-304.ibm.com/jct01004c/systems/support/supportsite.wss/docdisplay?brandind=5000008&lndocid=MIGR-59936)), not the 6M as he thought. Huge difference! So; as of this morning, Solution 2 is looking more attractive. A new RAID controller is another $950, but this (Standby) box (with 15K drives and faster controller), should be at least a 30% overall boost. That would also leave me with a far better standby solution (the old server). Slightly more risk since the OS needs rebuilding.

However; the Standby machine also has the same 6 bay backplane. So I'm stuck with the same Volume choice.

MCrowley - I realize I'm splitting hairs here. Although it is a real upgrade exercise, it's been a wonderful opportunity to display my ignorance, and by doing so, allow some of it to evaporate. :) However; any more "thinking" on this could be considered "playing around". It's really been an education to see the parts in motion, as it were. Well; back to the Application Software side of my job here. I feel more comfortable knowing I at least won't screw things up by eliminating RAID 5. Next year; I'll add a SAS or SAN box if the MRP grinds and expands as I think it will.|||As a caution, I do feel I have to say that learning this stuff is a good thing for you to do. But knowledge, being power, can be dangerous, too (just ask the Sorcerer's Apprentice). I do not know how your shop works, but as a generalism, businesses like to talk about ROI, and results, and other such rubbish. If you spend too much money for no noticeable effect, then the budget for next year may not consider your improvement ideas. It is an almost impossible hole to dig out of, too. Think about it this way. If your doctor prescribes a bunch of pills one week, then a whole new set the next week, and a third set the third week, and you are not feeling any better, how many weeks would you maintain faith in the doctor? This is much more a matter of office politics than technical detail, so your judgment there is going to carry much more weight than I am.|||As a caution, I do feel I have to say that learning this stuff is a good thing for you to do. But knowledge, being power, can be dangerous, too (just ask the Sorcerer's Apprentice). I do not know how your shop works, but as a generalism, businesses like to talk about ROI, and results, and other such rubbish. If you spend too much money for no noticeable effect, then the budget for next year may not consider your improvement ideas. It is an almost impossible hole to dig out of, too. Think about it this way. If your doctor prescribes a bunch of pills one week, then a whole new set the next week, and a third set the third week, and you are not feeling any better, how many weeks would you maintain faith in the doctor? This is much more a matter of office politics than technical detail, so your judgment there is going to carry much more weight than I am.
Thanks. I'm taking that to mean you don't think it's warranted. From a performance standpoint, you may be right. I'm not skilled enough reading Disk performance stats to really know. The upcoming MRP implementation is the motivation. It's well into the 6 digit number already, so $4K for adding 30% to 4 year old servers seems worth the insurance.

However; I'll take your advice and go with migrating the DB to Standby Server. This solution has the added ROI of improving the realistic capability of our Standby Server solution to actually assume the production load in the event of a failure. Coupled with the extra performance (and accompanying extra capacity for Load growth), it's a true ROI. Not ROI they may see, but "they" are in tune with the fact that not all ROIs are visible.

Of course; if I "upgraded" and made things worse by eliminating RAID-5 (ergo, this thread), then I'd (rightly) have a credibility problem.

The way this shop works is that my credibility is very good so they allow me to make all IT decisions, including spending - within a set budget, but they don't hesitate to expand that budget on my say-so. In 4 years, they have never once said "no" nor questioned my word, except as clarification. I've taken that trust to heart. In return; they're very satisfied with how smoothly IT runs itself (so to speak). They happily shell out my rate and give me utmost respect. It's wonderful and I would hate to forfeit that position so safeguarding my credibility is a worthy suggestion. Still; they spend $300K+ on new manufacturing gear, trucks, etc at the drop of a hat. $4K won't even be noticed (unless it buys them grief).

Appreciate all the help.

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

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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Monday, March 26, 2012

RAID & Drive Type for Server?

I'm shopping for a dedicated server service to host my web application that
uses SQL Server 2000 (eventually 2005) with everything on one box (IIS, COM+
application, etc.) although backups will go to a NAS unit. My database is
currently less than 300mb but I would certainly want to accommodate a 3gb+
database and accommodate ten or a hundred concurrent web users as my
business hopefully improves.
One of the hosting companies offers a number of RAID choices. Their choices
include RAID 0 (not fault tolerant - won't be using that), RAID 1 with two
drives, RAID 5 with three drives, and RAID 10 with four drives. There is
apparently NOT the luxury of further configuration options like more drives
using one RAID configuration, mixing RAID flavors with more drives, etc.
There is also the choice between using SATA II drives (7200 or 10000 rpm,
250 to 750gbs - about 200gb more than I will ever need) or SA-SCSI (10 or
15K rpm, 73gb only)
With these constrained choices, what would be your recommendations for the
best redundancy and best fault tolerance where the speed of read-writes is
less of an issue (because no user would ever notice the difference until the
server was running at 50% cpu or more)?
My thought is the more drives the better, the fast the rpm the better, but I
don't know enough about whether SATA drives is the next new thing compared
to SCSI technology, and I don't know whether the difference between RAID 5
or 10 would really make any difference (besides decreasing the chance of
failure with one more drive).
Thanks for any recommendations or thoughts.Given the small size of your database, I'd probably go with RAID 1. It would
give you enough space, less expensive than RAID 10, lower overhead than RAID
5, and better protection than RAID 0. And again given what you described, I'd
probably not want to explore any new(er) technologies, and I would just go
with the mature SCSI, have it set up, get it running and forget about it.
Linchi
"Don Miller" wrote:
> I'm shopping for a dedicated server service to host my web application that
> uses SQL Server 2000 (eventually 2005) with everything on one box (IIS, COM+
> application, etc.) although backups will go to a NAS unit. My database is
> currently less than 300mb but I would certainly want to accommodate a 3gb+
> database and accommodate ten or a hundred concurrent web users as my
> business hopefully improves.
> One of the hosting companies offers a number of RAID choices. Their choices
> include RAID 0 (not fault tolerant - won't be using that), RAID 1 with two
> drives, RAID 5 with three drives, and RAID 10 with four drives. There is
> apparently NOT the luxury of further configuration options like more drives
> using one RAID configuration, mixing RAID flavors with more drives, etc.
> There is also the choice between using SATA II drives (7200 or 10000 rpm,
> 250 to 750gbs - about 200gb more than I will ever need) or SA-SCSI (10 or
> 15K rpm, 73gb only)
> With these constrained choices, what would be your recommendations for the
> best redundancy and best fault tolerance where the speed of read-writes is
> less of an issue (because no user would ever notice the difference until the
> server was running at 50% cpu or more)?
> My thought is the more drives the better, the fast the rpm the better, but I
> don't know enough about whether SATA drives is the next new thing compared
> to SCSI technology, and I don't know whether the difference between RAID 5
> or 10 would really make any difference (besides decreasing the chance of
> failure with one more drive).
> Thanks for any recommendations or thoughts.
>
>|||I tend to agree with Linchi given the small size of the db. Most if not all
of the data should be in cache anyway so there should be very little disk
I/O on a constant basis.
--
Andrew J. Kelly SQL MVP
"Don Miller" <nospam@.nospam.com> wrote in message
news:eAH7N0W9GHA.3348@.TK2MSFTNGP03.phx.gbl...
> I'm shopping for a dedicated server service to host my web application
> that
> uses SQL Server 2000 (eventually 2005) with everything on one box (IIS,
> COM+
> application, etc.) although backups will go to a NAS unit. My database is
> currently less than 300mb but I would certainly want to accommodate a 3gb+
> database and accommodate ten or a hundred concurrent web users as my
> business hopefully improves.
> One of the hosting companies offers a number of RAID choices. Their
> choices
> include RAID 0 (not fault tolerant - won't be using that), RAID 1 with two
> drives, RAID 5 with three drives, and RAID 10 with four drives. There is
> apparently NOT the luxury of further configuration options like more
> drives
> using one RAID configuration, mixing RAID flavors with more drives, etc.
> There is also the choice between using SATA II drives (7200 or 10000 rpm,
> 250 to 750gbs - about 200gb more than I will ever need) or SA-SCSI (10 or
> 15K rpm, 73gb only)
> With these constrained choices, what would be your recommendations for the
> best redundancy and best fault tolerance where the speed of read-writes is
> less of an issue (because no user would ever notice the difference until
> the
> server was running at 50% cpu or more)?
> My thought is the more drives the better, the fast the rpm the better, but
> I
> don't know enough about whether SATA drives is the next new thing compared
> to SCSI technology, and I don't know whether the difference between RAID 5
> or 10 would really make any difference (besides decreasing the chance of
> failure with one more drive).
> Thanks for any recommendations or thoughts.
>|||One small caveat though, if a small database does a lot of writes, you can
still stress the I/O subsystem.
Linchi
"Andrew J. Kelly" wrote:
> I tend to agree with Linchi given the small size of the db. Most if not all
> of the data should be in cache anyway so there should be very little disk
> I/O on a constant basis.
> --
> Andrew J. Kelly SQL MVP
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:eAH7N0W9GHA.3348@.TK2MSFTNGP03.phx.gbl...
> > I'm shopping for a dedicated server service to host my web application
> > that
> > uses SQL Server 2000 (eventually 2005) with everything on one box (IIS,
> > COM+
> > application, etc.) although backups will go to a NAS unit. My database is
> > currently less than 300mb but I would certainly want to accommodate a 3gb+
> > database and accommodate ten or a hundred concurrent web users as my
> > business hopefully improves.
> >
> > One of the hosting companies offers a number of RAID choices. Their
> > choices
> > include RAID 0 (not fault tolerant - won't be using that), RAID 1 with two
> > drives, RAID 5 with three drives, and RAID 10 with four drives. There is
> > apparently NOT the luxury of further configuration options like more
> > drives
> > using one RAID configuration, mixing RAID flavors with more drives, etc.
> >
> > There is also the choice between using SATA II drives (7200 or 10000 rpm,
> > 250 to 750gbs - about 200gb more than I will ever need) or SA-SCSI (10 or
> > 15K rpm, 73gb only)
> >
> > With these constrained choices, what would be your recommendations for the
> > best redundancy and best fault tolerance where the speed of read-writes is
> > less of an issue (because no user would ever notice the difference until
> > the
> > server was running at 50% cpu or more)?
> >
> > My thought is the more drives the better, the fast the rpm the better, but
> > I
> > don't know enough about whether SATA drives is the next new thing compared
> > to SCSI technology, and I don't know whether the difference between RAID 5
> > or 10 would really make any difference (besides decreasing the chance of
> > failure with one more drive).
> >
> > Thanks for any recommendations or thoughts.
> >
> >
>
>|||Thanks for the quick reply yesterday and for your thoughts.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:A93579B9-CD81-4E39-89AA-8955C05CB114@.microsoft.com...
> One small caveat though, if a small database does a lot of writes, you can
> still stress the I/O subsystem.
> Linchi
> "Andrew J. Kelly" wrote:
> > I tend to agree with Linchi given the small size of the db. Most if not
all
> > of the data should be in cache anyway so there should be very little
disk
> > I/O on a constant basis.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> > "Don Miller" <nospam@.nospam.com> wrote in message
> > news:eAH7N0W9GHA.3348@.TK2MSFTNGP03.phx.gbl...
> > > I'm shopping for a dedicated server service to host my web application
> > > that
> > > uses SQL Server 2000 (eventually 2005) with everything on one box
(IIS,
> > > COM+
> > > application, etc.) although backups will go to a NAS unit. My database
is
> > > currently less than 300mb but I would certainly want to accommodate a
3gb+
> > > database and accommodate ten or a hundred concurrent web users as my
> > > business hopefully improves.
> > >
> > > One of the hosting companies offers a number of RAID choices. Their
> > > choices
> > > include RAID 0 (not fault tolerant - won't be using that), RAID 1 with
two
> > > drives, RAID 5 with three drives, and RAID 10 with four drives. There
is
> > > apparently NOT the luxury of further configuration options like more
> > > drives
> > > using one RAID configuration, mixing RAID flavors with more drives,
etc.
> > >
> > > There is also the choice between using SATA II drives (7200 or 10000
rpm,
> > > 250 to 750gbs - about 200gb more than I will ever need) or SA-SCSI (10
or
> > > 15K rpm, 73gb only)
> > >
> > > With these constrained choices, what would be your recommendations for
the
> > > best redundancy and best fault tolerance where the speed of
read-writes is
> > > less of an issue (because no user would ever notice the difference
until
> > > the
> > > server was running at 50% cpu or more)?
> > >
> > > My thought is the more drives the better, the fast the rpm the better,
but
> > > I
> > > don't know enough about whether SATA drives is the next new thing
compared
> > > to SCSI technology, and I don't know whether the difference between
RAID 5
> > > or 10 would really make any difference (besides decreasing the chance
of
> > > failure with one more drive).
> > >
> > > Thanks for any recommendations or thoughts.
> > >
> > >
> >
> >
> >

RAID - config Question

hard to really answer but here is the question
Your opinions please:
I have 6 15k 72GB Drives in an ARRAY.
This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO OLTP,
Etc)
All I do on this box is copy mass amounts of data from DB A to DB B. This is
dev platform only not a production environment.
Generally speaking, I know that placing Logs and TempDB on one Volume and
DATA\Indexes on another Volume is preferable.
However, My thoughts are this.....
If I create 2 seperate Volumes, Each volume would have 3 disks. NOt really
that great for Striping (I'm using RAID 0 As I need no Fault Tolerance)
If I use 1 Volume, I'm not getting the Seperation that we would typically
want in a production OLTP Environment. However, with 1 single volume, I will
have 6 disks to stripe across....
If data copy performance was your main objective, which would likely provide
best performance ?"
A) 2 volumes with 3 disks each
B) 1 volume with all 6 disks
This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
Read/Write Cache (Configured 50/50)
Thanks in advance
Greg Jackson
Portland, OR
GAJ
pdxJaxon wrote:
> hard to really answer but here is the question
> Your opinions please:
> I have 6 15k 72GB Drives in an ARRAY.
> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO
> OLTP, Etc)
> All I do on this box is copy mass amounts of data from DB A to DB B.
> This is dev platform only not a production environment.
> Generally speaking, I know that placing Logs and TempDB on one Volume
> and DATA\Indexes on another Volume is preferable.
> However, My thoughts are this.....
> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt
> really that great for Striping (I'm using RAID 0 As I need no Fault
> Tolerance)
> If I use 1 Volume, I'm not getting the Seperation that we would
> typically want in a production OLTP Environment. However, with 1
> single volume, I will have 6 disks to stripe across....
> If data copy performance was your main objective, which would likely
> provide best performance ?"
> A) 2 volumes with 3 disks each
> B) 1 volume with all 6 disks
> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery
> Backed Read/Write Cache (Configured 50/50)
> Thanks in advance
> Greg Jackson
> Portland, OR
> GAJ
Using only striping, I might consider a simple 2 disk array for t-logs
and tempdb and a 4disk array for the data. But it depends on how much
data you're inserting/updating. If the t-log is extremely active use 2
3-disk arrays.
Where is the OS located?
David Gugick
Imceda Software
www.imceda.com
|||o.s. will be with Tlogs and tempdb
Currently I have 3 and 3 and my DTS jobs are absolutely PEGGING the IO on my
TempDB Volume.
to be expected as I'm moving 10's of millions of records. BUT, if I can
improve by reconfiguring and speed this up, I can reduce the cost of the
effort here.
(taking 8+ hours now. If I can increase performance by 10% it is a
significant savings)
GAJ
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23igp2gsEFHA.3032@.TK2MSFTNGP12.phx.gbl...
> pdxJaxon wrote:
> Using only striping, I might consider a simple 2 disk array for t-logs and
> tempdb and a 4disk array for the data. But it depends on how much data
> you're inserting/updating. If the t-log is extremely active use 2 3-disk
> arrays.
> Where is the OS located?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||By volume I hope you mean array and not simply a logical device. If fault
tolerance is not an issue then why not have one disk for the OS and the Log
files (both tempdb and the user dbs). Then either use the other 5 for the
data files or take one or two for tempdb and the others for the user data
files. You absolutely need to separate the logs from the data files. If
you have that much tempdb you may want to split that data file out as well
but only testing will tell for sure. In either case change the caching on
the disk controller to be 100% write back and you should see a big
improvement as well. 8 hours to process only a few 10's of millions of rows
is pretty bad.
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:emi80CsEFHA.732@.TK2MSFTNGP12.phx.gbl...
> hard to really answer but here is the question
> Your opinions please:
> I have 6 15k 72GB Drives in an ARRAY.
> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO
> OLTP, Etc)
> All I do on this box is copy mass amounts of data from DB A to DB B. This
> is dev platform only not a production environment.
> Generally speaking, I know that placing Logs and TempDB on one Volume and
> DATA\Indexes on another Volume is preferable.
> However, My thoughts are this.....
> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt really
> that great for Striping (I'm using RAID 0 As I need no Fault Tolerance)
> If I use 1 Volume, I'm not getting the Seperation that we would typically
> want in a production OLTP Environment. However, with 1 single volume, I
> will have 6 disks to stripe across....
> If data copy performance was your main objective, which would likely
> provide best performance ?"
> A) 2 volumes with 3 disks each
> B) 1 volume with all 6 disks
> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
> Read/Write Cache (Configured 50/50)
> Thanks in advance
> Greg Jackson
> Portland, OR
> GAJ
>
|||actually I misspoke.
the entire job is moving a more than 10s of millions of records (~60GB).
I have individual tables with 10s of millions of records.
Not the biggest db I"ve ever played with, but it's non-trivial.
By Volume I mean an array.
I have 2 arrays with 3 disks each Both RAID 0.
On array 1 I have C: (OS) and D: (Logs and TempDB)
On Array 2, I have E: (Data and Indexes)
You think making the Cache 100% writes will help ?
I am reading from DB A and Writing to DB B .....
If so, that is an easy change.
GAJ
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OuYeHJtEFHA.1348@.TK2MSFTNGP14.phx.gbl...
> By volume I hope you mean array and not simply a logical device. If fault
> tolerance is not an issue then why not have one disk for the OS and the
> Log files (both tempdb and the user dbs). Then either use the other 5 for
> the data files or take one or two for tempdb and the others for the user
> data files. You absolutely need to separate the logs from the data files.
> If you have that much tempdb you may want to split that data file out as
> well but only testing will tell for sure. In either case change the
> caching on the disk controller to be 100% write back and you should see a
> big improvement as well. 8 hours to process only a few 10's of millions of
> rows is pretty bad.
> --
> Andrew J. Kelly SQL MVP
>
> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:emi80CsEFHA.732@.TK2MSFTNGP12.phx.gbl...
>
|||pdxJaxon wrote:
> actually I misspoke.
> the entire job is moving a more than 10s of millions of records
> (~60GB).
> I have individual tables with 10s of millions of records.
> Not the biggest db I"ve ever played with, but it's non-trivial.
> By Volume I mean an array.
> I have 2 arrays with 3 disks each Both RAID 0.
> On array 1 I have C: (OS) and D: (Logs and TempDB)
> On Array 2, I have E: (Data and Indexes)
>
> You think making the Cache 100% writes will help ?
> I am reading from DB A and Writing to DB B .....
> If so, that is an easy change.
>
If the databases are on the same server, I might try the following:
Array 1 - OS + TempDB (2 drives) - only writes
Array 2 - Database 1 - only reads
Array 3 - Database 2 - only writes
Make sure your clustered indexes are not causing the data to insert out
of order. If so, consider dropping the clustered indexes before the load
or redesign to a key that won't cause page splitting.
David Gugick
Imceda Software
www.imceda.com
|||Hi,
I think Andrew really nailed it.
This is might be a good opportunity to tweak the IoPageLockLimit registry
setting on your server. I believe Windows restricts the amount of RAM that
can be locked for file system operations to 512 kb. I would play around with
this setting and see if you can gain any performance...
Also, your G4 has at least 1 MB of L2 cache (depending how many CPUs you
have, it could be up to 2 MB). Windows is optimized for 256 KB so you might
want to check the SecondLevelDataCache setting.
If your business still requires better performance after all the tips given
by the previous people, you could invest 500$ to get a second array
controller (such as the Smart Array 641)...
Sasan Saidi
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
Senior DBA
"pdxJaxon" wrote:

> hard to really answer but here is the question
> Your opinions please:
> I have 6 15k 72GB Drives in an ARRAY.
> This box is ONLY used for doing Data Transfer Activity (DTS Jobs) (NO OLTP,
> Etc)
> All I do on this box is copy mass amounts of data from DB A to DB B. This is
> dev platform only not a production environment.
> Generally speaking, I know that placing Logs and TempDB on one Volume and
> DATA\Indexes on another Volume is preferable.
> However, My thoughts are this.....
> If I create 2 seperate Volumes, Each volume would have 3 disks. NOt really
> that great for Striping (I'm using RAID 0 As I need no Fault Tolerance)
> If I use 1 Volume, I'm not getting the Seperation that we would typically
> want in a production OLTP Environment. However, with 1 single volume, I will
> have 6 disks to stripe across....
> If data copy performance was your main objective, which would likely provide
> best performance ?"
> A) 2 volumes with 3 disks each
> B) 1 volume with all 6 disks
> This is an HP DL-380 with 6i Raid Controller and 128mb of Battery Backed
> Read/Write Cache (Configured 50/50)
> Thanks in advance
> Greg Jackson
> Portland, OR
> GAJ
>
>
|||thanks a ton.
I'm gonna look into these settings
GAJ
|||SQL Server does not need read cache on the controller since it does it's own
read ahead caching anyway and will usually do a better job at predicting
what it will need. You are doing massive writes and your disks probably
can't handle the load by them selves so going 100% write cache will help a
ton. Since you are doing so much tempdb and log activity you want to make
sure the tempdb is not on the same disk as the logs.
Andrew J. Kelly SQL MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:udwH1ftEFHA.960@.TK2MSFTNGP09.phx.gbl...
> actually I misspoke.
> the entire job is moving a more than 10s of millions of records (~60GB).
> I have individual tables with 10s of millions of records.
> Not the biggest db I"ve ever played with, but it's non-trivial.
> By Volume I mean an array.
> I have 2 arrays with 3 disks each Both RAID 0.
> On array 1 I have C: (OS) and D: (Logs and TempDB)
> On Array 2, I have E: (Data and Indexes)
>
> You think making the Cache 100% writes will help ?
> I am reading from DB A and Writing to DB B .....
> If so, that is an easy change.
>
> GAJ
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OuYeHJtEFHA.1348@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks,
I'm gonna light this up Today...!
GJ