November 21, 2008 at 8:01 am
Best practice is to split up data, log and backup files to different disks. This makes sense when you are using internal storage. This configuration gives you separate disk controllers and separate spindles for each which results in significantly reducing IO contention due to different workload characteristics.
It seems like things aren't so clear when you are using a SAN and your storage administrator is simply carving LUNs out of a large disk group. Chances are high that the SAN disks on your server are operating across the same physical sets of spindles. I imagine this is even more the case when your SAN is using something like virtual disk/vRaid technology.
I still break data, log and backup files onto separate disks. But I'm wondering at this point what that is actually getting me? It seems like the benefit on the physical IO side is somewhat reduced when using a SAN. What are the benefits on the SQL or OS side of keeping these on separate logical disks?
November 21, 2008 at 8:24 am
It seems like things aren't so clear when you are using a SAN and your storage administrator is simply carving LUNs out of a large disk group. Chances are high that the SAN disks on your server are operating across the same physical sets of spindles. I imagine this is even more the case when your SAN is using something like virtual disk/vRaid technology.
I would think if you are putting your files on a SAN it should not matter how it is managed. The SAN should intelligently migrate your files based on their usage stats...depending on what type you are using.
This question could always be better resolved by testing a SAN forcing your files onto separate physical disks / disk arrays versus just letting it manage where your files land on a LUN. Anyone got a SAN sandbox?
November 21, 2008 at 8:28 am
For critical production systems, I insist that the SAN administrator create dedicated RAID groups / LUNs for each of the purposes you describe (except maybe backups...). Not only does it make I/O performance issues easier to diagnose if the RAID groups aren't shared, but you then don't have the issue of potentially losing multiple volumes in the event that you lose more than one disk in a RAID group.
Maybe I'm just an old stick-in-the-mud, treating a SAN in much the same way I would local disk. I don't trust virtualization of production systems either for much the same reason.
November 21, 2008 at 8:28 am
You are correct in that you lose the benefit if the SAN admin is carving three partitions out of a LUN that is using the same physical drive array.
Of course, your initial assumption "should" be wrong. Your admin should be carving these out of individual physical disk arrays. They often really need different RAID configurations. Log files have lots of sequential writes, data files have lots of reads and non-sequential writes, and backups - well it is often best to put those on cheaper, slower drives so you can afford to retain a few days or weeks of them without breaking the bank.
The best place for information on how the SAN should be configured is your SAN vendor. MSSQL configurations are extremely common these days. They should have done enough of these to know what configurations will suit your needs - assuming you give them enough information. Every SAN solution seems to be a bit different, so talk to your vendor.
November 21, 2008 at 9:58 am
Michael Earl (11/21/2008)
You are correct in that you lose the benefit if the SAN admin is carving three partitions out of a LUN that is using the same physical drive array.Of course, your initial assumption "should" be wrong. Your admin should be carving these out of individual physical disk arrays. They often really need different RAID configurations. Log files have lots of sequential writes, data files have lots of reads and non-sequential writes, and backups - well it is often best to put those on cheaper, slower drives so you can afford to retain a few days or weeks of them without breaking the bank.
The best place for information on how the SAN should be configured is your SAN vendor. MSSQL configurations are extremely common these days. They should have done enough of these to know what configurations will suit your needs - assuming you give them enough information. Every SAN solution seems to be a bit different, so talk to your vendor.
I agree. That's how we did it at my last place, and will be doing here in a few months with our new data center. For instance, with 20 drives in the array, drives 1-4 can be drive E for logs, drives 5-8 can be drive F for data, drives 9-12 can be drive G for indexes, drive 13 & 14 can be drive H for backups etc .....
November 23, 2008 at 3:17 am
Michael Earl (11/21/2008)
Your admin should be carving these out of individual physical disk arrays. They often really need different RAID configurations.
couldnt agree more, does your SAN admin understand the RAID config required for SQL server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 23, 2008 at 7:42 am
Yes and no 😉
I've seen this happen in a few different places. Generally, the storage admins I've worked with have been top notch. They understand best practice for SQL when you are talking about normal RAID arrays. The issue starts to get more complicated when you factor in new storage technologies and the best way to share storage across the IT though.
As I mentioned, with things like virtual disk/vRaid technologies, this best practice is no longer so clear cut. While the vendor may recommend this or that strategy it has to be balanced against the fact that other servers in IT such as Exchange, ESX, clusters, DB's are going to share that storage. Throw in tight budgets and concerns about overall best use of capacity and you have an array of different competing factors that must be worked out.
November 23, 2008 at 9:46 am
Bear in mind that a san doesn't automatically make things fast. To quote a friend, 'There's no magic san dust'
Even with a san (or especially with a san) you need to follow IO best practices. Appropriate raid levels, dedicated disk arrays, aligned partitions, etc.
The MS best practice for sans is to never share disks between disparate workloads. ie, don't slice a LUN and give it to the data files and slice another from the same disks and give it to the log files
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2008 at 8:36 am
Even if you don't, as others recommend, have 'control' or even input as to the configuration of the SAN for your SQL Server storage needs, you probably still should create separate drives on the various LUNs you are provisioned. There is the possiblity that those LUNS ARE on separate disks, controllers, switches, etc which will provide benefits and reduce possible performance choke points. If none of the above is true, at least it shouldn't cause things to be markedly worse. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 24, 2008 at 9:06 am
Gotta agree with just about everyone here. Treat the physical disks in the SAN the same as you would internal disks, when it comes to SQL performance and disaster prevention. The only SQL thing I'd put on a SAN that wasn't handled that way, would be backup files.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 24, 2008 at 10:44 am
GilaMonster (11/23/2008)
don't slice a LUN and give it to the data files and slice another from the same disks and give it to the log files
this is very important, if you do this you may as well have sql server store all its files on the same logical drive
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 26, 2008 at 12:34 am
Hi Guru's,
Is there any whitepaper or document released by MS which will help SQL DBA's to understand the best practice for which RAID to choose, what are new technologies in RAID, SAN. How to find, troublshoot etc...
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
November 26, 2008 at 7:45 am
Sure. Tonnes
Try these for starters:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2010 at 11:17 am
Having this very same "discussion" with my SAN Admin. Everything has to be RAID 5. The SAN is an EMC NS480. The system has 73 450GB disks 4Gb/s and they are 15k RPM. My project has a requirement for over 5 TB.
Here's my question to my SAN Admin (similar to an earlier post) -
"....will you be carving out LUNs for the database files (Data, Log, TempDB, backups) from the same group of disks?"
And the answer was -
Yes it will plus be shared by other systems like VM and other Windows/Linux systems. As I stated on the phone if you want to be on your on disk this should either be local attached storage or disk that everyone agrees is to be only for your project.
Comments anyone?
Thanks!
KU
September 28, 2010 at 12:02 pm
Please post new questions in a new thread. Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply