2

We are planning to deploy an eCommerce Solution using three distinct databases, all to be hosted in MS SQL Server 2008 R2. The whole stack will be hosted in a VMWare environment (two physical machines with a parallel setup of VMs).

The question I have is whether to just host the three databases in one instance of SQL Server or to separate them into multiple VMs. I can see some added costs as a disadvantage of multiple VMs (more license fees, multiple OS copies in memory), I can see better separation as an advantage of multiple VMs (CPU, IO, security).

Since the vendor is the same across the databases, my main question is how much more control do I gain over CPU/IO allocation. In particular: would putting all three databases onto one VM create a potential of bad queries against one DB taking performance down on all three?

EDIT in reply to DaveH/Thirster42:

The stack is intended to be duplicated on two physical machines, using clustering between the DBs. IO is a potential bottleneck, although we intend to buy the highest performance SAN space the hosting company has to offer. Load will vary between the different databases, and since it is a new system it is still hard to guess. We are talking the CMS database, the MS Commerce Server DB and a customer sales tracking database for a fast food ordering site doing about 3 million page views/month. We expect upsell/cross-sell analysis to be used extensively and some complex customer analysis queries on the customer database (that's the scary one).

2
  • Can you provide more information around things like the specs of the physical machines and how much traffic they will expect to be seeing? Are you running a parallel setup of VM's for fail over? Clustering? Log shipping / Mirroring? What is your plan here. Sep 14, 2010 at 2:31
  • We actually made our decision, but based on a different angle compared to the question: we want to separate the DBs so we can set up the opposite active/passive configuration during normal operations, thus utilizing the physical machines better. The complex queries will then run on a different physical machine. In case of a failure we can always stop any ad-hoc querying against the customer data since that is not mission critical. That should allow staying productive with reasonable performance if one machine is down. Sep 19, 2010 at 22:52

2 Answers 2

2

I honestly couldn't foresee much of a performance gain from having three seperate vm's, mainly for the fact that i'm guessing they'll be on the same host, and therefore will still be fighting over disk i/o. Also, with seperate vm's any queries that go across databases will have to hit the other vms. Seperate vm's also means more overhead. Each vm means more overhead on the host, and more overhead for each seperate os. These are all resources that you otherwise could allocate to one vm..

0

It depends on the utilization, and how much you will need to scale. Vertical scaling (adding CPU and memory) in VMware only goes so far. Multiple copies of the O/S in memory don't necessarily apply because VMware will share memory between the VMs.

[side note]: http links aren't working for some reason, so here is the link to a small description about memory sharing: http://www.cdhtalkstech.com/2008/11/03/memory-sharing-–-vmware-has-it-and-the-others-don’t-part-1/

I'm curious why you are deploying a SQL cluster across two ESX hosts? Do you have VirtualCenter? So, to sum it up, running 3 DBs on a single VM is good until you need to scale. I would then consider splitting them out, and possibly put my ESX hosts in a cluster, and enable HA, DRS, and maybe fault tolerance. :D

Good luck!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .