0

I've got a an ESX VM and the storage is provided by a decent SAN. On a physical server, I would ordinary install SQL Server across 3 drives; one for the OS + SQL, one for data, and a third for log files. It's a fairly common approach that offers good performance.

However, is there any benefit to this approach when installed on a SAN? If I were to create 3 virtual disks to mirror the physical server approach, I am not going to yield any extra performance because all 3 disks would be drawn from the same SAN, surely?

So what is the best configuration for SQL Server on a VM/SAN?

Improve this question
2
  • 1
    Re: Your comment on Chopper3's answer. Tell them you want a new SAN if they are giving your logfiles storage on a RAID6 array wich also hosts the data disk... – pauska Feb 18 '11 at 11:58
  • @Pauska: This SQL server will get clustered at some point I expect, prior to going live. However, sometimes you have to make do with what you have available... You don't always get to have all the bells & whistles that you would like. – CJM Feb 18 '11 at 12:44
3

It depends on how the SAN is setup.

For instance the SAN could be presenting the LUNs/datastores from different disk groups with different performance characteristics and RAID levels. For instance you could have your OS on a R5 10krpm disk-group/LUN/datastore, your Data on a R10 15krpm datastore and your Logs on an SSD-based one.

If you only have one disk-group, LUN and/or datastore then there's unlikely to be any significant performance benefit between the two models but I would still suggest splitting them anyway simply because OS, Data and Logs shouldn't be on the same drive/volume anyway - even if this means being on the same disk-group.

If you can you should do your best to keep at least the Logs on a different disk-group/LUN/datastore for recovery purposes. You could also consider keeping your backups on a different, slower, one too.

In fact re-reading that there's actually no difference between a virtualised and physical MSSQL box in fact.

Improve this answer
4
  • AFAIK, there is one SAN appliance with 12 disks in RAID 6 with a hot spare. To each virtual disk will be hosted in the same place and offer the same performance. Out of interest, why must we still split OS/Data/Logs even if there is no performance benefit? Prepared to accept your word on it, but curious to understand the issues. Would have though that the resilience implications would be the same if all on one store. – CJM Feb 18 '11 at 11:50
  • Ok, well with that san spec there won't be any performance benefit no. The reason for splitting out the disks is that you should always keep your data away from your OS (they have very different IO profiles) and you want your logs away from your data so that you can use them to recover if you lost your data drive. It's an EXTREMELY common model - in fact I'd say keeping all three on one volume would almost be negligent. – Chopper3 Feb 18 '11 at 12:01
  • I understand it's a common model - I used it on all previous physical servers. However, if this SAN is compromised such that we lose data, we will also have lost the log files, surely? They are all on the same bunch of disks. I guess it helps if someone/something somehow manages to wipe one drive within the virtual OS... Don't get me wrong, I'm planning on maintaining this approach - I'm just trying to be realistic about its benefits. – CJM Feb 18 '11 at 12:42
  • Again it depends if all LUNs are being presented from one disk group - I suspect they are but what if someone deleted a single datastore, or it was corrupted, wouldn't you like to have your logs to hand? The only benefits are potential ones. – Chopper3 Feb 18 '11 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.