November 12, 2010 at 3:46 pm
Does using a SAN for SQL Server data and log files invalidate the "Best Practice" of having .mdf and .ldf files on separate storage arrays? I would be interested in hearing from everyone who knows if there is a new "Best Practice" concerning SQL .mdf and .ldf files & SAN. Thanks.
November 12, 2010 at 3:51 pm
michaelkinlb (11/12/2010)
Does using a SAN for SQL Server data and log files invalidate the "Best Practice" of having .mdf and .ldf files on separate storage arrays? I would be interested in hearing from everyone who knows if there is a new "Best Practice" concerning SQL .mdf and .ldf files & SAN. Thanks.
There isn't a change in the best practice. Separate physical spindles are still the most optimal setup for SQL Server. However, under normal usage, a SAN is 'good enough'. SAN engineers tend to not want to do a proper SQL Setup, and I don't do their jobs well enough to understand why they're usually against it besides the cost of RAID Controllers and rack space usage.
By preference, you want to put the tempdb, swap file, mdf files, and the ldfs on separate physical arrays. Best practice would be to have *1* ldf file per physical array, to speed the linear writing. You're rarely going to get that except on extreme systems that you need to do serious optimization on. Try to pre-grow your mdfs, too, so you can keep physical fragmentation to a minimum. Aim for about 2 years expected growth when you do your initial setup. Rough it from there.
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
November 12, 2010 at 5:32 pm
Craig Farrell (11/12/2010)
SAN engineers tend to not want to do a proper SQL Setup, and I don't do their jobs well enough to understand why they're usually against it besides the cost of RAID Controllers and rack space usage.
To add to this, I went off and found my friendly local SAN admin when he wasn't busy (for once) and got myself a thousand foot view education on SAN building and methodology.
The short form is until you hit a few Terabytes worth of data, dedicated disks aren't worth the loss of multi-spindle speed, especially once you bring in the write cache'ing of the SAN controllers on the far side of the Fiber Switches. The drives are just too big to really make it worth the price unless you absolutely need it.
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
November 13, 2010 at 9:14 am
Just keep in mind to remind your san-admins to double check their LUNs to they use disk alignment !!
If they don't your db will pay the price during operations because disk heads will not be in sync and will require extra physical movements to I/O data.
For the rest I always advise to use the same common sense as you would use with DAS concerning the RAID-level to use with the requested LUNs.
(unless you have a very recent SAN which may scatter data over all spindles anyway)
For you own origanizational purposes, keep the same directives as you would use with a non-san-connected instance.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 13, 2010 at 9:41 am
ALZDBA (11/13/2010)
Just keep in mind to remind your san-admins to double check their LUNs to they use disk alignment !!
if you are using windows 2008 this is done automatically. Pre OS 2008 very important though.
---------------------------------------------------------------------
November 13, 2010 at 7:25 pm
The technical answer is: It depends...
It depends on what SAN you are using, how many actual spindles are defined for your LUN - how that is utilized on the SAN and how many other systems are using the same SAN and possibly the same spindles.
On large scale enterprise SAN's - you could have your LUN's spread across 100's of spindles. If that is the case, it probably won't make any difference if all of your LUN's are presented from the same set of spindles.
With that said, for any large system I work with (100GB up to 1TB+), I always have at least 5 LUNs presented to my server. One for system databases, one for data files, one for log files, one for tempdb and the last one for backups. If needed, I will have additional LUNs presented for data files and/or log files to isolate IO for those databases. Most of the time, I don't need that though.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply