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).
Best Answer
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..