Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Moving to a SAN, but the sizing concept doesn't sound right.... Expand / Collapse
Author
Message
Posted Wednesday, December 7, 2011 3:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:47 AM
Points: 6,842, Visits: 13,368
Hi folks,

it's time for a (partial) hardware replacement and our company decided to provide a SAN as a replacemenet of the dedicated drives of let's say 10 server with two drives each, originally.

To be specific: there'll be only one rather small drive per server just to hold the OS and the pagefile, eventually. (I say "eventually" since the the folks responsible
for the sizing decided to use a standard config of less than 50GB, regardless of the servers memory. My question how they would provide a full memory dump for a server with 256GB memory in case of a support request to MS is "still under investigation"... but that's a different story).

The SAN will have 15 spindles in total and RAID0, RAID1, and RAID5 groups.

There will be about 50 SQL Server DBs (ranging from just a few MB up to 80GB) as well as several applications.

If I'm not completely wrong there'll be only max. 7 spindles net to hold all the LUNs (based on the RAID level and the physical number of spindles).

Even though the total size of storage capacity is enough to meet our needs, the number of physical spindles seems to be too low. From my perspective the better disc performance (e.g. 15krpm vs. currently 10krpm) will not compensate the parallel access from several server to the same spindle(s).

My question "Why not reduce the disc size in favor of more spindles?" has been answered with "The discs we've chosen are based on the lowest cost per GB and the best performance." I suggested to at least increase the local disc size to have TempDb stored locally (on a separate logical drive), but so far without any luck...

Am I worried for no reason?

Edit: I'm planning to "attack" the SAN using SQLIO concurrently from several servers prior to going into production to verify the performance metrics they've told us.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1218259
Posted Wednesday, December 7, 2011 3:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
There are lots of strategies that can be used. But I am a fan of RAID10 or Raid 0 + 1 or Raid 1 + 0, basically mirroring and striping, and unless someone can't point out some stats otherwise it is the fastest configuration in most cases..

A Quick look at http://en.wikipedia.org/wiki/RAID

It sounds like you are gonna be sharing these 15 spindles accross 10 servers. In most cases where I have control and available spindles I do RAID 10 for data log and tempdb. I am a little concerned about the number of servers sharing the storage but was thinking 6 drives RAID 10 for data, 4 drives RAID 10 for Logs, and 4 drives RAID 10 for Tempdb with 1 hot spare..

I will say I am by FAR not an expert on these things but I am usually looking for resiliency and performance.

When I got my new cluster I worked over all the drives with SQLIO, one drive at a time, then a couple at the same time, then ALL of them. It helped me understand what I could expect in terms of performance overall.

CEWII
Post #1218267
Posted Wednesday, December 7, 2011 3:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:47 AM
Points: 6,842, Visits: 13,368
Thank you for the prompt response, Elliot. Much appreciated.

I'm concerned, too. Especially, since all of our DBs now would have to share the same physical drives for logs and tempdb. The (low) physical number of spindles together with the RAID concept you suggested (which makes sense) doesn't leave that much room for alternatives.

I guess I'll invest some more time in planning the SQLIO test. I wasn't sure if I should expand the test to include all server at the same time. But it seems like this will at least provide me some more detailed figures what to expect. "Worst case" could be to be able to demonstrate the concept failed (in terms of not providing the promised performance).

Did you run the same config file on each server at the same time or did you use different cofigurations?




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1218279
Posted Wednesday, December 7, 2011 4:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
One of the things that I strongly recommend is formatting the SQL drives as 64K clusters. Which as I understand it allows SQL to grab 1 extent of data in a single read, where an extent is 64K, where the default which is 4K would require 16 reads..

I'm sure someone might be able to school me but thats my understanding..

Coincidently the way I laid it out Dat, Log, and Tempdb, their spindles are not shared within a server but ARE shared among the servers.. Server A uses the same spindles as Server B for Data, but Server A uses different spindles for Data/Log/TempDb, Server B uses the same spindles as Server A.

You might even consider not changing a little server in favor of less sharing.. Not sure of your hardware age..

CEWII
Post #1218286
Posted Wednesday, December 7, 2011 4:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:47 AM
Points: 6,842, Visits: 13,368
The SAN is provided by a 3rd party and a different group of our company is responsible for it.
But I'll try to raise the 64k formatting you mentioned. Let's see how they react...

The server we currently replace are three years old. Some of those would easily run for a few more years but due to some policies there'll be a replacement every three years without any further discussion. Is it the rule? Yes. Does it makes sense? No.

If I would have a vote I wouldn't replace half of the server at this time. But then the SAN would be over-sized leading to an even lower number of spindles (what would be your preferred spindle-setup for 8 spindles instead of 15 ). Unfortunately, the folks responsible for the SAN are only calculating by storage space required.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1218288
Posted Wednesday, December 7, 2011 4:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
Don't give them a choice.. Once the disk is presented to the machine it can be formatted any way you like.. I wouldn't bend on this topic.. Reformat it when you get it. There are some topics that are not open for discussion and this is one of them.

3y.. Hm, Actually I consider that old.. And I would argue it does make sense. Especially in light of things like hardware maintenance agreements that for after a certain period the vendor won't do OR jacks the price up. I think HP/Compaq does this.. This radically increases the risks associated with a hardware failure and that hardware might not be availabe to replace and you are then forced into a disorderly upgrade. So yeah, I think it makes sense. And in a lot of ways is good since we have a predictable maintenance schedule but the machines should be spaced so not ALL or even a substantial number get replaced at a time.

I like more spindles, so 15 isn't a problem, but of the 15 I would keep one out as a hot spare, but thats me.. I would break the remainder down the way I discussed earlier.. I was thinking same number of spindles less machines connecting to it..

Also, how will the connection to the SAN be made, Fiber? iSCSI?, other?

CEWII
Post #1218293
Posted Wednesday, December 7, 2011 4:33 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:18 PM
Points: 17,964, Visits: 15,968
Yes on the 64K, get them to format as 64K clusters. If filestreaming with larger files, you may want larger cluster sizes.

The spindles seems off.

The local drive is far to small (Server 2008 R2 for instance will chew up most of that 50GB for just the OS).

More spindles would be a far better idea.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1218296
Posted Wednesday, December 7, 2011 4:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 13, 2014 9:47 AM
Points: 6,842, Visits: 13,368
SQLRNNR (12/7/2011)
Yes on the 64K, get them to format as 64K clusters. If filestreaming with larger files, you may want larger cluster sizes.

The spindles seems off.

The local drive is far to small (Server 2008 R2 for instance will chew up most of that 50GB for just the OS).

More spindles would be a far better idea.


I hear both of ya!!

I'll keep trying to talk those folks into more spindles and much larger local drives (the fact they still didn't answer my memory dump related question counts for our group I guess...).
I'll also insist on the 64K setting. Thanks to both of you for bringing that up!

Right now nothing is ordered yet. Fortunately. I might need to get management involved if we can't reach an agreement. Seems like it's becoming a "battle zone" requiring a little more attention...

If I can't "win the battle" before the metal & fibre get installed, I'll do a little stress test (well, I'll do it anway...). If they can prove me wrong, I'd be happy... (sort of, at least)




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1218304
Posted Wednesday, December 7, 2011 6:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 5,446, Visits: 7,616
LutzM (12/7/2011)

I hear both of ya!!

I'll keep trying to talk those folks into more spindles and much larger local drives (the fact they still didn't answer my memory dump related question counts for our group I guess...).
I'll also insist on the 64K setting. Thanks to both of you for bringing that up!

Right now nothing is ordered yet. Fortunately. I might need to get management involved if we can't reach an agreement. Seems like it's becoming a "battle zone" requiring a little more attention...

If I can't "win the battle" before the metal & fibre get installed, I'll do a little stress test (well, I'll do it anway...). If they can prove me wrong, I'd be happy... (sort of, at least)


A third vote for 64k. With more than white papers, but biased provincial evidence. Insane returns on the run-times of warehouse queries, to the tune of ~15 seconds for each minute it used to run... on the exact same iron with just different formatting and settings.

One of the huge things you're going to need to deal with Lutz is how they're going to handle the SAN's cache. Raid5 vs. RAID 10 these days isn't enough of a big deal, mostly because of the cache and improvements to the RAID5 controller programming. There's still a difference but it's not the "What's wrong with you?!?!?!" question it used to be.

That cache and its usage is going to be critical to if you have enough spindles or not.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1218327
Posted Wednesday, December 7, 2011 6:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
SQLRNNR (12/7/2011)
The local drive is far to small (Server 2008 R2 for instance will chew up most of that 50GB for just the OS).
Not sure I agree with that.. I would expect 50GB to be about right, provided that NO system databases are allowed on the OS drives, which is my policy. I have quite a few installs of SQL 2008R2 on Win 2008R2 and I expect the base install with required components AND the components of SQL that must be installed on C: to use up right about 20GB + the page file.. I have seen this over and over again.. And I still have sufficient space for SPs of the OS..

CEWII
Post #1218331
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse