THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

SQL Server Virtualization

I just read the SQL Server Performance in a VMware Infrastructure 3 Environment performance study published by VMware (thanks to virtualization.info for the link).

Often I've been asked if it is a good move having a SQL Server in production in a virtual machine. As every consultant, my first answer is "it depends". And I really think this, it's not only a way to avoid the question!

If we look at an OLTP application, then my personal answer is that it could be a good move, especially if my hardware is so much larger than the one necessary to handle the daily workload. Numbers offered by VMware performance study are pretty good, considering that they are working on a reasonably hardware (not a monster one) that you can find in the real world. However, this paper simulates an OLTP scenario and not a Data Warehouse one. One of the signs for this assumption is the presence of a RAID 5 data storage, which could be a real bottleneck for ETL activities (and this is a good topic for another post in the future - I will not discuss about it here).

Until now, I always prefer working on a real hardware for both production AND especially development/test environments for a BI solution, at least for the back-end part (SQL/SSIS/SSAS). The reason is based on economics: a minimum VM for this kind of activities requires at least 4 cores, better if 8 cores (as a minimum), and at least 8 GB of RAM. Especially during execution of SSIS packages and process of SSAS cubes, all resources (I/O, memory and CPUs) are stressed at their limits. While a VM could be handled in a production environment just to balance resources between daily operations and night batches (but it would be like having a server that is turned off during the day - sometimes you can afford it, sometimes not), I'm not comfortable in using a VM like this for development. The reason is that the workload affects all concurrent VMs and if you virtualize a server, it is supposed that you keep other servers on the same machine.

At the end, until now I've found more affordable having dedicated servers for development instead of using virtualized ones. I know that this situation could change in the future. Virtualization technology is fast evolving and (more important) hardware cost for multiple cores and large-RAM-provided servers will be so much cheaper that the "entry-level" server in a data center will be too powerful to be used at the maximum of their capacity many data warehouse installations.

What is your experience here? Do you have a development environment for your data warehouse completely virtualized or not? What do you think about it? I'd like to get feedback on this.

Published Sunday, November 18, 2007 11:08 AM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

willgart said:

I agree, I prefer to have a complete non virtual server for my dev. environment.

The main reason: I want all the resources for the task I'm doing.

a week I'm focusing on SSIS, the next week on SSAS...

I don't want to loose resources by sharing something with other virtual machines.

Also SQL Server is designed to balanced the memory usage regarding the usage of the server, so during an SSAS process, SSAS consume more memory and SQL Server release unused memory, this is not possible between virtual machines.

With Windows 2008, where we can extend the memory and the CPUs on the fly in a virtual machine (without stopping the VM), maybe the dev. box can be a virtual server if you have a datacenter. you can create a pool of servers for the dev. team with a good scheduling for the ressources. "Tomorrow I'll work on cubes, so I need CPU for that day".

And the advantage of VM servers is the ability to separate your projects and your test/dev environment without adding physical servers. 1 day you work on a project, you start the environment for this project and you stop it when you no longer need it! Also you can move a dev. server to QA or test server just by copying it. so you reduce the setup time.

Thanks Marco to share this document.

There is a big lack in the test: comparing the same box without any virtualization!!! to have a better idea of what the hardware can do with SQL Server. and see the virtualization impact.

and another test not made in the document, a virtual env. ok... why you test it with only 1 VM running? there is no advantage for this. you have to compare 1 VM versus 2VM with SQL Server and test 1 SQL VM when another VM running a web based application, which is more a real world scenario.

Finally I will be more interested by the Dryad project...

http://research.microsoft.com/research/sv/dryad/

November 18, 2007 8:31 AM
 

SQLBI - Marco Russo said:

A few months ago I talked about SQL Server Virtualization . For me, taking a look at virtualization improvements

August 1, 2008 10:28 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement