SQL 2005 on a VM
-
Hi All
I was hoping someone could help. I am currently speccing for a sql server 2005 data warehousing environment on a VM. My knowledge of VMs is limited
but I know what I would want to do outside of this. Can someone advise me on how I can achieve the following in a VM:OS, programs and page file on a RAID1
Data RAID 1
Logs on own RAID1
Do I just create multiple virtual hard disks, are there any considerations I am missing?
I will not be configuring the VM but would certainly like to know what I am talking about when I have the converation with the server administrator!
Thanks
KT
-
I would want to do some serious benchmarking before putting any production SQL Server box on VM. Microsoft still has a long way to go with virtualisation, and still has performance issues that were fixed 25 years ago on mainframes.
The big bottlenecks with virtualisation on Windows are memory and I-O. The main things you need to get SQL Server working fast are good memory and I-O response, so you can see the conflict...
From what I can see, the virtual server memory space still has to be mapped to real memory by the host server, which puts a limit on performance. It is likely that Microsoft will need to use a similar system to IBM's memory pass-through, which allows a guest to directly address portions of host system memory, to overcome this issue. (I wonder how much a patent licence for this would cost...)
I-O also gets mapped from the guest to the host, which slows things down a lot. Even if you use 'raw' disks with the guest server, you still have up to a 50% performance penality. SQL Server has its own I-O scheduler, and if it detects slower than expected I-O responce from Windows, it will slow down the number of I-Os it issues. I think the idea of this is to avoid excessive I-O queue chains. The end result is that SQL can emphasise the slower I-O response of virtual machines. Again, the solution is to allow direct I-O to the real (raw) disks the guest is connected to (Direct I-O to virtual disks is a contradiction in terms).
Although using VM for non-production SQL Server should be fine, think of it as a proof of functionality, not a testbed for performance. Having said all this, I have heard of some shops running SQL under VM and getting performance that meets their SLA, so you really need to test how this will work in your environment and workload.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
I agree. Virtualization is a cool topic, but it will be better in the future with Vista and other OS's of the future. Personally, I would not use VM in a production environment unless it was for a very light-weight app. It would sound to me that a DW app is not light-weight - unless it is just a proof of concept.
-
Absolutely, use virtual servers for QA environments or when you want to test how something works in a cluster and can't afford to buy 2 new servers to do it.
Virtual Servers are way too slow (right now) for anything that i've ever come across in a production environment.
-
Thanks all - all this stuff is really useful. At least now I can be more confident in recommending against the VM in a production environment and with good reason. Thanks again.
-
When I started at my current company, they had the ALL of the development and system test SQL servers setup on VM.
They were having connectivity issues. ...... but I noticed that the issues were not occuring on my local machine.....
So we reinstalled the SQL Servers on Seperate machines and ALL of our connectivity issues went away.
VM machines may be OK for many environments, but SQL Server installs are not one of them.
Good Luck
Eric
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply