Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

VM SQL Server data/log location Expand / Collapse
Author
Message
Posted Tuesday, February 7, 2012 12:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:56 PM
Points: 3, Visits: 189
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,
Post #1248411
Posted Friday, March 30, 2012 1:56 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
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.
Post #1276015
Posted Tuesday, July 10, 2012 3:32 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:06 AM
Points: 961, Visits: 4,976
(Yeah, almost a zombie topic, but I just found it...)

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!
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
Post #1327950
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse