February 22, 2011 at 8:39 am
Hello,
I wondered if I could gather some opinions/advice on stand alone SQL 2008/5 machines sharing data and log locations on a SAN? I'm thinking its ok, I wonder whether I might just separate the 2005 from the 2008 though. Has anyone any thoughts on this?
Thanks for reading!
D.
February 22, 2011 at 9:34 am
It depends on the traffic each will see. My data warehouse has separate files for the different data sets in one DB, because it has so much traffic that we have to spread that one DB out to different SAN platters.
It come down to response time. IF you have a small environment, speaking in the context of the traffic these DB servers will see, then sharing SAN space may work out just fine. If you begin to experience lag time that can be attributed to the disk contention, you will want to begin splitting the log files from the data files, then splitting the data files from each other, etc.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 22, 2011 at 10:14 am
Oooh, SAN, the mythical black box of wonder...
No, It will not make a difference. SAN guys/gals separate the disks into different disk groups or Aggregates(depending on the san vendor). What that means is that the disk groups are collections of hard drives, and they are grouped by speed(10 / 15 k RPM) / size(146GB/ 300GB / 450GB/etc.) / technology(SAS/FAS/ISCSI/etc.). Based on those groupings the san folks usually offer different tiers of storage(tier 1 – really fast & $$, tier 2 – fast & $, tier 3 – archiving storage, etc.(we use SATA))
The luns are presented to the OS and are carved out of these groups/aggregates.
If you are really lucky and have a lot of money, the san people will separate the groups by I/O if you have enough spindles(disks). Again, depending on your san, and the amount of spindles in the group, it may not make sense. Some san vendors say not to bother separating I/O if you have less than 30 spindles. In other words, don't bother creating groups for data files / log files /temp db if you have less than 30 disks because even though the I/O is different, the benefit of the spindles outweighs the separation. tempdb is the same random read write as data files, just hit a lot more(as you know).
If you have multiple SQL boxes connected to your san, chances are they are already sharing the storage. Of course remember to align partitions pre-2008 & verify post 2008, unless you are using gpt partitions – still working on this one myself.
There's more to go into with the RAID ability of the SAN as well, but that's a different conversation, talk with your san person – a lot of times I have seen they are configured for storage benefit, not performance(because performance is synonymous with san....).
And then there are other SANs like the IBM XIV that use SATA disks and still perform amazingly fast due to the sindle count(and other factors I'm sure) .
Hope this helps
chris
February 22, 2011 at 2:51 pm
As with most things...it depends. Performance absolutely CAN be affected by sharing a SAN across multiple SQL instances. There are many different types of SANs...some allow you to isolate your spindles so only one instance may write to them, however others abstract the idea of spindles using disk virtualization meaning all instances will share all available physical spindles in an attempt to improve IOPs.
No matter the SAN spindle configuration you will likely be sharing an interface to the SAN, either directly (network) or indirectly (SAN disk access channels) or both. In a shared scenario performance can be affected by a large SAN operation initiated by one or more SQL instances where the disks would be able to keep up however the interface to the disks becomes a bottleneck.
The best advice I can give you is to work with your SAN vendor and your dedicated "storage team" if you are fortunate enough to have one in your org.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 22, 2011 at 3:34 pm
Talk to the SAN guys for their opinion, but in general, let the SAN guys do their thing. I usually only get involved these days in their 'turf' if and when I can prove my problem is related to unreasonable Disk I/O. Under most circumstances, you won't see significant issues unless you have a large throughput.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 22, 2011 at 5:44 pm
We share SAN drives (aggregate) for log and data. So, server1, server2 and server3 all have their data LUN's pointing to the same physical spindles. Same for log, where we have a dedicated aggregate for log LUNs only. The key thing to watch for on the SAN is latency. ms/read and ms/write. Keep an eye on what SQL Server is reporting on the file stats dmv also.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply