sql server

[billb]

We're having a very small accounts system upgraded from unix to sql ( 5-6 users ) and they have sent in an all in one quote and the server they're putting it on says just 3 raid disks. I am right in thinking theres something in sql that shouldnt be put on raid so i should have 2 mirrored disks for the system files and the sql bit and then the raid for the data?

any advice appreciated

[pdV6]

Mirrored disks = RAID-1, so I don't know what your problem with it would be?

Seriously, though, a harware RAID solution, whether mirrored, striped or both should appear to the o/s and thus SQL Server as a single disk drive, so there shouldn't be any problem.

[_DJ_]

SQL will work 'ok' with RAID5 Disks. However, RAID 5 isn't particularly good for disk writes, so if the DB is updated a lot then you might get poor perforance. RAID 1 is better for writes, but is more expensive (you need more disks for the same usable storage). You also often find the OS, Database and Logs been located on different disks. This is for 2 reasons:

1) performance. Transaction logs are written sequentially so you don't want the OS moving the disk heads around all the time

2) Resilience. If the DB drive fails you can recover all of your transactions using a restore and the logs, but only if the logs weren't on the same drive array.

Darren.

edited to add: I'm assuming they intend to use RAID 5 as RAID 1 (and 0+1) use an even number of drives and that's really the only other RAID level used commonly)


>> Edited by _DJ_ on Wednesday 31st March 15:19

[pdV6]

RAID0: Data "striped" over several disks. Fast writes, no redundancy, larger capacity.
RAID1: Mirrored disks. Slower writes, redundancy.
RAID5: Mirrored sets of striped disks. Best of both worlds i.e. fast writes with redundancy & extra capacity.

I'm guessing that with only 5-6 users and for an "accounting system", ultimate performance probably isn't an issue, but safety is. As your suppliers are quoting for "3 raid disks" I would assume that this was for RAID1, using 2 drives mirrored and a hot-swappable spare.

Sounds eminently sensible for the scenario you have described.

{edit:} Actually, my description of RAID5 is incorrect - I've just managed to successfully define RAID10!

some web site said:

RAID-5. This type includes a rotating parity array, thus addressing the write limitation in RAID-4. Thus, all read and write operations can be overlapped. RAID-5 stores parity information but not redundant data (but parity information can be used to reconstruct data). RAID-5 requires at least three and usually five disks for the array. It's best for multi-user systems in which performance is not critical or which do few write operations.

So maybe they are quoting for RAID5 after all!

>> Edited by pdV6 on Wednesday 31st March 15:25

[_DJ_]

pdV6 said:

RAID0: Data "striped" over several disks. Fast writes, no redundancy, larger capacity. RAID1: Mirrored disks. Slower writes, redundancy. RAID5: Mirrored sets of striped disks. Best of both worlds i.e. fast writes with redundancy & extra capacity. I'm guessing that with only 5-6 users and for an "accounting system", ultimate performance probably isn't an issue, but safety is. As your suppliers are quoting for "3 raid disks" I would assume that this was for RAID1, using 2 drives mirrored and a hot-swappable spare. Sounds eminently sensible for the scenario you have described.

Codswallop (is that a word?).

RAID 0 - quick read/writes, absolutely no redundany - the more disks, the bigger the chance of failure

RAID 1 - marginally slower reads, slower writes

RAID 5 - marginally slower reads, crap (3x) writes.

Your average IT place will specify RAID 5. RAID 1 and a hot spare is a safe solution, but your useable space would be the same as a single, non RAID'd disk. i.e if you've got 3x9GB disks, you'd only be able to store 9GB.

[j_s_g]

Another thing to think about... depending on the system being developed/bought, MSDE might be more appropriate than SQL Server. It's licensing is basically free (with a few caveats relating to owning MS Office), and in your situation will probably function identically.

[pdV6]

j_s_g said:

Another thing to think about... depending on the system being developed/bought, MSDE might be more appropriate than SQL Server. It's licensing is basically free (with a few caveats relating to owning MS Office), and in your situation will probably function identically.

Up to 5 users you'll be fine. Thereafter, an annoying choking algorithm kicks in to slow performance down so that you'll be tempted to go out and by SQL Server proper. Trouble is, once you get your usage down to 5 or less again, the performance hit remains in place.

Microsoft. Don't you just love 'em?

[pdV6]

_DJ_ said:

Codswallop (is that a word?).

Fair enough re: RAID5 - you'll notice that I spotted the mistake fairly quickly. I stand by my description of RAID0 and RAID1 tho'

[_DJ_]

pdV6 said:

_DJ_ said: Codswallop (is that a word?). Fair enough re: RAID5 - you'll notice that I spotted the mistake fairly quickly. I stand by my description of RAID0 and RAID1 tho'

OK, I'll give you that . If I were to be really picky I'd suggest RAID 0 doesn't give you larger capacity (it's just the same as a non RAID'd set of disks) though it is larger than any other RAID configuration....

[pdV6]

_DJ_ said:

If I were to be really picky I'd suggest RAID 0 doesn't give you larger capacity (it's just the same as a non RAID'd set of disks) though it is larger than any other RAID configuration....

Furry muff. What I really meant was that the logical drive you end up with is as large as the sum of the disks that comprise the array, so theoretically you can increase drive space simply by adding more elements to the array.

[andyf007]

Either RAID1 or RAID5 is good, but generally I find RAID5 easier and quicker to repair. The only thing you need to worry about is the quality of the RAID controller and the amount of RAM (1Gb minimum). The processors are not much of a concern and will probably fall asleep with just 5 users. If you can stretch to it upgrade to Gigabit networking, the server should have it as standard anyway. Also, dependant on the software package you may find some processing (reports and the like) runs on the desktops, so make sure they have plenty of RAM too.

Andy

[j_s_g]

pdV6 said:

j_s_g said: Another thing to think about... depending on the system being developed/bought, MSDE might be more appropriate than SQL Server. It's licensing is basically free (with a few caveats relating to owning MS Office), and in your situation will probably function identically. Up to 5 users you'll be fine. Thereafter, an annoying choking algorithm kicks in to slow performance down so that you'll be tempted to go out and by SQL Server proper. Trouble is, once you get your usage down to 5 or less again, the performance hit remains in place. Microsoft. Don't you just love 'em?

The whole "5 user" thing is a bit of a myth. What it actually does (having pulled it apart) is only allow 5 concurrent command executions... all incoming requests are pipelined through this 5-command pool. So, you can generally have many, many more than 5 users before really experiencing problems, assuming the transaction rate is fairly low. If, on the other hand, you're trying to run a web-site off it, you'll be stuffed if you're continually hitting it.

[pdV6]

That's very interesting. I heard today that in MSDE/SQL2000 equivalent, MS have quietly upped the limit to more like 20-25 users. "Quietly" because they still want people to think they have to go out and buy SQL Server proper at vast expense!

IIRC the 5 user thing was a right pain in MSDE/SQL7 equivalent. I'm pretty sure we had a customer who's performance went through the floor once they'd "accidentally" put too many users on one day and it never fully recovered, so we chalked it up to the 5-user limit.

[j_s_g]

pdV6 said:

That's very interesting. I heard today that in MSDE/SQL2000 equivalent, MS have quietly upped the limit to more like 20-25 users. "Quietly" because they still want people to think they have to go out and buy SQL Server proper at vast expense! IIRC the 5 user thing was a right pain in MSDE/SQL7 equivalent. I'm pretty sure we had a customer who's performance went through the floor once they'd "accidentally" put too many users on one day and it never fully recovered, so we chalked it up to the 5-user limit.

That's based upon the SQL Server 7 release of MSDE. The SQL Server 2000 one is much, much nicer. I've done a lot of work on it (writing articles on it, editing for books, etc.) - as long as you're licensed to use it, and you have some replacement for Enterprise Manager (www.aspenterprisemanager.com is coming along nicely), it's a good option to have.

[pdV6]

j_s_g said:

That's based upon the SQL Server 7 release of MSDE. The SQL Server 2000 one is much, much nicer. I've done a lot of work on it (writing articles on it, editing for books, etc.) - as long as you're licensed to use it, and you have some replacement for Enterprise Manager (www.aspenterprisemanager.com is coming along nicely), it's a good option to have.

Oh yes - most of our customers (apart from the larger ones) are now on it in place of Access. Having an Office Developer license allows you to distribute it with applications and we wrote a noddy EM/QA replacement for just this purpose...