February 7, 2012 at 12:07 pm
To preface this question, I should give a quick overview of my current situation.
I have recently taken over the DBA position in place of our previous DBA/Lead Developer (bad idea that everyone knows now). The current setup that I've walked in to is roughly 6 virtual SQL servers spread between three different Dell physical servers. The physical servers each contain a handful of virtual servers, most of which are low impact application servers. The virtual SQL servers are primarily SQL 2008 (half are R2; the others are currently just 2008, with a single 2005 box currently being removed.) Each virtual server that SQL is installed on is completely dedicated to SQL -- there are no other applications installed, just what is required for SQL to operate. However, with each virtual server our previous DBA asked to four virtual drives setup -- primary for OS, 2nd for data files, 3rd for log files and 4th for tempdb.
This brings me to my current situation and question I'd like to ask the experienced minds here. I understand the benefits behind mapping a specific SQL data source (data files, tempdb, trans logs) to their own physical hard drive, in an ideal environment. However, in our environment with each physical server that houses a virtual SQL server (or two), it doesn't seem that this would be a benefit. And just to clarify, currently there are no major performance bottlenecks that we have experienced. The reason for such a large variety of virtual SQL servers is due to the large variety of in-house custom programs, 3rd party programs related to my agency, SharePoint ECM and other various applications -- at least that is why they set things up this way. Would it make any major performance impact to only have 1-2 virtual drives per SQL server to handle the various data/log/tempdb files vs the current setup? What do you all think?
I am hoping and planning to learn more about the logistics and best practices of using VM's for SQL server, but I was hoping to get your educated opinions on it based on what I've said above.
Thanks in advance,
March 30, 2012 at 1:56 pm
pharrell (2/7/2012)
To preface this question, I should give a quick overview of my current situation.I have recently taken over the DBA position in place of our previous DBA/Lead Developer (bad idea that everyone knows now). The current setup that I've walked in to is roughly 6 virtual SQL servers spread between three different Dell physical servers. The physical servers each contain a handful of virtual servers, most of which are low impact application servers. The virtual SQL servers are primarily SQL 2008 (half are R2; the others are currently just 2008, with a single 2005 box currently being removed.) Each virtual server that SQL is installed on is completely dedicated to SQL -- there are no other applications installed, just what is required for SQL to operate. However, with each virtual server our previous DBA asked to four virtual drives setup -- primary for OS, 2nd for data files, 3rd for log files and 4th for tempdb.
This brings me to my current situation and question I'd like to ask the experienced minds here. I understand the benefits behind mapping a specific SQL data source (data files, tempdb, trans logs) to their own physical hard drive, in an ideal environment. However, in our environment with each physical server that houses a virtual SQL server (or two), it doesn't seem that this would be a benefit. And just to clarify, currently there are no major performance bottlenecks that we have experienced. The reason for such a large variety of virtual SQL servers is due to the large variety of in-house custom programs, 3rd party programs related to my agency, SharePoint ECM and other various applications -- at least that is why they set things up this way. Would it make any major performance impact to only have 1-2 virtual drives per SQL server to handle the various data/log/tempdb files vs the current setup? What do you all think?
I am hoping and planning to learn more about the logistics and best practices of using VM's for SQL server, but I was hoping to get your educated opinions on it based on what I've said above.
Thanks in advance,
I know this is an old question, but its an interesting topic and I was wondering if it was worth another look by folks. I remember reading a setup that each virtual drive that supported a virtual server could actually mirror the physical drive / lun layout, so that there _would_ be advantages. Anybody know if that could be set up that way? I know with just development boxes I could do that, but the suggestion for vmware server was rejected at a previous job as impractical. Seems like to me its recommended, some discussion here http://communities.vmware.com/message/1155436.
July 10, 2012 at 3:32 pm
(Yeah, almost a zombie topic, but I just found it...)
:Whistling:
Having done some work with MS Hyper-V Server, I'm going to chime in on this. I think what patrickmcginnis59 is thinking of is called "Pass-Through disk" in Hyper-V. Basically, the VM "virtual" disk is actually a physical disk (or a LUN on a SAN somewhere,) and will generally have the benefits of living on a physical disk. BUT (at least with Hyper-V, and especially in a Hyper-V cluster) this introduces problems with backing up the VM. Also, there was a blog posting by someone from MS a while back comparing the performance of a Pass-Through disk vs a Virtual Hard Disk (VHD,) which found the performance differences were slight.
Now, as for pharrells' question, I can see a couple possible reasons for doing it the way the previous guy did:
1. That's the way we do it with physical servers, that's the way we're going to do it with virtual servers! :hehe:
2. Depending on the backup solution, there may be a backup that is only backing up the data file and log file VHDs. Thus, if the physical server failed, you could deploy a new VM (with SQL installed,) restore and attach the two VHDs, do some cleanup, and go. If you're backing up all 4 VHDs, then you can even move the VM to a new physical server, restore your SQL backups to "catch up" and go.
Performance-wise, in a virtual environment I don't think this setup would give much performance boost.
That's my 2c...
Jason
November 13, 2014 at 9:18 am
Would it make any major performance impact to only have 1-2 virtual drives per SQL server to handle the various data/log/tempdb files vs the current setup? What do you all think?
I will assume you are running from a SAN. So depending of your SAN and LUN configuration, that will depend.
See... if you run on top of an automated tier system, it does not matter (performance wise). At hardware level, the SAN's software will be moving data from one tier to another one depending of usage.
Now, admin side of view, it does matter. Even though we have an automated tier system at work, I still asked and cofigured my systems with several LUNs and a mount point for each LUN: one for data files, one for tempdb, one for LUN. This has a huge benefit in terms of administration as mdf, ldf won't be mixed so easier to run scripts, etc.
The 2nd reason why it does matter and this is VMware side, is vMotion. It is my understanding that if you're having issues with a particular drive, you can vMotion the vmdk files underneath and migrate to a different LUN. That's transparent to the users and can be done live. So if you have mixed everything on same LUN and you are having issue with tempdb drive only, you won't be able to take advantage of that.
So yes... setup logical drives on Windows as you usually do on a bare metal machine. Depending of your SAN and RAID layout, it may not improve performance but will help administration.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply