March 16, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/shouldyoubuyasan.asp>http://www.sqlservercentral.com/columnists/awarren/shouldyoubuyasan.asp
March 18, 2003 at 10:19 am
One other thing is to check how the data is striped to the SAN. With a SAN, the drives can have a particular stripe size and the OS on the server can write to those drives with another stripe size. One site of our company ended up with 100% fragmentation across all drives...definately a slow-down.
Since there are differing opinions on how to stripe the drives, you will need to make your own educated guess. Microsoft recommends 64k stripe size for running SQL Server. Other companies' software running on top of SQL Server suggest different sizes.
We have found that 64k works for us. Even though SQL writes in 8k blocks.
Michelle
Michelle
March 26, 2003 at 12:55 pm
First off, to amplify a bit on what Andy said, I would NEVER place a database file on NAS. If you are looking for SQL Server storage definately look at SAN not NAS.
The only problem with SAN's is the expense, those with penny pinching CFO's beware! Also, not all SAN's are created equal. Our first SAN's were Xiotech. After several years of using them and suffering several incidents of data loss, we kicked them to the curb and went with EMC. We not only improved our reliability, but our I/O performance increased between 25%-30%! As I said before, it isn't cheap though, we price out our storage costs internally at $220 per Gigabyte! That figure includes the Fibre Channel cards, cabling, storage, administration, and backup.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 26, 2003 at 2:46 pm
Watch the RAID type you use. Unfortunately many vendors know about SANs, but not about SQL. Unless your application is appropriate, you don't want to end up with RAID 5 everywhere, but this appears to be the default for most suppliers, and consequently SANs get a bad rep.
Costs are high, but they are also good, if done well. They make especially good sense for clusters, and can offer good cost savings over multiple clusters compared to lots of DAS units.
Edited by - darren.green on 03/26/2003 2:47:34 PM
Darren Green
SQLDTS.com | SQLIS.com | Konesans Ltd
March 26, 2003 at 3:39 pm
Once operating on a SAN, does it still really matter that you keep log and data on separate drives? Would anyone go as far as to connect them via separate HBAs?
March 26, 2003 at 3:44 pm
Andy, I was reading with keen interest when the article ended! Yes, please provide more detail. We have a new EMC SAN at a Qwest data center--everything redundant. It's like driving a Cadillac. The hard part (still) is the SQL clustering. We have 2 4X Dell servers with failover--one for development and staging and another one for production. But we continue to have a tough time setting up the instance on the virtual servers. Anyway, a bit off-topic but a natural continuation of your article if you get the urge. Thanks.
Hank
March 26, 2003 at 3:45 pm
I'd love to hear more discussion on RAID configuration for databases on a SAN. We have a few relatively new SQL Servers on a SAN here, and right now the whole SAN is RAID 5, and it's all configured as one big pool on the SAN side, and the sysadmins just carve chunks out of this one big pool whenever we need space. Our performance is good, but I'm pretty sure that's because we're always writing to the cache. In short, I'm not very comfortable without mirrored or RAID 10 storage for my logs and separate RAID 5 or RAID 10 for my data.
So I'd like to hear people's war stories regarding the following: what happens to our throughput if we get busy enough to flood the cache? And how hard is it to flood a SAN cache? Has this happened to any of you? I'm concerned, because the attitude here seems to be "when that happens, we'll just buy more." Is my paranoia level justified?
By the way, even if you give me great anecdotal and theoretical evidence that our configuration is terrible, I might not be able to do much about it. Everybody here seems to be enthralled with our SAN performance because it's better than the local storage they used to have, so I might be in a politics-trump-technology situation.
Any comments?
March 27, 2003 at 6:15 am
I think whether you break up the logs onto separate drives (and in extreme cases separate HBA's) is situational, regardless of the current best practices. If you've got a 1TB db and log files, sure, why not break it up. But if you have 50 or 500 db's, you're not going to see the same amount of improvement by moving the log files - after all, the 'theory' is that log writes are mainly sequential. If SQL is writing to 500 log files, don't see that as very sequential.
As far as RAID5, let me go against the grain and vote for that too! Yes, there are times when RAID10 is more appropriate, but cost is almost always an issue. But as Darren mentioned, if you were on RAID10 and the SAN vendor sets up RAID5, you'll probably see the difference.
Hank, if you're still having issues with the cluster, post in the discussion area and maybe we can help you figure it out.
Thanks everyone for reading and commenting. I probably will do a follow up article in a few weeks. So far so good!
Andy
March 28, 2003 at 10:02 am
We've had a couple Compaq/HP SANS here for our Demo system, 90 servers, all with no local storage, booting remotely off the SAN.
We just got a new SAN, 12TB, for production use, of which a piece will be databases. Liely still will keep the c: drive (OS and SQL) on local storage, the data and log files on the SAN. Not my choice, just the way winds are blowing here.
We had a big meeting with HP. The SAN has 160 drives, basically carved into 2 groups. Lots of work on how the 2 groups are striped, basically some variation of RAID 5, can lose 4 of the 80 drives before there's data loss. Remote mirroring across the campus, blah, blah.
Anyway, the recommendation for dbs was to place Server 1 data on disk group 1, Server 1 logs on disk group 2. Then for server 2 you reverse for some fault tolerance. In the event a disk group dies (notice they tout how fault tolerant this is, but they want you to have 2 groups. Interesting), haven't lost all yo' stuff.
So I'm not thrilled. I've got a heavy performance monster going here, about .5TB ad hoc reporting, and they want the logs on one group, data on another. Plus file servers are on the SAN.
What does that mean for performance? HP claims shouldn't be a big deal. So many spindles. OK, I buy that, but is some big honking machine with 80 spindles really more efficient for a DB than a few 15 spindle arrays? NetIQ mentioned efficiency is way down after 9-10 drives. Wouldn't it be better to have a bunch of 10 drive arrays that can be striped together?
Maybe that's what they do and don't tell us, but I'm not sold. I'll be happy to report some experiences once we get live on the new SAN. Course the server hardware is going form a 4x4 (550MHz) to a 4X8 (2GHz), so that will mask things. Hey, why take chances.
Plus it will make the SAN guys look good. I'm still curious to see if anyone is ever going to run the TPC marks against the same server hardware with local SCSI and on a SAN. Not sure anyone wants to see the numbers (including vendors). I'm just not thrilled with sharing db disks with file disks, but we'll see.
Steve Jones
September 16, 2003 at 3:27 am
Steve
Did you do the install? any stats re the SAN? I have the same concerns as yourself.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
March 29, 2004 at 7:31 am
Would you gain any performance by keeping the logs on internal RAID 10 drives and just use the SAN for the data files?
We're setting up a COMPAQ/HP SAN here and are not sure what configuration to go with initially.
Doug
May 4, 2005 at 7:10 am
Campuuuusgle?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply